oracle sql replay,Oracle SQL REPLAY

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;