SQL Plan Baselines 实验01

实验说明：本实验通过自动捕获baseline执行计划的方式实现自动调整
实验描述：不开启自动捕获baseline计划，执行全表扫描，开启后生成走全表扫描的baseline计划，此时关闭自动捕获，创建索引，查看执行计划依旧走全表扫描，但会生成新的baseline信息，但是未被accepted。

##创建测试表和数据
SQL> conn scott/tiger;
SQL> create table t as select object_name,object_id from user_objects;
Table created.
##重新收集表统计信息
SQL> execute dbms_stats.gather_table_stats('SCOTT','T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
##执行查询
SQL> SELECT * FROM T WHERE OBJECT_ID=90001;

OBJECT_NAME                                              OBJECT_ID
------------------------------------------------------- ----------
SEQ_DJ_STEEDKING_U_ID                                        90001
##查看查询语句执行计划
SQL> SHOW USER ;
USER is "SYS"
SQL> SET LINE 200;
SQL> COL SQL_TEXT FOR A100;
SQL> SET PAGESIZE 20000;
SQL> SELECT SQL_ID,HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%OBJECT_ID=90001%';

SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- -------------------------------------------------------------------------------------
75zw7rfb50fzj 2521840625 SELECT * FROM T WHERE OBJECT_ID=90001
4matm8wgkzqg5  523229669 SELECT SQL_ID,HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%OBJECT_ID=90001%'

SQL> COL PLAN_TABLE_OUTPUT FOR A100;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('75zw7rfb50fzj',NULL));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  75zw7rfb50fzj, child number 0
-------------------------------------
SELECT * FROM T WHERE OBJECT_ID=90001

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=90001)


18 rows selected.

## 在没有开启基线自动收集计划功能时 ，查看sql计划基线记录
SQL> SHOW PARAMETER optimizer_capture_sql_plan_baselines;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE

SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         0
##开启自动收集基线计划并重新执行查询
SQL> alter system set optimizer_capture_sql_plan_baselines =true;         ##系统级别
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines =TRUE;  ##会话级别
SQL> SELECT * FROM T WHERE OBJECT_ID=90001;
SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         1
##查看基线计划表内容
select
sql_handle,sql_text,plan_name,creator,last_modified,last_executed,last_verified
from
dba_sql_plan_baselines ；

SQL_HANDLE        SQL_TEXT                PLAN_NAME            CREATOR    LAST_MODIFIED            LAST_EXECUTED            LAST_VERIFIED
SQL_a905a6689b8a9e53    SELECT * FROM T WHERE OBJECT_ID=90001    SQL_PLAN_ak1d6d2dsp7km94ecae5c    SCOTT    07-4月 -16 10.21.50.000000 上午    07-4月 -16 10.21.50.000000 上午    
##关闭基线并调整索引
SQL> alter system set optimizer_capture_sql_plan_baselines =false;

System altered

SQL> create index t_index on t(object_id);

Index created.
##重新执行查询并查看其执行计划
SQL> SELECT * FROM T WHERE OBJECT_ID=90001;


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('75zw7rfb50fzj',NULL));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  75zw7rfb50fzj, child number 1
-------------------------------------
SELECT * FROM T WHERE OBJECT_ID=90001

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=90001)

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


22 rows selected.

##查看基线计划表
SQL> select count(*) from dba_sql_plan_baselines where CREATOR='SCOTT';

  COUNT(*)
----------
         2

select
sql_handle,sql_text,plan_name,creator,enabled,accepted,fixed
from
dba_sql_plan_baselines WHERE CREATOR='SCOTT';

SQL_HANDLE                    SQL_TEXT                                                        PLAN_NAME                                 CREATOR    ENABLED     ACCEPTED    FIXED
SQL_a905a6689b8a9e53    SELECT * FROM T WHERE OBJECT_ID=90001    SQL_PLAN_ak1d6d2dsp7km94ecae5c    SCOTT    YES             YES                NO
SQL_a905a6689b8a9e53    SELECT * FROM T WHERE OBJECT_ID=90001    SQL_PLAN_ak1d6d2dsp7kmcdc4d0ab    SCOTT    YES            NO                 NO

##附录信息
--查询基线计划对应的执行计划
select *
from
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af'))；

--对某个已存在的sql_handle，再手动加载新的执行计划，为某个sql_handle,从库缓存中加载执行计划与性能信息，null的话会加载该sql_id对应的不同执行计划都会被加载。
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.load_plans_from_cursor_cache(
sql_handle=>'SQL_63dc5da680b1078f',
sql_id=>'2y5r75r8y3sj0',
--plan_hash_value=>'3694077449',
plan_hash_value=>NULL
);
end;
/

--对某个sql_handle,移除某个执行计划。
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SQL_63dc5da680b1078f',
plan_name=>'SQL_PLAN_67r2xnu0b21wg94ecae5c'
);
end;
/

--更改sql plan baseline中的属性
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SQL_e436abaac44f99d8',
plan_name=>'SQL_PLAN_f8dpbpb24z6fs94ecae5c',
attribute_name=>'fixed',
attribute_value=>'yes'
);
end;
/

--删除sql计划基线
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
sql_handle=>'mystgtab',        ##这两个参数至少要指定一个
plan_name=>'swew223'
);
end;
/

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

转载于:http://blog.itpub.net/27067062/viewspace-2076691/