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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
