This discussion is archived
8 Replies Latest reply: Jan 8, 2013 3:07 AM by user522961 RSS

tuning a query

user522961 Newbie
Currently Being Moderated
Hi,
on 11g R2, my query gives the following Explain Plan. Do you have any idea to tune it :
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Plan hash value: 1058093756

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                  |    84 |  4284 |   679  (25)| 00:00:09 |
|   1 |  UPDATE                       | PS_PROJECT       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL           | PS_PROJECT       |    84 |  4284 |     7   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE              |                  |     1 |    25 |            |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| PS_PROJ_RESOURCE |     4 |   100 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | PS_PROJ_RESOURCE |     4 |       |     2   (0)| 00:00:01 |
|   6 |   SORT AGGREGATE              |                  |     1 |    25 |            |          |
|   7 |    TABLE ACCESS BY INDEX ROWID| PS_PROJ_RESOURCE |     4 |   100 |     3   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN          | PS_PROJ_RESOURCE |     4 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PC_CHC_SW"='D')
   5 - access("PR"."BUSINESS_UNIT"=:B1 AND "PR"."PROJECT_ID"=:B2)
   8 - access("PR"."BUSINESS_UNIT"=:B1 AND "PR"."PROJECT_ID"=:B2)
I ran also sql_advisor but zero recommandation :
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
---------------------------------------------------------------------------------------------
There are no recommendations to improve the statement.
Thank you.
  • 1. Re: tuning a query
    P.Forstmann Guru
    Currently Being Moderated
    Please try to give more information about the query using HOW TO: Post a SQL statement tuning request - template posting
  • 2. Re: tuning a query
    user522961 Newbie
    Currently Being Moderated
    Hi again,
    then :

    here is :
    SQL> show parameter optimizer
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    optimizer_capture_sql_plan_baselines boolean     FALSE
    optimizer_dynamic_sampling           integer     2
    optimizer_features_enable            string      11.2.0.3
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE
    optimizer_use_invisible_indexes      boolean     FALSE
    optimizer_use_pending_statistics     boolean     FALSE
    optimizer_use_sql_plan_baselines     boolean     TRUE
    SQL>
    
    
    SQL> explain plan for UPDATE PS_PROJECT SET START_DT = ( SELECT /*+ INDEX (PS_PROJ_RESOURCE PS_PROJ_RES_IDX)*/ MIN(ACCOUNTING_DT) FROM PS_PROJ_RESO
    URCE PR WHERE PS_PROJECT.BUSINESS_UNIT = PR.BUSINESS_UNIT AND PS_PROJECT.PROJECT_ID = PR.PROJECT_ID), END_DT = ( SELECT /*+ INDEX (PS_PROJ_RESOURCE
     PS_PROJ_RES_IDX)*/MAX(ACCOUNTING_DT) FROM PS_PROJ_RESOURCE PR WHERE PS_PROJECT.BUSINESS_UNIT = PR.BUSINESS_UNIT AND PS_PROJECT.PROJECT_ID = PR.PRO
    JECT_ID) WHERE PC_CHC_SW = 'D';
    
    Explained.
    
    SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
    Plan hash value: 4143140836
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT    |                  |     1 |    17 |   556K  (1)| 01:51:20 |
    |   1 |  UPDATE             | PS_PROJECT       |       |       |            |          |
    |*  2 |   TABLE ACCESS FULL | PS_PROJECT       |     1 |    17 |    53   (0)| 00:00:01 |
    |   3 |   SORT AGGREGATE    |                  |     1 |    17 |            |          |
    |*  4 |    TABLE ACCESS FULL| PS_PROJ_RESOURCE |  2113 | 35921 |   278K  (1)| 00:55:40 |
    |   5 |   SORT AGGREGATE    |                  |     1 |    17 |            |          |
    |*  6 |    TABLE ACCESS FULL| PS_PROJ_RESOURCE |  2113 | 35921 |   278K  (1)| 00:55:40 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("PC_CHC_SW"='D')
       4 - filter("PR"."PROJECT_ID"=:B1 AND "PR"."BUSINESS_UNIT"=:B2)
       6 - filter("PR"."PROJECT_ID"=:B1 AND "PR"."BUSINESS_UNIT"=:B2)
    
    20 rows selected.
    Thanks and regards.
  • 3. Re: tuning a query
    Mohamed Houri Pro
    Currently Being Moderated
    First remark is that you are not using hints correctly.

    Second It seems for me that using MERGE will suit more your needs

    Something like this
    merge into ps_project ps
    using (select 
              min(accounting_dt) min_dat
            , max(accounting_dt) max_dat
           from  ps_proj_resource        
           ) pr
    on    (ps.business_unit = pr.business_unit
           and   ps.project_id    = pr.project_id )
    when matched then
         update
         set    ps.start_dt  = pr.min_dat ,
                ps.end_dt   =  pr.max_dat     
    Best regards
    Mohamed Houri
    www.hourim.wordpress.com
  • 4. Re: tuning a query
    user522961 Newbie
    Currently Being Moderated
    Thank you Mohamed.
    First remark is that you are not using hints correctly. 
    How should I use the hints ?

    Regards.
  • 5. Re: tuning a query
    Mohamed Houri Pro
    Currently Being Moderated
    Here below is an example (use the table alias in the hint)
    sql> explain plan for select e.* from emp e;
    
    Explained.
    
    sql> select * from table(dbms_xplan.display);
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    15 |   570 |     5   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    15 |   570 |     5   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    
    sql> explain plan for select /*+ index(emp emp_pk) */ e.* from emp e;
    
    Explained.
    
    d102@o102d00d.world> select * from table(dbms_xplan.display);
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    15 |   570 |     5   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    15 |   570 |     5   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    
    sql> explain plan for select /*+ index(e emp_pk) */ e.* from emp e;
    
    Explained.
    
    sql> select * from table(dbms_xplan.display);
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |    15 |   570 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    15 |   570 |     3   (0)| 00:00:01 |
    |   2 |   INDEX FULL SCAN           | EMP_PK |    15 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    Have you tried the MERGE operation?

    Best Regards
    Mohamed Houri
    www.hourim.wordpress.com
  • 6. Re: tuning a query
    user522961 Newbie
    Currently Being Moderated
    Thank you Mohamed.
    Here is merge :
    SQL> explain plan for
      2  merge into ps_project ps
      3  using (select
      4            min(accounting_dt) min_dat
      5          , max(accounting_dt) max_dat
      6         from  ps_proj_resource
      7         ) pr
      8  on    (ps.business_unit = pr.business_unit
      9         and   ps.project_id    = pr.project_id )
     10  when matched then
     11       update
     12       set    ps.start_dt  = pr.min_dat ,
     13              ps.end_dt   =  pr.max_dat;
           and   ps.project_id    = pr.project_id )
                                    *
    ERROR at line 9:
    ORA-00904: "PR"."PROJECT_ID": invalid identifier
  • 7. Re: tuning a query
    Mohamed Houri Pro
    Currently Being Moderated
    Try this. But I am not sure if it will be equivalent to your initial query. It is difficult without a create table script
    explain plan for
        merge into ps_project ps
        using (select
                  business_unit
                  project_id
                , min(accounting_dt) min_dat
                , max(accounting_dt) max_dat
               from  ps_proj_resource
               group by
                  business_unit
                  project_id
               ) pr
        on    (ps.business_unit     = pr.business_unit
               and   ps.project_id  = pr.project_id )
       when matched then
            update
            set    ps.start_dt   = pr.min_dat ,
                   ps.end_dt     =  pr.max_dat;
           
    Mohamed Houri
  • 8. Re: tuning a query
    user522961 Newbie
    Currently Being Moderated
    Thank you.

Legend

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