8 Replies Latest reply: Jan 8, 2013 5:07 AM by user522961 RSS

    tuning a query

    user522961
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Thank you.