sqlprofile在10g中出现，是9ioutline的升级版，即便现在11g 19c的大环境下，依然有较高的使用率。常用的场景为不改写sql的情况下，修改其执行计划。相较于11g的sqlbaseline，使用上个人感觉sqlbaseline简单点，因为可以指定sql_id，而sqlprofile只能指定sql_text。
难点部分其实只有一个，就是如何让原sql走正确的执行计划，比如怎么去加hint，加索引等。
详细测试步骤如下，可做参考：
1 构造实验环境
2 测试执行sql语句
3 这边我们手动优化下，让他走索引nl,可以看到，逻辑读少了很多。
4 获取语句执行的sql_id
5 替换执行计划
5.1 步骤 1 获取优化后的 outline
5.2 步骤 2 带入相关参数
5.3 步骤3 查看是否生效，已经生效了，可以看到执行计划中有test1_troy_sql_profile这一条
6 查询或者删除sql profile
1 构造实验环境
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
2 测试执行 sql 语句
SQL> set autot trace exp stat
SQL> set linesize 200
SQL> set pages 100
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 402 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 2500 | 100K| 402 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 66 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 86260 | 926K| 336 (1)| 00:00:05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
38 recursive calls
0 db block gets
1532 consistent gets
0 physical reads
0 redo size
2132 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
36 rows processed
3 这边我们手动优化下，让他走索引nl,可以看到，逻辑读少了很多。
select /*+use_nl(t1,t2) index(t2)*/t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2500 | 100K| 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2500 | 100K| 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 66 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
306 consistent gets
0 physical reads
0 redo size
2132 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
4 获取语句执行的sql_id
select sql_id,sql_text from v$sql where sql_text like '% and t1.object_id=t2.object_id%'; （这一步骤没啥必要）
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
7a3t45wbn1299
select /*+use_nl(t1,t2) index(t2)*/t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
5 替换执行计划
5.1 步骤 1 获取优化后的 outline
explain plan for select /*+use_nl(t1,t2) index(t2)*/t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2500 | 100K| 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2500 | 100K| 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 66 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
NLJ_BATCHING(@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
37 rows selected.
5.2 步骤 2 带入相关参数
-- 使用 sql profile, 我们选取必要的 hint 就 OK 了，其他的可以不要 , 如下， 多行的话 逗号隔开
-- 注意单引号要变成双引号，否则会提示格式错误
declare
v_hints sys.sqlprof_attr;
begin
v_hints := sys.sqlprof_attr(
'USE_NL(@"SEL$1" "T2"@"SEL$1")',
'LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")',
'INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))'
);
dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
v_hints,'test1_troy_sql_profile',
force_match => true,replace=>true);
end;
/
成功会提示
PL/SQL procedure successfully completed.
5.3 步骤 3 查看是否生效，已经生效了，可以看到执行计划中有 test1_troy_sql_profile 这一条
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 5067 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | 2500 | 100K| 5067 (1)| 00:01:01 |
| 2 | NESTED LOOPS | | 2500 | 100K| 5067 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 66 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "test1_troy_sql_profile" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
306 consistent gets
0 physical reads
0 redo size
2132 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
6 查询或者删除 sql profile
- 如果优化效果不理想，想要删除绑定的 sql_profile, 执行计划用的是未改变 slqprofile 之前的执行计划
-- 查看
select * from dba_sql_profiles;
SQL> BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'test1_troy_sql_profile');
END;
