Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: What's wrong with this query

RE: What's wrong with this query

From: Anne Yu <AYu_at_sos.state.tx.us>
Date: Tue, 11 Sep 2001 07:20:24 -0700
Message-ID: <F001.00389837.20010911055031@fatcity.com> 






Thanks Thomas, Lisa,     Attached is the  TKPROF EXPLAIN PLAN for this
query.    Please review.



Many thanks,




TKPROF: Release 8.0.6.3.0 - Production on Tue Sep 11 07:38:26 2001



(c) Copyright 1999 Oracle Corporation.  All rights reserved.



Trace file: texasprd_ora_24718.trc


Sort options: default






count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************







alter session set sql_trace = true




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        0      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        0      0.00       0.00          0          0          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        1      0.00       0.00          0          0          0
0



Misses in library cache during parse: 0


Misses in library cache during execute: 1
Optimizer goal: CHOOSE


Parsing user id: 111  







BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        4      0.00       0.00          0          0          0
0


Execute      4      0.00       0.00          0          0          0
4


Fetch        0      0.00       0.00          0          0          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        8      0.00       0.00          0          0          0
4



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: 111  







select /*+ INDEX(b)*/ distinct d.batch_number

  , d.document_number
  , d.entry_user_id
  , d.document_type_id
  , d.document_processed_date
  , b.batch_media_id, d.return_Method_Id


from submitter_batch b , document d


  , ucc_master_amendment m


where d.batch_number = b.batch_number

   and d.document_number = m.document_number
   and d.imaged = 0
   and b.batch_media_id = 4
   and d.document_status_id = 4

call     count       cpu    elapsed       disk      query    current


rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.00       0.00         32       3193          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.00       0.00         32       3193          0
0



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 111  



Rows     Row Source Operation

-------  ---------------------------------------------------
      0  SORT UNIQUE 
      0   NESTED LOOPS 
      1    NESTED LOOPS 
    147     TABLE ACCESS BY INDEX ROWID SUBMITTER_BATCH 
    148      INDEX RANGE SCAN (object id 10416)
   2722     TABLE ACCESS BY INDEX ROWID DOCUMENT 
   2869      INDEX RANGE SCAN (object id 10385)
      1    INDEX UNIQUE SCAN (object id 8484)

****************************************************************************







explain plan set statement_id='ANN_UCC_3'
for select /*+ INDEX(b)*/ distinct d.batch_number

  , d.document_number
  , d.entry_user_id
  , d.document_type_id
  , d.document_processed_date
  , b.batch_media_id, d.return_Method_Id


from submitter_batch b , document d


  , ucc_master_amendment m


where d.batch_number = b.batch_number

   and d.document_number = m.document_number
   and d.imaged = 0
   and b.batch_media_id = 4
   and d.document_status_id = 4

call     count       cpu    elapsed       disk      query    current


rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        0      0.00       0.00          0          0          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        2      0.00       0.00          0          0          0
0



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 111  



Rows     Row Source Operation

-------  ---------------------------------------------------
      0  SORT UNIQUE 
      0   NESTED LOOPS 
      0    NESTED LOOPS 
      0     TABLE ACCESS BY INDEX ROWID SUBMITTER_BATCH 
      0      INDEX RANGE SCAN (object id 10416)
      0     TABLE ACCESS BY INDEX ROWID DOCUMENT 
      0      INDEX RANGE SCAN (object id 10385)
      0    INDEX UNIQUE SCAN (object id 8484)

****************************************************************************






select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
  audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
  avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),


  nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0)
from


 tab$ where obj#=:1




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.00       0.00          2          3          0
1


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.00       0.00          2          3          0
1



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)







select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,
  i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,
  i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, 
  nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,i.pctthres$,i.indmethod#,
  i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0) 
from


 ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1)))



  deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=
  :1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and
i.bo#=


  :1




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.00       0.00          0          3          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.00       0.00          0          3          0
0



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)







select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
 


nvl(scale,-127/*MAXSB1MINAL*/),0),null$,offset,fixedstorage,nvl(deflength,0)
  ,default$,rowid,col#,property, charsetid, charsetform 
from


 col$ where obj#=:1




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch       24      0.00       0.00          0         26          0
23


------- ------  -------- ---------- ---------- ---------- ----------




total       26      0.00       0.00          0         26          0
23



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)







select con#,obj#,rcon#,enabled,nvl(defer,0) 
from


 cdef$ where robj#=:1




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.00       0.00          1          1          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.00       0.00          1          1          0
0



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)







select


con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
  rowid,cols,nvl(defer,0),mtime 


from


 cdef$ where obj#=:1




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.00       0.00          1          2          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.00       0.00          1          2          0
0



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)







select u.name,o.name 


from


 obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and 
  o.owner#=u.user# order by o.obj#




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.00       0.00          1          1          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.00       0.00          1          1          0
0



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)



Rows     Row Source Operation

-------  ---------------------------------------------------
      0  SORT ORDER BY 
      0   NESTED LOOPS 
      0    NESTED LOOPS 
      0     TABLE ACCESS BY INDEX ROWID TRIGGER$ 
      1      INDEX RANGE SCAN (object id 109)
      0     TABLE ACCESS BY INDEX ROWID OBJ$ 
      0      INDEX UNIQUE SCAN (object id 31)
      0    TABLE ACCESS CLUSTER USER$ 
      0     INDEX UNIQUE SCAN (object id 11)

****************************************************************************







insert into plan_table (statement_id, timestamp, operation, options,
  object_node, object_owner, object_name, object_instance, object_type,
  search_columns, id, parent_id, position, other,optimizer, cost,
cardinality,


   bytes, other_tag, partition_start, partition_stop, partition_id ) 
values


(:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,

  :20,:21)




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          1          0          1
0


Execute      9      0.00       0.00          1          1         17
9


Fetch        0      0.00       0.00          0          0          0
0


------- ------  -------- ---------- ---------- ---------- ----------




total       10      0.00       0.00          2          1         18
9



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 111     (recursive depth: 1)







select o.name, u.name 


from


 sys.obj$ o, sys.user$ u where obj# = :1and owner# = user#




call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      3      0.00       0.00          0          0          0
0


Fetch        3      0.00       0.00          5         15          0
3


------- ------  -------- ---------- ---------- ---------- ----------




total        7      0.00       0.00          5         15          0
3



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
      3  NESTED LOOPS 
      3   TABLE ACCESS BY INDEX ROWID OBJ$ 
      3    INDEX UNIQUE SCAN (object id 31)
      3   TABLE ACCESS CLUSTER USER$ 
      3    INDEX UNIQUE SCAN (object id 11)

****************************************************************************






select lpad(' ',2*(LEVEL-1))||
 operation || ' '||
 options || ' ' ||




 object_name || ' ' || optimizer "query plan"
 from plan_table where statement_id=upper('ANN_UCC_3')
 start with id=0


 connect by prior id=parent_id



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch    38302      0.00       0.00         15   15521542   15562533
574511


------- ------  -------- ---------- ---------- ---------- ----------




total    38304      0.00       0.00         15   15521542   15562533
574511



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 111  



Rows     Row Source Operation

-------  ---------------------------------------------------


5187510  FILTER 



5187510   CONNECT BY 


     88    TABLE ACCESS FULL PLAN_TABLE 
     10    TABLE ACCESS BY USER ROWID PLAN_TABLE 


456500880    TABLE ACCESS FULL PLAN_TABLE 












OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS


call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        7      0.00       0.00          0          0          0
0


Execute      8      0.00       0.00          0          0          0
4


Fetch    38303      0.00       0.00         47   15524735   15562533
574511


------- ------  -------- ---------- ---------- ---------- ----------




total    38318      0.00       0.00         47   15524735   15562533
574515



Misses in library cache during parse: 3


Misses in library cache during execute: 1




OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS


call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        8      0.00       0.00          1          0          1
0


Execute     18      0.00       0.00          1          1         17
9


Fetch       32      0.00       0.00         10         51          0
27


------- ------  -------- ---------- ---------- ---------- ----------




total       58      0.00       0.00         12         52         18
36



Misses in library cache during parse: 2



    9  user  SQL statements in session.


    7  internal SQL statements in session.
   16  SQL statements in session.






Trace file: texasprd_ora_24718.trc


Trace file compatibility: 7.03.02


Sort options: default


       1  session in tracefile.
       9  user  SQL statements in trace file.
       7  internal SQL statements in trace file.
      16  SQL statements in trace file.
      13  unique SQL statements in trace file.


   38531  lines in trace file.




-----Original Message-----


Sent: 10 September 2001 20:15


To: Multiple recipients of list ORACLE-L




Hey list,   Can anyone tell me what's wrong with this query?



many thanks,




select /*+ INDEX(b)*/ distinct d.batch_number

  , d.document_number
  , d.entry_user_id
  , d.document_type_id
  , d.document_processed_date
  , b.batch_media_id, d.return_Method_Id


from submitter_batch b , document d


  , ucc_master_amendment m


where d.batch_number = b.batch_number

   and d.document_number = m.document_number
   and d.imaged = 0
   and b.batch_media_id = 4
   and d.document_status_id = 4




      Submitter_batch -     97853 rows
      Document        -   8043272 rows (fk_d_batchnumber index on
batch_number)
      Ucc_master_adment   -    0 rows  (pk_uma_dn index on document_number)









Here is the explain plan:


explain plan                expected rows               object name    
____________________________________________________________________ 

select statement           164662478 
   sort (unique)                 164662478
   nested loops                 164662478
     nested loops                     2008079
       table access(full)                  46193              submitted
batch
          table access(by idx)          2008079              document
               index(range scan)              2008079
fk_d_batchnumber     non-unique
                 index(unique scan)                     82
pk_uma_dn             unique






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anne Yu
  INET: AYu_at_sos.state.tx.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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 Tue Sep 11 2001 - 09:20:24 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US