sql tuning advisor使用

DB tuning advisor是创建优化任务，对某些sql数据库进行分析，并尽量给出优化建议的一个强大的数据库工具。

自己平时几乎没用过这玩意，所以来测一测用法，其实对于一些sql一筹莫展的时候跑跑这个，看看数据库的建议也是一个突破点。

一般利用database tuning advisor的顺序：

找出要去优化调整的sql_id,创建优化任务

SET SERVEROUTPUT ON

declare

stmt_task VARCHAR2(40);

begin

stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'gf27vxf3rz2x7',time_limit=>600);

DBMS_OUTPUT.put_line('task_id: ' || stmt_task );

end;

task_id: TASK_43083

注：time_limit以秒为单位，就是这个优化任务最多跑多久，最大3600S，但一般都很快可以跑完，当然在跑这个优化任务的时候，数据库会去模拟执行sql或者其中一部分。

当然也可以直接把sql text作为输入

2. Run the SQL TUNING TASK

begin

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

end;

3. monitor the processing of the tuning task with the statement

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_43083';

SELECT status FROM dba_ADVISOR_TASKS WHERE task_name ='TASK_43083';

4. When the task has a status=COMPLETED, then run:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_43083') AS recommendations FROM dual;

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

begin

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_43083', task_owner => 'SYSTEM', replace => TRUE);

end;

6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin

DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');

end;

========================================================

如下是实际在sqlplus和OEM下的操作：

SQL> SET SERVEROUTPUT ON

SQL> declare

2 stmt_task VARCHAR2(40);

3 begin

stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'gf27vxf3rz2x7',time_limit=>600);

DBMS_OUTPUT.put_line('task_id: ' || stmt_task );

end;

/ 4 5 6 7

task_id: TASK_43083

PL/SQL procedure successfully completed.

SQL> begin

2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_43083');

3 end;

/ 4

PL/SQL procedure successfully completed.

SQL> SELECT status FROM dba_ADVISOR_TASKS WHERE task_name ='TASK_43083';

STATUS

-----------

COMPLETED

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_43083';

TASK_NAME STATUS

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

TASK_43083 COMPLETED

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_43083') AS recommendations FROM dual;

RECOMMENDATIONS

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

GENERAL INFORMATION SECTION

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

在这里没有recommend推荐，所以没内容

SQL> SELECT * FROM DBA_ADVISOR_TASKS T WHERE TASK_NAME='TASK_43083';

OWNER TASK_ID TASK_NAME DESCRIPTION ADVISOR_NAME CREATED LAST_MODI PARENT_TASK_ID PARENT_RXEC_ID LAST_EXECUTION EXECUTION_TYPE EXECUTION_TYPE# EXECUTION_DESCRIPTION EXECUTION EXECUTION STATUS

PCT_COMPLETION_TIME PROGRESS_METRIC METRIC_UNITS ACTIVITY_COUNTER RECOMMENDATION_COUNT

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

ERROR_MESSAGE

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

SYS 43083 TASK_43083 SQL Tuning Advisor 21-DEC-16 21-DEC-16 0 0 EXEC_43064 TUNE SQL 1 21-DEC-16 21-DEC-16 COMPLETED

0 0 0 0

CMD FALSE FALSE 4 3

从这里也可以看到RECOMMENDATION_COUNT为0，说明tuning advisor没啥建议。Task name为TASK_43083，task id为43083.

SQL> SELECT * FROM DBA_SQLTUNE_STATISTICS;

TASK_ID OBJECT_ID PARSING_SCHEMA_ID MODULE ACTION ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV COMMAND_TYPE

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

43083 1 JDBC Thin Client 4.3835E+10 1879546000 143899706 143034103 0 20730472 20732 4 4 657055 E289FB89E426A8004E011000AEF5C3E2CFFA331056414551519521105555551545545558591555449665851D5511058555555155515122555415A0EA0C55514542654554544490A1566E021696C6A75545150502541552441615579110A8456E820A5000030020000000000100001000000002002080007D000000000032000 3

SQL> SELECT * FROM DBA_SQLTUNE_BINDS;

no rows selected

SQL> SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID='43083';

SQL> SELECT TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') || ' -> ' || TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_time,

2 status,status_message,pct_completion_time

3 FROM DBA_ADVISOR_LOG

WHERE task_name='TASK_43083'; 4

EXECUTION_TIME STATUS

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

STATUS_MESSAGE

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

PCT_COMPLETION_TIME

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

21-dec-2016 08:45:18 -> 21-dec-2016 08:45:33 COMPLETED

================================================

USED SQL SUMMARY:

SELECT * FROM DBA_ADVISOR_TASKS T WHERE TASK_NAME='TASK_43083';

SELECT * FROM DBA_SQLTUNE_STATISTICS

SELECT * FROM DBA_SQLTUNE_BINDS

SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID='43083'

SELECT TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') || ' -> ' || TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_time,

status,status_message,pct_completion_time

FROM DBA_ADVISOR_LOG

WHERE task_name='TASK_43083';

==============================================================

OEM下的就简单很多了，点几下就够了：

选择sql tuning adviser:

这里的name可以自己定义，系统也会生成，即task name

总的时间限制30mins,comprehensive的分析范围，立即分析。这些都默认的就可以。

等待一下，正在执行优化任务

这里没什么推荐的所以为空，点击SPA Validation results看看是什么

如果有recommend生成的话，这个SPA是可以给新的优化来做测试的。

巴特西

sql tuning advisor使用

最新文章

热门文章