Thanks ,
That's what I guessed . But was feeling lazy to do 10046 tracing .
N E way thanks for the info .
-ak
- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, April 03, 2003 5:40 PM
> Ak,
>
> I think what you're seeing is the result of the parse not necessarily
> what ends up happening.
> I ran a similar circumstance with a 10046 trace on and it reports no
> physical reads, no logical reads, and returns 1 row. The "explain plan"
> still shows a full table scan of my table but the trace doesn't report
> any rows or blocks read from that table.
> It's probably also worth noting that 100% of my waits were sqlnet
> message to / from client.
> Also, I ran this test because I didn't know the answer and thought it a
> very good question.
>
> The table is one I created called crazy_table as select * from
> user_objects.
> The query is
> select count(*) from crazy_table where 1=2;
>
> Here, I paste relevant sections of my trace output, but to see the
> whole picture, you might want to run some tests of your own:
>
>
> SUMMARY OF CALLS BY USER (INTERNAL LAST) AND NON-RECURSIVE/RECURSIVE
> ====================================================================
>
> OVERALL TOTALS PER CALL FOR ALL NON-RECURSIVE STATEMENTS FOR USER 44
> (ARTHUR)
>
> call count cpu elapsed disk query
> current rows misses
> ------- --------- --------- --------- ------------ ------------
> ------------ ------------ ---------
> Parse 2 0.00 0.01 0 0
> 0 0 1
> Execute 3 0.00 0.00 0 0
> 0 1 0
> Fetch 2 0.00 0.00 0 0
> 0 1 0
> ------- --------- --------- --------- ------------ ------------
> ------------ ------------ ---------
> total 7 0.00 0.01 0 0
> 0 2 1
>
>
>
>
> SUMMARY OF CALLS BY COMMAND TYPE, USER (INTERNAL LAST) AND
> NON-RECURSIVE/RECURSIVE
>
>
> OVERALL TOTALS PER COMMAND TYPE FOR ALL NON-RECURSIVE STATEMENTS FOR
> USER 44 (ARTHUR)
>
> command type count cpu elapsed disk
> query current rows misses
> ----------------------- --------- --------- --------- ------------
> ------------ ------------ ------------ ---------
> select................. 4 0.00 0.01 0
> 0 0 1 1
> alter session.......... 1 0.00 0.00 0
> 0 0 0 0
> pl/sql execute......... 2 0.00 0.00 0
> 0 0 1 0
> ----------------------- --------- --------- --------- ------------
> ------------ ------------ ------------ ---------
> total.................. 7 0.00 0.01 0
> 0 0 2 1
>
>
> SUMMARY OF PHYSICAL READS, LOGICAL READS, ROWS AND MISSES PER CURSOR
>
> cursor user
> id id command type disk query
> current rows misses
> ------ ---- ----------------------- ------------ ------------
> ------------ ------------ ---------
> 1..... 44.. alter session.......... 0 0
> 0 0 0
> 2..... 44.. select................. 0 0
> 0 1 1
> 3..... 44.. pl/sql execute......... 0 0
> 0 1 0
> ------ ---- ----------------------- ------------ ------------
> ------------ ------------ ---------
> total. .... ....................... 0 0
> 0 2 1
>
>
> CURSOR_ID:2 LENGTH:43 ADDRESS:abb65150 HASH_VALUE:427590100
> OPTIMIZER_GOAL:CHOOSE USER_ID:44 (ARTHUR)
>
> select count(*) from crazy_table where 1=2
>
> call count cpu elapsed disk query
> current rows misses
> ------- --------- --------- --------- ------------ ------------
> ------------ ------------ ---------
> Parse 1 0.00 0.01 0 0
> 0 0 1
> Execute 1 0.00 0.00 0 0
> 0 0 0
> Fetch 2 0.00 0.00 0 0
> 0 1 0
> ------- --------- --------- --------- ------------ ------------
> ------------ ------------ ---------
> total 4 0.00 0.01 0 0
> 0 1 1
>
> | Rows Row Source Operation
> | ------------ ---------------------------------------------------
> | 1 SORT AGGREGATE
> | 0 .FILTER
> | 0 ..TABLE ACCESS FULL CRAZY_TABLE
>
> Explain Plan
> ---------------------------------------------------------------
> ...4 SELECT STATEMENT
> ...3 .SORT (AGGREGATE)
> ...2 ..FILTER
> ...1 ...TABLE ACCESS (FULL) OF 'ARTHUR.CRAZY_TABLE'
>
> OWNER.TABLE_NAME
> ...owner.index_name num rows
> blocks sample last analyzed date
> ----------------------------------------------------- ----------
> ---------- ---------- -------------------
> ARTHUR.CRAZY_TABLE...................................
>
> Event
> Times Count Max. Total Blocks
> waited on
> Waited Zero Time Wait Waited Accessed
> -----------------------------------------------------------------
> --------- --------- --------- --------- ---------
> SQL*Net message from client (idle)...............................
> 2 2 0.00 0.00
> SQL*Net message to client (idle).................................
> 2 2 0.00 0.00
> -----------------------------------------------------------------
> --------- --------- --------- --------- ---------
> total............................................................
> 4 4 0.00 0.00 0
>
> non-idle waits...................................................
> 0 0 0.00 0.00 0
> idle waits.......................................................
> 4 4 0.00 0.00
>
>
>
>
>
>
>
> >>> oramagic_at_hotmail.com 04/03/03 05:58PM >>>
> If I execute a query like ,
> select count(*) from some_table where 1=2 .
> why does oracle bothers to look at tables . Since there is only one
> condition which is false .
>
> If I write a sql like
>
> select count(*)
> from some_table
> where column_a= :a
> and a is not null;
>
> it should not even go to look at table , but explain plan suggests that
> it does a index /table scan .
>
> -ak
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Darrell Landrum
> INET: DLANDRUM_at_zalecorp.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: AK
INET: oramagic_at_hotmail.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Apr 04 2003 - 09:54:08 CST
