如何创建SQL 调优集（二） 从AWR加载

Example 1

--From SNAP ID 1,2 Load sql id dmqch2g6rtvzf with plan_hash_value
--and its sql plan and all execution statistics
--into STS test2 .
--This will create an empty SQL Tuning set test2.

exec DBMS_SQLTUNE.CREATE_SQLSET('test2');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1, 2,
'sql_id='||CHR(39)||'dmqch2g6rtvzf'||CHR(39)||' and plan_hash_value=1421641795',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('test2', baseline_ref_cursor);
end;

By default, select_workload_repository does not include the SQL Plan.
we should pass TYPICAL or ALL as the 'attribute_list' parameter to get
the plan. The default value of BASIC does not capture the plan. Please
refer to PL/SQL Package guide for the DBMS_SQLTUNE package.

DBMS_SQLTUNE.SELECT_WORKLAOD REPOSITORY (
baseline_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
ranking_measure1 IN VARCHAR2 := NULL,
ranking_measure2 IN VARCHAR2 := NULL,
ranking_measure3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := 1,
result_limit IN NUMBER := NULL)
attribute_list IN VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED;

Example 2

--This will load all the sqls captured in AWR snapshot FOR SNAP ID 1 AND 2 .

EXEC DBMS_SQLTUNE.CREATE_SQLSET('test3');
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1, 2,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('test3', baseline_ref_cursor);
end;
/

Example 3

In the following example all the sql plans for a given range of awr snapshot ids will capured in an STS.

set echo on

-- Displaying the list of AWR snapshot ids.
pause

select s.snap_id snap_id
, to_char(s.end_interval_time,'dd Mon YYYY HH24:mi') snapdate
from dba_hist_snapshot s order by snapdate;

pause

--These are the avaliable AWR snapshot ids.

pause

--Enter owner of SQL TUNING SET, owner name may be case sensitive in 11g.
--Enter the snapshot range from which you want capture the sql plans.
--Enter the STS NAME.

pause

declare
own VARCHAR2(30) := '&owner';
bid NUMBER := '&begin_snap';
eid NUMBER := '&end_snap';
stsname VARCHAR2(30) :='&stsname';
sts_cur dbms_sqltune.sqlset_cursor;
begin
dbms_sqltune.create_sqlset(sqlset_name => stsname, sqlset_owner =>
own);
open sts_cur for
select value(P) from table(dbms_sqltune.select_workload_repository(bid,
eid, null, null, null, null, null, 1, null, 'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name => stsname,
populate_cursor => sts_cur,
load_option => 'MERGE');
end;
/

--Now Verify the STS.

pause
select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

Verify how many sqls got loaded in the STS.

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

Verify the sql statements and its sql_d in the STS

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

Verify the execution Plan of a SQL_ID in the STS for an user sql.

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'test3','dmqch2g6rtvzf'));

Verify the Plan baseline to check how many plans before.

SQL> select count(*) from dba_sql_plan_baselines;

2) Load the Sql Plan Baseline from STS.

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'test2',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/

Note
Ensure that your source STS capture does not have any sys or system or default user queries from user like sysman. Always ensure to filter the SYS or system schema or any default user like sysman related
queries while loading in the SPM in  11g by using basic_filter
for example using the following procedure :-

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'test2',
sqlset_owner => 'SYS',
fixed => 'NO',
enabled => 'YES',
basic_filter => 'parsing_schema_name != ''SYS''');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/

Verify the Plan baseline to check how many plans in plan baseline.

SQL> select count(*) from dba_sql_plan_baselines;

On 11.1.0.7.0 Apply one off patch for   BUG 8922968  which is duplicate of BUG  7679287
So Please apply one off   patch for BUG 7679287. This bug is fixed in 11.2.0.1.0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-708258/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-708258/