全部博文（39）
分类： Oracle
2013-01-06 17:18:46
在11g中，oracle号称Sql PLAN Baseline(SPB)是outline的演化，不过
她起来还是跟sql profile最为为相近，所以我感觉SPB是sql profile到ou
tline（比前辈还弱）趋于完善化的产物，所以本文没有对SPB做过多讨论。
建议：使用SPB代替outline和sql PROFILE。
但是如果想分类，SQL PROFILE还是有用武之地的，相比之下sql PROFILE
比SPM和outline都要灵活和强大。
———————————————————————————————————————————————
1.建立测试表
--伪造大表
DROP TABLE spm_tab PURGE;
CREATE TABLE spm_tab(ID INT,deptno NUMBER,padding CHAR(100))
PCTFREE 85 PCTUSED 15
NOLOGGING;
INSERT INTO spm_tab NOLOGGING
SELECT LEVEL, MOD(LEVEL, 10), dbms_random.STRING('U', 10)
FROM dual
CONNECT BY LEVEL <= 10000;
COMMIT;
--创建索引
CREATE INDEX ind_spmt_id ON spm_tab(ID) NOLOGGING;
--收集统计信息
BEGIN
dbms_stats.gather_table_stats(ownname => USER,
tabname => 'spm_tab',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
cascade => TRUE);
END;
/
———————————————————————————————————————————————
2.转换执行计划到SPB中
SELECT * FROM spm_tab st WHERE st.id=1;
--索引扫描
--从LBC中找出对应CURSOR
SELECT sql_id, hash_value, plan_hash_value, child_number, sql_text
FROM v$sql s
WHERE s.sql_text LIKE '%SELECT * FROM spm_tab st WHERE st.id=1%'
AND lower(s.sql_text) NOT LIKE lower('%explain%')
AND lower(s.sql_text) NOT LIKE lower('%v$sql%');
--2nv46cskp3ufw 626125276 1309042254 0 SELECT * FROM spm_tab st WHERE st.id=1
--将LBC中执行计划转换成SPB
DECLARE
v_retval INT;
BEGIN
v_retval := dbms_spm.load_plans_from_cursor_cache(sql_id => '2nv46cskp3ufw',
plan_hash_value => '1309042254',
--sql_handle => 'spm_sql1',
fixed => 'YES',
enabled => 'YES');
IF v_retval = 0
THEN
dbms_output.put_line('未找到对应游标!');
ELSE
dbms_output.put_line(v_retval);
END IF;
END;
/
———————————————————————————————————————————————
3.SPB属性讨论
--其它重要属性（以字典中列展现）
SELECT * FROM dba_sql_plan_baselines dspb;
SIGNATURE
SQL_HANDLE --sql句柄（这个概念就不解释了）
SQL_TEXT
PLAN_NAME --对应句柄下的对应计划名（与句柄是多对一关系）
CREATOR
ORIGIN
PARSING_SCHEMA_NAME
DESCRIPTION
VERSION
CREATED
LAST_MODIFIED
LAST_EXECUTED --近期被使用时间（如果此plan最近没被使用过，且过期，是autopurge，非fixed，则会被删除；解释的有点疼）
LAST_VERIFIED
ENABLED --是否启用
ACCEPTED --与演化有关（本文没有介绍基线演化操作）
FIXED --过期后是否可被删除，且是否能被演化
AUTOPURGE --是否被自动删除
OPTIMIZER_COST
MODULE
ACTION
--运行时信息
EXECUTIONS
ELAPSED_TIME
CPU_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
ROWS_PROCESSED
FETCHES
END_OF_FETCH_COUNT
———————————————————————————————————————————————
4.查看SPB中执行计划
SELECT *
FROM TABLE(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SYS_SQL_9b347ce974080601',
plan_name => 'SQL_PLAN_9qd3wx5u0h1h1b3518535',
format => 'basic'));
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| SPM_TAB |
| 2 | INDEX RANGE SCAN | IND_SPMT_ID |
---------------------------------------------------
———————————————————————————————————————————————
5.手动生成新计划 - 并将此计划装载到同一sql句柄下
--生成执行计划到LBC中
SELECT --+full(st)
*
FROM spm_tab st
WHERE st.id = 1;
SELECT sql_id, hash_value, plan_hash_value, child_number, sql_text
FROM v$sql s
WHERE s.sql_text LIKE '%SELECT --+full(st)%'
AND lower(s.sql_text) NOT LIKE lower('%explain%')
AND lower(s.sql_text) NOT LIKE lower('%v$sql%');
--cr7b2cfzknjfk 3207218642 2547608915 0 SELECT --+full(st) * FROM spm_tab st WHERE st.id = 1
--装载LBC中新执行计划到同一sql句柄下
DECLARE
v_retval INT;
BEGIN
v_retval := dbms_spm.load_plans_from_cursor_cache(sql_id => 'cr7b2cfzknjfk',
plan_hash_value => '2547608915',
sql_handle => 'SYS_SQL_9b347ce974080601',
fixed => 'YES',
enabled => 'YES');
IF v_retval = 0
THEN
dbms_output.put_line('未找到对应游标!');
ELSE
dbms_output.put_line(v_retval);
END IF;
END;
/
———————————————————————————————————————————————
6.查看SPB使用情况
SET autotrace ON
SELECT * FROM spm_tab st WHERE st.id=1;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SPM_TAB | 1 | 108 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_SPMT_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_9qd3wx5u0h1h1b3518535" used for this statement
———————————————————————————————————————————————
7.使用新计划
--删除老的计划，也可以disable。
DECLARE
v_retval INT;
BEGIN
v_retval := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_9b347ce974080601',
plan_name => 'SQL_PLAN_9qd3wx5u0h1h1b3518535');
IF v_retval = 0
THEN
dbms_output.put_line('未找到对应的plan!');
ELSE
dbms_output.put_line(v_retval);
END IF;
END;
/
SELECT * FROM spm_tab st WHERE st.id=1;
执行计划
----------------------------------------------------------
Plan hash value: 2547608915
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 273 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| SPM_TAB | 1 | 108 | 273 (1)| 00:00:04 |
-----------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_9qd3wx5u0h1h1c00ed501" used for this STATEMENT
———————————————————————————————————————————————
8.周边探讨
--启用与禁用
ALTER session SET optimizer_use_sql_plan_baselines=FALSE;
--迁移（方法与sql profile一样）
DECLARE
v_retval NUMBER;
BEGIN
dbms_spm.create_stgtab_baseline(table_name => 'my_spm_tab1');
v_retval := dbms_spm.pack_stgtab_baseline(table_name => 'my_spm_tab1',
sql_handle => 'SYS_SQL_9b347ce974080601');
dbms_output.put_line(v_retval);
END;
/
--属性修改
DECLARE
v_retval NUMBER;
BEGIN
v_retval := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_9b347ce974080601',
plan_name => 'SQL_PLAN_9qd3wx5u0h1h1c00ed501',
attribute_name => 'description',
attribute_value => 'Just a test sql plan baseline!');
dbms_output.put_line(v_retval);
END;
/
貌似baseline不存在category的概念，感觉没sql PROFILE灵活。
--全局配置
SELECT * FROM dba_sql_management_config;
BEGIN
dbms_spm.configure(parameter_name => 'PLAN_RETENTION_WEEKS',
parameter_value => '83');
END;
/
--outline向SPM的迁移接口
dbms_spm.migrate_stored_outline
