Statistics :
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
execute dbms_stats.restore_table_stats('SYSTEM','MYOBJECTS',’ 23-MAR-11 11.38.05.015640 AM +08:00’);
select extract( day from snap_interval) *24*60+ extract( hour from snap_interval) *60+ extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+ extract( hour from retention) *60+ extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;
DBMS_WORKLOAD_REPOSITORY.create_baseline(1900, 2000, ‘EOY_PEAK_LOADS’);
execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 57600);
SQL Tuning Set and Advisor
SQL Tuning Sets : SQL can be loaded from
Cursor Cache
AWR last 24b hour snapshots
Preserver snapshots : Baseline load
User-Defined Workload
To reduce scope, you can further apply filter Options
Give a name to the job,schedhule and create the SQL tuning sets. STS may also create SQL profile.
SQL Tuning Advisor can be run using
Top Activity : high-load SQL statements
Period SQL : AWR
SQL Tuning Sets.
To run SQL Tuning Advisor manually
SELECT DISTINCT sql_id,hash_value, sql_text FROM v$sql
WHERE sql_text LIKE '%pktable_cat%'
AND sql_text NOT LIKE 'select distinct hash_value, sql_id%';
CREATE Tuning Task:
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id => '0j3dqg3a8aj8c', scope => 'COMPREHENSIVE', time_limit => 3600,
task_name => 'my_sql_tuning_task_1', description => 'Tune query using sqlid');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
END;
/
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SET pagesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') FROM DUAL;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;
To accept /disable /drop tuning task and sql profile
exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'DISABLED');
exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
SQL Access Advisor can be run using
SQL Statements from the Cache
Existing SQL Tuning Set
User-Defined Workload
Hypothetical Workload
http://kerryosborne.oracle-guy.com/2008/09/sql-tuning-advisor/
SQL Baseline :
Baselines are the prefered mechanism for changing the executions plans without touching the source code.
Get the sql id and hash value of two similar queries. Second query with spaces and hence producing different explain plan can be made to use the explain plan of first query.So baseline creates exact_matching_signature for different queries
declare
sqltext clob;
spm_op pls_integer;
begin
sqltext := 'select /*+dom_baseline*/ * from t1';
spm_op := dbms_spm.load_plans_from_cursor_cache(sql_id => '<sqlid>', plan_hash_value => <hash>, sql_text => sqltext);
end;
/
E.g of loading from STS
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
l_sqlset_name VARCHAR2(200) := my_test_tuning_set;
l_op PLS_INTEGER;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE(dbms_sqltune.select_workload_repository
(begin_snap => <begin_snap>,
end_snap => <end_snap>,
basic_filter => NULL,
ranking_measure1 => 'elapsed_time',
result_limit => 10,
attribute_list => 'ALL'
)) p;
DBMS_SQLTUNE.CREATE_SQLSET
(sqlset_name => l_sqlset_name);
DBMS_SQLTUNE.LOAD_SQLSET
(sqlset_name => l_sqlset_name,
populate_cursor => baseline_cursor);
l_op := DBMS_SPM.load_plans_from_sqlset(sqlset_name => l_sqlset_name);
END;
/
10g :
Need EXECUTE privilege on the DBMS_SPM package and ADMINISTER SQL MANAGEMENT OBJECT privilege.
A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.
You can create a SQL plan baseline in several ways:
1. using SQL Tuning Set (STS);
2. from cursor cache;
3. exporting from one database and importing into another;
4. automatically for every statement using optimizer_capture_sql_plan_baselines to TRUE at system or session level
Setting optimizer_capture_sql_plan_baselines = true permanently will result in a SQL plan baseline being created for every repeatable SQL statement on
the system
Automatic plan capture will not occur for a statement if a stored outline exists for it and is enabled and the parameter use_stored_outlines is TRUE.
In this case , turn on incremental capture of plans into an STS using the capture_cursor_cache_sqlset() of DBMS_SQLTUNE package.Then manually create
SQl plan baselines.
Going back to old statistics :
http://gavinsoorma.com/2011/03/restoring-optimizer-statistics/