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 09:32:04 -0700
Message-ID: <F001.0038A0E1.20010911084024@fatcity.com> 






Thanks Lisa,      I have been a system dba from many years.   This is my
very first involvement with the pl/sql.    The application was created by an
Oracle consultant Co   I'm just monitoring the system and feel funny about
this query.    There is nothing I can change.    
 



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



Sent: Tuesday, September 11, 2001 10:35 AM
To: Multiple recipients of list ORACLE-L





Hi Anne, 



What's the name of your index you want the query to use?  Change your hint
to correct syntax.  /*+ index (b index_name) */



Do you absolutely have to have the distinct in there? The number of rows
after the unique sort hasn't changed.  Taking distinct out of there will
help.  



Try both suggestions and try to trace again.  Do you understand what it's
telling you?  If not, ask. 



Lisa Koivu 


Oracle Database Administrator 


Fairfield Resorts, Inc. 


954-935-4117 




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


Sent:   Tuesday, September 11, 2001 9:51 AM 
To:     Multiple recipients of list ORACLE-L 



        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
<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). 

-- 
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 - 11:32:04 CDT












Original text of this message













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