Stat Enable/Disable

To Disable/Enable Job
=======================

DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB_XXX');

DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB_XXX');


To Drop Job
=========================
DBMS_SCHEDULER.DROP_JOB('GATHER_STATS_JOB_XXX');


To run Stat Job Manually
=============================
exec dbms_stats.gather_database_stats_job_proc


10g
=======

SELECT job_name, state
FROM dba_scheduler_jobs
WHERE job_name='GATHER_STATS_JOB'; 

select job_name, job_type, program_name, schedule_name, job_class
  from dba_scheduler_jobs
  where job_name = 'GATHER_STATS_JOB';

select  PROGRAM_ACTION 
  from dba_scheduler_programs 
  where PROGRAM_NAME = 'GATHER_STATS_PROG';

select * 
  from DBA_SCHEDULER_WINGROUP_MEMBERS
  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'; 

select window_name, repeat_interval, duration
  from dba_scheduler_windows
  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW') ;

11g
====
select client_name,status from Dba_Autotask_Client; 

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/ 


BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Run Stat Manually

To run Stat Job Manually(Exactly same as AUTO stat)
=============================
exec dbms_stats.gather_database_stats_job_proc

Stat Collection Options

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;


select dbms_stats.get_stats_history_availability from dual;


col value for a30
select 'CASCADE' setting,dbms_stats.get_param('CASCADE') value from dual
union
select 'DEGREE',dbms_stats.get_param('DEGREE') from dual
union
select 'ESTIMATE_PERCENT',dbms_stats.get_param('ESTIMATE_PERCENT') from dual
union
select 'METHOD_OPT',dbms_stats.get_param('METHOD_OPT') from dual
union
select 'NO_INVALIDATE',dbms_stats.get_param('NO_INVALIDATE') from dual
union
select 'GRANULARITY',dbms_stats.get_param('GRANULARITY') from dual
union
select 'AUTOSTATS_TARGET',dbms_stats.get_param('AUTOSTATS_TARGET') from dual;

STS Drop Caused error

select name,STATEMENT_COUNT from dba_sqlset where owner = 'VAZEP';

exec dbms_sqltune.drop_sqlset('STS_BSZVN56GN9RWJ');

If you get below error while dropping STS
"ORA-13757: “SQL Tuning Set” “sts_test_01″ owned by user “badger” is active."

Then Check

select description,sqlset_name
from DBA_SQLSET_REFERENCES
where owner = 'VAZEP';

And drop Sql Tuning task associate with STS(from above query)

DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'RNM_TT');

Then Drop STS

exec dbms_sqltune.drop_sqlset('STS_BSZVN56GN9RWJ');

Sql Profile Export&Import

Create Staging Table
==================
   exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'&stage_tab_name', schema_name=>'&Schema_Name');

Pack SQL Profile
==================
  EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => '&Stage_tab_name',profile_name=>'&Profile_name');

   Add more profile if you want with same command above. For multiple profiles, pl-sql code can be written to pack all profiles in one shot.

Export Staging Table
====================
    exp vazep/abc tables=SPM_0715,SPM_0719 file=spm_0719_exp.dmp log=spm0719_exp.log

Import Staging table on Target DB
=========================
    imp system/password file= file_name.dmp full=y log=mylog.lis

Unpack SQL Profile

    EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(staging_table_name => '&Stage_Tab_Name');
    EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => '&Stage_Tab_Name');

Sql Profile Notes

select * from dba_sql_profiles;

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0129d2c93fe60000');

The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');

The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/

The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/

SET LONG 1000000
PRINT :l_report

Sql Tuning Advisor-Manual Steps

SET SERVEROUTPUT ON


--For a SQL statement
exec DBMS_SQLTUNE.CREATE_SQLSET('STS_AUG18_5KR');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
'sql_id='||CHR(39)||'5kr1m2rx3ns2g'||CHR(39)||' and plan_hash_value=287817245',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('STS_AUG18_5KR', baseline_ref_cursor);
end;

-----------------------------------------

--For a ALL SQL statement from snapshot range
exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--For a top 10 SQL statement from snapshot range

exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,'ELAPSED_TIME',NULL,NULL,1,10,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--Note := Last parameter "ALL" is required to capture plan in STS . By default its BASIC, which dont capture plan. This is
-- required for STS which in turn used for SPM load. OEM by default set this as BASIC.




-- 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 => 764,
end_snap => 938,
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '&SQL_ID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '&TASK_NAME',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from an SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => '&STS_NAME',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '&STA_NAME',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';

l_sql := 'Select receiver_fund, nvl(arf,0), price_date, price, last_update_date '||
'FROM VALU_PEN_UV_CHANGES_14 WHERE last_update_date > '':SYS_B_1'' '||
'AND receiver_fund = '':SYS_B_2'' ';

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task',
description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='&STS_NAME';

select sql_id, substr(sql_text,1, 15) text
from dba_sqlset_statements
where sqlset_name = 'STS_AUG18_5KR'
order by sql_id;


Run Tuning Task
===========

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&TASK_NAME');
During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');


The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';


Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;
SET PAGESIZE 24


If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure:
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(20);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => '&TASK_NAME',
name => '&Profile_name');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
The NAME parameter is used to specify a name for the profile. If it is not specified a system generated name will be used.


The STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an SQL profile can be altered using the ALTER_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.alter_sql_profile (
name => '&Profile_Name',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
Existing SQL profiles can be dropped using the DROP_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => 'emp_dept_profile',
ignore => TRUE);
END;
/