1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query running slow

Discussion in 'SQL PL/SQL' started by mukulverma2408, Oct 19, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi Experts,

    Well this was asked to me in an interview and I am not sure how to answer it properly :
    Code (Text):

    I have a query which is using 100 tables and multiple joins in between them,
    it was working fine till now but today it's started working very slow,
    the same query which usually takes 30-35 minutes is now taking 4-5 hours
    to complete. what could be the possible reason for this??
     
    Note - All other statics like data in table, table index, load on database server etc. remains same.

    I have tried explaining that change in index or load on system or locking of table could be the possible reason for same, but he doesn't seems to be impressed.

    Can you help me to identify what else could have been checked in this scenario.

    Please Note - Since it's a hypothetical scenario, please don't ask me for for table schema or query being used, i just want to know the best approach that can be used in this situation.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi Sergey,

    Thanks for your reply, as i have said it's a hypothetical scenario, this was asked to me in an interview and i want to know to things that can be checked in this situation.

    I am using oracle 11G.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First the scenario is seriously flawed because there is no possibility that nothing has changed -- SOMETHING is different to produce that result. Additionally there isn't enough information to accurately diagnose WHY there is slow performance. Information such as Oracle version, whether the query is using bind variables, how current the statistics are all contribute to the performance. Newer versions of Oracle use bind variable peeking and cursor sharing to maintain performance; multiple cursors can exist for a given bind variable query depending upon the 'peeked' values, since some values point the optimizer to an index-access path where others point to full table scans. The optimizer is also known to 'flop' plans for no discernible reason in such cases so an index access path may be chosen for a set of predicate values better suited to a full table scan, which can double the work Oracle is doing (reading the index, reading the table rather than scanning the table directly) affecting performance. The question, as you posted it, is incomplete and can be 'answered' in many valid ways. It appears the interviewer doesn't know Oracle and is asking questions found on some website and relying solely on the 'answers' provided by his chosen source. I've seen such websites and many times they do not delve into more than one possibility and tout that as THE solution.

    Your best bet is to download Oracle, set up a situation similar to what was posed in that question, then test, test, test, change data, change statistics, use bind variables and see what various levels of performance you achieve. You'll be better prepared for such questions in the future and you will be less likely to doubt your own findings.
     
    mukulverma2408 likes this.
  5. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Zargon, will definitely going to replicate the same.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Unambiguously it is impossible to answer your question.
    Questions of optimization usually difficult questions and can be also solved differently. The only recipe doesn't exist.
    There is the general way of search of the solution of questions.
    This methodology it is well described in documentation

    For acquaintance : Database Performance Tuning Guide
     
    mukulverma2408 likes this.
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Well -- in an interview some years ago, I was asked a question that -- while on a completely separate topic -- was equally vague to the point of being unanswerable. The conversation went something like this:

    Interviewer: If you were going to purchase a server to house the Oracle database we want, what would be the cost?
    Me: Server prices vary hugely based on their specifications. In order to get even into a ballpark range, I need to be able to get an idea of how much server you need. How many users will be on the system at average and peak loads?
    Interviewer: That hasn't been determined yet.
    Me: What's a ballpark of how much data you expect the database to contain?
    Interviewer: I can't say at this time.
    Me: What kind of performance is required in terms of how fast pages render and queries complete.
    Interviewer: I don't have those details.
    Me: Without at least some of that information, there is no way for me to be able to come up with anything like a ballpark figure for the server costs. Once you're able to supply that data, my next steps would be to create a minimum set of server specifications and a recommended set. With those figures, I'd do some research from various vendors and be able to supply you with a range of prices and options.

    At the end of the above conversation, the interviewer was aware both that his question as posed was not answerable and that I knew how to answer it once I had sufficient details.

    Given the situation you indicated you were in, I would have acted in much the same fashion, asking the interviewer for additional details about the 'performance problem' -- how it happened, what changes might have occurred, etc. If (as seems likely) the interviewer indicated they did not have that information, I would have indicated how I would go about getting more details about the query -- execution plans, tracing, AWR (if they have the license), or STATSPACK (of they do not), etc.

    I know nothing about the interviewer or their goals. If they were not knowledgeable of Oracle, they might have thought the question was answerable as posed. If they were knowledgeable of Oracle, then I would guess that their goal was to have you ask them the questions that indicated that you knew both that the question as given had no answer and that you knew how to narrow down and diagnose the potential causes of a sudden unexplained performance degradation.
     
    mukulverma2408 likes this.