sql pan baseline

zhouwf0726發表於2019-03-04
SQL

從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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章