|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
April 22nd, 2008, 04:43 AM
|
|||
|
|||
|
Performance tuning of sql query
I got a trace file and I have executed TKprof.Previously the query is taking 45 mins for execution after adding hints to the query 3 mins was redusec for execution. But I want to improve the performance of the query more.So please give me any idea for this..if u need any information regarding this please let me know.
Thank you.
|
|
#2
April 22nd, 2008, 04:52 AM
|
||||
|
||||
|
Do you have fresh stats? Post the explain plan of that query.
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
#3
April 22nd, 2008, 06:10 AM
|
|||
|
|||
|
Quote:
Here I am posting the O/P of Tkprof. Code:
TKPROF: Release 9.2.0.4.0 - Production on Mon Apr 21 14:04:52 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: adqora3g_ora_230458_SQL_TRACE.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
********************************************************************************
The following statements encountered a error during parse:
ALTER SESSION SET EVENTS .10046 TRACE NAME CONTEXT FOREVER, LEVEL 8.
Error encountered: ORA-02246
--------------------------------------------------------------------------------
SELECT /*+
ordered
full(iem)
full(itl) use_hash(itl)
full(s) use_hash(s) swap_join_inputs(s)
no_merge(vcs)
use_hash(vcs) swap_join_inputs(vcs)
itl.loc store,
itl.item rms_sku,
vcs.case_size order_multiple,
itl.onsale_date onsale_date,
itl.offsale_date offsale_date,
iem.status status
FROM item_master iem,
item_loc itl,
store s,
v_case_size vcs
WHERE iem.pack_ind = 'N'
AND iem.item_level = iem.tran_level
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
AND iem.item = itl.item
AND itl.loc_type = 'S'
AND iem.item = vcs.item
AND vcs.primary_supp_ind = 'Y'
AND vcs.primary_country_ind = 'Y'
AND itl.loc=s.store
AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
Error encountered: ORA-00936
--------------------------------------------------------------------------------
select c.value || .\. || d.instance_name || ._ora_. || a.spid || ..trc. trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance d
Error encountered: ORA-00936
********************************************************************************
Alter session set SQL_Trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 14.96 14.96
********************************************************************************
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
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: 0
Optimizer goal: CHOOSE
Parsing user id: 315
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 274.54 383.25
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9 0.01 0.01 0 0 0 0
Fetch 9 0.00 0.00 0 19 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.01 0.01 0 19 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.01 0.00 0 0 0 0
Execute 8 0.00 0.01 0 0 0 0
Fetch 8 0.00 0.00 0 16 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.01 0.01 0 16 0 8
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=25 us)
********************************************************************************
select node,owner,name
from
syn$ 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 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select condition
from
cdef$ where rowid=:1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 292 0.00 0.00 0 0 0 0
Execute 292 0.02 0.01 0 0 0 0
Fetch 292 0.00 0.00 0 585 0 292
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 876 0.02 0.02 0 585 0 292
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 r=0 w=0 time=21 us)
********************************************************************************
SELECT /*+
ordered
full(iem)
full(itl) use_hash(itl)
full(s) use_hash(s) swap_join_inputs(s)
no_merge(vcs)
*/
itl.loc store,
itl.item rms_sku,
vcs.case_size order_multiple,
itl.onsale_date onsale_date,
itl.offsale_date offsale_date,
iem.status status
FROM item_master iem,
item_loc itl,
store s,
v_case_size vcs
WHERE iem.pack_ind = 'N'
AND iem.item_level = iem.tran_level
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
AND iem.item = itl.item
AND itl.loc_type = 'S'
AND iem.item = vcs.item
AND vcs.primary_supp_ind = 'Y'
AND vcs.primary_country_ind = 'Y'
AND itl.loc=s.store
AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.30 0.31 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 819704 936.22 2165.47 4806016 34044281 0 12295541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819706 936.52 2165.78 4806016 34044284 0 12295541
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Rows Row Source Operation
------- ---------------------------------------------------
12295541 HASH JOIN (cr=34044281 r=4806016 w=0 time=2139047518 us)
212315 VIEW (cr=7553336 r=10859 w=0 time=85039675 us)
212315 VIEW (cr=7553336 r=10859 w=0 time=84935492 us)
212315 SORT UNIQUE (cr=7553336 r=10859 w=0 time=84809086 us)
697081 HASH JOIN (cr=7553336 r=10859 w=0 time=80346870 us)
210609 HASH JOIN (cr=12485 r=9629 w=0 time=4530346 us)
210649 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=500 w=0 time=629484 us)
232549 HASH JOIN (cr=9152 r=9129 w=0 time=2975774 us)
210609 MERGE JOIN CARTESIAN (cr=6695 r=6675 w=0 time=1529249 us)
1 MERGE JOIN CARTESIAN (cr=30 r=13 w=0 time=121163 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=13 w=0 time=120964 us)
1 BUFFER SORT (cr=15 r=0 w=0 time=143 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=0 w=0 time=72 us)
210609 BUFFER SORT (cr=6665 r=6662 w=0 time=1322562 us)
210609 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=6662 w=0 time=1016023 us)
232590 TABLE ACCESS FULL ITEM_SUPPLIER (cr=2457 r=2454 w=0 time=557254 us)
1829931 VIEW (cr=7540851 r=1230 w=0 time=68727964 us)
1829931 UNION-ALL (cr=7540851 r=1230 w=0 time=66794426 us)
232590 INDEX FAST FULL SCAN PK_ITEM_SUPP_COUNTRY (cr=1956 r=370 w=0 time=1881373 us)(object id 27601)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=177110 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=188743 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=200532 us)
299857 VIEW (cr=9082 r=860 w=0 time=5321741 us)
299857 SORT UNIQUE (cr=9082 r=860 w=0 time=5166268 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=860 w=0 time=1598649 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4370777 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4212861 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=627394 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4406752 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4241925 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=646683 us)(object id 64316)
11847214 HASH JOIN (cr=26490945 r=4795157 w=0 time=2033417383 us)
769 TABLE ACCESS FULL STORE (cr=62 r=60 w=0 time=27019 us)
13033476 HASH JOIN (cr=26490883 r=4795097 w=0 time=2013607522 us)
15406 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=0 w=0 time=281867 us)
178209054 PARTITION HASH ALL PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=29907257715 us)
178209054 TABLE ACCESS FULL ITEM_LOC PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=25829497488 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 819705 0.00 0.40
db file sequential read 583673 4063.88 15999.28
db file scattered read 138180 4.50 819.63
latch free 17 0.01 0.02
SQL*Net message from client 819705 4179.10 14707.93
buffer busy waits 635 0.00 0.01
log buffer space 5 1.00 2.38
log file switch completion 10 1.00 1.98
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.03 4 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.03 4 9 0 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.03
********************************************************************************
select c.value || '\'|| d.instance_name || '_ora_' || a.spid ||'.trc' trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance d
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 16 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11607 1.78 1.76 0 0 0 174087
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11609 1.79 1.77 0 16 0 174087
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Rows Row Source Operation
------- ---------------------------------------------------
174087 NESTED LOOPS (cr=0 r=0 w=0 time=1207216 us)
6003 NESTED LOOPS (cr=0 r=0 w=0 time=68593 us)
261 MERGE JOIN (cr=0 r=0 w=0 time=12786 us)
846 SORT JOIN (cr=0 r=0 w=0 time=3497 us)
846 NESTED LOOPS (cr=0 r=0 w=0 time=1564 us)
1 NESTED LOOPS (cr=0 r=0 w=0 time=65 us)
1 NESTED LOOPS (cr=0 r=0 w=0 time=47 us)
1 FIXED TABLE FULL X$QUIESCE (cr=0 r=0 w=0 time=7 us)
1 FIXED TABLE FULL X$KVIT (cr=0 r=0 w=0 time=22 us)
1 FIXED TABLE FULL X$KSUXSINST (cr=0 r=0 w=0 time=17 us)
846 FIXED TABLE FULL X$KSPPCV (cr=0 r=0 w=0 time=1163 us)
261 FILTER (cr=0 r=0 w=0 time=8058 us)
846 SORT JOIN (cr=0 r=0 w=0 time=3710 us)
846 FIXED TABLE FULL X$KSPPI (cr=0 r=0 w=0 time=919 us)
6003 FIXED TABLE FULL X$KSUSE (cr=0 r=0 w=0 time=51946 us)
174087 FIXED TABLE FULL X$KSUPR (cr=0 r=0 w=0 time=1046576 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 11607 0.00 0.00
SQL*Net message from client 11607 6.65 14.82
SQL*Net more data to client 44 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.31 0.32 0 19 0 0
Execute 5 0.00 0.01 0 0 0 0
Fetch 831311 938.00 2167.23 4806016 34044281 0 12469628
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 831320 938.31 2167.57 4806016 34044300 0 12469628
Misses in library cache during parse: 3
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 831315 0.00 0.41
SQL*Net message from client 831315 4179.10 15120.98
SQL*Net break/reset to client 4 0.00 0.00
db file sequential read 583673 4063.88 15999.28
db file scattered read 138180 4.50 819.63
latch free 17 0.01 0.02
buffer busy waits 635 0.00 0.01
log buffer space 5 1.00 2.38
log file switch completion 10 1.00 1.98
SQL*Net more data to client 44 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 305 0.01 0.00 0 0 0 0
Execute 313 0.03 0.04 0 0 0 0
Fetch 313 0.00 0.03 4 632 0 305
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 931 0.04 0.08 4 632 0 305
Misses in library cache during parse: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.03
4 user SQL statements in session.
305 internal SQL statements in session.
309 SQL statements in session.
********************************************************************************
Trace file: adqora3g_ora_230458_SQL_TRACE.trc
Trace file compatibility: 9.00.01
Sort options: default
0 session in tracefile.
4 user SQL statements in trace file.
305 internal SQL statements in trace file.
309 SQL statements in trace file.
9 unique SQL statements in trace file.
3220466 lines in trace file.
Thank you
|
|
#5
April 22nd, 2008, 06:24 AM
|
|||
|
|||
|
Quote:
I think it's helpful.that's why i have posted Tkprof.here i'm posting Explain Plan Code:
TKPROF: Release 9.2.0.4.0 - Production on Mon Apr 21 14:04:52 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: g_ora_230458_SQL_TRACE.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
********************************************************************************
The following statements encountered a error during parse:
ALTER SESSION SET EVENTS .10046 TRACE NAME CONTEXT FOREVER, LEVEL 8.
Error encountered: ORA-02246
--------------------------------------------------------------------------------
SELECT /*+
ordered
full(iem)
full(itl) use_hash(itl)
full(s) use_hash(s) swap_join_inputs(s)
no_merge(vcs)
use_hash(vcs) swap_join_inputs(vcs)
itl.loc store,
itl.item rms_sku,
vcs.case_size order_multiple,
itl.onsale_date onsale_date,
itl.offsale_date offsale_date,
iem.status status
FROM item_master iem,
item_loc itl,
store s,
v_case_size vcs
WHERE iem.pack_ind = 'N'
AND iem.item_level = iem.tran_level
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
AND iem.item = itl.item
AND itl.loc_type = 'S'
AND iem.item = vcs.item
AND vcs.primary_supp_ind = 'Y'
AND vcs.primary_country_ind = 'Y'
AND itl.loc=s.store
AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
Error encountered: ORA-00936
--------------------------------------------------------------------------------
select c.value || .\. || d.instance_name || ._ora_. || a.spid || ..trc. trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance d
Error encountered: ORA-00936
********************************************************************************
Alter session set SQL_Trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 14.96 14.96
********************************************************************************
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
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: 0
Optimizer goal: CHOOSE
Parsing user id: 315
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 274.54 383.25
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9 0.01 0.01 0 0 0 0
Fetch 9 0.00 0.00 0 19 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.01 0.01 0 19 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.01 0.00 0 0 0 0
Execute 8 0.00 0.01 0 0 0 0
Fetch 8 0.00 0.00 0 16 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.01 0.01 0 16 0 8
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=25 us)
********************************************************************************
select node,owner,name
from
syn$ 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 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select condition
from
cdef$ where rowid=:1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 292 0.00 0.00 0 0 0 0
Execute 292 0.02 0.01 0 0 0 0
Fetch 292 0.00 0.00 0 585 0 292
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 876 0.02 0.02 0 585 0 292
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 r=0 w=0 time=21 us)
********************************************************************************
SELECT /*+
ordered
full(iem)
full(itl) use_hash(itl)
full(s) use_hash(s) swap_join_inputs(s)
no_merge(vcs)
*/
itl.loc store,
itl.item rms_sku,
vcs.case_size order_multiple,
itl.onsale_date onsale_date,
itl.offsale_date offsale_date,
iem.status status
FROM item_master iem,
item_loc itl,
store s,
v_case_size vcs
WHERE iem.pack_ind = 'N'
AND iem.item_level = iem.tran_level
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
AND iem.item = itl.item
AND itl.loc_type = 'S'
AND iem.item = vcs.item
AND vcs.primary_supp_ind = 'Y'
AND vcs.primary_country_ind = 'Y'
AND itl.loc=s.store
AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.30 0.31 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 819704 936.22 2165.47 4806016 34044281 0 12295541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819706 936.52 2165.78 4806016 34044284 0 12295541
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Rows Row Source Operation
------- ---------------------------------------------------
12295541 HASH JOIN (cr=34044281 r=4806016 w=0 time=2139047518 us)
212315 VIEW (cr=7553336 r=10859 w=0 time=85039675 us)
212315 VIEW (cr=7553336 r=10859 w=0 time=84935492 us)
212315 SORT UNIQUE (cr=7553336 r=10859 w=0 time=84809086 us)
697081 HASH JOIN (cr=7553336 r=10859 w=0 time=80346870 us)
210609 HASH JOIN (cr=12485 r=9629 w=0 time=4530346 us)
210649 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=500 w=0 time=629484 us)
232549 HASH JOIN (cr=9152 r=9129 w=0 time=2975774 us)
210609 MERGE JOIN CARTESIAN (cr=6695 r=6675 w=0 time=1529249 us)
1 MERGE JOIN CARTESIAN (cr=30 r=13 w=0 time=121163 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=13 w=0 time=120964 us)
1 BUFFER SORT (cr=15 r=0 w=0 time=143 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=0 w=0 time=72 us)
210609 BUFFER SORT (cr=6665 r=6662 w=0 time=1322562 us)
210609 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=6662 w=0 time=1016023 us)
232590 TABLE ACCESS FULL ITEM_SUPPLIER (cr=2457 r=2454 w=0 time=557254 us)
1829931 VIEW (cr=7540851 r=1230 w=0 time=68727964 us)
1829931 UNION-ALL (cr=7540851 r=1230 w=0 time=66794426 us)
232590 INDEX FAST FULL SCAN PK_ITEM_SUPP_COUNTRY (cr=1956 r=370 w=0 time=1881373 us)(object id 27601)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=177110 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=188743 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=200532 us)
299857 VIEW (cr=9082 r=860 w=0 time=5321741 us)
299857 SORT UNIQUE (cr=9082 r=860 w=0 time=5166268 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=860 w=0 time=1598649 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4370777 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4212861 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=627394 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4406752 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4241925 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=646683 us)(object id 64316)
11847214 HASH JOIN (cr=26490945 r=4795157 w=0 time=2033417383 us)
769 TABLE ACCESS FULL STORE (cr=62 r=60 w=0 time=27019 us)
13033476 HASH JOIN (cr=26490883 r=4795097 w=0 time=2013607522 us)
15406 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=0 w=0 time=281867 us)
178209054 PARTITION HASH ALL PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=29907257715 us)
178209054 TABLE ACCESS FULL ITEM_LOC PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=25829497488 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 819705 0.00 0.40
db file sequential read 583673 4063.88 15999.28
db file scattered read 138180 4.50 819.63
latch free 17 0.01 0.02
SQL*Net message from client 819705 4179.10 14707.93
buffer busy waits 635 0.00 0.01
log buffer space 5 1.00 2.38
log file switch completion 10 1.00 1.98
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.03 4 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.03 4 9 0 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.03
********************************************************************************
select c.value || '\'|| d.instance_name || '_ora_' || a.spid ||'.trc' trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance d
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 16 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11607 1.78 1.76 0 0 0 174087
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11609 1.79 1.77 0 16 0 174087
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Rows Row Source Operation
------- ---------------------------------------------------
174087 NESTED LOOPS (cr=0 r=0 w=0 time=1207216 us)
6003 NESTED LOOPS (cr=0 r=0 w=0 time=68593 us)
261 MERGE JOIN (cr=0 r=0 w=0 time=12786 us)
846 SORT JOIN (cr=0 r=0 w=0 time=3497 us)
846 NESTED LOOPS (cr=0 r=0 w=0 time=1564 us)
1 NESTED LOOPS (cr=0 r=0 w=0 time=65 us)
1 NESTED LOOPS (cr=0 r=0 w=0 time=47 us)
1 FIXED TABLE FULL X$QUIESCE (cr=0 r=0 w=0 time=7 us)
1 FIXED TABLE FULL X$KVIT (cr=0 r=0 w=0 time=22 us)
1 FIXED TABLE FULL X$KSUXSINST (cr=0 r=0 w=0 time=17 us)
846 FIXED TABLE FULL X$KSPPCV (cr=0 r=0 w=0 time=1163 us)
261 FILTER (cr=0 r=0 w=0 time=8058 us)
846 SORT JOIN (cr=0 r=0 w=0 time=3710 us)
846 FIXED TABLE FULL X$KSPPI (cr=0 r=0 w=0 time=919 us)
6003 FIXED TABLE FULL X$KSUSE (cr=0 r=0 w=0 time=51946 us)
174087 FIXED TABLE FULL X$KSUPR (cr=0 r=0 w=0 time=1046576 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 11607 0.00 0.00
SQL*Net message from client 11607 6.65 14.82
SQL*Net more data to client 44 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.31 0.32 0 19 0 0
Execute 5 0.00 0.01 0 0 0 0
Fetch 831311 938.00 2167.23 4806016 34044281 0 12469628
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 831320 938.31 2167.57 4806016 34044300 0 12469628
Misses in library cache during parse: 3
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 831315 0.00 0.41
SQL*Net message from client 831315 4179.10 15120.98
SQL*Net break/reset to client 4 0.00 0.00
db file sequential read 583673 4063.88 15999.28
db file scattered read 138180 4.50 819.63
latch free 17 0.01 0.02
buffer busy waits 635 0.00 0.01
log buffer space 5 1.00 2.38
log file switch completion 10 1.00 1.98
SQL*Net more data to client 44 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 305 0.01 0.00 0 0 0 0
Execute 313 0.03 0.04 0 0 0 0
Fetch 313 0.00 0.03 4 632 0 305
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 931 0.04 0.08 4 632 0 305
Misses in library cache during parse: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.03
4 user SQL statements in session.
305 internal SQL statements in session.
309 SQL statements in session.
********************************************************************************
Trace file: adqora3g_ora_230458_SQL_TRACE.trc
Trace file compatibility: 9.00.01
Sort options: default
0 session in tracefile.
4 user SQL statements in trace file.
305 internal SQL statements in trace file.
309 SQL statements in trace file.
9 unique SQL statements in trace file.
3220466 lines in trace file.
|
|
#6
April 22nd, 2008, 08:17 AM
|
|||
|
|||
You need to check if there are indexes for these:
Rows Row Source Operation ------- --------------------------------------------------- 210649 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=500 w=0 time=629484 us) 232590 TABLE ACCESS FULL ITEM_SUPPLIER (cr=2457 r=2454 w=0 time=557254 us) 232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=177110 us) 232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=188743 us) 232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=200532 us) 769 TABLE ACCESS FULL STORE (cr=62 r=60 w=0 time=27019 us) 15406 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=0 w=0 time=281867 us) 178209054 TABLE ACCESS FULL ITEM_LOC PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=25829497488 us) 
__________________
|
|
#7
April 22nd, 2008, 09:07 AM
|
|||
|
|||
|
Quote:
Indexes are available for these.
|
|
#8
April 22nd, 2008, 11:48 AM
|
|||
|
|||
|
Are you sure your query is working? It seems to be missing the closing '*/' for the hints.
Anyway, do you have index on item_loc (item + loc_type)? Also you may want to replace the view to directly join the underlying tables. 
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
