| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 9i Slow SQL Performance - EXPERTS apply within
I did the level 12 trace and got the following results. Time seems to
be in the fetch statement. I guess Optimising the query is the only way
ahead then.
TKPROF: Release 9.2.0.7.0 - Production on Wed Apr 5 15:52:46 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: 3624.txt
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 events '10046 trace name context forever, level 12'
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
Optimizer goal: CHOOSE
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
select a.COMPANY_ID, a.SHIP_ID, a.VOYAGE_NO, a.FIXTURE_ID, a.STARTDATE,
a.ENDDATE, a.ACCT_COMPANY_ID, a.MIN_VOYAGE_NO, a.MAX_VOYAGE_NO from
chops_web.va_voyage_list a,
chops_web.ch_fixture_summary b, chops_web.ma_company c where
a.company_id = 2 and
a.enddate >= to_date('01/01/2006 12:00:00 AM','mm/dd/yyyy hh:mi:ss
am')
and a.ship_id not in (select field_text_value from
chops_web.va_config_info
where company_id = 2 and field_id like 'EXCLUDE_SHIP%') and
a.company_id = b.company_id and a.fixture_id = b.fixture_id and a.company_id = c.company_id and ( b.exclude_from_pool = 0 or c.pool_flag
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 3 36.82 36.05 0 164592 0
30
total 5 36.82 36.06 0 164592 0
30
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
30 FILTER
443468 NESTED LOOPS
116 NESTED LOOPS
116 FILTER
8042 NESTED LOOPS OUTER
2736 NESTED LOOPS OUTER
2736 NESTED LOOPS
8626 HASH JOIN
8636 HASH JOIN
9257 HASH JOIN
264 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID OBJ#(37324)
1 INDEX UNIQUE SCAN OBJ#(37325) (object id 37325)
264 INDEX FULL SCAN OBJ#(37381) (object id 37381)
9257 INDEX FAST FULL SCAN OBJ#(37515) (object id 37515)
317 INDEX FAST FULL SCAN OBJ#(37392) (object id 37392)
9303 TABLE ACCESS FULL OBJ#(37520)
2736 TABLE ACCESS BY INDEX ROWID OBJ#(37215)
8616 INDEX UNIQUE SCAN OBJ#(37216) (object id 37216)
2736 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
2736 INDEX UNIQUE SCAN OBJ#(37496) (object id 37496)
8038 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
8038 AND-EQUAL
65431 INDEX RANGE SCAN OBJ#(37495) (object id 37495)
61193 INDEX RANGE SCAN OBJ#(37494) (object id 37494)
116 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
116 INDEX RANGE SCAN OBJ#(37496) (object id 37496)
443468 TABLE ACCESS FULL OBJ#(37215)
0 TABLE ACCESS FULL OBJ#(37573)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
0 BITMAP CONVERSION TO ROWIDS
0 BITMAP AND
0 BITMAP CONVERSION FROM ROWIDS
0 INDEX RANGE SCAN OBJ#(37494) (object id 37494)
0 BITMAP CONVERSION FROM ROWIDS
0 INDEX RANGE SCAN OBJ#(37495) (object id 37495)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 3 36.82 36.05 0 164592 0
30
total 6 36.82 36.06 0 164592 0
30
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0
0
Execute 0 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
total 0 0.00 0.00 0 0 0
0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
91 lines in trace file.
Received on Wed Apr 05 2006 - 10:21:09 CDT
 |
 |