Automatic SQL Tuning 原理
Automatic SQL Tuning 原理:
基於自動工作負載資訊庫最高位sql標識的sql語句進行優化。
1.如果是非dba使用者,要賦予advisor許可權
2.建立tuning任務
使用在awr報表中發現有問題的sql進行tuning,還可以直接使用sql_id,cache cursor,sqlset(sql集)等方法
建立該tuning任務
SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 369,
end_snap => 370,
sql_id => '3a13916h8yz59',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '3a13916h8yz59_AWR_tuning_task',
description => 'Tuning task for statement 3a13916h8yz59 in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
3.執行tuning任務
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3a13916h8yz59_AWR_tuning_task');
4.檢視tuning任務的執行情況
select * from dba_advisor_log
如果發現該任務狀態是COMPLETED的時候,表示tuning已經完成了
5.檢視tuning的結果
select dbms_sqltune.report_tuning_task('3a13916h8yz59_AWR_tuning_task') from dual
基於自動工作負載資訊庫最高位sql標識的sql語句進行優化。
1.如果是非dba使用者,要賦予advisor許可權
2.建立tuning任務
使用在awr報表中發現有問題的sql進行tuning,還可以直接使用sql_id,cache cursor,sqlset(sql集)等方法
建立該tuning任務
SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 369,
end_snap => 370,
sql_id => '3a13916h8yz59',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '3a13916h8yz59_AWR_tuning_task',
description => 'Tuning task for statement 3a13916h8yz59 in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
3.執行tuning任務
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3a13916h8yz59_AWR_tuning_task');
4.檢視tuning任務的執行情況
select * from dba_advisor_log
如果發現該任務狀態是COMPLETED的時候,表示tuning已經完成了
5.檢視tuning的結果
select dbms_sqltune.report_tuning_task('3a13916h8yz59_AWR_tuning_task') from dual
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29196873/viewspace-1086535/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Automatic SQL Tuning and SQL ProfilesSQL
- Automatic SQL Tuning in Oracle Database 11gSQLOracleDatabase
- 11g alert log中的automatic SQL Tuning及Resource Manager planSQL
- Oracle OCP 1Z0 053 Q118(Automatic SQL Tuning Task)OracleSQL
- Oracle 10g Automatic Undo Retention Tuning (zt)Oracle 10g
- sql tuningSQL
- Oracle OCP 1Z0 053 Q403(Automatic SQL Tuning Advisor task)OracleSQL
- 在紅帽Linux上部署Automatic System TuningLinux
- sql tuning set/sql tuning advisor(待完善)SQL
- sql tuning setSQL
- 熟悉SQL tuningSQL
- sql tuning task和sql profileSQL
- 【筆記】SQL tuning筆記SQL
- Oracle SQL Perfomance TuningOracleSQL
- SQL TUNING ADVISORSQL
- sql tuning之變通SQL
- SPA_SQL Performance Analyzer_SQL Tuning SetSQLORM
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- 快速SQL TUNING——1分鐘搞定超慢SQLSQL
- oracle實用sql(3)--sql tuning advisorOracleSQL
- SQL Tuning Advisor簡介SQL
- sql tuning 第一章SQL
- 轉貼_push_subq_sql tuningSQL
- oracle sql tuning 14 --10046OracleSQL
- 二個SQL tuning例子(使用case)SQL
- 【筆記】sql tuning advidor筆記SQL
- ORACLE SQL Tuning Health-Check(SQLHC)OracleSQL
- sql tuning advisor(STA) 建議 建立sql profileSQL
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- Sql Tuning Advisor 使用方法SQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- oracle sql tuning 2--調優工具OracleSQL
- oracle SQL最佳化器SQL Tuning Advisor (STA)OracleSQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL