oracle 基线与优化（四）

--基线优化测试
create table tab_jxyh as select rownum r ,  a.* from (select * from dba_objects )a

--创建索引
drop index all_ind;
create index all_ind on TAB_JXYH(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,TIMESTAMP);

--取值范围
SQL> select  OWNER,count(OWNER) from TAB_JXYH group by OWNER;
OWNER                                                                                      COUNT(OWNER)
------------------------------------------------------------------------------------------ ------------
OWBSYS_AUDIT                                                                                         12
MDSYS                                                                                              2009
PUBLIC                                                                                            34032
OUTLN                                                                                                10
CTXSYS                                                                                              389
OLAPSYS                                                                                             721
FLOWS_FILES                                                                                          13
OWBSYS                                                                                                2
SYSTEM                                                                                              618
ORACLE_OCM                                                                                            8
EXFSYS                                                                                              312
APEX_030200                                                                                        2561
SCOTT                                                                                                 6
DBSNMP                                                                                               65
ORDSYS                                                                                             2513
ORDPLUGINS                                                                                           10
SYSMAN                                                                                             3554
APPQOSSYS                                                                                             5
XDB                                                                                                1168
ORDDATA                                                                                             257
SYS                                                                                               37744
WMSYS                                                                                               333
SI_INFORMTN_SCHEMA                                                                                    8

--收集直方图
begin
  dbms_stats.gather_table_stats('SYS',tabname => 'TAB_JXYH',method_opt => 'FOR all COLUMNS  SIZE 75');
end;

--查看索引，验证直方图
select index_name from dba_indexes where table_name='TAB_JXYH'  
select column_name,num_distinct,histogram from dba_tab_col_statistics where table_name= 'TAB_JXYH'
 
--执行语句
select *
  from (select owner,OBJECT_NAME,OBJECT_TYPE
          from TAB_JXYH
         where OWNER = 'SYS');

select *
  from (select owner,OBJECT_NAME,OBJECT_TYPE
          from TAB_JXYH
         where OWNER = 'SCOTT');

--查看执行计划多版本
select sql_id,plan_hash_value from v$sql_plan where sql_id='4tdm3s9yz15uu'
alter session set cursor_sharing=force;
set pagesize 5000 linesize 140
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(to_char('4tdm3s9yz15uu'),NULL));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char('4tdm3s9yz15uu'),NULL));

--创建基线
declare  
m_clob clob;  
begin  
select sql_fulltext  
into m_clob  
from v$sql  
where sql_id = '4tdm3s9yz15uu'  
and child_number = 0;  
dbms_output.put_line(m_clob);  
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(  
sql_id          => '4tdm3s9yz15uu',  
sql_text        => m_clob,  
fixed           => 'YES',   
enabled         => 'YES'));  
end;  

--查看基线
select signature,
      sql_handle,
      plan_name,
      origin,
      enabled,
      accepted,
      fixed,
      autopurge
 from dba_sql_plan_baselines
where upper(sql_text) like   '%TAB_JXYH%';
--基线与执行计划，HASH的关系
set linesize 300
col sql_id for a20
col SQL_PLAN_BASELINE for a30
col SQL_TEXT for a60
select a.sql_id,a.plan_hash_value,a.sql_plan_baseline,a.SQL_TEXT
 from  v$sql a,dba_sql_plan_baselines b
where  a.sql_plan_baseline=b.plan_name

--禁用基线
var cnt number;
begin
:cnt := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SQL_1fd14d59a3778420',
plan_name => 'SQL_PLAN_1znadb6jrg11073b7a490',
attribute_name => 'fixed', attribute_value => 'no');
end;
/
--启用基线
var cnt number;
begin
:cnt := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SQL_1fd14d59a3778420',
plan_name => 'SQL_PLAN_1znadb6jrg11073b7a490',
attribute_name => 'fixed', attribute_value => 'yes');
end;
/


owner      table_name           no. num_rows                       time_other           sql_id
---------- -------------------- --- ------------------------------ -------------------- ---------------
SYS        TAB_JXYH               1 86350:no. && 82.464:pct        2015-01-29 13:16:32  4tdm3s9yz15uu
SYS        TAB_JXYH               2 SYSTEM:tbs                     11:sizM              4tdm3s9yz15uu
SYS        TAB_JXYH               3 TAB_JXYH_IND:idx               OBJECT_NAME          4tdm3s9yz15uu
SYS        TAB_JXYH               3 ALL_IND:idx                    OWNER                4tdm3s9yz15uu
SYS        TAB_JXYH               3 IND_R:idx                      R                    4tdm3s9yz15uu

select sql_id,plan_hash_value from v$sql_plan where sql_id='4tdm3s9yz15uu' 
 
SQL>  select signature,
  2          sql_handle,
  3          plan_name,
  4          origin,
  5          enabled,
  6          accepted,
  7          fixed,
  8          autopurge
  9     from dba_sql_plan_baselines
 10    where upper(sql_text) like   '%TAB_JXYH%'
 11  ;

 SIGNATURE SQL_HANDLE                                                                                 PLAN_NAME                                             ORIGIN                              ENABLED   ACCEPTED  FIXED     AUTOPURGE
---------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------ --------- --------- --------- ---------
2.2927E+18 SQL_1fd14d59a3778420                                                                       SQL_PLAN_1znadb6jrg11073b7a490                        MANUAL-LOAD                                 YES       YES       YES       YES
2.2927E+18 SQL_1fd14d59a3778420                                                                       SQL_PLAN_1znadb6jrg110ade568c4                        MANUAL-LOAD                                 YES       YES       YES       YES


--修改基线
 
可以通过dbms_spm.alter_sql_plan_baseline包来修改基线的一些属性，主要有如下几个属性
 
1.ENABLED ：设置该属性的值为NO告诉Oracle 11g临时禁用某个计划，一个SQL计划必须同时标记为ENABLED和ACCEPTED，否则CBO将忽略它
2.FIXED：设置为YES，那个计划将是优化器唯一的选择[最高优先级]，即使如果某个计划可能拥有更低的成本。这让DBA可以撤销SMB的默认行为，对于转换一个存储概要进入一稳定的SQL计划基线特别有用，注意当一个新计划被添加到被标记为FIXED的SQL计划基线，该新计划不能被利用除非它申明为FIXED状态
3.AUTOPURG：设置这个属性的值为NO告诉Oracle 11g无限期保留它，从而不用担心SMB的自动清除机制
4.plan_name : 改变SQL plan 名字
5.description : 改变SQL plan描述