oracle固定执行计划--sqlprofile

最新推荐文章于 2023-06-25 19:38:20 发布

conglushao5834

最新推荐文章于 2023-06-25 19:38:20 发布

阅读量278

点赞数

文章标签：数据库

Oracle Profile固定执行计划

默认采用索引
select * from test_table b where b.consumer_id='1454752907383264';
Execution Plan
----------------------------------------------------------
Plan hash value: 525319056

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| test_table | 1 | 106 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_BSC_CONSUID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

2 - access("B"."CONSUMER_ID"='1454752907383264')

强制全表扫
select /*+ full(b)*/ * from test_table b where b.consumer_id='1454752908886660';
Execution Plan
----------------------------------------------------------
Plan hash value: 3331775474

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| test_table | 1 | 106 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

1 - filter("B"."CONSUMER_ID"='1454752907383264')

select * from v$sql s where s.sql_text like '%test_table%';--获取sqlid
获取新的执行计划的outline
select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline'));
创建sql profile

declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "B"@"SEL$1")]',
q'[END_OUTLINE_DATA]'
);
dbms_sqltune.import_sql_profile(
'select * from test_table b where b.consumer_id=''1454752908886660''',
v_hints,'SQLPROFILE_001',
force_match=>true,replace=>true);
end;

select * from dba_sql_profiles;--查看创建的sqlprofile
--删除sqlprofile
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SQLPROFILE_001');
END;

固定执行计划后，查看sql
select * from test_table b where b.consumer_id='1454752907383264';
Execution Plan
----------------------------------------------------------
Plan hash value: 3331775474

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| test_table | 1 | 106 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

1 - filter("B"."CONSUMER_ID"='1454752907383264')

Note
-----
- SQL profile "SQLPROFILE_001" used for this statement

--显示使用了创建的sqlprofile
--sys.sqlprof_attr的格式要正确，否则即使显示使用也无效
以下sqlprof_attr写法，虽然显示使用sqlprofile，但实际不按照固定的执行计划走，
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE("11.2.0.4")',
'DB_VERSION("11.2.0.4")',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "B"@"SEL$1")',
'END_OUTLINE_DATA'

/
sql_text用于指定sql的全文本，可查询V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT获得。
-- NAME: import_sql_profile - import a SQL profile
-- PURPOSE: This procedure is only used by import.
-- INPUTS: (see accept_sql_profile)
-- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("CREATE ANY
-- SQL PROFILE" privilege deprecated)
--
PROCEDURE import_sql_profile(
sql_text IN CLOB,
profile IN sqlprof_attr,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN := TRUE,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE);

从11g开始，Oracle引入了SQL执行计划管理（SQL Plan Management）新特性，从而可以让系统自动的来控制SQL语句执行计划的稳定性，进而来防止由于执行计划发生变化而导致性能下降。
通过启用该特性，某条语句如果产生了一个新的执行计划，只有在它的性能比原来的执行计划好的情况下，才会被使用。
也支持手工维护SQL语句的plan history，作为对自动维护plan history 的功能补充。
optimizer_capture_sql_plan_baselines 默认为flase，不采集；
optimizer_use_sql_plan_baselines 默认为true，采用。
如果开启optimizer_capture_sql_plan_baselines boolean TRUE，当执行计划进入到dba_sql_plan_baselines中，索引失效UNUSAble，再次执行之前的sql会报错（有待深入）。
使用dbms_spm包可以进行手动的维护dba_sql_plan_baseline中，accepted=yes为采用。
http://download.csdn.net/detail/o8xv0123/4386410

--启用该特性后，查看执行计划，会显示被引用的sql_plan
SQL> select object_id from testi t where t.object_id=9538;

Execution Plan
----------------------------------------------------------
Plan hash value: 389868889

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_TESTI_OBJECTID | 1 | 5 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - access("T"."OBJECT_ID"=9538)

Note
-----
- SQL plan baseline "SQL_PLAN_3xudqsmd541yp25962f50" used for this statement

Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

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

转载于:http://blog.itpub.net/28258625/viewspace-1720565/