SQL Profile（第二篇）
在11GR2上SQL Tuning Advisor已经变得非常好用，我非常喜欢将一些非常复杂的SQL语句交给SQL Tuning Advisor来调优，几乎每次都不让我失望，通常调优结束后，SQL Tuning Advisor都会给你一些建议，例如建议你创建索引或者收集统计信息，或者建议你接受SQL Profile并且给出了接受SQL Profile后性能将得到的提升。本节将会给出一个示例来演示如何通过SQL Tuning Advisor来使用SQL Profile。首先我们需要构建一下需要用到的测试表：
SQL>CREATE TABLE test
2 AS
3 SELECT ROWNUM id,
4 DBMS_RANDOM.STRING('A', 12) name,
5 DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status
6 FROM all_objects a,dba_objects b
7 WHERE ROWNUM <= 50000;
Table created.
SQL>create index t_ind on t(status);
Index created.
SQL>begin
2 dbms_stats.gather_table_stats(ownname =>'test',
3 tabname => 'test',
4 no_invalidate => FALSE,
5 estimate_percent => 100,
6 force => true,
7 degree => 5,
8 method_opt => 'for all columns size 1',
9 cascade => true);
10 end;
11 /
SQL>select status,count(*) from test group by status;
STATUS COUNT(*)
---------------- ----------
Active 49900
Inactive 100
上面的代码做了下面几件事：
l创建了一张测试表test,总记录数50000。
l表上的字段status一共有2个唯一值：Active和Inactive，此字段有数据倾斜。
l列status上值为Active的值有49900个，占了表里绝大多数的记录，为Inactive的记录非常少，只有100个。
lstatus字段上有索引，分析了表的统计信息，但是status字段没有收集直方图。
我们来对status为Inactive的值做查询，由于status为Inactive的值非常少，因此走索引扫描性能更好，但是由于列上缺少直方图，因此执行计划会走全表扫描：
SQL>select count(name) from test where status='Inactive';
COUNT(NAME)
-----------
100
SQL>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID c37q7z5qjnwwf, child number 0
-------------------------------------
select count(name) from test where status='Inactive'
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 51 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| TEST | 25000 | 512K| 51 (2)| 00:00:01 |
---------------------------------------------------------------------------
上面执行计划里显示的经过谓词过滤后的全表扫描返回的基数为25000，因为缺少直方图，因此优化器就简单的通过 基数=表的总记录数/status字段的唯一值数量=50000/2=25000来得出基数。我们来通过SQL Tuning Advisor分析一下这个SQL，看看优化器能不能识别到这是一个低效的执行计划，能否给出我们一些建议：
SQL>var c varchar2(100)
SQL>exec :c := dbms_sqltune.CREATE_TUNING_TASK(SQL_ID=>'c37q7z5qjnwwf')
PL/SQL procedure successfully completed.
SQL>exec dbms_sqltune.execute_tuning_task(task_name => :c)
PL/SQL procedure successfully completed.
SQL>select dbms_sqltune.report_tuning_task(:c) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:C)
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_1112
Tuning Task Owner : TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 08/01/2014 15:59:32
Completed at : 08/01/2014 15:59:33
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : c37q7z5qjnwwf
SQL Text : select count(name) from test where status='Inactive'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 51.46%)
------------------------------------------
- Consider accepting the recommended SQL Profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1112',
task_owner => 'TEST', replace => TRUE);
Validation results
------------------
The SQL Profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .00212 .000221 89.57 %
CPU Time (s): .002099 .0002 90.47 %
User I/O Time (s): 0 0
Buffer Gets: 210 102 51.42 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL Profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 51 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| TEST | 100 | 2100 | 51 (2)| 00:00:01 |
---------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 4130896540
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2100 | 2 (0)| 00:00:01|
|* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Dbms_sqltune包被用来创建调优任务、执行调优任务、查看调优结果，通过dbms_sqltune包的CREATE_TUNING_TASK函数来为SQL_ID为c37q7z5qjnwwf的SQL创建了一个调优任务。然后通过execute_tuning_task过程来执行这个调优任务，任务运行后，优化器会利用动态采样等技术去验证评估内容与实际内容的差异，并且根据差异去调整执行计划。最后通过report_tuning_task来产生report查看调优的结果。调优结果里为我们提供了一个建议，建议我们采用一个SQL Profile，并且比对了采用SQL Profile后的性能提升，report的后面EXPLAIN PLANS SECTION部分展示了采用SQL Profile后，执行计划变为了索引扫描，而且基数的评估非常准确，从25000已经变为了100。
nNote：当你运行SQL Tuning Advisor后，建议你接受一个SQL Profile，如果你想在接受SQL Profile前知道它到底为你提供了些什么，可以运行以下查询获得：
sys@DLSP>select
2 -- b.ATTR1 -- 10g 列
3 b.ATTR5 -- 11g 列
4 from
5 wri$_adv_tasks a,
6 wri$_adv_rationale b
7 where
8 a.name = 'TASK_1112'
9 and b.task_id = a.id
10 order by
11 b.rec_id, b.id
12 ;
ATTR5
---------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "TEST"@"SEL$1", "T_IND", SCALE_ROWS=0.004)
OPTIMIZER_FEATURES_ENABLE(default)
wri$_adv_tasks的name字段为任务名，在我们上面的例子里，可以通过print c在SQLPLUS环境下获得任务名，也可以在dbms_sqltune.report_tuning_task(:c)的输出里找到任务名。
我们接受这个SQL Profile来看看再次查询是否能用到刚创建的SQL Profile：
SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1112',-
> task_owner => 'TEST', replace => TRUE);
PL/SQL procedure successfully completed.
SQL>select count(name) from test where status='Inactive';
COUNT(NAME)
-----------
100
SQL>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID c37q7z5qjnwwf, child number 0
-------------------------------------
select count(name) from test where status='Inactive'
Plan hash value: 4130896540
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2100 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement
接受SQL Tuning Advisor提供的SQL Profile后，执行计划Note部分：- SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement，显示已经使用到了SQL Profile，注意由SQL Tuning Advisor产生的SQL Profile名称都是SYS_SQLPROF作为前缀，使用到SQL Profile后执行计划也已经从全表扫描变为了索引扫描。dbms_sqltune的accept_sql_profile过程有多个参数可用，task_name、task_owner指创建SQL调优任务的任务名和所属用户，参数name和DESCRIPTION指SQL Profile的名字和对SQL Profile的描述，参数CATEGORY来指定创建的SQL Profile所属的类，默认的类为default。参数replace代表是否取代已有的SQL Profile，由于一个SQL只能有一个SQL Profile，不像Baseline，一个SQL可用有多个Baseline，因此如果一个SQL已经存在了SQL Profile，那么重新创建时，必须指定replace参数，设置为true，参数force_match指明了文本标准化的方式，默认为false。一旦接受SQL Profile，就可以通过视图dba_sql_profiles视图来查看SQL Profile的相关信息。因为SQL Profile并不属于某个用户，因此all_sql_profiles和user_sql_profiles视图都不可用。
如果一个SQL使用了SQL Profile，那么这个SQL的v$sql的sql_profile字段会显示使用到的SQL Profile的名字。下面的查询显示了系统中存在的SQL Profile和当前共享池中正在使用的SQL Profile的SQL。
SQL>select name, category, status, substr(sql_text,1,25) sql_text, force_matching
2 from dba_sql_profiles
3 where sql_text like nvl('&sql_text','%')
4 and name like nvl('&name',name)
5 order by last_modified
6 ;
Enter value for sql_text:
Enter value for name:
NAME CATEGORY STATUS SQL_TEXT FORCE_
------------------------------ --------- -------- ------------------------- ------
profile_c99yw1xkb4f1u_dwrose DEFAULT ENABLED select * from test NO
profile_bhm28h6575bjy_dwrose DEFAULT ENABLED select test2.object_name, NO
profile_51k1ug4rwah3c_dwrose DEFAULT ENABLED select distinct substr(ma NO
profile_cm6stbx539mcz_dwrose DEFAULT ENABLED select count(*) from tt NO
profile_c37q7z5qjnwwf_dwrose DEFAULT ENABLED select count(name) from t NO
SQL>select sql_id,
2 child_number cn,
3 plan_hash_value plan_hash,
4 sql_profile,
5 executions execs,
6 buffer_gets / decode(nvl(executions, 0), 0, 1, executions) avg_lio
7 from v$sql s
8 where upper(sql_text) like upper(nvl('&sql_text', sql_text))
9 and sql_text not like '%from v$sql where sql_text like nvl(%'
10 and sql_id like nvl('&sql_id', sql_id)
11 and sql_profile is not null
12 order by 1, 2, 3 ;
Enter value for sql_text:
SQL_ID CN PLAN_HASH SQL_PROFILE EXECS AVG_LIO
-------------- --- ---------- ------------------------------ ----- ----------
c37q7z5qjnwwf 0 4130896540 profile_c37q7z5qjnwwf_dwrose 1 108
c37q7z5qjnwwf 1 4130896540 profile_c37q7z5qjnwwf_dwrose 2 105
我们根据SQL_PROFILE的命名知道，这些SQL Profile都不是SQL Tuning Advisor创建的，是我们手工创建的，因为SQL Tuning Advisor创建的SQL Profile都是以SYS_SQLPROF作为前缀的。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
