SQL调优和诊断工具之DBMS_SQLDIAG介绍

SQLplusDB

发布于 2022-08-19 21:42:31

7240

发布于 2022-08-19 21:42:31

文章被收录于专栏：Oracle数据库技术

概述

通过调用SQL修复顾问（SQL Repair Advisor），能够诊断和修复SQL相关的问题。

而在命令行模式，我们可以通过DBMS_SQLDIAG程序包中提供的方法，来调用SQL修复顾问（SQL Repair Advisor）。

DBMS_SQLDIAG的主要方法

以下是DBMS_SQLDIAG程序包包含的主要方法：

详细内容可参考在线文档。

Database PL/SQL Packages and Types Reference
>152 DBMS_SQLDIAG
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLDIAG.html#GUID-37E72B14-17BB-47E1-9EA4-1EA1DE823867

通过DBMS_SQLDIAG诊断SQL问题

通过DBMS_SQLDIAG可以诊断很多SQL问题，例如SQL性能、SQL结果不正确、SQL编译或执行错误等问题，具体的方法可以参考MOS相关文档。

    通过DBMS_SQLDIAG诊断SQL问题
    How To Use DBMS_SQLDIAG To Diagnose Various Query Issues (Doc ID 1509192.1)

    诊断SQL性能问题
    NOTE:1386802.1 - How To Use DBMS_SQLDIAG To Diagnose Query Performance Issues

    诊断SQL结果不正确问题
    NOTE:1492650.1 - How to use DBMS_SQLDIAG to Assist Diagnosis of Wrong Results Issues

    诊断SQL编译或执行错误问题
    NOTE:1360120.1 - How to use DBMS_SQLDIAG to Diagnose Crash or Error Issues

笔者对DBMS_SQLDIAG程序包使用最多的功能是生成TEST CASE和收集10053 Trace（Optimizer Trace），用于对SQL问题的深入调查。

通过DBMS_SQLDIAG生成TEST CASE

Oracle提供的SQL Test Case Builder功能，可以自动地生成可以在其他环境中重现SQL问题的TEST CASE，以供我们进行深入的调查和诊断。

具体方法是，在命令行模式通过DBMS_SQLDIAG程序包中提供的方法EXPORT_SQL_TESTCASE ，来生成TEST CASE。

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE主要包括以下3种生成TEST CASE的方法：

    1.通过指定SQL文本生成TEST CASE
    2.通过指定内存中的SQL ID生成TEST CASE
    3.通过指定问题发生时的incident ID生成TEST CASE

下面介绍经常使用的前2种方法：

通过指定SQL文本生成TEST CASE

情况1：不包含绑定变量

1.定义TEST CASE的输出路径

CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '<路径>';

2.定义TEST CASE的SQL文本

create or replace package define_vars is
sql_stmt1 varchar2(32767) := q'# <SQL文内容>#';
end;
/

3.导出SQL TEST CASE

DECLARE
V_TESTCASE CLOB;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY => 'SQL_TEST_DIR', -- 输出路径
SQL_TEXT => define_vars.sql_stmt1, -- SQL文本
USER_NAME => '<UserName>', -- SQL解析用的用户名
exportData => TRUE, -- 是否导出数据
exportPkgbody=>TRUE, -- 是否导出相关程序包
testcase_name => 'tc1', -- TEST CASE名
testcase => V_TESTCASE); -- 指定声明的变量（CLOB）
end;
/

4.执行例

SQL> conn scott/tiger
Connected.
SQL> CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '/media/sf_share/Tools/dbms_sqldiag';
Directory created.

SQL> create or replace package define_vars is
sql_stmt1 varchar2(32767) := q'# select count(*) from emp #';
end;
/ 2 3 4

Package created.

SQL> DECLARE
V_TESTCASE CLOB;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY => 'SQL_TEST_DIR', -- 输出路径
SQL_TEXT => define_vars.sql_stmt1, -- SQL文本
USER_NAME => 'SCOTT', -- SQL解析用的用户名
exportData => TRUE, -- 是否导出数据
exportPkgbody=>TRUE, -- 是否导出相关程序包
testcase_name => 'tc1', -- TEST CASE名
testcase => V_TESTCASE); -- 指定声明的变量（CLOB）
end;
/
2 3 4 5 6 7 8 9 10 11 12 13

PL/SQL procedure successfully completed.

[oracle@db12102 dbms_sqldiag]$ cd /media/sf_share/Tools/dbms_sqldiag
[oracle@db12102 dbms_sqldiag]$ ls
tc1dpexp.dmp tc1dpexp.sql tc1main.xml tc1prmimp.sql tc1smrpt.html tc1ssimp.sql tc1ts.xml tc1xplo.sql tc1xpl.txt
tc1dpexp.log tc1dpimp.sql tc1ol.xml tc1README.txt tc1sql.xml tc1.trc tc1xplf.sql tc1xpls.sql
[oracle@db12102 dbms_sqldiag]$
[oracle@db12102 dbms_sqldiag]$ tar cvf tc1.tar tc1*

情况2：包含绑定变量

※通过bind_list参数指定绑定变量值。

1.定义TEST CASE的输出路径

CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '<路径>';

2.定义TEST CASE的SQL文本

create or replace package define_vars is
sql_stmt1 varchar2(32767) := q'# <SQL文内容>#';
end;
/

3.导出SQL TEST CASE

DECLARE
V_TESTCASE CLOB;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY => 'SQL_TEST_DIR', -- 输出路径
SQL_TEXT => define_vars.sql_stmt1, -- SQL文本
USER_NAME => 'SCOTT', -- SQL解析用的用户名
bind_list => sql_binds(anydata.ConvertVARCHAR2('<变量值>')),
-- 绑定变量的值
exportData => FALSE, -- 是否导出数据
exportPkgbody=>TRUE, -- 是否导出相关程序包
testcase_name => 'tc2', -- TEST CASE名
testcase => V_TESTCASE); -- 指定声明的变量（CLOB）
end;
/

4.执行例

SQL> conn scott/tiger
Connected.
SQL> CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '/media/sf_share/Tools/dbms_sqldiag';


Directory created.


SQL> create or replace package define_vars is
sql_stmt1 varchar2(32767) := q'# select count(*) from from emp where EMPNO=:var #';
end;
/
2 3 4
Package created.


SQL> create or replace package define_vars is
sql_stmt1 varchar2(32767) := q'# select count(*) from emp where EMPNO=:var #';
end;
/ 2 3 4


Package created.


SQL> DECLARE
V_TESTCASE CLOB;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY => 'SQL_TEST_DIR', -- 输出路径
SQL_TEXT => define_vars.sql_stmt1, -- SQL文本
USER_NAME => 'SCOTT', -- SQL解析用的用户名
bind_list => sql_binds(anydata.ConvertVARCHAR2('7900')),
-- 绑定变量的值
exportData => FALSE, -- 是否导出数据
exportPkgbody=>TRUE, -- 是否导出相关程序包
testcase_name => 'tc2', -- TEST CASE名
testcase => V_TESTCASE); -- 指定声明的变量（CLOB）
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15


PL/SQL procedure successfully completed.

[oracle@db12102 dbms_sqldiag]$ cd /media/sf_share/Tools/dbms_sqldiag
[oracle@db12102 dbms_sqldiag]$ tar cvf tc2.tar tc2*

通过指定内存中的SQL ID生成TEST CASE

1.查询要生成TEST CASE的SQL ID

select sql_id,plan_hash_value,sql_text from v$sql
where sql_text like '%<SQL文的一部分>%';

2.定义TEST CASE的输出路径

CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '<路径>';

3.导出SQL TEST CASE

DECLARE
V_TESTCASE CLOB;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY => 'SQL_TEST_DIR', -- 输出路径
sql_id => '<SQL_ID>', -- 指定SQL_ID
plan_hash_value => <plan_hash_value>, -- 指定plan_hash_value
exportData => FALSE, -- 是否导出数据
exportPkgbody=>TRUE, -- 是否导出相关程序包
testcase_name => 'tc3', -- TEST CASE名
testcase => V_TESTCASE); -- 指定声明的变量（CLOB）
end;
/

4.执行例

SQL> select count(*) from emp;
COUNT(*)
----------
12

SQL> select sql_id,plan_hash_value,sql_text from v$sql
where sql_text like 'select count(*) from emp%'; 2

SQL_ID PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT
--------------------------------------------------------------------------------
g59vz2u4cu404 2937609675
select count(*) from emp

SQL> CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '/media/sf_share/Tools/dbms_sqldiag';

Directory created.


SQL> DECLARE
V_TESTCASE CLOB;
BEGIN
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
DIRECTORY => 'SQL_TEST_DIR', -- 输出路径
sql_id => 'g59vz2u4cu404', -- 指定SQL_ID
plan_hash_value => 2937609675, -- 指定plan_hash_value
exportData => FALSE, -- 是否导出数据
exportPkgbody=>TRUE, -- 是否导出相关程序包
testcase_name => 'tc3', -- TEST CASE名
testcase => V_TESTCASE); -- 指定声明的变量（CLOB）
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13


PL/SQL procedure successfully completed.

导入生成的TEST CASE

1.建立测试用户

connect /as sysdba
create use test identitified by test;
grant dba to test;

2.定义TEST CASE的导入路径

CREATE OR REPLACE DIRECTORY SQL_TEST_DIR AS '<路径>';

3.执行导入程序

connect test/test
begin
dbms_sqldiag.import_sql_testcase(directory=>'SQL_TEST_DIR',
filename =>'tc2main.xml');--<TEST CASE名>main.xml
end;
/

参考：

Database SQL Tuning Guide
>17 Gathering Diagnostic Data with SQL Test Case Builder

Database PL/SQL Packages and Types Reference
>EXPLAIN_SQL_TESTCASE Function

通过DBMS_SQLDIAG生成Optimizer Trace（10053）

如之前文章中介绍的，在11gR2以后的版本中，追加DBMS_SQLDIAG.DUMP_TRACE()这个功能方法，可以针对内存中的SQL不需要重新执行SQL，而是通过相关的统计信息进行解析生成Optimizer Trace（10053）。

具体取得方法

--1.找到要跟踪SQL的SQL ID
SQL> select sql_id, child_number, sql_text from v$sql
where sql_text like '%Part of SQL%';


--2.确认当前的执行计划
SQL> set pages 0
SQL> select * from
table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&sqlid', cursor_child_no =>0,format=>'ALL'));


--3.生成Optimizer Trace（10053）
SQL> BEGIN
DBMS_SQLDIAG.DUMP_TRACE(
P_SQL_ID => '&P_SQL_ID',
P_CHILD_NUMBER => 0,
P_COMPONENT => 'Compiler',
P_FILE_ID => '10053_TRACE');
END;
/

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-05-26，如有侵权请联系 cloudcommunity@tencent.com 删除

sql