SELECT DISTINCT p.name sql_profile_name,
s.sql_id
FROM dba_sql_profiles p,
DBA_HIST_SQLSTAT s
WHERE p.name=s.sql_profile
AND s.sql_id = '&sql_id';
set serveroutput on begin dbms_sqltune.alter_sql_profile(name=>'SYS_SQLPROF_0146af25274a0117', attribute_name=>'STATUS',value=>'DISABLED'); end; /
execute dbms_sqltune.accept_sql_profile(task_name => 'staName69063',task_owner => 'A157584', replace => TRUE, force_match => TRUE); Automatically accept SQL profiles BEGIN dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'FALSE'); END; / set lines 300 col parameter_value form a10 col parameter_name form a30 SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK' AND parameter_name = 'ACCEPT_SQL_PROFILES';
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ' gvwhsjgqmrm3h',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => 'TUNING_TASK_T1419',
description => 'Tuning task for statement gvwhsjgqmrm3h');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'TUNING_TASK_T1419');
-- Get the report:
Spool gvwhsjgqmrm3h.lst
SET LONG 10000;
SET PAGESIZE 10000
SET LINESIZE 3000
SELECT DBMS_SQLTUNE.report_tuning_task('TUNING_TASK_T1403') AS recommendations FROM dual;
Spool off
select * from dba_sql_profiles where name = 'SYS_SQLPROF_0142e0c868100015' BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'SYS_SQLPROF_0142e0c868100015', attribute_name => 'STATUS', value => 'DISABLED'); END; / V$ses_optimizer_env to find out what values of optimizer_index_cost_adj and optimizer_index_caching are used. exec dbms_sqltune.accept_sql_profile(task_name => 'SYS_xxx',category => 'DEFAULT', force_match => true);
http://www.pythian.com/blog/wouldnt-automatic-sql-plan-management-be-the-11g-killer-app/
----------------------------------------------------------------------------------------
--
-- File name: unstable_plans.sql
--
-- Purpose: Attempts to find SQL statements with plan instability.
--
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for two values, both of which can be left blank.
--
-- min_stddev: the minimum "normalized" standard deviation between plans
-- (the default is 2)
--
-- min_etime: only include statements that have an avg. etime > this value
-- (the default is .1 second)
--
-- See http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/ for more info.
---------------------------------------------------------------------------------------
set lines 155
col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime,
stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/depre(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((buffer_gets_delta/depre(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
order by norm_stddev
/
set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a41
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/depre(nvl(executions,0),0,1,executions) avg_etime, u.username --,
--sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id
/
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/depre(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/depre(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
SELECT * FROM table(dbms_xplan.display_awr(nvl(�&sql_id�,�a96b61z6vp3un�),nvl(�&plan_hash_value�,null),null,�typical +peeked_binds�));
I have identified instances of the target SQL query using the available SQL profiles. Four of them were used at around 3am this morning. However, because they are aged out the shared pool and currently the SQL query below does not return query results for them.
SELECT c.sample_time, a.*, b.sql_text FROM dba_hist_sqlstat a, v$sql b,DBA_HIST_ACTIVE_SESS_HISTORY c WHERE a.sql_id = b.sql_id and b.sql_id = c.sql_id and a.sql_profile IS NOT NULL and b.sql_text like 'SELECT b.account_id,b.date_type,b.position_date%' ORDER BY a.snap_id DESC;
DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity.
DBA_HIST_SQLSTAT is populated by the snapshot process for AWR. You can have a SQL (even a busy SQL) which will leave information in V$SQL, but if the cursor in the shared pool is aged out or you flush the shared pool or the cursor is invalidated (because of DDL or grant/revoke activity) the information about that cursor will not be in DBA_HIST_SQLSTAT, because it wasn't there at the time of the snapshot.