Automatic SQL Tuning Advisor

最新推荐文章于 2024-08-01 00:15:00 发布

rgb_rgb

最新推荐文章于 2024-08-01 00:15:00 发布

阅读量2k

点赞数

分类专栏： Script 文章标签： sql null parameters 配置管理 oracle 报表

本文链接：https://blog.csdn.net/rgb_rgb/article/details/7909695

版权

Script 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql脚本,用于抽取占用资源较多的sql,并可以为指定的sql生成执行计划,资源占用较多的sql分为两部分

1 15 Most expensive SQL in the cursor cache
2 15 Most expensive SQL in the workload repository 
@?/rdbms/admin/sqltrpt.sql
--还有：
@?/rdbms/admin/awrinfo.sql
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/ashrpt.sql
@?/rdbms/admin/addmrpt.sql
@?/rdbms/admin/sqlhc.sql


--SQL性能备份恢复诊断安装配置管理
SQL> @?/rdbms/admin/hangdiag    
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
SQL> oradebug tracefile_name;



1 Automatic SQL Tuning Advisor
A 从AWR报表中识别High-Load SQL
B 调用SQL Tuning Advisor调优识别的High-Load SQL
C 执行SQL测试它的SQL Profile


2 Enabling Automatic SQL Tuning
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


3 Disabling Automatic SQL Tuning
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


4 Configuring Automatic SQL Tuning
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES', 
value => 'TRUE');
END;
/

说明：sys_auto_sql_tuning_task是系统产生的auto task.


5 查看tuning report
SQL> set long 1000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;

6 Automatic SQL Tuning Directory
SQL> select task_id,task_name from dba_advisor_tasks where task_id = 1;
TASK_ID TASK_NAME
---------- ------------------------------------------------------------
1 SYS_AUTO_SQL_TUNING_TASK
SQL> select task_id,name,value from WRI$_ADV_PARAMETERS where task_id = 1; 

7 注意事项
由于Automatic SQL Tuning Advisor是从AWR中识别 High-Load SQL，所以它不可能是实时地去工作。。。