如何用 SQL Tuning&nbsp…_sqltune 引号怎么处理-CSDN博客

本文链接：https://blog.csdn.net/li19236/article/details/41486383

如何用 SQL Tuning Advisor (STA) 优化SQL语句

在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在10g中，Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。

执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：

SQL> create user dave identified by dave;
用户已创建。

SQL> grant connect,resource to dave;
授权成功。

SQL> grant advisor to dave;
授权成功。

下面简单介绍一下如何优化一条找到的问题语句。

create table bigtab as select rownum as "id",a.* from sys.all_objects a;

create table smalltab as select rownum as "id", a.* FROM sys.all_tables a;

然后多运行几次下面的脚本，增加表里的数据：

insert into bigtab select rownum as "id",a.* from sys.all_objects a;

insert into smalltab select rownum as "id", a.* FROM sys.all_tables a;

这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询：

SQL> set timing on

SQL> set autot on

SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;

COUNT(*)
----------
2141537

已用时间: 00: 00: 20.05

执行计划

----------------------------------------------------------

Plan hash value: 3089226980

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 45 | 3146 (1)| 00:00:38 |

| 1 | SORT AGGREGATE | | 1 | 45 | | |

|* 2 | HASH JOIN | | 447K| 19M| 3146 (1)| 00:00:38 |

| 3 | TABLE ACCESS FULL| SMALLTAB | 27327 | 533K| 264 (1)| 00:00:04 |

| 4 | TABLE ACCESS FULL| BIGTAB | 712K| 16M| 2878 (1)| 00:00:35 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

31149 consistent gets

21058 physical reads

0 redo size

426 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

第一步：创建优化任务

通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：

SQL> set autot off
SQL> set timing off

SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'DAVE', -- 注意是大写，不然会报错，用户无效
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_sql_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
15 END;
16 /

PL/SQL procedure successfully completed.

在函数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_tuning_task函数可以获得优化任务的结果。

SQL> SET LONG 999999
SQL> set serveroutput on size 999999
SQL> SET LINESIZE 100

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 : DEMO
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 5/28/2010 13:16:43
Completed at : 5/28/2010 13:16:44
Number of Index Findings : 1

Schema Name: DEMO

SQL ID : 6p64dnnsqf9pm

SQL Text : select count(*) from bigtab a, smalltab b where a.object_name=b.table_name

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)

The execution plan of this statement can be improved by creating one or more indices.

Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design or creating the recommended index.

create index DEMO.IDX$$_06C50001 on SYS.SMALLTAB('TABLE_NAME');

- Consider running the Access Advisor to improve the physical schema design or creating the recommended index.

create index DEMO.IDX$$_06C50002 on SYS.BIGTAB('OBJECT_NAME');

Rationale
---------
Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.

EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original

-----------

Plan hash value: 3089226980

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 |

| 1 | SORT AGGREGATE | | 1 | 36 | | |

|* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 |

| 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

2- Using New Indices

--------------------

Plan hash value: 494801882

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 36 | 1108 (3)| 00:00:14 |

| 1 | SORT AGGREGATE | | 1 | 36 | | |

|* 2 | HASH JOIN | | 155K| 5462K| 1108 (3)| 00:00:14 |

| 3 | INDEX FAST FULL SCAN| IDX$$_06C50001 | 1223 | 22014 | 3 (0)| 00:00:01 |

| 4 | INDEX FAST FULL SCAN| IDX$$_06C50002 | 1205K| 20M| 1093 (2)| 00:00:14 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

------------------------------------------------------------------------------

看一下这个优化建议报告：

第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述：可以通过建立更多的索引来提高性能；然后是建议的具体内容：在表smalltab的字段table_name上创建索引，在表bigtab的字段object_name上创建索引；最后是相关注意事项：此次优化虽然给出了创建索引的建议，但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析，那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。

最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。

五、删除优化任务

通过调用dbms_sqltune.drop_tuning_task可以删除已经存在的优化任务

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.

注：整理自网络

转自：http://blog.csdn.net/tianlesoftware/article/details/5630888