Oracle SQL REPLAY

最新推荐文章于 2024-08-12 00:00:00 发布

henrybai

最新推荐文章于 2024-08-12 00:00:00 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE11G 文章标签： sql oracle performance database table cache

本文链接：https://blog.youkuaiyun.com/henrybai/article/details/6104789

ORACLE11G 专栏收录该内容

96 篇文章

订阅专栏

本文介绍了一种通过SQL Replay来评估数据库变更前后性能差异的方法。包括收集SQL、创建SQL Tuning Set、执行前后性能对比及展示结果等步骤。

摘要生成于 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;