sql语句大全之初次使用SQL调优建议工具--SQL Tuni

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之初次使用SQL调优建议工具--SQL Tuni

在10g中，Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。但是我认为使用这种工具，仅适合完全不懂SQL的调优的人群，不要认为工具能解决好问题。SQL说到底是表达的是一个业务，工具怎么可能理解业务。SQL调优还是要用autotrace,10046,10053,display_cursor这些优秀的工具做诊断，然后根据业务和所具备的oracle基础的知识进行调优，这是最好的方法。今天在这里玩这个工具只是玩票。

1.创建数据，故意不建索引，然后做查询

SQL> create table test1 as select * from dba_objects;

SQL> create table test2 as select * from dba_objects;

SQL> exec dbms_stats.gather_table_stats(user,'test1');

SQL> exec dbms_stats.gather_table_stats(user,'test2');

SQL> set timing on

SQL> set autotrace traceonly

SQL> select count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_id;

已用时间: 00: 00: 00.07

执行计划

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

Plan hash value: 2544416891

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 298 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | 10 | | |

|* 2 | HASH JOIN | | 50981 | 497K| 298 (1)| 00:00:04 |

| 3 | TABLE ACCESS FULL| TEST1 | 50982 | 248K| 149 (1)| 00:00:02 |

| 4 | TABLE ACCESS FULL| TEST2 | 50983 | 248K| 149 (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

统计信息

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

1 recursive calls

0 db block gets

1410 consistent gets

0 physical reads

0 redo size

410 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace off

2.通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务，然后通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行调优任务，生成调优建议。

SQL> DECLARE

my_task_name VARCHAR2(300);

my_sqltext CLOB;

BEGIN

my_sqltext := 'select count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_id';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => 'TEST', -- 必须大写

scope => 'COMPREHENSIVE',

time_limit => 10,

task_name => 'tuning_sql_test',

description => 'Task to tune a query on a specified table');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');

END;

官方文档解析：

sql_text The text of a SQL statement

user_name The username for whom the statement is to be tuned

scope Tuning scope (limited/comprehensive)

time_limit The maximum duration in seconds for the tuning session

task_name An optional tuning task name

description A task of the SQL tuning session to a maximum of 256 characters

3.检查优化任务的状态

SQL> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;

TASK_NAME ADVISOR_NAME STATUS

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

tuning_sql_test SQL Tuning Advisor COMPLETED

4.查看优化结果

SQL> set LONGCHUNKSIZE 999999

SQL> set serveroutput on size 999999

SQL> set long 999999

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 : TEST

Scope : COMPREHENSIVE

Time Limit(seconds) : 10

Completion Status : COMPLETED

Started at : 05/23/2014 08:50:40

Completed at : 05/23/2014 08:50:41

Number of Index Findings : 1

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

Schema Name: TEST

SQL ID : afjq3us3nf5dt

SQL Text : select count(*) from test1 t1, test2 t2 where t1.object_id =

t2.object_id

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

FINDINGS SECTION (1 finding)

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

1- Index Finding (see explain plans section below)

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

通过创建一个或多个索引可以

Recommendation (estimated benefit: 100%)

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

-考虑运行可以改进物理方案设计的 Access Advi

create index TEST.IDX

0C890001onTEST.TEST1(′OBJECTID′);−考虑运行可以改进物理方案设计的AccessAdvicreateindexTEST.IDX

_0C890002 on TEST.TEST2('OBJECT_ID');

Rationale

---------

创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的

可能比单个语句更可取。通过这种方法可以获得全面的索引建

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 2544416891

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 298 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | 10 | | |

|* 2 | HASH JOIN | | 50981 | 497K| 298 (1)| 00:00:04 |

| 3 | TABLE ACCESS FULL| TEST1 | 50982 | 248K| 149 (1)| 00:00:02 |

| 4 | TABLE ACCESS FULL| TEST2 | 50983 | 248K| 149 (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

2- Using New Indices

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

Plan hash value: 3060659111

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 53 (2)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 10 | | |

|* 2 | HASH JOIN | | 50981 | 497K| 53 (2)| 00:00:01 |

| 3 | INDEX FAST FULL SCAN| IDX

0C890001|50982|248K|26(0)|00:00:01||4|INDEXFASTFULLSCAN|IDX

_0C890002 | 50983 | 248K| 26 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

5.验证一下

根据advisor的建议进行调优，1410降到204，是乎有点效果。

SQL>create index ind_t1_object_id on test1(object_id);

SQL> create index ind_t2_object_id on test2(object_id);

SQL> set autotrace traceonly

SQL> select count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_id;

已用时间: 00: 00: 00.06

执行计划

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

Plan hash value: 1069114244

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 51 (2)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 10 | | |

|* 2 | HASH JOIN | | 50981 | 497K| 51 (2)| 00:00:01 |

| 3 | INDEX FAST FULL SCAN| IND_T1_OBJECT_ID | 50982 | 248K| 25 (0)| 00:00:01 |

| 4 | INDEX FAST FULL SCAN| IND_T2_OBJECT_ID | 50983 | 248K| 25 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

统计信息

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

1 recursive calls

0 db block gets

240 consistent gets

226 physical reads

0 redo size

410 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

6.删除优化任务

SQL> exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

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

作者：深圳gg

来源：CSDN

原文：https://blog.csdn.net/stevendbaguo/article/details/26669105

栏目列表