This discussion is archived
1 2 Previous Next 15 Replies Latest reply: May 2, 2013 7:01 AM by $phinx19 RSS

PT question

$phinx19 Newbie
Currently Being Moderated
Hi All,

I found this question on of the sites, I need your expert inputs on this.
We cannot rewrite the query due to business logic and statistics gather and index creation is correct for this query.This query is slow in giving result? How to troubleshoot it?
Thanks
  • 1. Re: PT question
    rp0428 Guru
    Currently Being Moderated
    You can find lots of questions on lots of sites.

    What is YOUR question or issue?

    Please read the FAQ to see how to post a question.
  • 2. Re: PT question
    KR10822864 Pro
    Currently Being Moderated
    $phinx19 wrote:
    Hi All,

    I found this question on of the sites, I need your expert inputs on this.
    We cannot rewrite the query due to business logic and statistics gather and index creation is correct for this query.This query is slow in giving result? How to troubleshoot it?
    Thanks
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 3. Re: PT question
    KR10822864 Pro
    Currently Being Moderated
    post oracle/os version ?
    what exactly your looking for?
  • 4. Re: PT question
    $phinx19 Newbie
    Currently Being Moderated
    LOL

    Just missed it!

    OS: AIX
    DB:11.2.0.1

    Regards,
  • 5. Re: PT question
    rp0428 Guru
    Currently Being Moderated
    You STILL need to actually ask a question and post the relevant details about it.
  • 6. Re: PT question
    $phinx19 Newbie
    Currently Being Moderated
    Hi ,

    This is the question:
    There is a random query and due to business logic we cannot rewrite this query and also the index creation is correct for this query, this query was earlier running fine but suddenly its performance has been a concern. So my question is as a DBA, What should be the approach that one should have to troubleshoot this problem.
    Hope my question is good this time around.

    Regards,
  • 7. Re: PT question
    1004913 Newbie
    Currently Being Moderated
    This is the question:
    
    There is a random query and due to business logic we cannot rewrite this query and also the index creation is correct for this query, this query was earlier running fine but suddenly its performance has been a concern. So my question is as a DBA, What should be the approach that one should have to troubleshoot this problem.
    
    Hope my question is good this time around.
    
    Regards,
    This is pretty question which generally DBA will rise, and this open question from develper as well. As a DBA we need verify whats went wrong in the query at paticular moment, so there will be N number of problems - Generally i do is i will take trace the query/session and verify tkproof of that. Also at that paticular interval i will take AWR report as well and see the wait events as well
  • 8. Re: PT question
    Vishnusivathej Newbie
    Currently Being Moderated
    There are many questions that you might have to answer first.

    1. Is the query performing well before and if the slowness is recent then were there any plan changes.
    2. If plan changes then what is the reason for this.
    3. Have you looked at the waits that this SQL have been waiting on apart from the plan changes.
    4. Were there any statistics collection, upgrade or any application related changes done recently.
    5. What are the sizes of the base tables (if they are steadily increasing have you considered partitioning the tables/indexes).
    6. if the base query cannot be rewritten have you considered the option of materialized views.
    7. If plan changes did you try to create a SQL plan baseline to check if the old plan is working well.

    and even more...

    thanks,
    Vishnu
  • 9. Re: PT question
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    troubleshooting any performance problem always starts with the same first step: making the right diagnosis. You need to find whether the amount of work performed by the database to process your query is adequate, given the amount of data requested and the way data is organized in the database, and then based on your findings, come up with the best way to improve the situation within your constraints (e.g. in your case -- the query text has to remain the same).

    Best regards,
    Nikolay
  • 10. Re: PT question
    User477708-OC Journeyer
    Currently Being Moderated
    $phinx19 wrote:
    Hi ,

    This is the question:
    There is a random query and due to business logic we cannot rewrite this query and also the index creation is correct for this query, this query was earlier running fine but suddenly its performance has been a concern. So my question is as a DBA, What should be the approach that one should have to troubleshoot this problem.
    Hope my question is good this time around.

    Regards,
    So a developer is able to tell you the DBA that the index is correct? that's my starting point right there (without telling developer). Is the query using the index? how can the developer tell? Has he tracing capabilities on your production database? As others have said, trace it, explain plan it, get the waits.
  • 11. Re: PT question
    rp0428 Guru
    Currently Being Moderated
    >
    There is a random query and due to business logic we cannot rewrite this query and also the index creation is correct for this query, this query was earlier running fine but suddenly its performance has been a concern. So my question is as a DBA, What should be the approach that one should have to troubleshoot this problem.
    >
    Sounds like this was an interview question because you seem don't seem to remember exactly what was ask and seem to be making this up as you go.

    First you said
    >
    statistics gather and index creation is correct
    >
    but now you say NOTHING about the statistics.

    The first step is always to make sure you even have a problem.

    The first part of that is to gather all of the available facts. This is not a fact:
    >
    suddenly its performance has been a concern
    >
    That has NO meaning and NO value. So my initial questions would be to find out what that even means.

    1. A concern to who?
    2. Why is it a concern?
    3. What is it about the query that makes someone, somewhere think there is an issue?

    A change in performance does NOT automatically mean there is a problem. The query might have been returning 10 rows before and now it has to return 1 million. It will now be slower. But that doesn't mean there is a problem.

    Performance changes when some other factor(s) changes. You need to examine all of the things that might have changed.
  • 12. Re: PT question
    Aman.... Oracle ACE
    Currently Being Moderated
    I know that OP hasn't asked a proper question but if we just consider the exact text and in the answer, say that one can use STA and if suggested by it, can implement sql profile on the query (which is expected to have a better plan) , it should give some benefit ? May be that's what interviewer might be willing to hear if he asked such kind of question?

    Regards
    Aman....
  • 13. Re: PT question
    rp0428 Guru
    Currently Being Moderated
    >
    if we just consider the exact text and in the answer, say that one can use STA and if suggested by it, can implement sql profile on the query (which is expected to have a better plan) , it should give some benefit ? May be that's what interviewer might be willing to hear if he asked such kind of question?
    >
    Personally I have never much cared for those 'how would you tune this query' style of questions. The person/interviewer asking the question never wants to provide any info about why the query even needs to be tuned. I see far too many people trying to 'tune' a query that doesn't need tuned. More often the problem is not the query but the environment: missing/invalid stats, batch loads of data, missing indexes.

    I never believe anyone that tells me it is the query unless I see something to back it up.
  • 14. Re: PT question
    Aman.... Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    >
    if we just consider the exact text and in the answer, say that one can use STA and if suggested by it, can implement sql profile on the query (which is expected to have a better plan) , it should give some benefit ? May be that's what interviewer might be willing to hear if he asked such kind of question?
    >
    Personally I have never much cared for those 'how would you tune this query' style of questions. The person/interviewer asking the question never wants to provide any info about why the query even needs to be tuned. I see far too many people trying to 'tune' a query that doesn't need tuned. More often the problem is not the query but the environment: missing/invalid stats, batch loads of data, missing indexes.

    I never believe anyone that tells me it is the query unless I see something to back it up.
    Yep, completely agree but unfortunately, I see too many such kind of questions thrown at the time of the interview and even otherwise when one has to "show" that how well he/she knows about a certain parameter or a hint and can do just magic with a badly performing query :) .

    Regards
    Aman....
1 2 Previous Next

Legend

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