Home » RDBMS Server » Performance Tuning » Sql query tuning (9.0.4.0)
Sql query tuning [message #313905] Tue, 15 April 2008 02:16 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #314614 is a reply to message #313905] Thu, 17 April 2008 05:24 Go to previous messageGo to next message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

can you attach the explainplan in a text file

gopu
Re: Sql query tuning [message #314623 is a reply to message #314614] Thu, 17 April 2008 05:41 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Gopu,
Explain plan is mentioned above..

Thank you
Re: Sql query tuning [message #314628 is a reply to message #313905] Thu, 17 April 2008 05:48 Go to previous messageGo to next message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

yes dear,
but it is not in a proper order to read

gopu
Re: Sql query tuning [message #314643 is a reply to message #314628] Thu, 17 April 2008 06:11 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Gopu,

Here I have attached the Explain plan Doc.
  • Attachment: explan.txt
    (Size: 3.05KB, Downloaded 269 times)
Re: Sql query tuning [message #314648 is a reply to message #313905] Thu, 17 April 2008 06:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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.
Previous Topic: Performance Tuning
Next Topic: How to check for Null values
Goto Forum:
  


Current Time: Fri Dec 02 22:55:18 CST 2016

Total time taken to generate the page: 0.14743 seconds