很多时候可能我们都希望CBO能够帮我们生成正确、高效的执行计划，但是很多时候事实并非如此，可能因为各种各样的原因(如，统计信息不正确或者CBO天生的缺陷等)都会导致生成的执行计划特别的低效。之前的一家公司有一台专门用于批量做数据校验清洗的数据库，每次校验清洗完成数据就会清理掉，统计信息经常会发生较大的变更，之前跑得好好的SQL，可能有时候跑5-6个小时都跑不完了，这时候查看执行计划，发现不正确的统计信息导致了执行计划的变更。
这时候我们就希望数据库中运行的SQL都能有正确、稳定的执行计划，在10g开始的版本中可以通过SQL Profile来稳定执行计划或者在不改变SQL的情况下修改执行计划。11g开始可以使用偏主动的稳定执行计划的手段——SPM(SQL PLAN MANAGEMENT)，保证只有被验证过的执行计划才会被启用。
SQL Profile
SQL Profile是包含特定于SQL语句的辅助统计信息的数据库对象，可以改进优化器基数估计，从而选择更好的执行计划。
当选择执行计划时优化器会考虑以下信息：
- SQL Profile提供的辅助统计信息
- 当时SQL的运行环境，如数据库配置，变量绑定，与优化器相关的统计信息等。
所以，上面两个条件的任意一个发生变化，都有可能导致执行计划的改变。下面看下SQL Profile的一些基本操作以及如何在线进行SQL的调整。
accepting sql profile
通过DBMS_SQLTUNE.ACCEPT_SQL_PROFILE存储过程可以接受一个SQL Profile，只有在我们接受了一个SQL Profile之后，优化器才能使用他作为产生执行计划的输入。这个存储过程有两个比较重要的参数：
- profile_type
这个参数用于控制是否改变并行执行行为，REGULAR_PROFILE不更改为并行执行，PX_PROFLE用于更改并行执行的SQL Profile。 - force_match
该参数用于控制SQL语句匹配，有两个值——TRUE和FALSE。对于SQL语句中where条件的字面值，当force_match=TRUE时，会将其替换为变量绑定，所以当字面值不同时也可以重用该SQL Profile。值为FALSE时，where条件的字面值则不会替换。
下面是ACCEPT_SQL_PROFILE的例子：
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'STA_SPECIFIC_EMP_TASK'
, name => 'my_sql_profile'
, profile_type => DBMS_SQLTUNE.PX_PROFILE
, force_match => true
);
END;
/
Listing SQL Profile
可以通过DBA_SQL_PROFILES数据字典视图来查看存储在数据库中的SQL Profile。
SQL> SELECT NAME,CATEGORY,SQL_TEXT,FORCE_MATCHING,STATUS FROM DBA_SQL_PROFILES;
NAME CATEGORY SQL_TEXT FOR STATUS
------------------------------ ---------- ------------------------- --- --------
SYS_SQLPROF_016986bccd640000 DEFAULT select /*+ use_nl(a b) in NO ENABLED
dex(b) */a.brwyid,a.yljgd
m,a.jzlsh,b.mzzddm from t
est_e
Altering SQL Profile
通过ALTER_SQL_PROFILE中的attribute_name参数可以修改SQL Profile相应的参数值。
BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name => 'my_sql_profile'
, attribute_name => 'FORCE_MATCH'
, value => 'TRUE'
);
END;
/
Droping SQL Profile
通过DROP_SQL_PROFILE存储过程可以删除特定的SQL Profile
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name => 'my_sql_profile'
);
END;
/
通过SQL Profile调整线上SQL执行计划
通过手工创建SQL Profile的方式，可以在不更改目标SQL的SQL文本的情况下修改SQL的执行计划，而且可以很好的稳定SQL的执行计划。
下面是手工创建SQL Profile的例子,在TEST_ENV.TB_TABLE_LIST的列TABLE_NAME上有一个名为IDX_TB_TABLE_LIST_TBNAME的B树索引：
1、首先加一个全表扫描的HINTS来执行下面的SQL，模拟线上的一个执行低效的SQL，并查看其执行计划。
SQL> SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
------------------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 1a319c1c2b3rz, child number 0
-------------------------------------
SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'
Plan hash value: 1475094007
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| TB_TABLE_LIST | 1 | 18 | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]
已选择 43 行。
2、然后加入走索引的HINTS来更正这个SQL的执行计划，得到下面的执行计划相关信息，此时我们就需要用这个执行计划来替换掉上面走全表扫描的SQL的执行计划。
SQL> SELECT /*+ INDEX(T IDX_TB_TABLE_LIST_TBNAME) */TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';
TABLE_NAME
------------------------------
ACCESS$
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 44j1ysb93cwdq, child number 0
-------------------------------------
SELECT /*+ INDEX(T IDX_TB_TABLE_LIST_TBNAME) */TABLE_NAME FROM
TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$'
Plan hash value: 3318876060
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 1 | INDEX RANGE SCAN| IDX_TB_TABLE_LIST_TBNAME | 1 | 18 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"='ACCESS$')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TABLE_NAME"[VARCHAR2,128]
已选择 43 行。
3、下面查看对应的SQL_ID。
SQL> SELECT SQL_TEXT,SQL_ID FROM V$SQLAREA WHERE SQL_TEXT LIKE '%TABLE_NAME FROM TEST_ENV.TB_TABLE%';
SQL_TEXT SQL_ID
------------------------- -------------
SELECT /*+ INDEX(T IDX_TB 44j1ysb93cwdq
_TABLE_LIST_TBNAME) */TAB
LE_NAME FROM TEST_ENV.TB_
TABLE_LIST T WHERE TABLE_
NAME='ACCESS$'
SELECT SQL_TEXT,SQL_ID FR g4v1sg4ycf96y
OM V$SQLAREA WHERE SQL_TE
XT LIKE '%TABLE_NAME FROM
TEST_ENV.TB_TABLE%'
SQL_TEXT SQL_ID
------------------------- -------------
SELECT /*+FULL(T)*/ TABLE 1a319c1c2b3rz
_NAME FROM TEST_ENV.TB_TA
BLE_LIST T WHERE TABLE_NA
ME='ACCESS$'
4、创建SQL PROFILE，用正确的执行计划的OUT LINE DATA来创建SQL Profile
SQL> declare
2 v_hints sys.sqlprof_attr;
3 clsql_text clob;
4 begin
5 v_hints := sys.sqlprof_attr('BEGIN_OUTLINE_DATA',
6 'IGNORE_OPTIM_EMBEDDED_HINTS',
7 'OPTIMIZER_FEATURES_ENABLE(''18.1.0'')',
8 'DB_VERSION(''18.1.0'')',
9 'ALL_ROWS',
10 'OUTLINE_LEAF(@"SEL$1")',
11 'INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))',
12 'END_OUTLINE_DATA');
13
14 select sql_fulltext into clsql_text from v$sqlarea where sql_id='1a319c1c2b3rz';
15
16 dbms_sqltune.import_sql_profile(clsql_text,v_hints,'my_sql_profile',force_match=>true,r
最低0.47元/天 解锁文章
143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
