201811042125 sql tuning advisor

[terry笔记]Oracle SQL 优化之sql tuning advisor (STA)
前言:经常可以碰到优化sql的需求,开发人员直接扔过来一个SQL让DBA优化,然后怎么办?
当然,经验丰富的DBA可以从各种方向下手,有时通过建立正确索引即可获得很好的优化效果,但是那些复杂SQL错综复杂的表关联,却让DBA们满头大汗。
如下特别介绍一种oracle官方提供的科学优化方法STA,经过实践,不敢说此特性绝对有效,但是可以开阔思路,并且从中学到许多知识,不再用“猜”的方式去创建索引了。

SQL优化器SQL Tuning Advisor (STA),是oracle的sql优化补助工具。
其实优化sql主要有两个方案,其一是改写sql本身,改写sql需要对sql语法、数据库的执行方式都要有较好地理解。
其二就是这个STA,它属于DBMS_SQLTUNE包,它的主要作用是对于sql使用到的表创建正确的索引。

使用STA前提:

要保证优化器是CBO模式下。
show parameter OPTIMIZER_MODE
all_rows /CBO,sql所有返回行都采用基于成本的方式运行/
first_rows /CBO,使用成本和试探法相结合的方法,查找一种可以最快返回前面少数行/
first_rows_n /CBO,全部采用基于成本的优化方法CBO,并以最快的速度,返回前N行记录/
choose /如果有统计信息,采用CBO,否则采用RBO/
rule /RBO/

执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:
grant advisor to scott;

如下是STA使用例子:
1.首先创建两个练习表obj与ind,仅创建表,无需创建索引:

SQL> create table obj as select * from dba_objects;
表已创建。

SQL> create table ind as select * from dba_indexes;
表已创建。

SQL> insert into obj select * from obj;
已创建 74603 行。

SQL> insert into obj select * from obj;
已创建 149206 行。

SQL> insert into obj select * from obj;
已创建 298412 行。

SQL> insert into ind select * from ind;
已创建 5134 行。

SQL> insert into ind select * from ind;
已创建 10268 行。

SQL> insert into ind select * from ind;
已创建 20536 行。

2.然后对这两个表,obj与ind进行联合查询,并通过autotrace查看其执行计划:

SQL> set timing on
SQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;

已用时间: 00: 00: 00.15

执行计划

Plan hash value: 380737209


| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 83 | | 5063 (1)| 00:01:01 |
| 1 | SORT AGGREGATE | | 1 | 83 | | | |
|* 2 | HASH JOIN | | 5861K| 463M| 1272K| 5063 (1)| 00:01:01 |
| 3 | TABLE ACCESS FULL| IND | 44789 | 743K| | 379 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| OBJ | 577K| 36M| | 2472 (1)| 00:00:30 |

Predicate Information (identified by operation id):

2 - access(“O”.“OBJECT_NAME”=“I”.“INDEX_NAME”)

Note

  • dynamic sampling used for this statement (level=2)

统计信息

      9  recursive calls
      4  db block gets
  10406  consistent gets
      0  physical reads
      0  redo size
    425  bytes sent via SQL*Net to client
    415  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed 

通过执行计划,可以清晰的看到,在执行以上两个表的联合查询的时候,两张表走的全表扫和hash join。

正式使用STA进行优化:
第一步:创建优化任务
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务,调用存储过程DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行该任务:

SQL> set autot off
SQL> set timing off
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘select count(*) from obj o, ind i where o.object_name=i.index_name’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => ‘SCOTT’,
scope => ‘COMPREHENSIVE’,
time_limit => 30,
task_name => ‘tuning_sql_test’,
description => ‘tuning’);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_sql_test’);
END;
/
PL/SQL 过程已成功完成。

如下是参数解释:
函数CREATE_TUNING_TASK,
sql_text是需要优化的语句,
user_name是该语句通过哪个用户执行,用户名大写,
scope是优化范围(limited或comprehensive),
time_limit优化过程的时间限制,
task_name优化任务名称,
description优化任务描述。

第二步: 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task(‘tuning_sql_test’);
PL/SQL 过程已成功完成。

第三步:检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =‘tuning_sql_test’;

TASK_NAME STATUS


tuning_sql_test COMPLETED

第四步:查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
SQL> set long 999999
SQL> set serveroutput on size 999999
SQL> set line 120
SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_sql_test’) from dual;

如下是显示优化的结果:

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_SQL_TEST’)

GENERAL INFORMATION SECTION

Tuning Task Name : tuning_sql_test
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_112
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 08/29/2013 11:10:10
Completed at : 08/29/2013 11:10:12


Schema Name: SCOTT
SQL ID : 6wruu2mxyu8g3
SQL Text : select count(*) from obj o, ind i where
o.object_name=i.index_name


FINDINGS SECTION (3 findings)

1- Statistics Finding

尚未分析表 “SCOTT”.“IND”。

Recommendation

  • 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname => ‘IND’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

Rationale

为了选择好的执行计划, 优化程序需要此表的最新统计信息。

2- Statistics Finding

尚未分析表 “SCOTT”.“OBJ”。

Recommendation

  • 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname => ‘OBJ’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

Rationale

为了选择好的执行计划, 优化程序需要此表的最新统计信息。

3- Index Finding (see explain plans section below)

通过创建一个或多个索引可以改进此语句的执行计划。

Recommendation (estimated benefit: 75.74%)

  • 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index SCOTT.IDX$$_00790001 on SCOTT.OBJ(“OBJECT_NAME”);

  • 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index SCOTT.IDX$$_00790002 on SCOTT.IND(“INDEX_NAME”);

Rationale

创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

EXPLAIN PLANS SECTION

1- Original

Plan hash value: 380737209


| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time

0
1
* 2
3
4

Predicate Information (identified by operation id):

2 - access(“O”.“OBJECT_NAME”=“I”.“INDEX_NAME”)

2- Using New Indices

Plan hash value: 4048334321


| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 83 | | 1228 (2)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | 83 | | ||
| 2 | MERGE JOIN | | 5861K| 463M| | 1228 (2)| 00:00:15 |
| 3 | INDEX FULL SCAN | IDX 0 0790001 ∣ 577 K ∣ 36 M ∣ ∣ 944 ( 1 ) ∣ 00 : 00 : 12 ∣ ∣ ∗ 4 ∣ S O R T J O I N ∣ ∣ 44789 ∣ 743 K ∣ 2120 K ∣ 268 ( 1 ) ∣ 00 : 00 : 04 ∣ ∣ 5 ∣ I N D E X F A S T F U L L S C A N ∣ I D X _00790001 | 577K| 36M| | 944 (1)| 00:00:12 | |* 4 | SORT JOIN | | 44789 | 743K| 2120K| 268 (1)| 00:00:04 | | 5 | INDEX FAST FULL SCAN| IDX 00790001577K36M944(1)00:00:124SORTJOIN44789743K2120K268(1)00:00:045INDEXFASTFULLSCANIDX_00790002 | 44789 | 743K| | 18 (0)| 00:00:01 |

Predicate Information (identified by operation id):

4 - access(“O”.“OBJECT_NAME”=“I”.“INDEX_NAME”)
filter(“O”.“OBJECT_NAME”=“I”.“INDEX_NAME”)

报告如上,
仔细阅读此报告,主要给出了两种建议:1.收集两张表obj与ind的统计信息;2.在这两张表上面创建相应索引。
并且特别贴心的把语句都给了出来,完全可以拿去直接运行,
更加贴心的是,报告中把优化前后的预计效果都展示了出来,完全一目了然,更加方便了DBA考虑是否进行建议优化方案。

五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务,可以释放资源。
SQL>exec dbms_sqltune.drop_tuning_task(‘tuning_sql_test’);

结语:如上就是SQL优化器SQL Tuning Advisor (STA)的使用方法,可能语法有些复杂,但是确实给优化的工作带来了很大帮助,经常使用多加练习即可熟练。

tuning advisor 是对输入的sql set的执行计划进行优化
accsee advisor 是对sql set当前如果引入索引、分区的话,性能有多大的提升,给出建议你

sql tuning advisor是从一方面给出的建议,access advisor可以针对一个表或者多个表的所有操作给出分析建议,比如说对于select语句他会建议增加索引,而会降低DML的性能,sql access advisor则针对所有的操作包括select,DML等总体的综合分析

SQL tunning advise
构建SQL profile
检查丢失的或者陈旧的统计信息的查询
建议重建用坏的执行计划的sql查询
Statistics analysis
SQL profiling
Access path analysis
SQL structure analysis

SQL access advise
对物化视图的优化提出建议
对位图基于函数的和B-tree的索引提出建议

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值