您现在的位置是：首页 > 数据库

当前栏目

Oracle的学习心得和知识总结（十二）|Oracle数据库Real Application Testing之SQL Performance Analyzer实操(一)

Oracle 数据库 SQL 总结知识 application 十二 Testing

2023-09-14 09:15:34 时间

目录结构

注：提前言明本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、参考书籍：《Oracle Database SQL Language Reference》
2、参考书籍：《PostgreSQL中文手册》
3、EDB Postgres Advanced Server User Guides，点击前往
4、PostgreSQL数据库仓库链接，点击前往
5、PostgreSQL中文社区，点击前往
6、Oracle Real Application Testing 官网首页，点击前往
7、Oracle 21C RAT Testing Guide，点击前往

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享抛砖引玉一起学习
3、本文不提供任何资源不存在任何交易与任何组织和机构无关
4、大家可以根据需要自行复制粘贴以及作为其他个人用途，但是不允许转载不允许商用（写作不易，还请见谅 💖）

Oracle数据库Real Application Testing之SQL Performance Analyzer实操

文章快速说明索引
SPA实操案例演示

文章快速说明索引

学习目标：

目的：接下来这段时间我想做一些兼容Oracle数据库Real Application Testing (即：RAT)上的一些功能开发，本专栏这里主要是学习以及介绍Oracle数据库功能的使用场景、原理说明和注意事项等，基于PostgreSQL数据库的功能开发等之后由新博客进行介绍和分享！

学习内容：（详见目录）

1、Oracle数据库Real Application Testing之SQL Performance Analyzer实操(一)

学习时间：

2023年03月23日 20:30:14

学习产出：

1、Oracle数据库Real Application Testing之SQL Performance Analyzer实操(一)
2、CSDN 技术博客 1篇

注：下面我们所有的学习环境是Centos7+PostgreSQL15.0+Oracle19c+MySQL5.7

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)

postgres=#

#-----------------------------------------------------------------------------#

SQL> select * from v$version; 

BANNER									    BANNER_FULL 								BANNER_LEGACY									CON_ID
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production		     0
									    Version 19.3.0.0.0


SQL>
#-----------------------------------------------------------------------------#

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.06 sec)

mysql>

SPA实操案例演示

准备工作

创建用户如下：

create user c##spa123 identified by spa123 default tablespace users; 
grant dba to c##spa123; 
grant advisor to c##spa123; 
grant select any dictionary to c##spa123; 
grant administer sql tuning set to c##spa123;

SQL> select * from v$version; 

BANNER									    BANNER_FULL 								BANNER_LEGACY									CON_ID
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production		     0
									    Version 19.3.0.0.0


SQL> conn c##spa123/spa123
Connected.
SQL>

建表及插入数据，如下：

SQL> create table spa_tbl (id number not null);

Table created.

SQL> begin
	for i in 1..20000 loop
		insert into spa_tbl(id) values (i);
	end loop;
end;
/  2    3    4    5    6  

PL/SQL procedure successfully completed.

SQL>

运行测试SQL语句并获取执行计划，如下：

SQL> set serveroutput off;
SQL> col id format 99999
SQL> select a.id, b.id from spa_tbl a, spa_tbl b where a.id=b.id and b.id=666;

    ID	   ID
------ ------
   666	  666

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	37gfcdapk3bt6, child number 0
-------------------------------------
select a.id, b.id from spa_tbl a, spa_tbl b where a.id=b.id and b.id=666

Plan hash value: 3470770333

------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	     |	     |	  22 (100)|	     |
|*  1 |  HASH JOIN	   |	     |	   1 |	  26 |	  22   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| SPA_TBL |	   1 |	  13 |	  11   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| SPA_TBL |	   1 |	  13 |	  11   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")
   2 - filter("A"."ID"=666)
   3 - filter("B"."ID"=666)


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


26 rows selected.

SQL>

此时我们可以先行查看一下，如下：

-- 查看SQLSET
SQL> select owner,name,STATEMENT_COUNT from dba_sqlset where OWNER != 'SYS';

no rows selected

-- 查看分析任务
SQL> select owner,task_id,task_name,created,LAST_MODIFIED,STATUS from DBA_ADVISOR_TASKS where OWNER != 'SYS';

no rows selected

SQL>

变化之前

创建优化集，如下：

SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
   sqlset_name => 'test_sts',
   description => 'STS for SPA');
END;
/  2    3    4    5    6  

PL/SQL procedure successfully completed.

SQL>

---- 查询快照：

SQL> select INSTANCE_NUMBER ,min(snap_id),max(snap_id) from dba_hist_snapshot group by INSTANCE_NUMBER;

INSTANCE_NUMBER MIN(SNAP_ID) MAX(SNAP_ID)
--------------- ------------ ------------
	      1 	1106	     1116

SQL>

通过查询游标缓冲向STS中增加查询，如下：

SQL> DECLARE
	l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
	OPEN l_cursor FOR
		SELECT VALUE(p) FROM TABLE (
				DBMS_SQLTUNE.select_cursor_cache(
				'sql_id = ''&sql_id''', -- basic_filter
				NULL, -- object_filter
				NULL, -- ranking_measure1
				NULL, -- ranking_measure2
				NULL, -- ranking_measure3
				NULL, -- result_percentage
				1)    -- result_limit
			) p;
	DBMS_SQLTUNE.load_sqlset (
		sqlset_name    => 'test_sts',
		populate_cursor => l_cursor);
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  
Enter value for sql_id: 37gfcdapk3bt6
old   7: 				'sql_id = ''&sql_id''', -- basic_filter
new   7: 				'sql_id = ''37gfcdapk3bt6''', -- basic_filter

PL/SQL procedure successfully completed.

SQL>

这里查询一下STS内容以确定该SQL已被正确注册，如下：

SQL> col sql format a50
SQL> set lines 120
SQL> 
SQL> SELECT sql_id, substr(sql_text, 1, 50) sql FROM TABLE(DBMS_SQLTUNE.select_sqlset ('test_sts'));

SQL_ID	      SQL
------------- --------------------------------------------------
37gfcdapk3bt6 select a.id, b.id from spa_tbl a, spa_tbl b where

SQL>

接下来，需要注意：改变数据库前，生成和存储该SQL语句执行统计信息。该步骤可能会花费一些时间，因为要运行STS查询和存储其执行统计信息。按顺序执行如下步骤：

-- 第一步：创建一个参考STS的SQLPA分析任务

SQL> var v_out char(50)
begin
 :v_out:=dbms_sqlpa.create_analysis_task(sqlset_name => 'test_sts',task_name=> 'test_spa_task');
end;
/
print v_outSQL>   2    3    4  
PL/SQL procedure successfully completed.

SQL> 

V_OUT
------------------------------------------------------------------------------------------------------------------------
test_spa_task

SQL>

-- 第二步：检查该任务是否已创建

SQL> col TASK_NAME format a14
SQL> col ADVISOR_NAME format a24
SQL> 
SQL> select TASK_NAME, ADVISOR_NAME, created from DBA_ADVISOR_TASKS where task_name='test_spa_task';

TASK_NAME      ADVISOR_NAME		CREATED
-------------- ------------------------ ---------
test_spa_task  SQL Performance Analyzer 23-MAR-23

SQL>

-- 第三步：运行该SQLPA分析任务

SQL> begin
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
      task_name      => 'test_spa_task',
      execution_type => 'TEST EXECUTE',
      execution_name => 'test_spa_task_before');
end;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL>

-- 第四步：监视该任务及其状态，直到任务完成

SQL> col TASK_NAME format a20
SQL> col EXECUTION_NAME format a30
SQL>     
SQL> select execution_name,status,execution_end from DBA_ADVISOR_EXECUTIONS where task_name='test_spa_task' order by execution_end;

EXECUTION_NAME		       STATUS	   EXECUTION
------------------------------ ----------- ---------
test_spa_task_before	       COMPLETED   23-MAR-23

SQL>

执行改变

-- 在表上创建一个索引：

SQL> create unique index test_idx on spa_tbl(id);

Index created.

SQL>

改变之后

在改变数据库后，运行该SQLPA分析任务。与前面类似，只是需改变名字以区别数据库改变前后的执行统计信息，如下：

SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
      task_name      => 'test_spa_task',
      execution_type => 'TEST EXECUTE',
      execution_name => 'test_spa_task_after');
end;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL>

-- 监视该任务及其状态,直到其完成

SQL> select execution_name,status,execution_end from DBA_ADVISOR_EXECUTIONS where task_name='test_spa_task' order by execution_end;

EXECUTION_NAME		       STATUS	   EXECUTION
------------------------------ ----------- ---------
test_spa_task_before	       COMPLETED   23-MAR-23
test_spa_task_after	       COMPLETED   23-MAR-23

SQL>

分析报告

最后就可以比较因数据库改变而发生的执行改变，需再次运行分析任务这次分析器将比较和存储相较的结果，如下：

SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name        => 'test_spa_task',
  execution_type   => 'COMPARE PERFORMANCE',
  execution_name   => 'test_spa_task_compare',
  execution_params => dbms_advisor.arglist('comparison_metric', 'buffer_gets'));
end;
/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

SQL>

分析完成，下面生成报告如下：

SQL> set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> 
SQL> spool /home/oracle/spahtml/spa1.html
SQL> 
SQL> SELECT dbms_sqlpa.report_analysis_task('test_spa_task', 'HTML', 'ALL','ALL', execution_name=>'test_spa_task_compare',top_sql=>10) FROM dual;

<html>
    <head>
	<title>
   SQL Performance Impact Analyzer Report
   ...
    </body>

</html>

SQL> spool off;
SQL>

报告如下：

在这里插入图片描述

除此之外，还可以在下面的视图中进行查看，如下：

SQL> select * from DBA_ADVISOR_FINDINGS where owner = 'C##SPA123';

C##SPA123	      2176 test_spa_task	test_spa_task_compare			1
normal, successful completion
SYMPTOM 	     2		0	   6

	 0
The structure of the SQL execution plan has changed.

N	   0

C##SPA123	      2176 test_spa_task	test_spa_task_compare			2
normal, successful completion
INFORMATION	     4		0	   6


97.3684211
The performance of this SQL has improved.

N	   0

SQL>

-- DBA_ADVISOR_SQLPLANS
-- DBA_ADVISOR_SQLSTATS

环境清理

-- 查看SQLSET
SQL> select owner,name,STATEMENT_COUNT from dba_sqlset where OWNER != 'SYS';

OWNER
---------------
NAME
------------------------------------------------------------------------------------------------------------------------
STATEMENT_COUNT
---------------
C##SPA123
test_sts
	      1

-- 查看分析任务
SQL> select owner,task_id,task_name,created,LAST_MODIFIED,STATUS from DBA_ADVISOR_TASKS where OWNER != 'SYS';

OWNER		   TASK_ID TASK_NAME		CREATED   LAST_MODI STATUS
--------------- ---------- -------------------- --------- --------- -----------
C##SPA123	      2176 test_spa_task	23-MAR-23 23-MAR-23 COMPLETED

-- 删除ANALYSIS_TASK
SQL> exec dbms_sqlpa.DROP_ANALYSIS_TASK('test_spa_task');

PL/SQL procedure successfully completed.

-- 删除sqlset
SQL> exec dbms_sqltune.DROP_SQLSET('test_sts');

PL/SQL procedure successfully completed.

SQL>

SQL> drop table spa_tbl;

Table dropped.

SQL>

猜你喜欢

MySQL中row函数的使用方法（mysqlrow）
php curl 302跳转的最终结果
Oracle让绝对值拥抱一列（oracle一列绝对值）
澳大利亚正考虑要求苹果开放Apple Pay 与同行直面竞争
快速高效地执行Oracle脚本（oracle脚本执行）
驭势而上YAM文件配置Redis（yam文件配置redis）
详解CAN总线：CAN协议分层结构及功能
绘制PR曲线[通俗易懂]
获取Oracle序列值的步骤指南（获取oracle序列值）
jQuery布局插件UILayout简介及使用方法
“科创中国”开源产业科技服务团荣获2022年度中国科协优秀科技服务团
南大人工智能学院包揽华为「难题揭榜」9项火花奖！多位学者灵魂提问，任正非万字长文解答
「Linux上的视频处理软件」（linux视频处理软件）
Oracle两表联查询实践获取精准信息（oracle两表联查询）

相关主题

Oracle数据库
Oracle数据库imp
Oracle Loop
Oracle 日期操作
Oracle的序列
Oracle 数据库导入与出
Oracle 数据库备份
Oracle SUn
oracle 分区表

zl程序教程