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