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!

SQL PL/SQL Query Optimization

Discussion in 'Other Development Tools' started by tyro, Oct 9, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    When you are developing code in SQL or PL/SQL it is useful to be able to optimise your queries, and gain the maximum efficiency advantage. It saves time, money and frustration.

    The ability to understand the optimizer for handling queries and techniques for finding the swiftest access path to data cannot be underestimated. It is necessary to understand how cost-based and heuristic (self learning) based optimisers work. Understanding optimisers also includes being able to understand the output of query execution plans. This provides the user with guidance for amending or deleting code which is not efficient under execution. Both DBAs and developers should develop these skills to be effective.

    Here I will provide a brief step by step guide on how to work with the optimizer and plan execution in Oracle.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Step 1) Create a PLAN_TABLE for your schema (This only needs to be done once):

    Code (Text):

    CREATE TABLE PLAN_TABLE (
    STATEMENT_ID    VARCHAR2(30),
    TIMESTAMP       DATE,
    REMARKS     VARCHAR2(80),
    OPERATION       VARCHAR2(30),
    OPTIONS     VARCHAR2(30),
    OBJECT_NODE VARCHAR2(128),
    OBJECT_OWNER    VARCHAR2(30),
    OBJECT_NAME VARCHAR2(30),
    OBJECT_INSTANCE NUMERIC,
    OBJECT_TYPE VARCHAR2(30),
    OPTIMIZER       VARCHAR2(255),
    SEARCH_COLUMNS  NUMBER,
    ID          NUMERIC,
    PARENT_ID       NUMERIC,
    POSITION        NUMERIC,
    COST            NUMERIC,
    CARDINALITY NUMERIC,
    BYTES           NUMERIC,
    OTHER_TAG       VARCHAR2(255),
    PARTITION_START VARCHAR2(255),
    PARTITION_STOP  VARCHAR2(255),
    PARTITION_ID    NUMERIC,
    OTHER       LONG,
    DISTRIBUTION    VARCHAR2(30));
     
    This table is where all the statistics and details of the optimiser results are stored. As it is all in a table the results can be analysed and produced in a suitable report format.

    Step 2) Run a query using a plan table command:

    Here is an example of a query ran for the specific purpose of providing plan information. Obviously your results will be different:

    Code (Text):

    explain plan
    set statement_id = 'TEST' -- where 'TEST' is a unique identifier for the query.
    for
    -- <Put the sql below here>
    SELECT
    emp.emp_name,
    emp.emp_hiredate,
    ep.ep_proj_id,
    ep.ep_hourly_rate,
    ea.ea_act_id,
    ea.ea_planned_hours
    FROM employee emp,
    employee_on_project ep,
    employee_on_activity ea
    WHERE
    emp.emp_id = ep.ep_emp_id
    AND ep.ep_proj_id = ea.ea_proj_id
    /
    Explained.
     
    Step 3) Run a query to obtain the results of the query plan.

    Code (Text):

    select LPAD(' ',2*Level)||Operation||' '||Options||' '||Object_Name Execution_Path
    from PLAN_TABLE
    where statement_ID = 'TEST'
    connect by prior ID = Parent_ID and Statement_ID = 'TEST'
    start with ID=1 ;
     
    This will produce something like the following:

    EXECUTION_PATH
    NESTED LOOPS
    MERGE JOIN CARTESIAN
    TABLE ACCESS FULL EMPLOYEE
    TABLE ACCESS FULL EMPLOYEE
    BUFFER SORT
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    BUFFER SORT
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    MERGE JOIN CARTESIAN
    TABLE ACCESS FULL EMPLOYEE
    TABLE ACCESS FULL EMPLOYEE
    BUFFER SORT
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    BUFFER SORT
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    TABLE ACCESS FULL EMPLOYEE_ON_ACTIVITY
    TABLE ACCESS BY INDEX ROWID EMPLOYEE_ON_PROJECT
    INDEX UNIQUE SCAN EP_PK
    INDEX UNIQUE SCAN EP_PK
    TABLE ACCESS BY INDEX ROWID EMPLOYEE_ON_PROJECT
    INDEX UNIQUE SCAN EP_PK

    What this tells us is that there are many steps involved in the query execution, and full table scans are not uncommon. This implies an inefficient query. The plus point is that it does make use of indexes on a few of the tables.

    Step 4) Use hints to determine which optimizer to use.

    It is possible to “force” the optimizer to use a specific kind of algorithm. This can be “rule based” or “cost based”.

    The rule based optimizer forces the use of the rule based technique of optimisation.
    This can be achieved in the query by placing a hint like so:

    Code (Text):

    SELECT /*+ RULE(employee) */
     
    For cost based, you would use:

    Code (Text):

    SELECT /*+ COST(employee) */
     
    The use of ‘Cost’ and ‘Rule’ based optimizers is outside the scope of this article. However, it is important to be aware of their existence. Different results will be produced depending on the type of optimization algorithm used. The default is usually “Cost” based and is set in the init.ora file in the parameter OPTIMIZER_MODE.
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    -Oracle does not have RBO and never use hints.

    -You use hints mean you are bringing your query slow down. Your plan was optimize and you deoptimized it.

    -And SQL*plus has better and user friendly plan by setting set autot trace instead of making plan table.
     
  4. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Very Good observations arju, perhaps tyro had written the above on an Oracle 9i version. As far as I remember Rule Based Optimizers were de-supported with the release of Oracle 10g
     
  5. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Yes, your remember is correct.
     
  6. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Yes it used to have until 9i. I should have mentioned that in my original post.

    Point noted!
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    De-supported means no modifications will be made to enhance the option or fix bugs. It does not mean the RBO is not present in the code, nor does it mean it cannot be used. I would look at some of the Oracle-supplied recursive SQL and note how many of those queries invoke the RULE based optimizer via hints.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're incorrect, the RBO still exists, even though it is no longer supported for code changes and/or bug fixes. Oracle uses it in some of its recursive queries; setting event 10046 at level 8 or 12 for an extended period of time will produce such recursive queries and their associated /*+ RULE */ hints.

    Why should one *never* use hints? Oracle uses them in their own code (the 'recursive' SQL reported by tkprof and in error messages when some operations fail). Offering such 'blanket' advice is, in my opinion, an ill-conceived act worthy of reconsideration. And how do *you* know the query, with hints, is 'deoptimised'? Have you seen the query plan? Have you seen the per-query statistics generated by autotrace? I expect not.

    Are you not aware that autotrace uses that very same PLAN_TABLE to provide its output? Apparently not, else you'd not have mentioned *not* creating one.

    This 'advice' of yours appears to be poor, indeed.
     
  9. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    You use hints with query and statistics are changing everyday but same execution plan is generated with use of hints and thus your performance of query always degrades.

    Make your statistics update, don't use hints.

    And about the RBO if you query v$sql you can see still many is there for oracle internal use. Note that they are for internal. There may be the reason is oracle still using the old tactics inside and in future you may not find any.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Prove that first statement of yours, please, and provide examples where EVERY case of using a hint results in degraded performance. There can be no exceptions. I believe you'll find that your 'blanket' statement is in error.

    Updating statistics will not necessarily fix some query plans; I strongly suggest y0u read Jonathan Lewis' text "Cost-based Oracle Fundamentals". You will likely be surprised at what you learn.

    Yes, the RBO is no longer a supported feature, however it's STILL available even in 11g if for no other reason than for backward compatibility with older, established queries against the data dictionary. To bluntly claim it is not is a falsehood.
     
  11. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Ok The debate is getting hot.

    Yes I would agree to zargon and would take his side of the debate.

    There is no "blanket advice" or "silver bullets" in case of oracle performance tuning.

    EVERY ADVICE IS VERY CASE SPECIFIC.

    A particular tuning advice may improve query performance of one query and the same may degrade other.

    Similer case applies in terms of using "Hints" also. They may or may not optimize queries depending on the query itself and statistics.