To check the sanp ID where wrong plan ID picked.
SELECT snap_id,SQL_PROFILE,OPTIMIZER_COST,ROWS_PROCESSED_TOTAL,ELAPSED_TIME_DELTA
FROM dba_hist_sqlstat WHERE sql_id = '&Sql_id' AND plan_hash_value = '
&PLAN_ID' ORDER BY 1
TO check List of plan
ID’s for given SQLID.
SET PAUSE ON
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
(
SELECT /*+
NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM
dba_hist_snapshot ss
WHERE
ss.begin_id in (&sql_id)
TO check the no.of
available plans and recommendations for given SQLID and it will create the
tunning task, later we can accept the tuning task.
@?/rdbms/admin/sqltrpt.sql
execute dbms_sqltune.accept_sql_profile(task_name =>
'TASK_70456',task_owner => 'SYS', replace => TRUE);
TO create the Tuning
task manually for Given SQLID:
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id
:= DBMS_SQLTUNE.create_tuning_task (
sql_id => 'dzw8bvzhzwgnn',
scope =>
DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name
=> 'dzw8bvzhzwgnn_tuning_task',
description => 'Tuning task for statement dzw8bvzhzwgnn.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
TO create the Tuning
task manually for Given snaps which is the best plan in between snaps:
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id :=
DBMS_SQLTUNE.create_tuning_task (
sql_id => 'dzw8bvzhzwgnn',
begin_snap
=> 58827,
end_snap
=> 59118,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'dzw8bvzhzwgnn_tuning_task_AWR',
description =>
'Tuning task for statement dzw8bvzhzwgnn.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: '
|| l_sql_tune_task_id);
END;
/
TO create the Tuning
task manually for Given SQL TEXT :
DECLARE
l_sql_tune_task_id VARCHAR2(100);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT statement copy here';
l_sql_tune_task_id
:= DBMS_SQLTUNE.create_tuning_task (
sql_text => my_sqltext,
scope
=> DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'dzw8bvzhzwgnn',
description =>
'Tuning task for statement dzw8bvzhzwgnn.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: '
|| l_sql_tune_task_id);
END;
/
TO drop Tuning task:
SQL> begin DBMS_SQLTUNE.drop_tuning_task (task_name =>
'TASK_70455');
2 end;
3 /
TO accept Tuning
Task:
execute dbms_sqltune.accept_sql_profile(task_name =>
'dzw8bvzhzwgnn_tuning_task');
TO create the SQL
base Line for a TASK:
execute dbms_sqltune.create_sql_plan_baseline(task_name
=> 'TASK_70467', owner_name => 'SYS', plan_hash_value => 1765760088);
TO check currently running sql ID which
plan ID using:
select
PLAN_HASH_VALUE from v$sql where
sql_id='dzw8bvzhzwgnn';
select
TIMESTAMP,PLAN_HASH_VALUE,time from
V$SQL_PLAN where SQL_ID='dzw8bvzhzwgnn';
select sql_text, plan_name, enabled, accepted from
dba_sql_plan_baselines;
