1 2 3 Previous Next 39 Replies Latest reply: Mar 5, 2014 9:00 AM by jgarry RSS

Performance issue again

petra-K Expert
Currently Being Moderated

Hi All,

 

Just today weekend Sunday while sleeping at 3am, the computer operator called saying the batch jobs which took  usual1 hr to complete, has exceeding 3hrs processing time and still did not complete. This will miss the deadlines in sending reports to the clients. I tried restarting the database as the first line of solution but still the report when restarted exceeds 1 hr, I tried gathering schema stats but still I can see high cpu  usage using "topas" monitor. I tried running addmrpt and this is the output: https://app.box.com/s/hqncp3hhbxv0kbpz8pwq

 

How do I run sql tuning advisor for these slow queries using thier "SQL_ID"? Because the query is very long to enter and has variable inputs :value.

How do I generate its sqlplan using awr?

If you are in my shoes how do u attached sql queries issues?

 

Thanks a lot,

pK

  • 2. Re: Performance issue again
    Alvaro Pro
    Currently Being Moderated
    Just today weekend Sunday while sleeping at 3am, the computer operator called saying the batch jobs which took  usual1 hr to complete, has exceeding 3hrs processing time and still did not complete.

     

    This could happen for a myriad of reasons. To name a few: database-level concurrency, low resources available on host due to other jobs, recent change of optimizer plans, etc.

     

    To begin, I would focus on the plan of these queries, along with their ash reports for the wait events. Run an SQL AWR on them to see if the plan has changed recently. After that you should have a good idea of what the bottleneck is.

     

    Regards,

     

    Álvaro

  • 3. Re: Performance issue again
    rp0428 Guru
    Currently Being Moderated
    Just today weekend Sunday while sleeping at 3am, the computer operator called saying the batch jobs which took  usual1 hr to complete, has exceeding 3hrs processing time and still did not complete. This will miss the deadlines in sending reports to the clients. I tried restarting the database as the first line of solution but still the report when restarted exceeds 1 hr, I tried gathering schema stats but still I can see high cpu  usage using "topas" monitor. I tried running addmrpt and this is the output: https://app.box.com/s/hqncp3hhbxv0kbpz8pwq

     

    How do I run sql tuning advisor for these slow queries using thier "SQL_ID"? Because the query is very long to enter and has variable inputs :value.

    How do I generate its sqlplan using awr?

    If you are in my shoes how do u attached sql queries issues?

    IMHO you have a serious flaw in your batch architecture (based only on what you have posted of course).

     

    You either haven't instrumented those 'batch jobs' at all or your instrumentation isn't collecting the data you need.

     

    ETL processes simply MUST BE instrumented. The extent of that implementation depends on the complexity of the processing that the processes do.

     

    At a minimum that instrumentation should include the logging/capture of:

     

    1. the start time of each step of the process

    2. the end time of each step of the process

    3. the number of rows affected by each step of the process

     

    That basic information can easily tell you how long each step of each process takes to execute. Using that info you can chart/track typical execution times and determine when those times get out of whack.

     

    Another facet of instrumentation is being able to dynamically alter the logging level so that the actual queries being executed can be captured when necessary.

     

    So in DEBUG mode the logging instrumentation would capture those 'variable inputs' and the actual query itself. Then you can easily execute the query manually to generate execution plans. That is the standard method of developing ETL processes, especially if those processes use ANY dynamic sql to construct/execute queries.

     

    My suggestion is that the first step you take should be to fix that fundamental flaw in the architecture and add at least minimal instrumentation to those processes to begin capturing the key metrics about them.

     

    Performance tuning is all about COMPARISON: performance/plan NOW versus performance/plan BEFORE. Without that instrumentation you have no before to compare anything to: that 'which took  usual1 hr to complete' is only someones 'guess' as to how long it took and, even then, is only about the total time and not the individual steps.

     

    I know the above isn't the answer to the specific question you ask but it is what I would do first in your situation. You need to begin capturing the information now so that you are in a better position in the future to know what is happening.

  • 4. Re: Performance issue again
    petra-K Expert
    Currently Being Moderated

    Thanks all.

    Its so complicated a process and analysis . I thought  oracle being an intelligent db with its new hi-tech tools like the auto tuning advisor  will just tell me " create indexes on these columns" .

     

    My hunch or suspesions is that the query need more indexes to speed up. Can you help me get the explain plan using SQL_ID. so that I can see which columns does it use full table scans?

  • 5. Re: Performance issue again
    sb92075 Guru
    Currently Being Moderated

    unwilling or incapable to Read The Fine Manual yourself?

     

    see URL below

     

    Oracle Database Search Results: explain plan

  • 6. Re: Performance issue again
    rp0428 Guru
    Currently Being Moderated
    Its so complicated a process and analysis

    It doesn't need to be. Nothing I said in my reply is 'complicated'; those steps are very simple and easy to implement.

     

    Building a sand castle on the beach without having a detailed drawing is fun. If you make mistakes the tide just washes them away.

     

    But would you let a contractor build you a house without a blueprint? You might wind up with a toilet without a sewer connection.

     

    Then when you flush the toilet you get @#*% all over the floor.

     

    That's what happens when you let developers write code without a technical requirements doc.

     

    You get @#*% all over the place.

    Its so complicated a process and analysis

    Yep! @#*% is a mess to clean up. Especially if you have to do it on your hands and knees by yourself!

  • 7. Re: Performance issue again
    petra-K Expert
    Currently Being Moderated

    Thanks rp, al, sb

     

    So this is all the fault of the developers? Why are the operators calling me when it got performance issue? Should I tell him to call the developers to tune their prograns. They are not testing their programs first before taking to PROD. But sometimes I can not blame them because our clients are so demanding that they make change request in an hour and need to be implemented the next hour or next day. How can you have time to test? It is also based on the facts that the Dev knew the code very well that they knew the performance effect of the changes they made. They have done it for a long time now. Sometimes performance is okay sometimes not.

     

    The operators said the performance was good a week ago? So If I have awr report of same time a week  ago and compare it today, How do I inspect them?

     

    Thanks

  • 8. Re: Performance issue again
    sb92075 Guru
    Currently Being Moderated

    Repeat after me. "We have met the enemy, and they is us."

     

    Ready, Fire, AIM is not a sound software maintenance approach.

     

    If things ran OK in the past, then a place to start looking is at at code that has recently changed.

  • 9. Re: Performance issue again
    petra-K Expert
    Currently Being Moderated

    Thanks Sb,

     

    So If I know that the sql plan was changed, whether due to  some required modification or not, then what will I do?

     

    I have seen before AWRRPT that auto generates and EXPLAIN PLAN report, especially the top sql resource intensive. How do I do that?

    My awrrpt does not generates any.

  • 10. Re: Performance issue again
    sb92075 Guru
    Currently Being Moderated

    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

     

    Thanks Sb,

     

    So If I know that the sql plan was changed, whether due to  some required modification or not, then what will I do?

     

    It depends upon what specifically changed.

     

    Did SQL change?

    Did data change?

     

    when all else fails Read The Fine Manual (in URL below)

     

    Contents

  • 11. Re: Performance issue again
    petra-K Expert
    Currently Being Moderated

    Hi Sb and All,

     

    Is the ADDMRPT report I posted above showed the problematic query? Or I need to ask the operators what program they had run?

    What I mean, is the result in ADDMRPT not so "reliable", meaning does not hit the target problematic query?

     

    Thanks,

  • 13. Re: Performance issue again
    ABOracle Explorer
    Currently Being Moderated

    Your addm report shows that a2g4xxjcwrs6n sql_id takes 35 mins 7*301=2107 seconds during 5-6 window. It appears to be sub-optimal query.

     

    You need to get execution plan for the same sql_id from awr repository. If awr retention is higher, you might see multiple plan hash values of this query. Problem appears when query executes with sub-optimal execution plan. Couple of ways to solve such issues:

     

    1/ Use SPM to stabilize the query with best plan

    2/ introduce Index or some hint

    3/ check with histogram related stats available for all necessary columns.

    4/ Identify whether there is any index which are not efficient still present in the table and generates sub-optimal plan, plan to drop

    5/ introduce a new index

     

    Thanks

  • 14. Re: Performance issue again
    petra-K Expert
    Currently Being Moderated

    Thanks abo,

     

    I. Do you know how to get the explain plan using AWR?

1 2 3 Previous Next

Legend

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