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 Tuning question: What after EXPLAIN PLAN?

Discussion in 'SQL PL/SQL' started by jamuna_j, Nov 30, 2012.

  1. jamuna_j

    jamuna_j Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    Hi
    A difficulty I often face when asked about the tuning process...
    We know that one of the first steps is to see the current query execution process using EXPLAIN PLAN.
    But I find myself stumped in explaining anything beyond the point of determining the Indexes and Join methods used.
    As I don't have much DBA experience, I find it difficult to clearly explain the actual steps you would take to 'tune' the code after gathering data from the plan table.
    Can anyone help me with the set of steps to follow using the plan output?
    I agree that it is largely situation-based, but I'm looking for a general algorithm which would help me approach a query performance issue.
    Thanks!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    EXPLAIN PLAN does NOT give you the current query execution process -- it still uses antiquated methods to guess at what Oracle will do with the query. To get the actual plan a session trace must be taken, autotrace must be enabled (set autotrace on, NOT set autotrace explain) or you must query V$SQL_PLAN for the sql_id in question which records the actual plan generated at run time. I have seen cases where EXPLAIN PLAN reported a 'plan' that did not match the actual plan the optimizer generated at run time. That being said the issue here is how to go about reading the plan and interpreting what Oracle is doing, when, and how to influence the optimizer to produce a 'better' execution path. Let's look at a valid execution plan:

    Code (SQL):
    SQL> CREATE TABLE account(
    2 account_number NUMBER NOT NULL,
    3 id varchar2(10) NOT NULL,
    4 lowest_amount NUMBER(22,4),
    5 highest_amount NUMBER(22,4)
    6 );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE tmp_account(
    2 account_number NUMBER NOT NULL,
    3 amount NUMBER(22,4),
    4 descr varchar2(40)
    5 );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..8000 loop
    3 INSERT INTO account
    4 (account_number, id, lowest_amount, highest_amount)
    5 VALUES
    6 (i, 'Ex '||i, MOD(i, 337), MOD(i, 93));
    7 IF i <= 150 THEN
    8 INSERT INTO tmp_account
    9 (account_number, amount, descr)
    10 VALUES
    11 (i, MOD(i, 43), 'Example record '||i);
    12 END IF;
    13 END loop;
    14
    15 commit;
    16
    17 END;
    18 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT a.account_number, a.id, b.*
    2 FROM account a, tmp_account b
    3 WHERE b.amount BETWEEN a.lowest_amount AND a.highest_amount;
     
    ACCOUNT_NUMBER ID ACCOUNT_NUMBER AMOUNT DESCR
    -------------- ---------- -------------- ---------- ----------------------------------------
    6782 Ex 6782 42 42 Example record 42
    1390 Ex 1390 42 42 Example record 42
    7793 Ex 7793 42 42 Example record 42
    7119 Ex 7119 42 42 Example record 42
    5097 Ex 5097 42 42 Example record 42
    4423 Ex 4423 42 42 Example record 42
    6108 Ex 6108 42 42 Example record 42
    4086 Ex 4086 42 42 Example record 42
    2401 Ex 2401 42 42 Example record 42
    1727 Ex 1727 42 42 Example record 42
    3412 Ex 3412 42 42 Example record 42
    716 Ex 716 42 42 Example record 42
    42 Ex 42 42 42 Example record 42
    6781 Ex 6781 42 42 Example record 42
    ...
    6104 Ex 6104 85 42 Example record 85
    4082 Ex 4082 85 42 Example record 85
    2397 Ex 2397 85 42 Example record 85
    1723 Ex 1723 85 42 Example record 85
    3408 Ex 3408 85 42 Example record 85
    712 Ex 712 85 42 Example record 85
    6777 Ex 6777 85 42 Example record 85
    1385 Ex 1385 85 42 Example record 85
    ...
    364 Ex 364 127 41 Example record 127
    6766 Ex 6766 127 41 Example record 127
    1374 Ex 1374 127 41 Example record 127
    7777 Ex 7777 127 41 Example record 127
    5081 Ex 5081 127 41 Example record 127
    5755 Ex 5755 127 41 Example record 127
    6092 Ex 6092 127 41 Example record 127
    4070 Ex 4070 127 41 Example record 127
    2385 Ex 2385 127 41 Example record 127
    3396 Ex 3396 127 41 Example record 127
    3059 Ex 3059 127 41 Example record 127
    700 Ex 700 127 41 Example record 127
    ...
    2374 Ex 2374 24 24 Example record 24
    2037 Ex 2037 24 24 Example record 24
    1700 Ex 1700 24 24 Example record 24
    3385 Ex 3385 24 24 Example record 24
    3048 Ex 3048 24 24 Example record 24
    ...
    2022 Ex 2022 86 0 Example record 86
    2359 Ex 2359 86 0 Example record 86
    4044 Ex 4044 86 0 Example record 86
    5392 Ex 5392 86 0 Example record 86
    6066 Ex 6066 86 0 Example record 86
    5729 Ex 5729 86 0 Example record 86
    4381 Ex 4381 86 0 Example record 86
    4718 Ex 4718 86 0 Example record 86
    5055 Ex 5055 86 0 Example record 86
    7414 Ex 7414 86 0 Example record 86
    7751 Ex 7751 86 0 Example record 86
    6403 Ex 6403 86 0 Example record 86
    6740 Ex 6740 86 0 Example record 86
    337 Ex 337 86 0 Example record 86
     
    52108 ROWS selected.
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3631114592
    --------------------------------------------------------------------------------------------
    | Id | Operation | Name | ROWS | Bytes |TempSpc| Cost (%CPU)| TIME |
    --------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 3000 | 275K| | 110 (4)| 00:00:02 |
    | 1 | MERGE JOIN | | 3000 | 275K| | 110 (4)| 00:00:02 |
    | 2 | SORT JOIN | | 150 | 7200 | | 4 (25)| 00:00:01 |
    | 3 | TABLE ACCESS FULL | TMP_ACCOUNT | 150 | 7200 | | 3 (0)| 00:00:01 |
    |* 4 | FILTER | | | | | | |
    |* 5 | SORT JOIN | | 8000 | 359K| 952K| 105 (2)| 00:00:02 |
    | 6 | TABLE ACCESS FULL| ACCOUNT | 8000 | 359K| | 9 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
    4 - FILTER("B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
    5 - access(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST_AMOUNT"
    ))
    FILTER(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST_AMOUNT"
    ))
    Note
    -----
    - dynamic sampling used FOR this statement
     
    Statistics
    ----------------------------------------------------------
    152 recursive calls
    0 db block gets
    92 consistent gets
    0 physical reads
    0 redo SIZE
    1324733 bytes sent via SQL*Net TO client
    38588 bytes received via SQL*Net FROM client
    3475 SQL*Net roundtrips TO/FROM client
    4 sorts (memory)
    0 sorts (disk)
     
    52108 ROWS processed
     
    SQL>
     
    Notice the MERGE JOIN step in the actual plan -- also notice (in the abbreviated data output) that the same record from one table is matched with EVERY record from the second table before the first table gets to a new record (150 rows * 8000 rows). The MERGE JOIN step (sometimes reported as MERGE JOIN CARTESIAN) is the problem step in this plan and indicates that the query does not provide proper conditions to execute the desired join. Modifying the query and generating a new plan we see:

    Code (SQL):
    SQL> SELECT a.account_number, a.id, b.*
    2 FROM account a, tmp_account b
    3 WHERE b.account_number = a.account_number
    4 AND b.amount BETWEEN a.lowest_amount AND a.highest_amount;
     
    ACCOUNT_NUMBER ID ACCOUNT_NUMBER AMOUNT DESCR
    -------------- ---------- -------------- ---------- ----------------------------------------
    1 Ex 1 1 1 Example record 1
    2 Ex 2 2 2 Example record 2
    3 Ex 3 3 3 Example record 3
    4 Ex 4 4 4 Example record 4
    5 Ex 5 5 5 Example record 5
    6 Ex 6 6 6 Example record 6
    7 Ex 7 7 7 Example record 7
    8 Ex 8 8 8 Example record 8
    9 Ex 9 9 9 Example record 9
    10 Ex 10 10 10 Example record 10
    11 Ex 11 11 11 Example record 11
    12 Ex 12 12 12 Example record 12
    13 Ex 13 13 13 Example record 13
    14 Ex 14 14 14 Example record 14
    15 Ex 15 15 15 Example record 15
    16 Ex 16 16 16 Example record 16
    17 Ex 17 17 17 Example record 17
    18 Ex 18 18 18 Example record 18
    19 Ex 19 19 19 Example record 19
    20 Ex 20 20 20 Example record 20
    21 Ex 21 21 21 Example record 21
    22 Ex 22 22 22 Example record 22
    23 Ex 23 23 23 Example record 23
    24 Ex 24 24 24 Example record 24
    25 Ex 25 25 25 Example record 25
    26 Ex 26 26 26 Example record 26
    27 Ex 27 27 27 Example record 27
    28 Ex 28 28 28 Example record 28
    29 Ex 29 29 29 Example record 29
    30 Ex 30 30 30 Example record 30
    31 Ex 31 31 31 Example record 31
    32 Ex 32 32 32 Example record 32
    33 Ex 33 33 33 Example record 33
    34 Ex 34 34 34 Example record 34
    35 Ex 35 35 35 Example record 35
    36 Ex 36 36 36 Example record 36
    37 Ex 37 37 37 Example record 37
    38 Ex 38 38 38 Example record 38
    39 Ex 39 39 39 Example record 39
    40 Ex 40 40 40 Example record 40
    41 Ex 41 41 41 Example record 41
    42 Ex 42 42 42 Example record 42
     
    42 ROWS selected.
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1664268811
    ----------------------------------------------------------------------------------
    | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
    ----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 94 | 13 (8)| 00:00:01 |
    |* 1 | HASH JOIN | | 1 | 94 | 13 (8)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TMP_ACCOUNT | 150 | 7200 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS FULL| ACCOUNT | 8000 | 359K| 9 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
    1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER")
    FILTER("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND
    "B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
    Note
    -----
    - dynamic sampling used FOR this statement
     
    Statistics
    ----------------------------------------------------------
    9 recursive calls
    0 db block gets
    81 consistent gets
    0 physical reads
    0 redo SIZE
    2401 bytes sent via SQL*Net TO client
    407 bytes received via SQL*Net FROM client
    4 SQL*Net roundtrips TO/FROM client
    2 sorts (memory)
    0 sorts (disk)
    42 ROWS processed
     
    SQL>
     
    Notice here that only 42 records were returned by the join, which is the expected result of a properly coded inner join since out of the 8000 account records and 150 tmp_account records only 42 satisfy the conditions set forth in the query. Notice also the MERGE JOIN step has vanished.

    Execution plans are read from bottom to top, following the indentions to group actions that are taken. Let's look at the second plan again and read it:

    Code (SQL):
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1664268811
    ----------------------------------------------------------------------------------
    | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME |
    ----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 94 | 13 (8)| 00:00:01 |
    |* 1 | HASH JOIN | | 1 | 94 | 13 (8)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TMP_ACCOUNT | 150 | 7200 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS FULL| ACCOUNT | 8000 | 359K| 9 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
    1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER")
    FILTER("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND
    "B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
     
     
    Since no indexes exist for either table the first steps are full table scans of both tables (listed in steps 2 and 3). Above that we see a HASH JOIN operation, which performs the inner join since the one table (of 150 rows) can easily fit into memory so a hash table can be created. A second pass is performed on the next table (of 8000 rows, also small enough to fit into memory) in the join and the hashed values are compared, returning results only for matched hash keys. When tables are small this is a very fast method. Finally the driving SELECT statement is shown, to which the results are returned.

    Please note that a plan may indicate that an index is being used -- it may not be the CORRECT index for the query but Oracle has decided that this index is the 'cheapest' to access. This indicates problems with, or a complete absence of, current statistics on the tables and indexes involved. Fixing such a problem requires generating or estimating statistics on the required tables/indexes. Another issue is problems with how statistics are gathered in various releases of Oracle -- you may need to modify statistics gathering processes to correct problem statistics. If you really want to tune queries you need to be familiar with to trace events:

    10046
    10053

    The 10046 event traces a session and reports on all activity from that session including wait events and reporting values for bind variables (this would be a level 12 trace). As an example of how to start such a trace:

    SQL> alter session set events='10046 trace name context forever, level 12';

    This sets a level 12 trace which, as stated earlier, reports on a lot of the session activity including wait events/times and bind variable values. You would process this raw output with tkprof, a utility provided by Oracle for just such tasks:

    $ tkprof <input trace file> <output file>

    If you have a trace file name bob.trc then you would process this trace file as follows:

    $ tkprof bob.trc bob.out

    You would then have a bob.out file, formatted for easy reading and interpretation.

    The 10053 trace provides a record of all of the decisions made by the optimizer to establish the execution plan for a given query. All information is provided, including statistics values used to 'weed out' expensive plans. It is helpful to generate at least one of these trace files and examine it thoroughly. No translation is needed for a 10053 trace as it's perfectly readable.

    I hope this has helped.
     
    jamuna_j likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Nice explanation, David . Hats off :hurray
     
  4. jamuna_j

    jamuna_j Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    Thank you! Yes this has helped me get a good idea of it... :)