Oracle SQL REPLAY

最新推荐文章于 2023-01-12 13:37:45 发布

最新推荐文章于 2023-01-12 13:37:45 发布 · 175 阅读

文章标签：

本文详细介绍了使用 SQL Replay 的步骤，包括收集 SQL 语句、创建 SQL 调优集 (STS)、进行数据库变更前后性能对比等。通过具体示例展示了如何利用 Oracle 提供的 DBMS_SQLTUNE 包来完成 SQL Replay 测试流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;