Steps for SQL REPLAY
Collect Sqls
Create the SQL Replay Task with SQL Tuning Set (STS)
Collect SQL Performance Before Changes
Make Database Change ( eg collect stats etc)
Collect SQL Performance After Changes
Comparing SQL Performance Before and After Change
Display the Results of a SQL Replay Task
1.Collect SQLs.
We can collect the sql from the cursor cache and AWR.
from the cursor cache:
SQL> conn henry/henry
已连接。
SQL> exec dbms_sqltune.create_sqlset ('MYSQLSET');
PL/SQL 过程已成功完成。
SQL> DECLARE
2
3 cur dbms_sqltune.sqlset_cursor;
4
5 BEGIN
6
7 OPEN cur FOR
8 SELECT VALUE(p)
9 FROM TABLE (dbms_sqltune.select_cursor_cache) p;
10
11 dbms_sqltune.load_sqlset(
12 sqlset_name => 'MYSQLSET',
13 populate_cursor => cur);
14
15 END;
16 /
PL/SQL 过程已成功完成。
from the AWR.
SQL> DECLARE
2 cur DBMS_SQLTUNE.SQLSET_CURSOR;
3 BEGIN
4
5 OPEN cur FOR
6 SELECT VALUE(P)
7 FROM table(
8 DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,100)) P;
9
10 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MYSQLSET',
11 populate_cursor => cur,
12 load_option => 'MERGE',
13 update_option => 'ACCUMULATE');
14 END;
15 /
DECLARE
*
第 1 行出现错误:
ORA-13768: 快照 ID 必须介于 231 和 355 之间。
ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 4561
ORA-06512: 在 line 10
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 cur DBMS_SQLTUNE.SQLSET_CURSOR;
3 BEGIN
4 OPEN cur FOR
5 SELECT VALUE(P)
6 FROM table(
7 DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(231,354)) P;
8 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MYSQLSET',
9 populate_cursor => cur,
10 load_option => 'MERGE',
11 update_option => 'ACCUMULATE');
12* END;
13 /
PL/SQL 过程已成功完成。
2.Create the SQL Replay Task with SQL Tuning Set (STS)
SQL> ed
已写入 file afiedt.buf
1 declare
2 l_task_id varchar2(20);
3 begin
4 l_task_id := dbms_sqltune.create_tuning_task (
5 sqlset_name => 'MYSQLSET',
6 task_name => 'MY_REPLAY_TASK');
7* end;
SQL> /
declare
*
第 1 行出现错误:
ORA-13607: 指定的任务或对象 MY_REPLAY_TASK 已存在
ORA-06512: 在 "SYS.DBMS_SQLTUNE_INTERNAL", line 7718
ORA-06512: 在 "SYS.PRVT_SQLADV_INFRA", line 275
ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 674
ORA-06512: 在 line 4
SQL> ed
已写入 file afiedt.buf
1 declare
2 l_task_id varchar2(20);
3 begin
4 l_task_id := dbms_sqltune.create_tuning_task (
5 sqlset_name => 'MYSQLSET',
6 task_name => 'MY_REPLAY_TASK1');
7* end;
SQL> /
PL/SQL 过程已成功完成。
3.Collect SQL Performance Before Changes
SQL> begin
2 dbms_sqltune.execute_tuning_task (
3 execution_name => 'MY_REPLAY_EXECUTE_BEFORE',
4 task_name => 'MY_REPLAY_TASK1');
5 end;
6 /
PL/SQL 过程已成功完成。
4.Make Database Change
SQL> alter system set "_b_tree_bitmap_plans" = false;
系统已更改。
SQL> alter system set optimizer_index_cost_adj=1;
系统已更改。
5.Collect SQL Performance After Changes
SQL> begin
2
3 dbms_sqltune.execute_tuning_task (
4 execution_name => 'MY_REPLAY_EXECUTE_AFTER',
5 task_name => 'MY_REPLAY_TASK1');
6 end;
7 /
PL/SQL 过程已成功完成。
6. Comparing SQL Performance Before and After Change
SQL> begin
2
3 dbms_sqltune.execute_tuning_task (
4 execution_name => 'MY_COMPARE_EXECUTION',
5 task_name => 'MY_REPLAY_TASK1');
6 end;
7 /
PL/SQL 过程已成功完成。
7.Display the Results of a SQL Replay Task
set serveroutput on size 999999 set long 999999 select dbms_sqltune.report_tuning_task (‘MY_REPLAY_TASK1’) from dual;