This discussion is archived
10 Replies Latest reply: Sep 18, 2013 12:21 AM by BillyVerreynne RSS

Procedure run time is different.!

SudhakarR Newbie
Currently Being Moderated

Hi All,

 

I have procedure which contains insert,delete,update statements for different tables. During the night batch, we are running this same procedure for different countries one by one. Except last country/run(total 48mins), all other countries/runs are taking very less time. Obviously, the last country is having more volumn than other country. but if I rerun the same procedure for last country, its taking very less time(13mins). So summary of issue as normal run is 48mins, but rerun is 13mins only. I took below steps for this issue, but no solve so far.

 

* monitor through vsql during the normal run, transaction is keep happening and not stuck anywhere.

* collected stats on table level and schema level.

* tried to rebuild the indexes.

* though I did flush shared pool, it took only 13-14mins in the 2nd run.

 

not sure where its stucking and what I need to do next?

 

Can anyone help me for this issue?

 

Thanks and Regards,

Sudhakar.M

 

 

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

  • 1. Re: Procedure run time is different.!
    SudhakarR Newbie
    Currently Being Moderated

    Can anyone suggest something for this issue? it will be really helpful for me.

     

    Thanks.

  • 2. Re: Procedure run time is different.!
    Ishan Journeyer
    Currently Being Moderated

    Check this out:

     

    DBMS_PROFILER

    DBMS_TRACE

     

    These will help you identify the bottlenecks in the Procedure.

     

    Thanks,

    Ishan

  • 3. Re: Procedure run time is different.!
    EdStevens Guru
    Currently Being Moderated

    KMSudhakar wrote:

     

    Hi All,

     

    I have procedure which contains insert,delete,update statements for different tables. During the night batch, we are running this same procedure for different countries one by one. Except last country/run(total 48mins), all other countries/runs are taking very less time. Obviously, the last country is having more volumn than other country. but if I rerun the same procedure for last country, its taking very less time(13mins). So summary of issue as normal run is 48mins, but rerun is 13mins only. I took below steps for this issue, but no solve so far.

     

    * monitor through vsql during the normal run, transaction is keep happening and not stuck anywhere.

    * collected stats on table level and schema level.

    * tried to rebuild the indexes.

    * though I did flush shared pool, it took only 13-14mins in the 2nd run.

     

    not sure where its stucking and what I need to do next?

     

    Can anyone help me for this issue?

     

    Thanks and Regards,

    Sudhakar.M

     

     

    BANNER

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

     

    Perhaps some (a lot) of the work done on the initial run simply doesn't occur on the re-run ... inserts and updates that don't have to happen on the re-run

  • 4. Re: Procedure run time is different.!
    sb92075 Guru
    Currently Being Moderated

    post both EXPLAIN PLAN  when fast & slow

  • 5. Re: Procedure run time is different.!
    davidp 2 Pro
    Currently Being Moderated

    "During the night batch" ... it takes 48 minutes, rerun only takes 13 minutes

    A common reason is because it's running while your system or database backups are running, so the system disks or SAN are really busy. When you re-run it the backups aren't running and the procedure can run faster.

    Ask your system administrator and DBA when the backups happen.

    Last year I had similar problems, and it was other systems' backups on the same SAN, although different disks on the SAN, saturating the SAN processor.

  • 6. Re: Procedure run time is different.!
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    To add to Dave's response - you need to

    • look at the AMOUNT of work done (should be the same from your description)
    • look at the WAIT STATES of each (this seems to vary greatly from your description)

     

    As Dave said, the batch run can have very high I/O wait times due to SAN contention, whereas the rerun does not.

  • 7. Re: Procedure run time is different.!
    SudhakarR Newbie
    Currently Being Moderated

    our night batch will start only after backup is completed. no backups are running at that time.

  • 8. Re: Procedure run time is different.!
    SudhakarR Newbie
    Currently Being Moderated

    am not sure about SAN contention. do I need to contact DBA for the same? or please share how to verify the same.

     

    Thanks.

  • 9. Re: Procedure run time is different.!
    SudhakarR Newbie
    Currently Being Moderated

    one more thing, I want to share, not sure this will cause for this issue.

     

    we are trying to insert/update records through triggers which are in another schema. the purpose of trigger is validation. once validation is completed, only the valid record will go to target tables which are in same schema where the procedure is running.

     

    so,

     

    run the procedure from schema A,

    do the validation by triggers from schema B (like interface). These interface tables dont hold any records always.

    if data look good, move to target table in schema A. if not, move to error table in A.

     

    I collected stats on both schema(A,B), but no help for this issue.

     

    is there anyother way to make the schema in good condition? Please suggest.

     

    Thanks.

  • 10. Re: Procedure run time is different.!
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    KMSudhakar wrote:

    am not sure about SAN contention. do I need to contact DBA for the same?

    What would this achieve if you do not know whether SAN I/O is a bottleneck.

     

    As I mentioned already, you need to identify the workload. Is the workload the same for the batch run as it is for the rerun? Data volumes are seldom static. So in order to compare the batch and rerun processes, you need to confirm that they are each doing the same workload in order for a meaningful comparison.

     

    Secondly, you need to determine what the batch process is doing. How much I/O is done? How much CPU is used? How much time is spend on waiting for some resource or some lock or latch? Each process has events (stuff that happens) and wait state (stuff waited on). The virtual performance view details these. And this is needed to determine what the process is doing.

     

    If it is spending little time on I/O and not waiting on I/O, looking at SAN as the potential problem will be a pretty useless exercise.

     

     

    we are trying to insert/update records through triggers which are in another schema. the purpose of trigger is validation. once validation is completed, only the valid record will go to target tables which are in same schema where the procedure is running.

    This is likely a major overhead in processing. Triggers are not the best answer in many cases.

     

    is there anyother way to make the schema in good condition?

    Why do you blame the schema for being in a "bad" condition? I'm pretty sure that what is bad is the design of the tables, the use of triggers, and PL/SQL and SQL code design.

     

    You have not provided any evidence to say that this is not the case. The fact that the rerun is faster than batch run, is meaningless without context. Can these processes be compared directly (does it have the same workload)? Are the same resources available (e.g. does a backup cause an I/O bottleneck that does not exist with the rerun).

     

    Performance Tuning Overview : Principles

    Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck.

     

    You have not identified the problem. And without knowing what the problem is, tuning cannot be done. And adhoc gathering schema stats is not going to fix this unkown problem. Make it a known problem first.

Legend

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