oracle 11g sql plan baseline(4)使用baseline覆蓋hint

看trouble shooting preformance介紹使用baseline的一個好方法

覆蓋hints
當應用的sql text中使用hints
強制了訪問路徑之類已經不允許調整，但此時你發現了更好的執行計劃就用下面的方法

SQL> drop table t;

Table dropped.

SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

建立測試表
SQL> create table t (a int, b int);

Table created.

SQL>    declare
2              begin
3              for i in 1..1000 loop
4              insert into t values(i,i+1);
5              end loop;
6              commit;
7              end;
8         /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('XH','T');

PL/SQL procedure successfully completed.

SQL> SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

COUNT(*)
----------
1
強制走fts

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4u1qj7t4xp6g0, child number 1
-------------------------------------
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 | SORT AGGREGATE    |      |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T | 1 | 4 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"=2)

19 rows selected.

強制走fts

建立index
SQL> CREATE INDEX t_ind ON t (a);

Index created.

SQL>
SQL> SELECT /*+index(t) */ count(*) FROM t WHERE a=2;

COUNT(*)
----------
1

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0x4t5g90683np, child number 0
-------------------------------------
SELECT /*+index(t) */ count(*) FROM t WHERE a=2

Plan hash value: 468740019

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     1 (100)|          |
|   1 | SORT AGGREGATE   |       |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| T_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - access("A"=2)

當發現這個強制走fts的語句走index更好時，但有不能修改使用hint走fts的sql語句，按下面的步驟

首先為這個用hints走fts的sql語句建立 sql plan baseline推薦使用從library cache中載入建立

測試用的是自動捕捉建立,原理看（1）
SQL> SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

Session altered.

SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

COUNT(*)
----------
1

SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

COUNT(*)
----------
1

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

Session altered.

SQL> SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

COUNT(*)
----------
1

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4u1qj7t4xp6g0, child number 1
-------------------------------------
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 | SORT AGGREGATE    |      |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T | 1 | 4 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"=2)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_b805d5513fdbb376 used for this statement

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

23 rows selected.

可以看到sql plan baseline建立好了而且使用了

SQL> SQL> SELECT sql_handle, plan_name
2      FROM dba_sql_plan_baselines
3      WHERE creator = user
4     AND created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SYS_SQL_c0ba6edab805d551 SYS_SQL_PLAN_b805d5513fdbb376~~~~~~~~~用hints有fts的sql plan baseline

下面修改這個sql plan baseline
方法
1.用library中載入走index的 plan 到用hints走fts 的sql plan baseline中（通過sql_handle確定sql plan baseline等於載入到這個sql_handle中）
2.此時這個用hints走fts的sql plan baseline中就有2個plan 一個走 index，一個走fts，刪除走fts的（用plan_name 確認走hints走fts的）

SQL> DECLARE
2    ret PLS_INTEGER;
3 BEGIN
4    ret := dbms_spm.load_plans_from_cursor_cache(
5             sql_id          => '&sql_id_index_scan',
6             plan_hash_value => '&plan_hash_value_index_scan',
7             sql_handle      => '&sql_handle'
8           );
9    dbms_output.put_line(ret || ' SQL plan baseline(s) created');
10    ret := dbms_spm.drop_sql_plan_baseline(
11             sql_handle => '&sql_handle',
12             plan_name => '&plan_name'
13           );
14    dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
15 END;
16 /
Enter value for sql_id_index_scan: 0x4t5g90683np
old   5:            sql_id          => '&sql_id_index_scan',
new   5:            sql_id          => '0x4t5g90683np',
Enter value for plan_hash_value_index_scan: 468740019
old   6:            plan_hash_value => '&plan_hash_value_index_scan',
new   6:            plan_hash_value => '468740019',
Enter value for sql_handle: SYS_SQL_c0ba6edab805d551
old   7:            sql_handle      => '&sql_handle'
new   7:            sql_handle      => 'SYS_SQL_c0ba6edab805d551'
Enter value for sql_handle: SYS_SQL_c0ba6edab805d551
old 11:            sql_handle => '&sql_handle',
new 11:            sql_handle => 'SYS_SQL_c0ba6edab805d551',
Enter value for plan_name: SYS_SQL_PLAN_b805d5513fdbb376
old 12:            plan_name => '&plan_name'
new 12:            plan_name => 'SYS_SQL_PLAN_b805d5513fdbb376'

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE creator = user
4 AND created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ENA ACC
--- ---
SYS_SQL_c0ba6edab805d551
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2 ~~~此時這個sql plan baseline中就有了走index的plan，但sql text沒變還是加hint full
YES YES

SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

COUNT(*)
----------
1

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4u1qj7t4xp6g0, child number 2
-------------------------------------
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2

Plan hash value: 468740019

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     1 (100)|          |
|   1 | SORT AGGREGATE   |       |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| T_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - access("A"=2)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_b805d55126cd6d8d used for this statement

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

23 rows selected.

此時走index覆蓋了hint走fts

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-628961/，如需轉載，請註明出處，否則將追究法律責任。

oracle 11g sql plan baseline(4)使用baseline覆蓋hint

相關文章