SQL Tuning Advisor(STA) 到底做了什么?
来源:互联网 发布:质量控制系统 php 编辑:程序博客网 时间:2024/04/29 18:55
SQL Tuing Advisor(STA) 是Automatic Tuning Optimizer(自动优化调整器)的一部分。在前面的文章使用SQL tuning advisor(STA)自动优化SQL中描述了SQL Tuing Advisor(STA)的相关背景并给出示例。本文主要是描述STA底层到底为我们作了什么使得SQL语句得以优化,同时演示绑定变量的情形下接受sql profile后,后续SQL是否采纳对应的sql profile的执行计划的情形。最后给出了awr中的SQL通过STA tuning的脚本。
1、使用STA优化library cache中的SQL
- --演示环境
- hr@CNMMBO> select * from v$version where rownum<2;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- --下面直接根据sql_id优化library cache中的SQL语句
- hr@CNMMBO> @tune_cache_sql
- Enter value for input_sql_id: 8rnmr2dpnjvk8
- Enter value for input_task_name: hr_query
- RECS
- ---------------------------------------------------------------------------------------
- GENERAL INFORMATION SECTION
- -------------------------------------------------------------------------------
- Tuning Task Name : hr_query
- Tuning Task Owner : HR
- Scope : COMPREHENSIVE
- Time Limit(seconds) : 1800
- Completion Status : COMPLETED
- Started at : 06/07/2013 11:40:27
- Completed at : 06/07/2013 11:40:28
- Number of SQL Profile Findings : 1
- Number of SQL Restructure Findings: 1
- -------------------------------------------------------------------------------
- Schema Name: HR
- SQL ID : 8rnmr2dpnjvk8
- SQL Text : SELECT /*+ ORDERED */
- *
- FROM employees e, locations l, departments d
- WHERE e.department_id = d.department_id AND l.location_id =
- d.location_id AND e.employee_id < :bnd
- -------------------------------------------------------------------------------
- FINDINGS SECTION (2 findings)
- -------------------------------------------------------------------------------
- 1- SQL Profile Finding (see explain plans section below)
- --------------------------------------------------------
- A potentially better execution plan was found for this statement.
- Recommendation (estimated benefit: 90.74%)
- ------------------------------------------
- - Consider accepting the recommended SQL profile.
- execute dbms_sqltune.accept_sql_profile(task_name => 'hr_query', replace
- => TRUE);
- 2- Restructure SQL finding (see plan 1 in explain plans section)
- ----------------------------------------------------------------
- An expensive cartesian product operation was found at line ID 3 of the
- execution plan.
- Recommendation
- --------------
- - Consider removing the "ORDERED" hint.
- Rationale
- ---------
- The "ORDERED" hint might force the optimizer to generate a cartesian
- product. A cartesian product should be avoided whenever possible because
- it is an expensive operation and might produce a large amount of data.
- -------------------------------------------------------------------------------
- EXPLAIN PLANS SECTION
- -------------------------------------------------------------------------------
- 1- Original With Adjusted Cost
- ------------------------------
- Plan hash value: 3871948714
- -----------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 85 | 11645 | 103 (1)| 00:00:02 |
- |* 1 | HASH JOIN | | 85 | 11645 | 103 (1)| 00:00:02 |
- | 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
- | 3 | MERGE JOIN CARTESIAN | | 1973 | 225K| 99 (0)| 00:00:02 |
- | 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 86 | 5848 | 3 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 86 | | 1 (0)| 00:00:01 |
- | 6 | BUFFER SORT | | 23 | 1127 | 96 (0)| 00:00:02 |
- | 7 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 1 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND
- "L"."LOCATION_ID"="D"."LOCATION_ID")
- 5 - access("E"."EMPLOYEE_ID"<:BND)
- 2- Using SQL Profile
- --------------------
- Plan hash value: 2153960720
- --------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 85 | 11645 | 10 (20)| 00:00:01 |
- |* 1 | HASH JOIN | | 85 | 11645 | 10 (20)| 00:00:01 |
- | 2 | MERGE JOIN | | 27 | 1863 | 6 (17)| 00:00:01 |
- | 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
- | 4 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | | 1 (0)| 00:00:01 |
- |* 5 | SORT JOIN | | 23 | 1127 | 4 (25)| 00:00:01 |
- | 6 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
- | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 86 | 5848 | 3 (0)| 00:00:01 |
- |* 8 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 86 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
- 5 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
- filter("L"."LOCATION_ID"="D"."LOCATION_ID")
- 8 - access("E"."EMPLOYEE_ID"<:BND)
- -------------------------------------------------------------------------------
- -->上面的advisor report中得到了两个findings,一个是建议我们接受profile,一个建议我们移出ordered hint,因为它导致了笛卡尔集
2、根据优化建议接受SQL profile
- -->根据上面的advisor,接下来我们accept这个profile,唯一不同的增加了一个名字,如果不指定名字,系统会自动生成一个
- hr@CNMMBO> execute dbms_sqltune.accept_sql_profile(task_name => 'hr_query',replace=> TRUE,name=>'hr_profile');
- PL/SQL procedure successfully completed.
- --所有生成的SQL profile的相关信息都存放在数据字典dba_sql_profiles中
- hr@CNMMBO> select name,category,signature,sql_text,created,type,status,force_matching from dba_sql_profiles;
- NAME CATEGORY SIGNATURE SQL_TEXT CREATED TYPE STATUS FOR
- ------------ ---------- -------------------- ----------------------------------- ----------------- --------- -------- ---
- hr_profile DEFAULT 8856746596263812636 SELECT /*+ ORDERED */ 20130607 14:27:33 MANUAL ENABLED NO
- *
- FROM employees e, locations l, de
- partments d
- WHERE e.department_id = d.departme
- nt_id AND l.location_id = d.locatio
- n_id AND e.employee_id < :bnd
- ------------------------------------------------------------------------------------------------------------
- --下面是dba_sql_profiles数据字典中的列几个重要的说明
- a、CATEGORY: Category of the SQL profile
- -- 用于对SQL Profile实施分类管理,指明了当前的SQL profile属于哪一个分类,缺省情况下dafault类会被激活
- -- 可以在接受SQL profile时指定分类,具体参考包dbms_sqltune.accept_sql_profile
- -- 初始化参数sqltune_category用于控制系统和会话级别的category被激活,也就是说单个session仅仅支持单一的category激活
- -- 如下查看当前设定的category及如何修改
- -->查看系统的sqltune_category参数
- hr@CNMMBO> show parameter sqltune
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- sqltune_category string DEFAULT
- -->通过下面的方式来修改缺省的catagory类别
- alter session | system sqltune_category = category_name;
- -------------------------------------------------------------------------------------------------------------------
- b、SIGNATURE: Unique identifier generated from normalized SQL text
- -- 用于控制标准化SQL文本的到唯一签名ID并将该ID的profile的信息存储到数据字典
- -- 在category激活以及profile被启用的情形下,后续的SQL语句运行时如果得到的签名ID与存储到数据字典的ID相同,则SQL profile被使用
- -------------------------------------------------------------------------------------------------------------------
- c、TYPE: Type of the SQL profile (how it was created):MANUAL AUTO-TUNE
- -- 该列表明对应的sql profile是自动还是手动产生的
- -------------------------------------------------------------------------------------------------------------------
- d、STATUS: Status of the SQL profile: ENABLED DISABLED VOID
- -- 该列表明对于sql profile是否处于启用状态
- -------------------------------------------------------------------------------------------------------------------
- e、FORCE_MATCHING
- -- If TRUE this causes SQL Profiles to target all SQL statements which have the same text after normalizing all literal values to bind variables.
- -- (Note that if a combination of literal values and bind variables is used in the same SQL text, no transformation occurs).
- -- This is analogous to the matching algorithm use by the FORCE option of the CURSOR_SHARING parameter.
- -- If FALSE, literals are not transformed.
- -- This is analogous to the matching algorithm used by the EXACT option of the CURSOR_SHARING parameter.
- --FORCE_MATCHING描述了SQL文本是模糊匹配还是精确匹配
- --为true时等同于CURSOR_SHARING参数的force,不区分空格,大小写及字面量。如果字面量与绑定变量混合则产生不同的签名ID
- --为false时等同于CURSOR_SHARING参数的exact,不区分空格和大小写
- --CURSOR_SHARING参数设置影响解析,字面量产生硬解析,绑定变量产生软解析(library cache中对应的sql存在时,可以参考Oracle硬解析与软解析
- --字面量与绑定变量
- select * from scott.emp where ename='SCOTT'; --SCOTT即是字面量
- select * from scott.emp where ename=:b1; --b1即是绑定变量
- --上面的查询结果为no,也就是说此时使用的是缺省值false.
- --前面我们完成tuning后生成的report中提到了建议接受一个profile以及remove提示ordered
- --那我们看看接受sql profile之后Oracle到底干了什么
- --从下面的查询可知,sql profile告诉优化器忽略该SQL语句中的提示,这就是他做的事情
- hr@CNMMBO> SELECT attr_val
- 2 FROM sys.sqlprof$ p, sys.sqlprof$attr a
- 3 WHERE p.sp_name = 'hr_profile' AND p.signature = a.signature AND p.category = a.category;
- ATTR_VAL
- --------------------------------------------------------------------------------------------------
- IGNORE_OPTIM_EMBEDDED_HINTS
3、测试接受SQL profile后的情形
- --对于OLTP的情形,大部分SQL语句使用了绑定变量,下面来测试一下绑定变量是否影响sql profile的运用
- --以及测试SQL语句在使用大小写,添加或移除空格的情形时sql profile是否被使用到
- hr@CNMMBO> variable bnd number; --定义绑定变量并赋值
- hr@CNMMBO> exec :bnd:=166
- PL/SQL procedure successfully completed.
- hr@CNMMBO> set autot trace exp;
- hr@CNMMBO> SELECT /*+ ORDERED */
- 2 *
- 3 FROM employees e, locations l, departments d
- 4 WHERE e.department_id = d.department_id AND l.location_id =
- 5 d.location_id AND e.employee_id < :bnd;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 685643925
- ------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5 | 685 | 9 (23)| 00:00:01 |
- |* 1 | HASH JOIN | | 5 | 685 | 9 (23)| 00:00:01 |
- | 2 | MERGE JOIN | | 5 | 440 | 5 (20)| 00:00:01 |
- | 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
- | 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
- |* 5 | SORT JOIN | | 5 | 340 | 3 (34)| 00:00:01 |
- | 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 340 | 2 (0)| 00:00:01 |
- |* 7 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 2 | | 1 (0)| 00:00:01 |
- | 8 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
- 5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
- filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
- 7 - access("E"."EMPLOYEE_ID"<TO_NUMBER(:BND))
- Note
- -----
- - SQL profile "hr_profile" used for this statement --->这个提示表明sql profile已经被使用
- --下面我们给绑定变量重新赋值,以及使用小写来替换原来的SQL语句,并且移除一些空格,看看profile是否依旧会被使用
- hr@CNMMBO> exec :bnd:=133;
- PL/SQL procedure successfully completed.
- hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d
- 2 where e.department_id = d.department_id AND l.location_id = d.location_id
- 3 and e.employee_id < :bnd;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 685643925
- ------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5 | 685 | 9 (23)| 00:00:01 |
- |* 1 | HASH JOIN | | 5 | 685 | 9 (23)| 00:00:01 |
- | 2 | MERGE JOIN | | 5 | 440 | 5 (20)| 00:00:01 |
- | 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 |
- | 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
- |* 5 | SORT JOIN | | 5 | 340 | 3 (34)| 00:00:01 |
- | 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 340 | 2 (0)| 00:00:01 |
- |* 7 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 2 | | 1 (0)| 00:00:01 |
- | 8 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 3 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
- 5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
- filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
- 7 - access("E"."EMPLOYEE_ID"<TO_NUMBER(:BND))
- Note
- -----
- - SQL profile "hr_profile" used for this statement --->这个提示表明sql profile已经被使用
- --从上面的测试可知,大小写差异,空格多少以及绑定变量对上面的SQL语句并没有影响,之前的profile依旧有效
4、对比优化前后的I/O 开销
- a、先测试优化后的I/O cost
- hr@CNMMBO> set autot trace stat;
- hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d
- 2 where e.department_id = d.department_id AND l.location_id = d.location_id
- 3 and e.employee_id < :bnd;
- 33 rows selected.
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 14 consistent gets
- 10 physical reads
- 0 redo size
- 5362 bytes sent via SQL*Net to client
- 514 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 33 rows processed
- --上面的Statistics表明优化后的consistent gets为14,physical reads为10
- --将sql profile disable,则该profile不会生效
- hr@CNMMBO> set autot off;
- hr@CNMMBO> BEGIN
- 2 DBMS_SQLTUNE.alter_sql_profile (name => 'hr_profile', attribute_name => 'STATUS', VALUE => 'DISABLED');
- 3 END;
- 4 /
- PL/SQL procedure successfully completed.
- --Author : Robinson
- --Blog : http://blog.csdn.net/robinson_0612
- -->查看disable后的状态
- hr@CNMMBO> select name,status from dba_sql_profiles where name='hr_profile';
- NAME STATUS
- ------------------------------ --------
- hr_profile DISABLED
- hr@CNMMBO> print bnd;
- BND
- ----------
- 133
- --下面来查看disable后的统计信息,SQL语句会回到优化之前
- hr@CNMMBO> set autot trace stat;
- hr@CNMMBO> select /*+ ORDERED */ * from employees e, locations l, departments d
- 2 where e.department_id = d.department_id AND l.location_id = d.location_id
- 3 and e.employee_id < :bnd;
- 33 rows selected.
- Statistics
- ----------------------------------------------------------
- 9 recursive calls
- 0 db block gets
- 27 consistent gets
- 5 physical reads
- 0 redo size
- 5403 bytes sent via SQL*Net to client
- 514 bytes received via SQL*Net from client
- 4 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 33 rows processed
- --从上面的统计信息可知,SQL优化之前的consistent gets为27,大于优化之后的consistent gets
- --注,此处未对比physical reads,一般情形下我们考虑的是consistent gets,如果需要两个都对比,应每次执行SQL前flush buffer cache
- -->接下来可以移除sql profile
- hr@CNMMBO> set autot off;
- hr@CNMMBO> exec dbms_sqltune.drop_sql_profile('hr_profile');
- PL/SQL procedure successfully completed.
5、使用STA优化awr中SQL的脚本
- robin@SZDB:~/dba_scripts/custom/sql> more tune_awr_sql.sql
- SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF
- SET SCAN ON PAGESIZE 9999
- SET LONG 1000000 LINESIZE 180
- COL recs FORMAT a145
- --Author : Robinson
- --Blog : http://blog.csdn.net/robinson_0612
- VARIABLE tuning_task VARCHAR2(30)
- DECLARE
- l_sql_id v$session.prev_sql_id%TYPE:='&input_sql_id';
- BEGIN
- :tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id,
- begin_snap=>&input_begin_snap,
- end_snap=>&input_end_snap,
- task_name=>'&input_task_name');
- dbms_sqltune.execute_tuning_task(:tuning_task);
- END;
- /
- SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs
- FROM dual;
- SET VERIFY ON FEEDBACK ON
6、小结
a、SQL优化器在tuning模式下不会对原始SQL语句文本做任何修改。
b、对于使用STA优化的SQL语句,SQL优化器会转换优化器的模式或移除添加hint(如本例是忽略hint)以实现优化。
c、SQL profile会纠正查询优化器对基数的错误评估。
d、对于统计信息缺失或过时或根本不存在统计信息的情形,SQL优化器会给出建议以获取真实的统计信息。
e、SQL profile能够被导入导出。导出之前,该profile必须先被accept。
f、SQL profile所依赖的对象被删除后,SQL profile并不会被删除。但如果使用drop user username cascade时是例外。
Forward from http://blog.csdn.net/leshami/article/details/9054431
0 0
- SQL Tuning Advisor(STA) 到底做了什么?
- SQL Tuning Advisor(STA) 到底做了什么?
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- STA(SQL Tuning Advisor) SQL调优顾问简介
- 用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 使用SQL tuning advisor(STA)自动优化SQL
- SQL Tuning Advisor(SQL调优顾问,STA)
- 使用SQL Tuning Advisor (STA) 优化SQL语句
- 使用SQL tuning advisor(STA)自动优化SQL
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 如何用 SQL Tuning Advisor (STA) 优化SQL语句
- 小银行系统
- Eclipse快捷键大全(转载)
- 获取本地视频缩略图,既第一帧图片的方法
- INSERT FIRST && INSERT ALL(按条件进行多表插入)
- zynq中断入门
- SQL Tuning Advisor(STA) 到底做了什么?
- adb的基本操作
- restful理解
- cocos2d-x3.0 关于ScrollView的使用
- 黑马程序员之Java--枚举类型
- 关于EXPORT_SYMBOL
- Qt5官方Demo解析集1——Fortune Server/Client
- 史上最经典的Linux内核学习方法论
- 在iOS / Android的统一字体问题