3 Replies Latest reply: May 15, 2013 11:56 PM by user10274093 RSS

    sql_advisor recommendation

    user10274093
      Hi,
      on 11g R2, I ran SQL_Advisor on a query and it suggests a new profile and in report it gives two explain plans. But it seems to me that the new one (the second one) has much CPU cost than the original. Am I mistaking :
       
      1- Original With Adjusted Cost 
      ------------------------------ 
      Plan hash value: 132727024 
      
      ------------------------------------------------------------------------------------------------- 
      | Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     | 
      ------------------------------------------------------------------------------------------------- 
      |   0 | SELECT STATEMENT                |               |   321 | 39483 |   331   (1)| 00:00:04 | 
      |   1 |  SORT UNIQUE                    |               |   321 | 39483 |   331   (1)| 00:00:04 | 
      
      ---------------------------------------------------------------------------------------------------- 
      |   2 |   NESTED LOOPS                  |               |       |       |            |          | 
      |   3 |    NESTED LOOPS                 |               |   321 | 39483 |   328   (0)| 00:00:04 | 
      |   4 |     NESTED LOOPS                |               |   321 | 26322 |     7   (0)| 00:00:01 | 
      |*  5 |      INDEX RANGE SCAN           | PSAPSTREEDEFN |     3 |   123 |     1   (0)| 00:00:01 | 
      |   6 |       SORT AGGREGATE            |               |     1 |    26 |            |          | 
      |*  7 |        INDEX SKIP SCAN          | PSAPSTREEDEFN |     1 |    26 |     1   (0)| 00:00:01 | 
      |*  8 |      TABLE ACCESS BY INDEX ROWID| PSTREENODE    |   102 |  4182 |     2   (0)| 00:00:01 | 
      |*  9 |       INDEX RANGE SCAN          | PSFPSTREENODE |    19 |       |     1   (0)| 00:00:01 | 
      |* 10 |     INDEX UNIQUE SCAN           | PS_PSRECDEFN  |     1 |       |     0   (0)| 00:00:01 | 
      |* 11 |    TABLE ACCESS BY INDEX ROWID  | PSRECDEFN     |     1 |    41 |     1   (0)| 00:00:01 | 
      ------------------------------------------------------------------------------------------------- 
      
      2- Using SQL Profile 
      -------------------- 
      Plan hash value: 723101745 
      
      
      DBMS_SQLTUNE.REPORT_TUNING_TASK(:STMT_TASK) 
      
      ------------------------------------------------------------------------------------------------ 
      | Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     | 
      ------------------------------------------------------------------------------------------------ 
      |   0 | SELECT STATEMENT               |               |   468 | 68796 |   414   (1)| 00:00:05 | 
      |   1 |  HASH UNIQUE                   |               |   468 | 68796 |   414   (1)| 00:00:05 | 
      |*  2 |   HASH JOIN                    |               |   468 | 68796 |   413   (1)| 00:00:05 | 
      |   3 |    NESTED LOOPS                |               |       |       |            |          | 
      |   4 |     NESTED LOOPS               |               |   468 | 49608 |    12   (9)| 00:00:01 | 
      |   5 |      NESTED LOOPS              |               |     5 |   325 |     2  (50)| 00:00:01 | 
      |   6 |       VIEW                     | VW_SQ_1       |    55 |  1320 |     2  (50)| 00:00:01 | 
      |   7 |        HASH GROUP BY           |               |    55 |  1430 |     2  (50)| 00:00:01 | 
      
      ---------------------------------------------------------------------------------------------------- 
      |*  8 |         INDEX SKIP SCAN        | PSAPSTREEDEFN |    65 |  1690 |     1   (0)| 00:00:01 | 
      |*  9 |       INDEX RANGE SCAN         | PSAPSTREEDEFN |     1 |    41 |     0   (0)| 00:00:01 | 
      |* 10 |      INDEX RANGE SCAN          | PSFPSTREENODE |    19 |       |     1   (0)| 00:00:01 | 
      |* 11 |     TABLE ACCESS BY INDEX ROWID| PSTREENODE    |   102 |  4182 |     2   (0)| 00:00:01 | 
      |* 12 |    TABLE ACCESS FULL           | PSRECDEFN     | 41083 |  1644K|   401   (1)| 00:00:05 | 
      ------------------------------------------------------------------------------------------------ 
      Thanks for your opinion.