Capturar un SQL Tuning Set del Repositorio AWR

TEST_INST >set echo on
TEST_INST >
TEST_INST >alter session set nls_date_format = ‘dd-mon-yyyy hh24:mi:ss’;
TEST_INST >

TEST_INST >
TEST_INST >– Seleccionar el SNAP_ID de las ultimas 24hrs del AWR.
TEST_INST >pause

TEST_INST >
TEST_INST >set pagesize 9999
TEST_INST >COL snapdate FORMAT a30
TEST_INST >COL snap_id FORMAT 999999
TEST_INST >select s.snap_id snap_id
2 , to_char(s.end_interval_time,’dd Mon YYYY HH24:mi’) snapdate
3 from dba_hist_snapshot s
4 where s.end_interval_time > sysdate-1
5 order by snapdate desc;

SNAP_ID SNAPDATE
——- ——————————
16092 02 Nov 2010 22:00
16091 02 Nov 2010 21:00
16090 02 Nov 2010 20:00
16089 02 Nov 2010 19:00
16088 02 Nov 2010 18:00
16087 02 Nov 2010 17:00
16086 02 Nov 2010 16:00
16085 02 Nov 2010 15:00
16084 02 Nov 2010 14:00
16083 02 Nov 2010 13:00
16082 02 Nov 2010 12:00
16081 02 Nov 2010 11:00
16080 02 Nov 2010 10:00
16079 02 Nov 2010 09:00
16078 02 Nov 2010 08:00
16077 02 Nov 2010 07:00
16076 02 Nov 2010 06:00
16075 02 Nov 2010 05:00
16074 02 Nov 2010 04:00
16073 02 Nov 2010 03:00
16072 02 Nov 2010 02:00
16071 02 Nov 2010 01:00
16070 02 Nov 2010 00:00
16069 01 Nov 2010 23:00

24 rows selected.

TEST_INST >
TEST_INST >pause

TEST_INST >–Teclea el nombre del SQL TUNING SET.
TEST_INST >–Teclea el rango de SNAPSHOTS que deseas capturar en el STS de la carga de trabajo del AWR.
TEST_INST >–Teclea el nombre del STS.
TEST_INST >
TEST_INST >
TEST_INST >pause

TEST_INST >
TEST_INST >declare
2 own VARCHAR2(30) := ‘&owner’;
3 bid NUMBER := ‘&begin_snap’;
4 eid NUMBER := ‘&end_snap’;
5 stsname VARCHAR2(30) :=’&stsname’;
6 sts_cur dbms_sqltune.sqlset_cursor;
7 begin
8 dbms_sqltune.create_sqlset(sqlset_name => stsname, sqlset_owner =>
9 own);
10 open sts_cur for
11 select value(P) from table(dbms_sqltune.select_workload_repository(bid, eid,’UPPER(PARSING_SCHEMA_NAME) = ”SCOTT” AND ELAPSED_TIME >= 6.0E8′, null, null, null, null, 1, null, ‘ALL’)) P;
12 dbms_sqltune.load_sqlset(sqlset_name => stsname,
13 populate_cursor => sts_cur,
14 load_option => ‘MERGE’);
15 end;
16 /
Enter value for owner: SYSTEM
old 2: own VARCHAR2(30) := ‘&owner’;
new 2: own VARCHAR2(30) := ‘SYSTEM’;
Enter value for begin_snap: 16069
old 3: bid NUMBER := ‘&begin_snap’;
new 3: bid NUMBER := ‘16069’;
Enter value for end_snap: 16092
old 4: eid NUMBER := ‘&end_snap’;
new 4: eid NUMBER := ‘16092’;
Enter value for stsname: SCOTT_TEST
old 5: stsname VARCHAR2(30) :=’&stsname’;
new 5: stsname VARCHAR2(30) :=’SCOTT_TEST’;

PL/SQL procedure successfully completed.

TEST_INST >
TEST_INST >–Verifica que el STS existe.
TEST_INST >
TEST_INST >pause

TEST_INST >select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME
——————————————————————————–
OWNER
——————————————————————————–
CREATED STATEMENT_COUNT
————— —————
SCOTT_TEST
SYSTEM
02-NOV-10 24