This discussion is archived
8 Replies Latest reply: May 2, 2012 10:28 AM by Gaff RSS

Noob in SQL Tuning seeking help

apex_disco Newbie
Currently Being Moderated
I am trying to learn SQL Tuning and the workings of it. I know the first step is probably explain execution plan of the sql statement? My problem is I do not understand what am I suppose to gather from the execution plan.
SQL> set autotrace traceonly explain
SQL> select * from employees A
     where A.last_employed =
     ( SELECT TO_CHAR(MAX(TO_DATE(PAY_DATE,'MMRR')),'MMYY')
       FROM PAYROLL_VW1 B
       WHERE B.SSN = A.SSN AND B.PAY_TYPE_CODE IN ('02','12','22','32')
      AND STATUS = 'ACTIVE' AND A.BRANCH=B.BRANCH
     );
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=47427 Card=1 Bytes
          =180)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=1618 Card
          =297964 Bytes=53633520)

   3    1     SORT (AGGREGATE)
   4    3       VIEW OF 'PAYROLL_VW1' (VIEW) (Cost=2290 Card=2 Bytes=
          46)

   5    4         SORT (UNIQUE) (Cost=2290 Card=2 Bytes=217)
   6    5           UNION-ALL
   7    6             FILTER
   8    7               TABLE ACCESS (FULL) OF 'PAYROLL2012' (TABLE) (C
          ost=2284 Card=1 Bytes=105)

   9    6             FILTER
  10    9               TABLE ACCESS (BY INDEX ROWID) OF 'PAYROLL2011'
          (TABLE) (Cost=4 Card=1 Bytes=112)

  11   10                 INDEX (RANGE SCAN) OF 'PAYROLL2011_IDX1' (IND
          EX) (Cost=3 Card=29)
So what do I do with the execution plan? What else do I need to check on it? How do I tune the query?

Edited by: apex_disco on May 1, 2012 5:57 AM
  • 1. Re: Noob in SQL Tuning seeking help
    Gaff Journeyer
    Currently Being Moderated
    Hi:

    Execution plans are one of those things that are part science, part "art". There are general rules, but many exceptions. You are looking for "inefficiencies". Your plan shows

    * A full table scan on EMPLOYEES. Full table scans are OFTEN non-desirable, but not always. If your EMPLOYEES table is a 50 row table then it might not be worth using an index even if one exists (do you have an index on EMPLOYEES, say, on SSN?)

    * You've got a UNION ALL with PAYROLL2011 and PAYROLL2012 to make a PAYROLL_VW1. Oracle was able to find an index for PAYROLL2011 and do a range scan on it but it did a full table scan on PAYROLL2012. Again, maybe there is no index (on PAYROLL2012.SSN), maybe it wasn't more efficient to use an index even if it existed for the proper column.

    If you have Enterprise Manager/Grid control, you can use tools within that to a) show your current explain plan and b) give hints as to steps you might take (create index on...) to improve query performance. So it might be educational to look at some queries in that and see what EM suggests and try and understand why it is suggesting what it suggests.

    Have a look at some of these docs. http://www.google.com/search?q=reading+oracle+explain+plans&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

    Edited by: Gaff on Apr 30, 2012 5:46 PM
  • 2. Re: Noob in SQL Tuning seeking help
    rp0428 Guru
    Currently Being Moderated
    >
    I am trying to learn SQL Tuning and the workings of it. I know the first step is probably explain execution plan of the sql statement?
    >
    In my opinion the two biggest rules are

    1. If it ain't broke, don't fix it!

    2. If it doesn't work don't tune it - fix it!

    In other words don't try to tune something that doesn't work to begin with. And once it does work make sure it really needs tuned.

    It doesn't look to me like your query can possibly return useful results; that is, I don't think it works. So I would never spend any time doing anything related to tuning until I fix teh query.

    The query starts with
    SQL> select * from employees A
      2  where A.ssn in
      3  ( SELECT TO_CHAR(MAX(TO_DATE(PAY_DATE,'MMRR')),'MMYY')
    So how can an 'ssn' value (which I would assume to be a 9 digit number) possibly be contained in a date string that consists of a 2 digit month and year?

    That doesn't even make any sense, is unlikely to ever return any rows at all and if it did return rows it would be a fluke based on very strange data.

    Based on my two rules above I would reject the code as invalid and ask you to post an actual sample query that you think has a performance problem.

    And also post the reasons you think the query has a performance problem. See the FAQ for how to post a tuning request because it will give you a lot of the information that YOU need to acquire if you plan to do any tuning yourself.

    So the place to start, once you have a working query and factual indications that there may be performance issues, is to gather the same information we ask for in the FAQ.

    Once you have done that, post the query and information and we can help show you where to go from there.
  • 3. Re: Noob in SQL Tuning seeking help
    apex_disco Newbie
    Currently Being Moderated
    You are right. I was trying a few things and pasted the query where I was looking for SSN. I was actually looking for last time some employees were with us; hence looking for last pay date. Blame it on too many sql plus screen.

    Anyway, did a timing on before I left last night and traceonly:

    Time elapsed was *2 hours 35 mins 22 seconds*
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
     1172490142  consistent gets
           4914  physical reads
         191496  redo size
           2340  bytes sent via SQL*Net to client
            329  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
         115881  sorts (memory)
              0  sorts (disk)
              0  rows processed
    So, I know the sql will execute. I do need to tune it ... 2 hours + to run that is way too long.
  • 4. Re: Noob in SQL Tuning seeking help
    Gaff Journeyer
    Currently Being Moderated
    Well, you can go into trace files and see what's happening and sometimes you really have to,but especially for single queries, I start off with EM/GC. The SQL Tuning parts of that will graphically show you what's eating up what resources and rank the things to tune.
  • 5. Re: Noob in SQL Tuning seeking help
    apex_disco Newbie
    Currently Being Moderated
    Enterprise Manager (EM) - those stats AWR and ADDM requires additional licensing doesn't it?

    I did as you said on your first post - added indexes to the 2 tables. Then, I did a
    EXEC DBMS_STATS.gather_table_stats
    on those tables and below are my stats now. I think it's a major improvement. Time elapsed has greatly been reduced to 00:00:00.65
    Statistics
    ----------------------------------------------------------
            352  recursive calls
              0  db block gets
          31498  consistent gets
           1101  physical reads
              0  redo size
           2340  bytes sent via SQL*Net to client
            329  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
             13  sorts (memory)
              0  sorts (disk)
              0  rows processed
    So, thank you very much Gaff for the pointers. I still welcome any ideas / suggestions.
  • 6. Re: Noob in SQL Tuning seeking help
    Gaff Journeyer
    Currently Being Moderated
    I'm not sure how the licensing works. There is a difference between EM and Grid Control. I believe if you just have a single instance, Enterprise Manager is installed and if anything is free I would think that would be. Now, how much of the diagnostic stuff is free? Who knows.

    But it may not matter. If you are just playing around "trying to learn SQL Tuning" say, at home on a Linux box, you can install anything you want and play with it.

    If adding an index sped things up (in your re-written query that now makes sense) then you must have had a fair amount of records in one of those unindexed tables. If you look at the explain plan now it'll probably show the indexes being used, less records searched, less cost, etc. than the other plan.

    But maybe I missed your initial point. Are you trying to learn how to tune queries in general or are you trying to tune this particular query? I assumed the former for my previous replies.


    As to your results, your stats </font>tell at least the high level info. The consistent gets and physical reads went WAY down.



    Initial Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    <font color="red"> 1172490142 consistent gets
    4914 physical reads
    191496 redo size</font>

    2340 bytes sent via SQL*Net to client
    329 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    <font color="red">115881 sorts (memory) </font>
    0 sorts (disk)
    0 rows processed


    on those tables and below are my stats now. I think it's a major improvement. Time elapsed has greatly been reduced to 00:00:00.65

    Tuned Statistics
    ----------------------------------------------------------
    352 recursive calls
    0 db block gets
    <font color="red">31498 consistent gets
    1101 physical reads
    0 redo size
    </font>
    2340 bytes sent via SQL*Net to client
    329 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    <font color="red">13 sorts (memory) </font>
    0 sorts (disk)
    0 rows processed

    Edited by: Gaff on May 1, 2012 1:18 PM
  • 7. Re: Noob in SQL Tuning seeking help
    Gaff Journeyer
    Currently Being Moderated
    apex_disco wrote:
    Enterprise Manager (EM) - those stats AWR and ADDM requires additional licensing doesn't it?

    I did as you said on your first post - added indexes to the 2 tables. Then, I did a
    EXEC DBMS_STATS.gather_table_stats
    on those tables and below are my stats now. I think it's a major improvement. Time elapsed has greatly been reduced to 00:00:00.65
    >
    So, thank you very much Gaff for the pointers. I still welcome any ideas / suggestions.
    From more than 2 hours down to 0.65 seconds and you're looking for more improvement? Congratulations! You've stumbled into one of the pitfalls of tuning - knowing when to quit ;) Seriously. Tuning is one of those things were at a certain point you're just pushing the bulge around. It's as good as it's going to consistently get. Just take the victory and walk away.

    Gaff
  • 8. Re: Noob in SQL Tuning seeking help
    Gaff Journeyer
    Currently Being Moderated
    If this question is answered correctly, please mark it as such.

    Gaff

Legend

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