sql tuning with sql_id
2021/11/24 2:09:50
本文主要是介绍sql tuning with sql_id,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Suppose the sql id is – 87s8z2zzpsg88
1. Create Tuning Task
DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '87s8z2zzpsg88', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 500, task_name => '87s8z2zzpsg88_tuning_task11', description => 'Tuning task1 for statement 87s8z2zzpsg88'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; /
2. Execute Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');
3. Get the Tuning advisor report.
set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task('87s8z2zzpsg88_tuning_task11') from dual;
4. Get list of tuning task present in database:
We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;
5. Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('87s8z2zzpsg88_tuning_task11');
What if the sql_id is not present in the cursor , but present in AWR snap?
SQL_ID =24pzs2d6a6b13
First we need to find the begin snap and end snap of the sql_id.
select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes, executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b where sql_id='&sql_id' and a.snap_id=b.snap_id and a.instance_number=b.instance_number order by snap_id desc, a.instance_number;
From here we can get the begin snap and end snap of the sql_id.
begin_snap -> 235
end_snap -> 240
1. Create the tuning task:
DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 235, end_snap => 240, sql_id => '24pzs2d6a6b13', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => '24pzs2d6a6b13_AWR_tuning_task', description => 'Tuning task for statement 24pzs2d6a6b13 in AWR'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; /
2. Execute the tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '24pzs2d6a6b13_AWR_tuning_task');
3. Get the tuning task recommendation report
SET LONG 10000000; SET PAGESIZE 100000000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('24pzs2d6a6b13_AWR_tuning_task') AS recommendations FROM dual; SET PAGESIZE 24
SEE ALSO – COLLECTION OF USEFUL DATABASE MONITORING SCRIPT
这篇关于sql tuning with sql_id的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-28AI给的和自己写的Python代码,都无法改变输入框的内容,替换也不行
- 2024-09-27Sentinel配置限流资料:新手入门教程
- 2024-09-27Sentinel配置限流资料详解
- 2024-09-27Sentinel限流资料:新手入门教程
- 2024-09-26Sentinel限流资料入门详解
- 2024-09-26Springboot框架资料:初学者入门教程
- 2024-09-26Springboot框架资料详解:新手入门教程
- 2024-09-26Springboot企业级开发资料:新手入门指南
- 2024-09-26SpringBoot企业级开发资料新手指南
- 2024-09-26Springboot微服务资料入门教程