Home » RDBMS Server » Performance Tuning » Sql query tuning (9.0.4.0)
| Sql query tuning [message #313905] |
Tue, 15 April 2008 02:16  |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi All,
I have 3 tables and 1 view. I wrote one query as follows. But it's taking 45 mins for executing.
Can you please write a query for this to improve performance.the tables are as follows.
1. View_name=" Case" 2. Item 3. Master 4. Warehouse
columns are
Case returns : Item, Supply_indicator , Country_Indicator , item_size
Item : Item , location ,location_type, ondate, offdate,status,create_date
Master :item , status, pack_indicator, Item_level(either 1 or 0),Trns_level(either 1 or 0),create_date,Foreind (either Y or N)
Warehouse : Warehose_no, Name , Address
SELECT im.location ,
im.item ,
cs.case_size ,
im.ondate ,
im.offdate ,
mas.status
FROM case_size cs,
item im,
master mas,
warehouse wh
WHERE
mas.pack_ind = 'N'
AND mas.item_level = mas.trns_level
AND mas.status = 'A'
AND mas.foreind = 'Y'
AND mas.item = im.item
AND im.location_type = 'S'
AND mas.item = cs.item
AND cs.supply_indicator = 'Y'
AND cs.country_indicator = 'Y'
AND im.location =wh.warehose_no
AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')
pls help me in this issue..
Thank you,
[Updated on: Tue, 15 April 2008 04:09] Report message to a moderator |
|
|
|
| Re: Sql query tuning [message #313934 is a reply to message #313905] |
Tue, 15 April 2008 04:26  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Post the:
- Explain Plan
- Row count for each table
- Row count for each table AFTER filters (WHERE clauses) applied
- Total row count for the query
Ross Leishman
|
|
|
|
| Re: Sql query tuning [message #313935 is a reply to message #313905] |
Tue, 15 April 2008 04:26 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Michel,Sharma,Littlefoot and all...
I expect ideas from you guys. But I didn't get yet. Pls help me in this issue.
I have done as follows:
Explain plan
run the <sql query>
Then pls tell me the next step.
Thank you.
|
|
|
|
| Re: Sql query tuning [message #313940 is a reply to message #313934] |
Tue, 15 April 2008 04:39 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
hi here is the explain plan
Execution Plan
---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15085 Card=1139512 Bytes=134462416)
1 0 HASH JOIN (Cost=15085 Card=1139512 Bytes=134462416) 2 1 HASH JOIN (Cost=580 Card=30772 Bytes=1569372)
3 2 TABLE ACCESS (FULL) OF 'MASTER' (Cost=63
Card=1306 Bytes=18284)
4 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ITEM'
(Cost=2 Card=105311 Bytes=3369952)
5 4 NESTED LOOPS (Cost=86 Card=4296685 Bytes=158977345)
6 5 TABLE ACCESS (FULL) OF 'WAREHOUSE' (Cost=4 Card=41 Bytes=205)
7 5 INDEX (RANGE SCAN) OF 'PK_LOCATION' (UNIQUE)
(Cost =1 Card=210622)
8 1 VIEW (Cost=14271 Card=48399 Bytes=3242733)
9 8 SORT (UNIQUE) (Cost=14271 Card=48399 Bytes=6098274)
10 9 HASH JOIN (Cost=992 Card=187614 Bytes=23639364)
11 10 HASH JOIN (Cost=186 Card=7449 Bytes=581022)
12 11 TABLE ACCESS (FULL) OF 'MASTER' (Cost=63
Card=10451 Bytes=156765)
13 11 HASH JOIN (Cost=105 Card=12489 Bytes=786807)
14 13 MERGE JOIN (CARTESIAN) (Cost=40 Card=12489 Bytes=549516)
15 14 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=13)
16 15 TABLE ACCESS (FULL) OF 'SYSTEM'
(Cost=3 Card=1 Bytes=3)
17 15 BUFFER (SORT) (Cost=3 Card=1 Bytes=10)
18 17 TABLE ACCESS (FULL) OF 'SYSTEM' (Cost=3 Card=1 Bytes=10)
19 14 BUFFER (SORT) (Cost=37 Card=12489 Bytes=387159)
20 19 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=12489 Bytes=387159)
21 13 TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=2 8 Card=24989 Bytes=474791)
22 10 VIEW (Cost=536 Card=172449 Bytes=8277552) 23 22 UNION-ALL
24 23 INDEX (FAST FULL SCAN) OF 'PK_ITEM_SUPPLIER_COUNTR Y' (UNIQUE) (Cost=11 Card=24978 Bytes=324714)
25 23 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=399648)
26 23 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=374670)
27 23 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=499560)
28 23 VIEW (Cost=141 Card=24179 Bytes=1039697) 29 28 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
30 29 INDEX (FAST FULL SCAN) OF
'PK_CASE_U PDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
31 23 VIEW (Cost=141 Card=24179 Bytes=1039697) 32 31 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
33 32 INDEX (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
34 23 VIEW (Cost=141 Card=24179 Bytes=1039697) 35 34 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507 759)
36 35 INDEX (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179 Bytes=507759)
no.of rows from MASTER : 200987
iteM : 4556565
WAREHOUSE :900
CASE(it's a view) :30000
No.Of rows after executing the query are : 400000
Thank you
[Updated on: Tue, 15 April 2008 04:51] Report message to a moderator |
|
|
|
| Re: Sql query tuning [message #313979 is a reply to message #313934] |
Tue, 15 April 2008 06:47 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Rleishman,
I am waiting for your reply. Please give me any idea to resolve this issue. Here I have posted "Explain plan" and No. of rows .
Thank you
|
|
|
|
| Re: Sql query tuning [message #314170 is a reply to message #313979] |
Tue, 15 April 2008 22:55 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
@user71408, you misunderstood my level of interest in your problem. I was not sitting at my desk at 21:47 local time last night busily working on your problem. I was at home with my family watching Biggest Loser on the tele. Sorry if I mis-led you.
Take a look at the Explain Plan you posted and tell me if you can read it. You have a number of line-breaks missing and indented lines wrapping to a left-justified new line. It's just not worth the effort required to try to decypher it.
I also asked:| Quote: | - Row count for each table AFTER filters (WHERE clauses) applied
|
But you chose not to answer.
Considering the size of the unreadable Explain Plan, I am guessing that the CASE_SIZE view is considerably complex. That is going to make it much harder to tune.
Can you trace a session and post the TKPROF output. This will give us row-counts for each step of the plan, which will be befeficial in isolating a problem area.
Ross Leishman
|
|
|
|
| Re: Sql query tuning [message #314608 is a reply to message #314170] |
Thu, 17 April 2008 05:10 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
I have followed as per your guidance. I got trace file and I got tkporf. I gave the command as follows for TKPROF
tkprof g_ora_14292.trc output.txt explain= / insert=store.sql sys=no
the output as follows
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
direct path write 5506 0.22 4.39 direct path read 42 0.00 0.06
db file sequential read 73643 0.32 47.06
db file scattered read 123335 0.93 318.67
latch free 3 0.00 0.00 SQL*Net break/reset to client 0.00 0.00
SQL*Net message from client 1 181.03 181.03
********************************************************************************
<select statement>
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.32 0.29 0 593 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 819704 874.76 1529.15 3879188 12619996 5 12295541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819706 875.08 1529.45 3879188 12620589 5 12295541
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------12295541 HASH JOIN (cr=12619996 r=3879188 w=2408 time=1503903336 us)
212315 VIEW (cr=7553336 r=2408 w=2408 time=82297538 us)
<as per my prevous post >
Rows Execution Plan
------- ---------------------------------------------------
<as per my previous post>
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 819704 0.00 0.45
direct path write 4 0.00 0.00 direct path read 51 0.00 0.07
db file sequential read 94884 0.41 60.91
db file scattered read 222696 2302.70 4675.10 SQL*Net message from client819704 3672.87 6299.98 latch free 2 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 30 0.66 3.81 0 1209 0 0
Execute 359369 80.45 157.24 2373 685165 1421669 287451
Fetch 1179026 1243.52 2250.98 5638169 23437045 14 12654850
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1538425 1324.63 2412.03 5640542 24123419 1421683 12942301
Misses in library cache during parse: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1179098 0.00 0.77 SQL*Net message from client 1179097 3672.87 8511.63 library cache pin 2 3.00 3.00 SQL*Net break/reset to client 5 0.01 0.01
log file sync 37 1.00 4.11
db file sequential read 170903 0.41 150.29 latch free 314 0.29 2.38 direct path write 5514 0.22 4.41 direct path read 107 0.14 0.43 SQL*Net more data to clien 1350 0.02 0.24 buffer busy waits 212 0.49 4.02 SQL*Net more data from client 3715 0.04 0.23 log buffer space 203 1.00 26.34 enqueue 24 0.31 0.90 buffer deadlock 3 0.00 0.00 log file switch completion 4 0.11 0.18
db file scattered read 346031 2302.70 4993.78
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 669 0.00 0.01 0 0 0 0
Execute 676 0.02 0.06 0 4 1 1
Fetch 947 0.01 0.13 3 1837 0 874
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2292 0.03 0.22 3 1841 1 875
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 3 0.01 0.03 33 user SQL statements in session. 668
internal SQL statements in session. 701
SQL statements in session. 25
statements EXPLAINed in this session.
********************************************************************************
trace file: g_ora_14292.trc
Trace file compatibility: 9.00.01
Sort options: default
2 sessions in tracefile.
63 user SQL statements in trace file.
739 internal SQL statements in trace file.
701 SQL statements in trace file.
41 unique SQL statements in trace file.
25 SQL statements EXPLAINed using schema: DEVEL.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
6446643 lines in trace file.
So please guide me for the next step.
Thank you
[Updated on: Thu, 17 April 2008 05:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Sql query tuning [message #314648 is a reply to message #313905] |
Thu, 17 April 2008 06:36 |
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
|
Member |
|
|
You can try the following re-designed query.
SELECT
im.location ,
im.item ,
cs.case_size ,
im.ondate ,
im.offdate ,
mas.status
FROM
case_size cs,
item im,
master mas,
warehouse wh
WHERE
mas.pack_ind = 'N'
AND mas.item_level = mas.trns_level
AND mas.status = 'A'
AND mas.foreind = 'Y'
AND mas.item = cs.item
AND cs.item = im.item
AND cs.supply_indicator = 'Y'
AND cs.country_indicator = 'Y'
AND im.location_type = 'S'
AND im.location = wh.warehose_no
AND nvl(wh.close_date,'99990404')>=to_date(&verdate,'YYYYMMDD')
Please note that since mas.item joins with cs.item and im.item, rearranged so that mas.item = cs.item and cs.item=im.item
Also note if wh.close_date nvl() to 04/04/9999, that means you want to select all null date cases and if it is not null then condition specified, then you can replace last line in the re-designed query as follows
AND (wh.CLOSE_DATE is null or wh.close_date>=E(&versDATE, 'YYYYMMDD')
Hope you have indexes as follows
master : pack_ind, item_level, status, foreind, item
case_size : Item, supply_indicator, country_indicator
Item : Item, location_type, location
Warehouse : warehose_no, close_date
Try this. Good luck.
Regards,
MSMallya
|
|
|
|
| Re: Sql query tuning [message #314668 is a reply to message #314648] |
Thu, 17 April 2008 07:47 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Mallya,
In the query there is a problem in the last condition.
when we are comapring either null or close_date with versdate
if we r comparing with close_date with versdate it's possible but when we are comparing null with Versdate we will get the problem...
Thank you
|
|
|
|
| Re: Sql query tuning [message #314682 is a reply to message #314668] |
Thu, 17 April 2008 08:28 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Oh, for pity's sake. You just don't want help do you?
I ask for the TKPROF output so that we can see the row count for each step of the plan. What do you provide? The Tkprof (barely readable because of the missing line feeds) WITH THE PLAN EDITED OUT!!!!!!!!!!!!
| Quote: | Rows Row Source Operation
------- ---------------------------------------------------12295541 HASH JOIN (cr=12619996 r=3879188 w=2408 time=1503903336 us)
212315 VIEW (cr=7553336 r=2408 w=2408 time=82297538 us)
<as per my prevous post >
Rows Execution Plan
------- ---------------------------------------------------
<as per my previous post>
|
Why did you go to so much effort to remove the only thing in that post would permit me to help you.
I give up. I'm moving on to more worthy threads.
Ross Leishman
|
|
|
|
| Re: Sql query tuning [message #314694 is a reply to message #314682] |
Thu, 17 April 2008 08:58 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Ross,
Here I am posting total TKPROF output.
TKPROF: Release 9.2.0.4.0 - Production on Thu Apr 17 10:46:10 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: g_ora_14292.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 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: 315 (DEVEL)
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 0.00 0.00
********************************************************************************
alter SESSION SET TIMED_STATISTICS = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 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.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
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 0.00 0.00
********************************************************************************
alter SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
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: 315 (DEVEL)
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 0.00 0.00
********************************************************************************
alter SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 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.01 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
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 0.00 0.00
library cache pin 2 3.00 3.00
********************************************************************************
select /*+ INDEX(rc pk_restart_control) +*/ process_flag ,driver_name ,
num_threads ,commit_max_ctr
from
restart_control rc where program_name=LOWER(:b0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 3.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 3.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ#(28470) (cr=2 r=0 w=0 time=14 us)
1 INDEX UNIQUE SCAN OBJ#(28471) (cr=1 r=0 w=0 time=8 us)(object id 28471)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESTART_CONTROL'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RESTART_CONTROL'
(UNIQUE)
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 0.00 0.00
********************************************************************************
select sid
from
v$session where process=to_char(:b0)
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 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 FIXED TABLE FULL X$KSUSE (cr=0 r=0 w=0 time=71 us)
error during execute of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 91
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 0.00 0.00
********************************************************************************
select p.spid
from
v$session s ,v$process p where (s.audsid=userenv('SESSIONID') and s.paddr=
p.addr)
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 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 MERGE JOIN (cr=0 r=0 w=0 time=376 us)
19 FIXED TABLE FULL X$KSUPR (cr=0 r=0 w=0 time=37 us)
1 SORT JOIN (cr=0 r=0 w=0 time=268 us)
1 FIXED TABLE FULL X$KSUSE (cr=0 r=0 w=0 time=245 us)
error during execute of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 107
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 0.00 0.00
********************************************************************************
select /*+ INDEX(rps pk_restart_program_status) +*/ ROWIDTOCHAR(rowid)
from
restart_program_status rps where (restart_name=LOWER(:b0) and (restart_flag=
'Y' or program_status='ready for start'))
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 5 0.00 0.00 0 12 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 12 0 9
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
9 TABLE ACCESS BY INDEX ROWID OBJ#(28475) (cr=12 r=0 w=0 time=189 us)
10 INDEX RANGE SCAN OBJ#(28477) (cr=6 r=0 w=0 time=102 us)(object id 28477)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESTART_PROGRAM_STATUS'
10 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_RESTART_PROGRAM_STATUS' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 0.00 0.03
********************************************************************************
select NVL(restart_flag,'N') ,thread_val
from
restart_program_status where (rowid=CHARTOROWID(:b0) and (restart_flag='Y'
or program_status='ready for start')) for update nowait
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9 0.00 0.00 0 11 3 0
Fetch 8 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 11 3 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
0 FOR UPDATE (cr=0 r=0 w=0 time=0 us)
1 TABLE ACCESS BY USER ROWID RESTART_PROGRAM_STATUS (cr=2 r=0 w=0 time=66 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 FOR UPDATE
1 TABLE ACCESS GOAL: ANALYZED (BY USER ROWID) OF
'RESTART_PROGRAM_STATUS'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 2 0.01 0.01
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 0.04 0.11
********************************************************************************
update restart_program_status set program_status='started',restart_flag=null
,start_time=DECODE(restart_flag,'Y',start_time,SYSDATE),restart_time=
DECODE(restart_flag,'Y',SYSDATE,null ),finish_time=null ,current_pid=:b0,
current_operator_id=:b1,current_oracle_sid=:b2,current_shadow_pid=:b3,
err_message=null
where
rowid = :x
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 1 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 1 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE (cr=1 r=0 w=0 time=147 us)
1 TABLE ACCESS BY USER ROWID OBJ#(28475) (cr=1 r=0 w=0 time=23 us)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
1 UPDATE OF 'RESTART_PROGRAM_STATUS'
1 TABLE ACCESS GOAL: ANALYZED (BY USER ROWID) OF
'RESTART_PROGRAM_STATUS'
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 0.01 0.01
********************************************************************************
insert into restart_bookmark (restart_name,thread_val,bookmark_string,
application_image,out_file_string,non_fatal_err_flag,num_commits,
avg_time_btwn_commits)
values
(LOWER(:b0),:b1,null ,null ,null ,DECODE(:b2,1,'Y','N'),0,null )
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 1 7 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 7 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
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 0.00 0.00
********************************************************************************
select TO_CHAR(p.vdate,'YYYYMMDD') ,so.multichannel_ind
from
period p ,system_options so
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 19 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 19 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 MERGE JOIN CARTESIAN (cr=19 r=0 w=0 time=233 us)
1 TABLE ACCESS FULL OBJ#(28207) (cr=4 r=0 w=0 time=65 us)
1 BUFFER SORT (cr=15 r=0 w=0 time=137 us)
1 TABLE ACCESS FULL OBJ#(29081) (cr=15 r=0 w=0 time=61 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 MERGE JOIN (CARTESIAN)
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PERIOD'
1 BUFFER (SORT)
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SYSTEM_OPTIONS'
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 0.56 0.56
********************************************************************************
select t.tsf_no ,t.from_loc ,t.from_loc_type ,t.to_loc ,t.to_loc_type ,
TO_CHAR(t.delivery_date,'YYYYMMDD') ,NVL(t.routing_code,' ') ,
NVL(t.freight_code,'N') ,t.item ,t.tsf_qty ,NVL(t.supp_pack_size,'-1') ,
ROWIDTOCHAR(t.rowid )
from
tsf_staging t where ((:b0<>'Y' and t.process_ind is null ) and
t.from_loc=:b1) union all select t.tsf_no ,t.from_loc ,t.from_loc_type ,
t.to_loc ,t.to_loc_type ,TO_CHAR(t.delivery_date,'YYYYMMDD') ,
NVL(t.routing_code,' ') ,NVL(t.freight_code,'N') ,t.item ,t.tsf_qty ,
NVL(t.supp_pack_size,'-1') ,ROWIDTOCHAR(t.rowid ) from tsf_staging t
where (:b0='Y' and t.process_ind='F') order by from_loc,tsf_no
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 9 1.63 6.99 713 64359 4 71859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 1.63 6.99 713 64359 4 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 SORT ORDER BY (cr=64359 r=713 w=712 time=6546143 us)
71859 UNION-ALL (cr=64359 r=1 w=0 time=3038270 us)
71859 FILTER (cr=64359 r=1 w=0 time=2231615 us)
71859 TABLE ACCESS BY INDEX ROWID OBJ#(83393) (cr=64359 r=1 w=0 time=2089038 us)
71859 INDEX RANGE SCAN OBJ#(95748) (cr=253 r=1 w=0 time=353042 us)(object id 95748)
0 FILTER (cr=0 r=0 w=0 time=2 us)
0 TABLE ACCESS FULL OBJ#(83393) (cr=0 r=0 w=0 time=0 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
71859 SORT (ORDER BY)
71859 UNION-ALL
71859 FILTER
71859 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSF_STAGING'
71859 FILTER
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSF_STAGING'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 9 0.00 0.00
db file sequential read 1 0.00 0.00
latch free 12 0.03 0.10
direct path write 4 0.01 0.01
direct path read 14 0.14 0.28
SQL*Net message from client 9 0.11 0.15
SQL*Net more data to client 1350 0.02 0.24
********************************************************************************
select 'Y'
from
tsfhead where tsf_no=:b0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71859 8.81 7.90 0 0 0 0
Fetch 71859 6.90 24.57 0 350082 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143719 15.71 32.47 0 350082 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
0 INDEX UNIQUE SCAN OBJ#(29253) (cr=350082 r=0 w=0 time=24105625 us)(object id 29253)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_TSFHEAD' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 71859 0.00 0.09
SQL*Net message from client 71859 0.59 140.66
buffer busy waits 60 0.49 2.73
latch free 172 0.29 1.13
log buffer space 89 1.00 15.03
********************************************************************************
select wh ,stockholding_ind
from
wh where wh=:b0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71859 8.96 7.62 0 0 0 0
Fetch 71859 3.38 2.68 0 143718 0 71859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143719 12.34 10.30 0 143718 0 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 TABLE ACCESS BY INDEX ROWID OBJ#(29746) (cr=143718 r=0 w=0 time=1219455 us)
71859 INDEX UNIQUE SCAN OBJ#(29747) (cr=71859 r=0 w=0 time=539987 us)(object id 29747)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
71859 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'WH'
71859 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_WH' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 71859 0.00 0.06
SQL*Net message from client 71859 0.55 90.34
latch free 5 0.01 0.01
********************************************************************************
select stockholding_ind
from
store where store=:b0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71859 8.31 6.54 0 0 0 0
Fetch 71859 3.93 2.46 0 215577 0 71859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143719 12.24 9.00 0 215577 0 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 TABLE ACCESS BY INDEX ROWID OBJ#(28943) (cr=215577 r=0 w=0 time=1122924 us)
71859 INDEX UNIQUE SCAN OBJ#(28955) (cr=143718 r=0 w=0 time=599947 us)(object id 28955)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
71859 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'STORE'
71859 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_STORE' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 71859 0.00 0.05
SQL*Net message from client 71859 0.32 69.30
latch free 2 0.00 0.00
********************************************************************************
select TO_CHAR(TO_DATE(:b0,'YYYYMMDDHH24MISS')) into :b1
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71859 9.13 5.64 0 0 0 0
Fetch 71859 8.66 5.00 0 215577 0 71859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143719 17.79 10.65 0 215577 0 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 TABLE ACCESS FULL OBJ#(222) (cr=215577 r=0 w=0 time=2806595 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
71859 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DUAL'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 71859 0.00 0.05
SQL*Net message from client 71859 0.28 80.65
latch free 23 0.01 0.11
********************************************************************************
select status ,item_level ,tran_level ,pack_ind ,dept ,class ,subclass ,
DECODE(waste_type,'SL',1,0) wastage_ind ,DECODE(waste_type,'SL',
(waste_pct/100),0) waste_pct
from
item_master where item=:b0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71859 11.08 7.04 0 0 0 0
Fetch 71859 5.32 3.07 2 215577 0 71859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143719 16.40 10.12 2 215577 0 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 TABLE ACCESS BY INDEX ROWID OBJ#(27569) (cr=215577 r=2 w=0 time=1502990 us)
71859 INDEX UNIQUE SCAN OBJ#(27577) (cr=143718 r=0 w=0 time=845782 us)(object id 27577)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
71859 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ITEM_MASTER'
71859 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_ITEM_MASTER'
(UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 71859 0.00 0.05
SQL*Net message from client 71859 1.06 974.02
latch free 6 0.01 0.01
db file sequential read 2 0.02 0.02
********************************************************************************
insert into tsfhead (tsf_no,from_loc_type,from_loc,to_loc_type,to_loc,
tsf_type,status,freight_code,routing_code,create_date,create_id,
approval_date,approval_id,delivery_date,repl_tsf_approve_ind)
values
(:b1,:b2,:b3,:b4,:b5,'dep','I',:b6,:b7,get_vdate,'TSFUPLD',TO_DATE(:b8,
'YYYYMMDD'),'TSFUPLD',TO_DATE(:b8,'YYYYMMDD'),'N')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 3.79 19.79 990 3631 242374 71859
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 3.79 19.79 990 3631 242374 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 1537 0.04 0.12
db file sequential read 990 0.40 12.39
log buffer space 20 0.35 1.81
log file sync 6 0.20 0.46
buffer busy waits 8 0.00 0.00
enqueue 3 0.06 0.11
latch free 4 0.01 0.02
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 0.00 0.03
buffer deadlock 1 0.00 0.00
log file switch completion 2 0.02 0.03
********************************************************************************
SELECT sysavail,
vdate,
start_454_half,
end_454_half,
start_454_month,
mid_454_month,
end_454_month,
half_no,
next_half_no,
curr_454_day,
curr_454_week,
curr_454_month,
curr_454_year,
curr_454_month_in_half,
curr_454_week_in_half
from period
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 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 433 (PRODUCTION) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL PERIOD (cr=4 r=0 w=0 time=39 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PERIOD'
********************************************************************************
insert into tsfdetail (tsf_no,tsf_seq_no,item,tsf_qty,supp_pack_size,
publish_ind)
values
(:b1,:b2,:b3,:b4,:b5,'N')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 3.05 31.45 1075 7279 372396 71859
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 3.05 31.45 1075 7279 372396 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 1113 0.00 0.03
db file sequential read 1075 0.35 21.91
buffer busy waits 103 0.15 1.06
log file sync 18 1.00 2.62
log buffer space 14 0.40 1.34
enqueue 21 0.31 0.78
buffer deadlock 2 0.00 0.00
latch free 3 0.01 0.01
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 0.00 0.01
********************************************************************************
delete from tsf_staging
where
rowid=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 8.06 18.79 0 175290 294507 71859
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 8.06 18.79 0 175290 294507 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 DELETE (cr=175290 r=0 w=0 time=10548624 us)
71859 TABLE ACCESS BY USER ROWID OBJ#(83393) (cr=175130 r=0 w=0 time=1601247 us)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT GOAL: CHOOSE
71859 DELETE OF 'TSF_STAGING'
71859 TABLE ACCESS GOAL: ANALYZED (BY USER ROWID) OF
'TSF_STAGING'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 712 0.00 0.03
log buffer space 38 0.43 3.54
latch free 8 0.02 0.05
buffer busy waits 7 0.00 0.00
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 0.01 0.04
********************************************************************************
update tsfhead set status='A'
where
tsf_no=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 19.25 52.38 308 498917 512356 71859
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 19.25 52.38 308 498917 512356 71859
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
71859 UPDATE (cr=498917 r=308 w=0 time=43891595 us)
71859 INDEX UNIQUE SCAN OBJ#(29253) (cr=497791 r=0 w=0 time=21701628 us)(object id 29253)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
71859 UPDATE OF 'TSFHEAD'
71859 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_TSFHEAD' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 308 0.26 7.97
latch free 74 0.10 0.89
log buffer space 42 0.30 4.60
buffer busy waits 34 0.04 0.22
SQL*Net more data from client 353 0.00 0.03
log file sync 1 0.03 0.03
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 0.00 0.00
log file switch completion 2 0.11 0.15
********************************************************************************
update /*+ INDEX(rb pk_restart_bookmark) +*/ restart_bookmark rb set
bookmark_string=:b0,application_image=:b1,out_file_string=:b2,
non_fatal_err_flag=DECODE(:b3,1,'Y','N'),num_commits=(num_commits+1),
avg_time_btwn_commits=DECODE(num_commits,0,null ,((((num_commits-1)*
NVL(avg_time_btwn_commits,0))+:b4)/num_commits))
where
(restart_name=LOWER(:b5) and thread_val=TO_NUMBER(:b6))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 0.01 0.00 0 18 11 9
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.01 0.00 0 18 11 9
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
9 UPDATE (cr=18 r=0 w=0 time=1130 us)
9 INDEX UNIQUE SCAN OBJ#(28469) (cr=18 r=0 w=0 time=109 us)(object id 28469)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
9 UPDATE OF 'RESTART_BOOKMARK'
9 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RESTART_BOOKMARK'
(UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 0.00 0.00
********************************************************************************
update /*+ INDEX(rps pk_restart_program_status) +*/ restart_program_status
rps set program_status=DECODE(:b0,1,'aborted in process',2,'aborted in
init',3,'aborted in final','completed'),restart_flag=null ,finish_time=
DECODE(:b0,0,SYSDATE,null ),current_pid=null ,current_operator_id=null ,
current_oracle_sid=null ,current_shadow_pid=null ,err_message=DECODE(:b0,1,
'Terminated in process',2,'Terminated in init',3,'Terminated in final',null
)
where
(restart_name=LOWER(:b3) and thread_val=TO_NUMBER(:b4))
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 2 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 2 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE (cr=2 r=0 w=0 time=137 us)
1 INDEX UNIQUE SCAN OBJ#(28477) (cr=2 r=0 w=0 time=18 us)(object id 28477)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
1 UPDATE OF 'RESTART_PROGRAM_STATUS'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_RESTART_PROGRAM_STATUS' (UNIQUE)
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 0.00 0.00
********************************************************************************
insert into restart_program_history (restart_name,thread_val,start_time,
program_name,commit_max_ctr,restart_time,finish_time,num_threads,shadow_pid,
success_flag,non_fatal_err_flag,num_commits,avg_time_btwn_commits)(select
/*+ INDEX(rps pk_restart_program_status) +*/ rps.restart_name ,
rps.thread_val ,rps.start_time ,rps.program_name ,rc.commit_max_ctr ,
rps.restart_time ,rps.finish_time ,rc.num_threads ,:b0 ,DECODE(:b1,0,'Y',
'N') ,rb.non_fatal_err_flag ,rb.num_commits ,rb.avg_time_btwn_commits from
restart_program_status rps ,restart_control rc ,restart_bookmark rb where
((((rps.restart_name=LOWER(:b2) and rps.thread_val=TO_NUMBER(:b3)) and
rps.program_name=rc.program_name) and rps.restart_name=rb.restart_name) and
rps.thread_val=rb.thread_val))
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 9 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 9 6 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=8 r=0 w=0 time=77 us)
1 NESTED LOOPS (cr=8 r=0 w=0 time=72 us)
1 NESTED LOOPS (cr=6 r=0 w=0 time=46 us)
1 TABLE ACCESS BY INDEX ROWID OBJ#(28468) (cr=3 r=0 w=0 time=24 us)
1 INDEX UNIQUE SCAN OBJ#(28469) (cr=2 r=0 w=0 time=12 us)(object id 28469)
1 TABLE ACCESS BY INDEX ROWID OBJ#(28475) (cr=3 r=0 w=0 time=14 us)
1 INDEX UNIQUE SCAN OBJ#(28477) (cr=2 r=0 w=0 time=5 us)(object id 28477)
1 TABLE ACCESS BY INDEX ROWID OBJ#(28470) (cr=2 r=0 w=0 time=17 us)
1 INDEX UNIQUE SCAN OBJ#(28471) (cr=1 r=0 w=0 time=12 us)(object id 28471)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
1 FILTER
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESTART_BOOKMARK'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_RESTART_BOOKMARK' (UNIQUE)
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESTART_PROGRAM_STATUS'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_RESTART_PROGRAM_STATUS' (UNIQUE)
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESTART_CONTROL'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_RESTART_CONTROL' (UNIQUE)
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 0.00 0.00
********************************************************************************
delete /*+ INDEX(rb pk_restart_bookmark) +*/ from restart_bookmark
where
(restart_name=LOWER(:b0) and thread_val=TO_NUMBER(:b1))
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 2 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 4 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE (cr=2 r=0 w=0 time=83 us)
1 INDEX UNIQUE SCAN OBJ#(28469) (cr=2 r=0 w=0 time=6 us)(object id 28469)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT GOAL: CHOOSE
1 DELETE OF 'RESTART_BOOKMARK'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RESTART_BOOKMARK'
(UNIQUE)
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 0.00 0.00
********************************************************************************
update /*+ INDEX(rps pk_restart_program_status) +*/ restart_program_status
rps set program_status='ready for start'
where
(restart_name=LOWER(:b0) and thread_val=TO_NUMBER(:b1))
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 2 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 2 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE (cr=2 r=0 w=0 time=113 us)
1 INDEX UNIQUE SCAN OBJ#(28477) (cr=2 r=0 w=0 time=12 us)(object id 28477)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
1 UPDATE OF 'RESTART_PROGRAM_STATUS'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_RESTART_PROGRAM_STATUS' (UNIQUE)
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 0.00 0.00
********************************************************************************
delete /*+ INDEX(rb pk_restart_bookmark) +*/ from restart_bookmark rb
where
(restart_name=LOWER(:b0) and thread_val=TO_NUMBER(:b1))
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 2 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE (cr=2 r=0 w=0 time=10 us)
0 INDEX UNIQUE SCAN OBJ#(28469) (cr=2 r=0 w=0 time=8 us)(object id 28469)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT GOAL: CHOOSE
0 DELETE OF 'RESTART_BOOKMARK'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_RESTART_BOOKMARK'
(UNIQUE)
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 0.00 0.00
********************************************************************************
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.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.02 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
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 630.51 674.29
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
SELECT /*+leading(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 v_case_size vcs,
item_loc itl,
item_master iem,
store s
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.33 0.48 0 616 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 338.94 677.03 1758266 9612126 5 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 339.27 677.51 1758266 9612742 5 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
0 HASH JOIN (cr=0 r=0 w=0 time=0 us)
6161725 HASH JOIN (cr=9612125 r=1758234 w=2408 time=2625092582 us)
15989 HASH JOIN (cr=7560001 r=2408 w=2408 time=81619953 us)
212315 VIEW (cr=7553336 r=2408 w=2408 time=81058149 us)
212315 SORT UNIQUE (cr=7553336 r=2408 w=2408 time=80958818 us)
697081 HASH JOIN (cr=7553336 r=2408 w=2408 time=76507154 us)
210609 HASH JOIN (cr=12485 r=0 w=0 time=2923341 us)
210649 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=249455 us)
232549 HASH JOIN (cr=9152 r=0 w=0 time=1727673 us)
210609 MERGE JOIN CARTESIAN (cr=6695 r=0 w=0 time=654287 us)
1 MERGE JOIN CARTESIAN (cr=30 r=0 w=0 time=355 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=0 w=0 time=178 us)
1 BUFFER SORT (cr=15 r=0 w=0 time=140 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=0 w=0 time=83 us)
210609 BUFFER SORT (cr=6665 r=0 w=0 time=563221 us)
210609 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=0 w=0 time=268814 us)
232590 TABLE ACCESS FULL ITEM_SUPPLIER (cr=2457 r=0 w=0 time=184676 us)
1829931 VIEW (cr=7540851 r=2408 w=2408 time=66339321 us)
1829931 UNION-ALL (cr=7540851 r=2408 w=2408 time=64305693 us)
232590 INDEX FAST FULL SCAN PK_ITEM_SUPP_COUNTRY (cr=1956 r=0 w=0 time=138049 us)(object id 27601)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=173372 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=186309 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=204421 us)
299857 VIEW (cr=9082 r=0 w=0 time=4488596 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4332822 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=657277 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4502275 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4336513 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=673263 us)(object id 64316)
299857 VIEW (cr=9082 r=2408 w=2408 time=4912354 us)
299857 SORT UNIQUE (cr=9082 r=2408 w=2408 time=4750923 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=668371 us)(object id 64316)
15406 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=0 w=0 time=276030 us)
86032675 PARTITION HASH ALL PARTITION: 1 16 (cr=2052125 r=1755858 w=0 time=5678872646 us)
86032675 TABLE ACCESS FULL ITEM_LOC PARTITION: 1 16 (cr=2052125 r=1755858 w=0 time=3484995974 us)
0 TABLE ACCESS FULL STORE (cr=0 r=0 w=0 time=0 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 HASH JOIN
6161725 HASH JOIN
15989 HASH JOIN
212315 VIEW
212315 SORT (UNIQUE)
697081 HASH JOIN
210609 HASH JOIN
210649 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
232549 HASH JOIN
210609 MERGE JOIN (CARTESIAN)
1 MERGE JOIN (CARTESIAN)
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'SYSTEM_OPTIONS'
1 BUFFER (SORT)
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'SYSTEM_OPTIONS'
210609 BUFFER (SORT)
210609 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_MASTER'
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPPLIER'
1829931 VIEW
1829931 UNION-ALL
232590 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_ITEM_SUPP_COUNTRY' (UNIQUE)
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
299857 VIEW
299857 SORT (UNIQUE)
1175224 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE)
299857 VIEW
299857 SORT (UNIQUE)
1175224 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE)
299857 VIEW
299857 SORT (UNIQUE)
1175224 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE)
15406 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_MASTER'
86032675 PARTITION HASH (ALL) PARTITION: START=1 STOP=16
86032675 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_LOC'
PARTITION: START=1 STOP=16
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'STORE'
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
direct path write 5506 0.22 4.39
direct path read 42 0.00 0.06
db file sequential read 73643 0.32 47.06
db file scattered read 123335 0.93 318.67
latch free 3 0.00 0.00
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message from client 1 181.03 181.03
********************************************************************************
SELECT /*+All_ROWS(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 v_case_size vcs,
item_loc itl,
item_master iem,
store s
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.32 0.29 0 593 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 819704 874.76 1529.15 3879188 12619996 5 12295541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819706 875.08 1529.45 3879188 12620589 5 12295541
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 315 (DEVEL)
Rows Row Source Operation
------- ---------------------------------------------------
12295541 HASH JOIN (cr=12619996 r=3879188 w=2408 time=1503903336 us)
212315 VIEW (cr=7553336 r=2408 w=2408 time=82297538 us)
212315 SORT UNIQUE (cr=7553336 r=2408 w=2408 time=82196703 us)
697081 HASH JOIN (cr=7553336 r=2408 w=2408 time=1611842355 us)
210609 HASH JOIN (cr=12485 r=0 w=0 time=3006259 us)
210649 TABLE ACCESS FULL OBJ#(27596) (cr=3333 r=0 w=0 time=252569 us)
232549 HASH JOIN (cr=9152 r=0 w=0 time=1792328 us)
210609 MERGE JOIN CARTESIAN (cr=6695 r=0 w=0 time=672733 us)
1 MERGE JOIN CARTESIAN (cr=30 r=0 w=0 time=414 us)
1 TABLE ACCESS FULL OBJ#(29081) (cr=15 r=0 w=0 time=224 us)
1 BUFFER SORT (cr=15 r=0 w=0 time=151 us)
1 TABLE ACCESS FULL OBJ#(29081) (cr=15 r=0 w=0 time=84 us)
210609 BUFFER SORT (cr=6665 r=0 w=0 time=588521 us)
210609 TABLE ACCESS FULL OBJ#(27569) (cr=6665 r=0 w=0 time=282828 us)
232590 TABLE ACCESS FULL OBJ#(27592) (cr=2457 r=0 w=0 time=193494 us)
1829931 VIEW (cr=7540851 r=2408 w=2408 time=1601326949 us)
1829931 UNION-ALL (cr=7540851 r=2408 w=2408 time=1599477138 us)
232590 INDEX FAST FULL SCAN OBJ#(27601) (cr=1956 r=0 w=0 time=148965 us)(object id 27601)
232590 TABLE ACCESS FULL OBJ#(27596) (cr=3333 r=0 w=0 time=182131 us)
232590 TABLE ACCESS FULL OBJ#(27596) (cr=3333 r=0 w=0 time=196427 us)
232590 TABLE ACCESS FULL OBJ#(27596) (cr=3333 r=0 w=0 time=208067 us)
299857 VIEW (cr=9082 r=0 w=0 time=4366181 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4209094 us)
1175224 INDEX FAST FULL SCAN OBJ#(64316) (cr=9082 r=0 w=0 time=669001 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4346393 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4186605 us)
1175224 INDEX FAST FULL SCAN OBJ#(64316) (cr=9082 r=0 w=0 time=649200 us)(object id 64316)
299857 VIEW (cr=9082 r=2408 w=2408 time=4992268 us)
299857 SORT UNIQUE (cr=9082 r=2408 w=2408 time=4829837 us)
1175224 INDEX FAST FULL SCAN OBJ#(64316) (cr=9082 r=0 w=0 time=669465 us)(object id 64316)
11847214 HASH JOIN (cr=5066660 r=3876780 w=0 time=1400482785 us)
15406 TABLE ACCESS FULL OBJ#(27569) (cr=6665 r=0 w=0 time=288486 us)
161989081 HASH JOIN (cr=5059995 r=3876780 w=0 time=5271269810 us)
769 TABLE ACCESS FULL OBJ#(28943) (cr=62 r=0 w=0 time=1407 us)
178209054 PARTITION HASH ALL PARTITION: 1 16 (cr=5059933 r=3876780 w=0 time=5045798853 us)
178209054 TABLE ACCESS FULL OBJ#(27216) PARTITION: 1 16 (cr=5059933 r=3876780 w=0 time=4974317964 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: HINT: ALL_ROWS
12295541 HASH JOIN
212315 VIEW
212315 SORT (UNIQUE)
697081 HASH JOIN
210609 HASH JOIN
210649 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
232549 HASH JOIN
210609 MERGE JOIN (CARTESIAN)
1 MERGE JOIN (CARTESIAN)
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'SYSTEM_OPTIONS'
1 BUFFER (SORT)
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'SYSTEM_OPTIONS'
210609 BUFFER (SORT)
210609 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_MASTER'
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPPLIER'
1829931 VIEW
1829931 UNION-ALL
232590 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_ITEM_SUPP_COUNTRY' (UNIQUE)
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
232590 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'ITEM_SUPP_COUNTRY'
299857 VIEW
299857 SORT (UNIQUE)
1175224 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE)
299857 VIEW
299857 SORT (UNIQUE)
1175224 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE)
299857 VIEW
299857 SORT (UNIQUE)
1175224 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_CASE_UPDATES' (UNIQUE)
11847214 HASH JOIN
15406 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_MASTER'
161989081 HASH JOIN
769 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'STORE'
178209054 PARTITION HASH (ALL) PARTITION: START=1 STOP=16
178209054 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_LOC'
PARTITION: START=1 STOP=16
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 819704 0.00 0.45
direct path write 4 0.00 0.00
direct path read 51 0.00 0.07
db file sequential read 94884 0.41 60.91
db file scattered read 222696 2302.70 4675.10
SQL*Net message from client 819704 3672.87 6299.98
latch free 2 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 30 0.66 3.81 0 1209 0 0
Execute 359369 80.45 157.24 2373 685165 1421669 287451
Fetch 1179026 1243.52 2250.98 5638169 23437045 14 12654850
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1538425 1324.63 2412.03 5640542 24123419 1421683 12942301
Misses in library cache during parse: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1179098 0.00 0.77
SQL*Net message from client 1179097 3672.87 8511.63
library cache pin 2 3.00 3.00
SQL*Net break/reset to client 5 0.01 0.01
log file sync 37 1.00 4.11
db file sequential read 170903 0.41 150.29
latch free 314 0.29 2.38
direct path write 5514 0.22 4.41
direct path read 107 0.14 0.43
SQL*Net more data to client 1350 0.02 0.24
buffer busy waits 212 0.49 4.02
SQL*Net more data from client 3715 0.04 0.23
log buffer space 203 1.00 26.34
enqueue 24 0.31 0.90
buffer deadlock 3 0.00 0.00
log file switch completion 4 0.11 0.18
db file scattered read 346031 2302.70 4993.78
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 669 0.00 0.01 0 0 0 0
Execute 676 0.02 0.06 0 4 1 1
Fetch 947 0.01 0.13 3 1837 0 874
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2292 0.03 0.22 3 1841 1 875
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 3 0.01 0.03
33 user SQL statements in session.
668 internal SQL statements in session.
701 SQL statements in session.
25 statements EXPLAINed in this session.
********************************************************************************
Trace file: adqora3g_ora_147292.trc
Trace file compatibility: 9.00.01
Sort options: default
2 sessions in tracefile.
63 user SQL statements in trace file.
739 internal SQL statements in trace file.
701 SQL statements in trace file.
41 unique SQL statements in trace file.
25 SQL statements EXPLAINed using schema:
devel.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
6446643 lines in trace file.
[Updated on: Fri, 18 April 2008 07:25] Report message to a moderator |
|
|
|
| Re: Sql query tuning [message #315331 is a reply to message #314668] |
Mon, 21 April 2008 02:33 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Ross,
I imposted TKPROF output previously. So please guide me to
improve the performance of the query. I have
added "use_hash",Merge" "Ordered" hints to the query. PSo previously it's taking 45 mins. Now it's taking 42 mins. So I
want to see more improvement in Performance.So pls guide me.
Thank you.
[Updated on: Mon, 21 April 2008 02:34] Report message to a moderator |
|
|
|
| Re: Sql query tuning [message #315374 is a reply to message #313905] |
Mon, 21 April 2008 05:50 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
I don't know if this of any meaning but why the SQL*Net message to client is too high.
Imean it essentialy means the server is idle.
Could it be a network issue?
|
|
|
|
| Re: Sql query tuning [message #315814 is a reply to message #315374] |
Tue, 22 April 2008 18:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Excellent. Ready to listen.
There are two important issues here. The first is probably the least important one. See the following section of the plan:
11847214 HASH JOIN
15406 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_MASTER'
161989081 HASH JOIN
769 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'STORE'
178209054 PARTITION HASH (ALL) PARTITION: START=1 STOP=16
178209054 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ITEM_LOC' PARTITION: START=1 STOP=16
We can see from this that 178M rows were read from ITEM_LOC, scanning all partitions. These 178M rows were then joined to 15,406 rows from ITEM_MASTER, yeilding only 11.8M rows after the join. This means that 166M or the 178M rows read from ITEM_LOC were wasted.
This is almost certainly due to the filter clauses on ITEM_MASTER:
WHERE iem.pack_ind = 'N'
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
If not for these filter clauses, probably ALL 178M rows would have been selected.
Problem: How can we avoid scanning 166M rows redundantly?
Answer: Given your current architecture, you can't. Using 11g, you might partition ITEM_MASTER on the most seletive of these columns and then REF partition ITEM_LOC. I haven't tried it myself, but that would give you a reasonable chance of partition-pruning the unwanted rows.
Otherwise, you would need to denormalise the most selective of these columns from ITEM_MASTER onto ITEM_LOC (have them maintained by the application), and then redo your partitioning based on this new column. Unless you used 11g with its HASH-LIST composite partitioning, that would mean abandoning your current HASH partitioning. Probably not such a good idea.
My recommendation is that as long as you stay on 9i, there is nothing much you can do about this. If you are very desperate, you could create an index in ITEM_LOC(item, loc_type, loc, onsale_date, offsale_date)
The key to this index is that it contains the join-key to ITEM_MASTER (item), the filter column loc_type, and all of the other columns used in the query (loc, onsale_date, offsale_date). This will allow it to be used in the join from ITEM_MASTER and AVOID the need to access the table row as the index contains all of the columns required. If you do not include all of these columns, any benefit of using the index will be eaten up by 12M table accesses.
The second problem is that the SQL returns 12M rows across the network
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.32 0.29 0 593 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 819704 874.76 1529.15 3879188 12619996 5 12295541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819706 875.08 1529.45 3879188 12620589 5 12295541
This is an awful lot of data. You cannot expect this to be very fast when it is returning so much data. Do you REALLY need this much data? Who the heck is going to read all of it? If you are just manipulating it and then writing it back to the database, consider using PL/SQL; then you don't have to pull it across the network.
The only thing you can do is to make sure it is returned efficiently. You can see that it performs 819K Fetches for its 12M rows. That means you are fetching 15 rows at a time (array size of 15). If you set your array size a bit larger; say 1000; then you would have less to-ing and fro-ing across the network.
Are you using SQL*Plus? If so, use SET TERMOUT OFF so that the output does not get rendered on the screen - that will slow it down too.
Ross Leishman
|
|
|
|
| Re: Sql query tuning [message #315936 is a reply to message #315814] |
Wed, 23 April 2008 05:11 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Ross,
This is daily job script.So we need all the data.I have already mentioned" set Termout off" in the script.
Now it's taking 42 mins as mentioned previouly.If u need any information regarding this pls let me know.
Thank you.
|
|
|
|
| Re: Sql query tuning [message #316150 is a reply to message #315936] |
Wed, 23 April 2008 23:26 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Excellent! This time you read at least one line of my post. That's an improvement.
There's two more suggestions cleverly hidden amongst all of those words. I'll leave them there as Easter Eggs for you to discover and delight over in the future.
If you are going to ask for help, have the courtesy to at least read to the response.
Ross Leishman
|
|
|
|
| Re: Sql query tuning [message #316187 is a reply to message #316150] |
Thu, 24 April 2008 03:30 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Ross,
Can you please give me the suggestion for the following...
I want to solve this using Multi thread concept. Is it preferable? Please let me know. And I have already done using Partition concept and multi-thread concept.Since it was taking 34 mins. is there any other way to solve this?
Thank you.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 01:22:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
