This discussion is archived
11 Replies Latest reply: Dec 14, 2012 9:23 AM by rp0428 RSS

Batch Slow

user3266490 Newbie
Currently Being Moderated
Hi,

Db :11.2.0.3
Os :Aix 6

I am asked to monitor a batch which usally takes 4 hours.Last two days it took 9 hours.
Today we gather stats those tables which invlove in the batch.
Where to start monitor the bactch at OS level,db level?
Iam't interested to go with EM(sql profiler etc) without understanding what happened inside?

Thanks & Regards,
VN
  • 1. Re: Batch Slow
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    if you have the Diagnostic Pack License, then ASH is probably the best place to start. Just see what SQL took most time and what wait events were observed when running it:
    select sql_id, count(*) -- you can transform # of samples in approximate time in seconds by multiplying it by 10 
    from dba_hist_active_sess_history ash
    where session_id = :sid
    and sample_time between :starttime and :endtime
    group by sql_id
    order by 2 desc
    select event, count(*) -- you can transform # of samples in approximate time in seconds by multiplying it by 10 
    from dba_hist_active_sess_history ash
    where session_id = :sid
    and sample_time between :starttime and :endtime
    group by event
    order by 2 desc
    Best regards,
    Nikolay
  • 2. Re: Batch Slow
    P.Forstmann Guru
    Currently Being Moderated
    If you don't have Diagnostic Pack license, then Statspack is probably the best place to start.

    If not already installed, install it with instructions from <ORACLE_HOME>/rdbms/admin/spdoc.txt.
    Then you can:
    - use <ORACLE_HOME>/rdbms/admin/spreport.sql to generate 4 or 9 reports corresponding to the default snaphosts run every hour
    - in each report check the different SQL sections (SQL ordered by ...) to see which SQL statements took most time.

    About Statspack report interpretation please have a look to J. Lewis blog: http://jonathanlewis.wordpress.com/statspack-examples/.

    PS: message is a reply to OP and not to Nikolay.

    Edited by: P. Forstmann on 12 déc. 2012 19:33

    Edited by: P. Forstmann on 12 déc. 2012 19:42
  • 3. Re: Batch Slow
    xerosaburu Newbie
    Currently Being Moderated
    I'd run the process with a smaller result set to test using the event trace facility 10046 on the process id including the bind variables.

    Edited by: xerosaburu on Dec 12, 2012 1:45 PM
  • 4. Re: Batch Slow
    rp0428 Guru
    Currently Being Moderated
    >
    I am asked to monitor a batch which usally takes 4 hours.Last two days it took 9 hours.
    Today we gather stats those tables which invlove in the batch.
    Where to start monitor the bactch at OS level,db level?
    >
    I consider any batch process that doesn't perform logging for each step it executes to be a poorly designed and implemented process.

    The first thing I would do is modify the batch stream to add logging for each step. At a minimum the start time, step name and stop time should be logged somewhere for each step of the batch. Ideally this information would be logged into a DB table. Then it is simple to produce reports that show the runtime history for each step and you can see what steps are taking longer than usual.

    Since the time and effort to log the start/stop of each step is miniscule compared to the time and effort to track down the problems later there is no excuse, in my opinion, for not being proactive and capturing this information.

    I would assume you probably don't have execution plans for each step when the batch process ran well either do you?

    Part of what I consider to be best practices in designing and implementing batch processes:

    1. design/write/test each step independently
    2. capture and save the execution plan for each step once it performs satisfactorily. This will be your baseline when future problems occur.
    3. add code to capture start time, step name, stop time as part of the step
    4. create test data FOR EACH STEP so that every step can be tested independently without needing other steps that might normally execute first.
    5. As steps are completed test sequences of steps to ensure that you are getting the correct results at the end of each step. If you parallel what you might use for JUnit testing each step will have 'setup' (where you create any starting data needed for the test), 'execute' and 'teardown' (where you remove any data created by the test so that the test can be run again) stages.

    Then step #2 can use the data resulting from the execution of step #1. When you test the sequence 'execute step #1, step #2' you don not execute the step #1 teardown and that lets step #2 use the data created by step #1.

    Without baseline timing results and execution plans any analysis you do after a problem occurs is mostly going to be guesswork. You will probably get lucky and be able to find and fix that particular problem but all of that effort won't be of any use when the next problem occurs.
  • 5. Re: Batch Slow
    user3266490 Newbie
    Currently Being Moderated
    Hi,


    Thanks for your reply.

    The same batch takes 4 hours to complete in test database.But in production it takes 6 to 7 hours.
    The code is same in both evn.

    Thanks & Regards,
    VN
  • 6. Re: Batch Slow
    rp0428 Guru
    Currently Being Moderated
    >
    The same batch takes 4 hours to complete in test database.But in production it takes 6 to 7 hours.
    The code is same in both evn.
    >
    What is your point?

    Code is generally the same in all environments. What is usually different is the amount or distribution of data, the numbers and types of indexes, the existence of and status of the statistics and the environment itself (different hardware, storage systems, numbers of concurrent users).

    As I mentioned in my first reply performance is a comparative attribute. You have to compare performance now with performance at some prior time. You have to compare the current execution plan with a prior execution plan.

    Until you start doing that all you are doing is guessing. You can monitor the current process all you want but unless you have something from earlier runs to compare that to it still won't do you any good.

    You need to instrument the batch process as I suggested previously so that it logs each step it does, how many records it does it with and how long it takes to do it.

    You haven't provided any information about what your batch process is doing, how much data it is doing it with or whether you have any information at all about any particular part of the process that might be a contributing cause of any problem.

    Until you give us some information to work with how do you expect us to be of help?
  • 7. Re: Batch Slow
    user3266490 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.

    We don't know exactly what application team doing.Both of us sit in different location.
    We know only It's a parallel batch (mostly insert statement).The batch using around 12 tables.

    Test Env
                      Per Second     Per Transaction     Per Exec     Per Call
    DB Time(s):     4.4     0.8     0.00     0.12
    DB CPU(s):     1.2     0.2     0.00     0.03
    Redo size:     490,715.9     93,578.5            
    Logical reads:     228,528.0     43,579.8            
    Block changes:     2,408.5     459.3            
    Physical reads:     899.5     171.5            
    Physical writes:     807.3     154.0            
    User calls:     37.6     7.2            
    Parses:     1.4     0.3            
    Hard parses:     0.3     0.1            
    W/A MB processed:     37.1     7.1            
    Logons:     0.1     0.0            
    Executes:     1,693.9     323.0            
    Rollbacks:     0.0     0.0            
    Transactions:     5.2                  
    Production Env
    Load Profile
    
     Per Second Per Transaction Per Exec Per Call 
    DB Time(s): 14.8 2.7 0.02 0.18 
    DB CPU(s): 3.6 0.7 0.00 0.04 
    Redo size: 492,932.3 88,626.8     
    Logical reads: 862,428.9 155,060.5     
    Block changes: 3,053.6 549.0     
    Physical reads: 431.0 77.5     
    Physical writes: 60.1 10.8     
    User calls: 82.4 14.8     
    Parses: 18.7 3.4     
    Hard parses: 14.4 2.6     
    W/A MB processed: 7.1 1.3     
    Logons: 0.1 0.0     
    Executes: 747.2 134.4     
    Rollbacks: 0.0 0.0     
    Transactions: 5.6       
    
     
    what basis(threshold value) we need to compare the above value?

    How to compare execution plan with previous in OEM.

    Thanks & Regards,
    VN

    Edited by: user3266490 on Dec 14, 2012 6:01 AM
  • 8. Re: Batch Slow
    rp0428 Guru
    Currently Being Moderated
    >
    We don't know exactly what application team doing.Both of us sit in different location.
    >
    Again, what is your point? Amazing as it may seem, I am also sitting in a different location and yet you are communicating with me.

    How do you expect to troubleshoot a problem if you don't even know what the process is supposed to be doing? You need to know what the process is doing as well as HOW it does that when it is working properly. The HOW is what the actual execution plan, or trace file, will show you.
    >
    How to compare execution plan with previous in OEM.
    >
    There is no execution plan in what you posted.

    Also, you posted a snapshot but didn't provide ANY explanation as to what it actually represents.
  • 9. Re: Batch Slow
    user3266490 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.
    LAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    SQL_ID  0qfuprj5stcds, child number 0
    -------------------------------------
    INSERT INTO TP_STO( ITEM, LOC, EFF, DISC, FILTERLOGIC, SUPPLIER,
    CALCMETHOD, CALCSHARE, SUPPLYSHARE ) SELECT DISTINCT /*+ ORDERED*/
    SMX.ITEM, SMX.LOC, SMX.EFF, SMX.DISC, :B7 , SUP.SUPPLIER, CASE WHEN
    S.DEST = (SELECT PARAMVALUE1 FROM U_DS_PARAMETER WHERE PARAMTYPE = :B8
    AND PARAMVALUE1 = S.DEST AND M_DELETEDATE = :B2 ) THEN (SELECT
    PARAMVALUE2 FROM U_DS_PARAMETER WHERE PARAMTYPE = :B8 AND PARAMVALUE1 =
    S.DEST AND M_DELETEDATE = :B2 AND ROWNUM =1) ELSE 'W' || L.U_WHOLESALER
    END CALCMETHOD, CASE WHEN SMX.DISTRMODE = :B6 THEN SUP.SUPPLYSHARE WHEN
    SMX.DISTRMODE = :B5 THEN SUP.SUPPLYSHARE - NVL(SUP.DIRECTSHARE,0) END
    CALCSHARE, CASE WHEN SMX.DISTRMODE = :B6 THEN SUP.SUPPLYSHARE WHEN
    SMX.DISTRMODE = :B5 THEN SUP.SUPPLYSHARE - NVL(SUP.DIRECTSHARE,0) END
    SUPPLYSHARE FROM U_X_SU SMX, LOC L, U_SUPPLIER SUP, SOURCING S
    WHERE SMX.ITEM = SUP.ITEM AND SMX.SMXMARKET = SUP.MARKET AND SMX.SMXEFF
    = SUP.EFF AND L.LOC = S.DEST AND S.SOURCING = SMX.ITEM || S.DEST ||
    SUP.SUPPLIER || TO_CHAR(S.E
    
    Plan hash value: 2560186442
    
    ------------------------------------------------------------------------------
    ------------------------------
    | Id  | Operation                            | Name                | Rows  | B
    ytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    ------------------------------
    |   0 | INSERT STATEMENT                     |                     |       |
         | 37591 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL             |                     |       |
         |            |          |
    |*  2 |   INDEX RANGE SCAN                   | U_DS_PARAMETER_IDX1 |     1 |
      29 |     1   (0)| 00:00:01 |
    |*  3 |    COUNT STOPKEY                     |                     |       |
         |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID      | U_DS_PARAMETER      |     1 |
      35 |     2   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN                | U_DS_PARAMETER_IDX1 |     1 |
         |     1   (0)| 00:00:01 |
    |   6 |   HASH UNIQUE                        |                     |     1 |
     199 | 37591   (1)| 00:08:47 |
    |*  7 |    FILTER                            |                     |       |
         |            |          |
    |*  8 |     FILTER                           |                     |       |
         |            |          |
    |   9 |      NESTED LOOPS                    |                     |       |
         |            |          |
    |  10 |       NESTED LOOPS                   |                     |     1 |
     199 | 37581   (1)| 00:08:47 |
    |* 11 |        HASH JOIN                     |                     |  1488 |
     183K| 33112   (1)| 00:07:44 |
    |* 12 |         HASH JOIN                    |                     |  2301 |
     195K| 29245   (1)| 00:06:50 |
    |* 13 |          TABLE ACCESS BY INDEX ROWID | SOURCING            |  4185 |
     306K| 29049   (1)| 00:06:47 |
    |* 14 |           INDEX RANGE SCAN           | XIF3SOURCING        | 45752 |
         |    88   (2)| 00:00:02 |
    |  15 |          TABLE ACCESS FULL           | LOC                 | 23204 |
     271K|   194   (1)| 00:00:03 |
    |* 16 |         TABLE ACCESS FULL            | U_SUPPLIER      | 11848 |
     451K|  3867   (1)| 00:00:55 |
    |* 17 |        INDEX RANGE SCAN              | U_X_SU_PK         |     1 |
         |     2   (0)| 00:00:01 |
    |* 18 |         FILTER                       |                     |       |
         |            |          |
    |* 19 |          INDEX RANGE SCAN            | TP_STO_PK    |     1 |
     324 |     1   (0)| 00:00:01 |
    |* 20 |          COUNT STOPKEY               |                     |       |
         |            |          |
    |  21 |           TABLE ACCESS BY INDEX ROWID| U_DS_PARAMETER      |     1 |
      35 |     2   (0)| 00:00:01 |
    |* 22 |            INDEX RANGE SCAN          | U_DS_PARAMETER_IDX1 |     1 |
         |     1   (0)| 00:00:01 |
    |* 23 |       TABLE ACCESS BY INDEX ROWID    | U_X_SU            |     1 |
      73 |     3   (0)| 00:00:01 |
    |* 24 |     COUNT STOPKEY                    |                     |       |
         |            |          |
    |* 25 |      FILTER                          |                     |       |
         |            |          |
    |* 26 |       FILTER                         |                     |       |
         |            |          |
    |* 27 |        TABLE ACCESS BY INDEX ROWID   | SOURCING            |     1 |
      49 |     4   (0)| 00:00:01 |
    |* 28 |         INDEX RANGE SCAN             | XIF4SOURCING        |     1 |
         |     3   (0)| 00:00:01 |
    |* 29 |       COUNT STOPKEY                  |                     |       |
         |            |          |
    |* 30 |        TABLE ACCESS BY INDEX ROWID   | SOURCING            |     1 |
      30 |     4   (0)| 00:00:01 |
    |* 31 |         INDEX RANGE SCAN             | XIF3SOURCING        |     1 |
         |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    ------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("PARAMTYPE"=:B8 AND "PARAMVALUE1"=:B1 AND "M_DELETEDATE"=:B2)
       3 - filter(ROWNUM=1)
       5 - access("PARAMTYPE"=:B8 AND "PARAMVALUE1"=:B1 AND "M_DELETEDATE"=:B2)
       7 - filter("S"."DEST"=)
       8 - filter(:B4<:B3)
      11 - access("S"."SOURCE"="SUP"."SUPPLIER")
      12 - access("L"."LOC"="S"."DEST")
      13 - filter(("S"."U_DCGROUP" IS NOT NULL AND "S"."M_DELETEDATE"=:B2))
      14 - access("S"."ITEM">=:B4 AND "S"."ITEM"<:B3)
      16 - filter(("SUP"."ITEM"<:B3 AND "SUP"."ITEM">=:B4))
      17 - access("S"."ITEM"="SMX"."ITEM")
           filter(("SMX"."ITEM"<:B3 AND SUBSTR("LOC",1,3)='STO' AND "SMX"."ITEM">=
    :B4 AND
                  "SMX"."ITEM"="SUP"."ITEM" AND "S"."SOURCING"="SMX"."ITEM"||"S"."
    DEST"||"SUP"."SUPPLIER"||TO_CHAR(INT
                  ERNAL_FUNCTION("S"."EFF"),'YYYYMMDD') AND  IS NULL))
      18 - filter(("A"."CALCMETHOD"='W'||:B1 OR "A"."CALCMETHOD"=))
      19 - access("A"."ITEM"=:B1 AND "A"."LOC"=:B2 AND "A"."EFF"=:B3 AND "A"."SUPP
    LIER"=:B4)
      20 - filter(ROWNUM=1)
      22 - access("PARAMTYPE"=:B8 AND "PARAMVALUE1"=:B1 AND "M_DELETEDATE"=:B2)
      23 - filter(("SMX"."DCGROUP" IS NOT NULL AND ("SMX"."DISC"=:B2 OR "SMX"."DIS
    C">=:B1) AND
                  INTERNAL_FUNCTION("SMX"."DISTRMODE") AND "SMX"."M_DELETEDATE"=:B
    2 AND
                  "SMX"."SMXMARKET"="SUP"."MARKET" AND "SMX"."SMXEFF"="SUP"."EFF"
    AND
                  "S"."U_DCGROUP"="SMX"."DCGROUP"))
      24 - filter(ROWNUM=1)
      25 - filter( IS NOT NULL)
      26 - filter(:B1<=NVL(CASE :B2 WHEN :B2 THEN NULL ELSE :B3 END ,:B9))
      27 - filter(("A"."U_DCGROUP"=:B1 AND "A"."EFF">=:B2 AND "A"."M_DELETEDATE"=:
    B2 AND
                  "A"."U_SOURCINGTYPE"=2 AND "A"."EFF"<=NVL(CASE :B3 WHEN :B2 THEN
     NULL ELSE :B4 END ,:B9)))
      28 - access("A"."ITEM"=:B1 AND "A"."DEST"=:B2)
      29 - filter(ROWNUM=1)
      30 - filter(("B"."DEST"=:B1 AND "B"."U_DCGROUP"=:B2))
      31 - access("B"."ITEM"=:B1 AND "B"."SOURCE"=:B2)
    
    Note
    -----
       - dynamic sampling used for this statement (level=4)
       - automatic DOP: skipped because of IO calibrate statistics are missing
    
    
    97 rows selected.
    Test Env
    LAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    SQL_ID  0qfuprj5stcds, child number 0
    -------------------------------------
    INSERT INTO TP_STO( ITEM, LOC, EFF, DISC, FILTERLOGIC, SUPPLIER,
    CALCMETHOD, CALCSHARE, SUPPLYSHARE ) SELECT DISTINCT /*+ ORDERED*/
    SMX.ITEM, SMX.LOC, SMX.EFF, SMX.DISC, :B7 , SUP.SUPPLIER, CASE WHEN
    S.DEST = (SELECT PARAMVALUE1 FROM U_DS_PARAMETER WHERE PARAMTYPE = :B8
    AND PARAMVALUE1 = S.DEST AND M_DELETEDATE = :B2 ) THEN (SELECT
    PARAMVALUE2 FROM U_DS_PARAMETER WHERE PARAMTYPE = :B8 AND PARAMVALUE1 =
    S.DEST AND M_DELETEDATE = :B2 AND ROWNUM =1) ELSE 'W' || L.U_WHOLESALER
    END CALCMETHOD, CASE WHEN SMX.DISTRMODE = :B6 THEN SUP.SUPPLYSHARE WHEN
    SMX.DISTRMODE = :B5 THEN SUP.SUPPLYSHARE - NVL(SUP.DIRECTSHARE,0) END
    CALCSHARE, CASE WHEN SMX.DISTRMODE = :B6 THEN SUP.SUPPLYSHARE WHEN
    SMX.DISTRMODE = :B5 THEN SUP.SUPPLYSHARE - NVL(SUP.DIRECTSHARE,0) END
    SUPPLYSHARE FROM U_X_SU SMX, LOC L, U_SUPPLIER SUP, SOURCING S
    WHERE SMX.ITEM = SUP.ITEM AND SMX.SMXMARKET = SUP.MARKET AND SMX.SMXEFF
    = SUP.EFF AND L.LOC = S.DEST AND S.SOURCING = SMX.ITEM || S.DEST ||
    SUP.SUPPLIER || TO_CHAR(S.E
    
    Plan hash value: 2560186442
    
    ------------------------------------------------------------------------------
    ------------------------------
    | Id  | Operation                            | Name                | Rows  | B
    ytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    ------------------------------
    |   0 | INSERT STATEMENT                     |                     |       |
         | 37591 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL             |                     |       |
         |            |          |
    |*  2 |   INDEX RANGE SCAN                   | U_DS_PARAMETER_IDX1 |     1 |
      29 |     1   (0)| 00:00:01 |
    |*  3 |    COUNT STOPKEY                     |                     |       |
         |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID      | U_DS_PARAMETER      |     1 |
      35 |     2   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN                | U_DS_PARAMETER_IDX1 |     1 |
         |     1   (0)| 00:00:01 |
    |   6 |   HASH UNIQUE                        |                     |     1 |
     199 | 37591   (1)| 00:08:47 |
    |*  7 |    FILTER                            |                     |       |
         |            |          |
    |*  8 |     FILTER                           |                     |       |
         |            |          |
    |   9 |      NESTED LOOPS                    |                     |       |
         |            |          |
    |  10 |       NESTED LOOPS                   |                     |     1 |
     199 | 37581   (1)| 00:08:47 |
    |* 11 |        HASH JOIN                     |                     |  1488 |
     183K| 33112   (1)| 00:07:44 |
    |* 12 |         HASH JOIN                    |                     |  2301 |
     195K| 29245   (1)| 00:06:50 |
    |* 13 |          TABLE ACCESS BY INDEX ROWID | SOURCING            |  4185 |
     306K| 29049   (1)| 00:06:47 |
    |* 14 |           INDEX RANGE SCAN           | XIF3SOURCING        | 45752 |
         |    88   (2)| 00:00:02 |
    |  15 |          TABLE ACCESS FULL           | LOC                 | 23204 |
     271K|   194   (1)| 00:00:03 |
    |* 16 |         TABLE ACCESS FULL            | U_SUPPLIER      | 11848 |
     451K|  3867   (1)| 00:00:55 |
    |* 17 |        INDEX RANGE SCAN              | U_X_SU_PK         |     1 |
         |     2   (0)| 00:00:01 |
    |* 18 |         FILTER                       |                     |       |
         |            |          |
    |* 19 |          INDEX RANGE SCAN            | TP_STO_PK    |     1 |
     324 |     1   (0)| 00:00:01 |
    |* 20 |          COUNT STOPKEY               |                     |       |
         |            |          |
    |  21 |           TABLE ACCESS BY INDEX ROWID| U_DS_PARAMETER      |     1 |
      35 |     2   (0)| 00:00:01 |
    |* 22 |            INDEX RANGE SCAN          | U_DS_PARAMETER_IDX1 |     1 |
         |     1   (0)| 00:00:01 |
    |* 23 |       TABLE ACCESS BY INDEX ROWID    | U_X_SU            |     1 |
      73 |     3   (0)| 00:00:01 |
    |* 24 |     COUNT STOPKEY                    |                     |       |
         |            |          |
    |* 25 |      FILTER                          |                     |       |
         |            |          |
    |* 26 |       FILTER                         |                     |       |
         |            |          |
    |* 27 |        TABLE ACCESS BY INDEX ROWID   | SOURCING            |     1 |
      49 |     4   (0)| 00:00:01 |
    |* 28 |         INDEX RANGE SCAN             | XIF4SOURCING        |     1 |
         |     3   (0)| 00:00:01 |
    |* 29 |       COUNT STOPKEY                  |                     |       |
         |            |          |
    |* 30 |        TABLE ACCESS BY INDEX ROWID   | SOURCING            |     1 |
      30 |     4   (0)| 00:00:01 |
    |* 31 |         INDEX RANGE SCAN             | XIF3SOURCING        |     1 |
         |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    ------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("PARAMTYPE"=:B8 AND "PARAMVALUE1"=:B1 AND "M_DELETEDATE"=:B2)
       3 - filter(ROWNUM=1)
       5 - access("PARAMTYPE"=:B8 AND "PARAMVALUE1"=:B1 AND "M_DELETEDATE"=:B2)
       7 - filter("S"."DEST"=)
       8 - filter(:B4<:B3)
      11 - access("S"."SOURCE"="SUP"."SUPPLIER")
      12 - access("L"."LOC"="S"."DEST")
      13 - filter(("S"."U_DCGROUP" IS NOT NULL AND "S"."M_DELETEDATE"=:B2))
      14 - access("S"."ITEM">=:B4 AND "S"."ITEM"<:B3)
      16 - filter(("SUP"."ITEM"<:B3 AND "SUP"."ITEM">=:B4))
      17 - access("S"."ITEM"="SMX"."ITEM")
           filter(("SMX"."ITEM"<:B3 AND SUBSTR("LOC",1,3)='STO' AND "SMX"."ITEM">=
    :B4 AND
                  "SMX"."ITEM"="SUP"."ITEM" AND "S"."SOURCING"="SMX"."ITEM"||"S"."
    DEST"||"SUP"."SUPPLIER"||TO_CHAR(INT
                  ERNAL_FUNCTION("S"."EFF"),'YYYYMMDD') AND  IS NULL))
      18 - filter(("A"."CALCMETHOD"='W'||:B1 OR "A"."CALCMETHOD"=))
      19 - access("A"."ITEM"=:B1 AND "A"."LOC"=:B2 AND "A"."EFF"=:B3 AND "A"."SUPP
    LIER"=:B4)
      20 - filter(ROWNUM=1)
      22 - access("PARAMTYPE"=:B8 AND "PARAMVALUE1"=:B1 AND "M_DELETEDATE"=:B2)
      23 - filter(("SMX"."DCGROUP" IS NOT NULL AND ("SMX"."DISC"=:B2 OR "SMX"."DIS
    C">=:B1) AND
                  INTERNAL_FUNCTION("SMX"."DISTRMODE") AND "SMX"."M_DELETEDATE"=:B
    2 AND
                  "SMX"."SMXMARKET"="SUP"."MARKET" AND "SMX"."SMXEFF"="SUP"."EFF"
    AND
                  "S"."U_DCGROUP"="SMX"."DCGROUP"))
      24 - filter(ROWNUM=1)
      25 - filter( IS NOT NULL)
      26 - filter(:B1<=NVL(CASE :B2 WHEN :B2 THEN NULL ELSE :B3 END ,:B9))
      27 - filter(("A"."U_DCGROUP"=:B1 AND "A"."EFF">=:B2 AND "A"."M_DELETEDATE"=:
    B2 AND
                  "A"."U_SOURCINGTYPE"=2 AND "A"."EFF"<=NVL(CASE :B3 WHEN :B2 THEN
     NULL ELSE :B4 END ,:B9)))
      28 - access("A"."ITEM"=:B1 AND "A"."DEST"=:B2)
      29 - filter(ROWNUM=1)
      30 - filter(("B"."DEST"=:B1 AND "B"."U_DCGROUP"=:B2))
      31 - access("B"."ITEM"=:B1 AND "B"."SOURCE"=:B2)
    
    Note
    -----
       - dynamic sampling used for this statement (level=4)
       - automatic DOP: skipped because of IO calibrate statistics are missing
    
    
    97 rows selected.
    Thanks & Regards,
    VN
  • 10. Re: Batch Slow
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    your plans are identical (see the plan hash value). The difference in performance is probably coming from different hardware, workload or data size. Getting back to your original question -- you were wondering about the job that used to take X hours suddently taking more than that. Then what you should be comparing is the plan when is was performing better with the current plan. In addition to that, I would also look at I/O stats from DBA_HIST_SQLSTAT (requires Diagnostic Pack License).

    Best regards,
    Nikolay
  • 11. Re: Batch Slow
    rp0428 Guru
    Currently Being Moderated
    And now you make another post of stuff with NO explanation at all of what it is you posted or what your issue or problem is with it.

    You also appear to have simply posted the same plan info twice since each plan is IDENTICAL; every single character is the same.

    I copied each plan out and saved it as a file and when you compare the two files there are NO DIFFERENCES at all.

    The most likely explanation for that is that you just posted two copies of the same thing.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points