sql pan baseline

从outline到sql profile到11g的sql plan baseline，执行计划的管理和控制增强了很多，
11g的sql plan baseline可以很方便的创建，删除，修改属性。
outline : http://space.itpub.net/756652/viewspace-544642
sql profile : http://space.itpub.net/756652/viewspace-713990

生产库两个类似的语句消耗cpu资源非常高，查看该SQL有4个执行计划（11g新特性也是有利有弊），其中最差一个就是有个子查询两有索引的大表union，没有push谓词进去。根据sql_id load产生sql plan baseline，第一个语句进来4个，把其中不好的删除或者disable，fix好的执行计划 ；第二个语句只有一个差的执行计划，只能利用提示use_nl产生一个新的语句，然后再把他load到对应的sql handle中，把不好的删除或者disable，fix好的执行计划。问题得到解决。

最差的计划大致如下：
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |    20 | 32840 |  1280   (1)| 00:00:16 |
|   1 |  NESTED LOOPS                      |                         |       |       |            |          |
|   2 |   NESTED LOOPS                     |                         |    20 | 32840 |  1280   (1)| 00:00:16 |
|*  3 |    VIEW                            |                         |    20 | 32360 |  1276   (1)| 00:00:16 |
|*  4 |     COUNT STOPKEY                  |                         |       |       |            |          |
|*  5 |      FILTER                        |                         |       |       |            |          |
|*  6 |       HASH JOIN                    |                         |    20 | 12380 |  1276   (1)| 00:00:16 |
|   7 |        TABLE ACCESS BY INDEX ROWID | XXXXXXXXX_ID_PUNISH     | 21449 |  1780K|   891   (0)| 00:00:11 |
|*  8 |         INDEX RANGE SCAN DESCENDING| XXXXXXXXX_ID_PUNISH_IDX | 39486 |       |    27   (0)| 00:00:01 |
|   9 |        VIEW                        |                         | 66299 |    16M|   384   (1)| 00:00:05 |
|  10 |         UNION-ALL                  |                         |       |       |            |          |
|  11 |          TABLE ACCESS FULL         | XXXXXXXXX_USERINFO      |    71M|  3661M|   411K  (1)| 01:22:15 |
|  12 |          TABLE ACCESS FULL         | XXXXXXXXX_APPENDUSER    |  9665 |   556K|    68   (0)| 00:00:01 |
|* 13 |    INDEX UNIQUE SCAN               | RESULT_UNIQUE_CHECK     |     1 |       |     1   (0)| 00:00:01 |
|  14 |   TABLE ACCESS BY INDEX ROWID      | XXXXXXXXX_PUNISH_RESULT |     1 |    24 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

最好的计划大致如下：
SYS@AS SYSDBA minor> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_71xt5pxkcpr1cf6e28209'));
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_71xt5pxkcpr1cf6e28209         Plan id: 4142039561
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2370743769

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |    20 | 33820 | 14620   (1)| 00:02:56 |
|   1 |  NESTED LOOPS                        |                           |       |       |            |          |
|   2 |   NESTED LOOPS                       |                           |    20 | 33820 | 14620   (1)| 00:02:56 |
|   3 |    NESTED LOOPS                      |                           |    20 | 33340 | 14616   (1)| 00:02:56 |
|*  4 |     VIEW                             |                           |    20 | 32720 | 14614   (1)| 00:02:56 |
|*  5 |      COUNT STOPKEY                   |                           |       |       |            |          |
|*  6 |       FILTER                         |                           |       |       |            |          |
|   7 |        NESTED LOOPS                  |                           |  7150 |  2464K| 14614   (1)| 00:02:56 |
|   8 |         TABLE ACCESS BY INDEX ROWID  | XXXXXXXXX_ID_PUNISH       |  7149 |   600K|   315   (0)| 00:00:04 |
|*  9 |          INDEX RANGE SCAN DESCENDING | XXXXXXXXX_ID_PUNISH_IDX   | 13160 |       |    27   (0)| 00:00:01 |
|  10 |         VIEW                         |                           |     1 |   267 |     2   (0)| 00:00:01 |
|  11 |          UNION ALL PUSHED PREDICATE  |                           |       |       |            |          |
|  12 |           TABLE ACCESS BY INDEX ROWID| XXXXXXXXX_USERINFO        |     1 |    54 |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | XXXXXXXXX_USERINFO_IDX8   |     1 |       |     1   (0)| 00:00:01 |
|  14 |           TABLE ACCESS BY INDEX ROWID| XXXXXXXXX_APPENDUSER      |     1 |    59 |     1   (0)| 00:00:01 |
|* 15 |            INDEX UNIQUE SCAN         | XXXXXXXXX_APPENDUSER_IDX5 |     1 |       |     1   (0)| 00:00:01 |
|  16 |     TABLE ACCESS BY INDEX ROWID      | XXXXXXXXX_REPORTINFO      |     1 |    31 |     1   (0)| 00:00:01 |
|* 17 |      INDEX UNIQUE SCAN               | XXXXXXXXX_REPORTINFO_PK   |     1 |       |     1   (0)| 00:00:01 |
|* 18 |    INDEX UNIQUE SCAN                 | RESULT_UNIQUE_CHECK       |     1 |       |     1   (0)| 00:00:01 |
|  19 |   TABLE ACCESS BY INDEX ROWID        | XXXXXXXXX_PUNISH_RESULT   |     1 |    24 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

过了两天，又发现第三个语句，第四个语句。。。。。。，原来该语句是由程序动态拼写出来的，其中根据几个输入框内容拼写的，输入框可选，而且该语句还涉及分页，页码不定。所以不能利用baseline了，只能让业务修改下该语句，添加push_pred()提示来控制了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-743110/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-743110/