热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
SQL优化工具——收集SQL信息包DM_SQL_RPT
技术分享/
文章详情
/
SQL优化工具——收集SQL信息包DM_SQL_RPT
PYZ
2025/03/07
207
10
1
收集SQL信息包，实现功能如下，可以获取关于问题SQL更详细的信息，便于更精准的分析SQL性能问题。
CREATE OR REPLACE PACKAGE DM_SQL_RPT
AS
--获取所有
PROCEDURE GET_ALL_INFO(EXEC_ID IN BIGINT);
--获取SQL涉及表信息
PROCEDURE GET_TAB_INFO(EXEC_ID IN BIGINT);
--获取表涉及索引
PROCEDURE GET_IDX_INFO(EXEC_ID IN BIGINT);
--获取SQL涉及表的统计信息
PROCEDURE GET_STAT_INFO(EXEC_ID IN BIGINT);
--获取SQL执行计划
PROCEDURE GET_SQL_PLAN(EXEC_ID IN BIGINT);
--获取et信息
PROCEDURE GET_SQLET_INFO(EXEC_ID IN BIGINT);
--获取SQL缓存的执行计划
PROCEDURE GET_SQLPLN_CACHE(EXEC_ID IN BIGINT);
END DM_SQL_RPT;
完整工具：
DM_SQL_RPT.sql
使用示例：
--disql登录，创建包后，开启 MONITOR_SQL_EXEC
SQL> SF_SET_SESSION_PARA_VALUE ('MONITOR_SQL_EXEC', 1);
DMSQL 过程已成功完成
已用时间: 2.339(毫秒). 执行号:100400.
--开启 set serveroutput on
SQL> set serveroutput on
--执行问题SQL
SQL> select count(*)
from (
select t1.object_id,t1.object_name,t1.object_type,t1.last_ddl_time
from t1,t2,t_part t3
where t1.object_id = t2.object_id
AND T1.OBJECT_ID = T3.ID
and t2.status='VALID');
行号 COUNT(*)
---------- --------------------
1 0
已用时间: 00:00:12.929. 执行号:100402.
--获得 SQL执行ID，调用存储过程"获取所有"
SQL> call DM_SQL_RPT.GET_ALL_INFO(100402);
--以下为输出信息
#################################################################################
##### TABLE INFO : HR.T2
---------------------------------------------------------------------------------
| OWNER| TABLE_NAME| NUM_ROWS| LAST_ANALYZED| PARTITIONED| TEMPORARY|
---------------------------------------------------------------------------------
| HR| T2| 20| 2024-11-17 21:04:31| NO| N|
---------------------------------------------------------------------------------
##### COLUMN INFO : HR.T2
---------------------------------------------------------------------------------
| COLUMN_ID| COLUMN_NAME| COLUMN_TYPE| NULLABLE| DEFAULT_VAL|
---------------------------------------------------------------------------------
| 1| OWNER| VARCHAR2(128,0)| Y| NULL|
| 2| OBJECT_NAME| VARCHAR2(128,0)| Y| NULL|
| 3| SUBOBJECT_NAME| VARCHAR2(128,0)| Y| NULL|
| 4| OBJECT_ID| NUMBER(0,0)| Y| NULL|
| 5| DATA_OBJECT_ID| NUMBER(0,0)| Y| NULL|
| 6| OBJECT_TYPE| VARCHAR2(18,0)| Y| NULL|
| 7| CREATED| TIMESTAMP(8,6)| Y| NULL|
| 8| LAST_DDL_TIME| TIMESTAMP(8,6)| Y| NULL|
| 9| TIMESTAMP| TIMESTAMP(8,6)| Y| NULL|
| 10| STATUS| VARCHAR2(7,0)| Y| NULL|
| 11| TEMPORARY| VARCHAR2(1,0)| Y| NULL|
| 12| GENERATED| VARCHAR2(1,0)| Y| NULL|
| 13| SECONDARY| VARCHAR2(1,0)| Y| NULL|
| 14| NAMESPACE| VARCHAR2(1,0)| Y| NULL|
| 15| EDITION_NAME| VARCHAR2(1,0)| Y| NULL|
#################################################################################
##### TABLE INFO : HR.T1
---------------------------------------------------------------------------------
| OWNER| TABLE_NAME| NUM_ROWS| LAST_ANALYZED| PARTITIONED| TEMPORARY|
---------------------------------------------------------------------------------
| HR| T1| 1000000| 2024-11-17 21:04:28| NO| N|
---------------------------------------------------------------------------------
##### COLUMN INFO : HR.T1
---------------------------------------------------------------------------------
| COLUMN_ID| COLUMN_NAME| COLUMN_TYPE| NULLABLE| DEFAULT_VAL|
---------------------------------------------------------------------------------
| 1| OWNER| VARCHAR2(128,0)| Y| NULL|
| 2| OBJECT_NAME| VARCHAR2(128,0)| Y| NULL|
| 3| SUBOBJECT_NAME| VARCHAR2(128,0)| Y| NULL|
| 4| OBJECT_ID| NUMBER(0,0)| Y| NULL|
| 5| DATA_OBJECT_ID| NUMBER(0,0)| Y| NULL|
| 6| OBJECT_TYPE| VARCHAR2(18,0)| Y| NULL|
| 7| CREATED| TIMESTAMP(8,6)| Y| NULL|
| 8| LAST_DDL_TIME| TIMESTAMP(8,6)| Y| NULL|
| 9| TIMESTAMP| TIMESTAMP(8,6)| Y| NULL|
| 10| STATUS| VARCHAR2(7,0)| Y| NULL|
| 11| TEMPORARY| VARCHAR2(1,0)| Y| NULL|
| 12| GENERATED| VARCHAR2(1,0)| Y| NULL|
| 13| SECONDARY| VARCHAR2(1,0)| Y| NULL|
| 14| NAMESPACE| VARCHAR2(1,0)| Y| NULL|
| 15| EDITION_NAME| VARCHAR2(1,0)| Y| NULL|
#################################################################################
##### TABLE INFO : HR.T_PART
---------------------------------------------------------------------------------
| OWNER| TABLE_NAME| NUM_ROWS| LAST_ANALYZED| PARTITIONED| TEMPORARY|
---------------------------------------------------------------------------------
| HR| T_PART| 9378516| 2024-11-17 21:04:32| YES| N|
---------------------------------------------------------------------------------
##### TABLE PART INFO : HR.T_PART
------------------------------------------------------------------------------------------------
|PART_POSI| PART_NAME| COLUMN_NAME|SUBPART_CNT| HIGH_VALUE| NUM_ROWS| LAST_ANALYZED|
------------------------------------------------------------------------------------------------
| 1| P1| CREATE_TIME| 0|DATE'2023-01| 0| 2024-11-17 21:04:46|
| 2|SYS_P27440_27| CREATE_TIME| 0|DATE'2023-08| 460875| 2024-11-17 21:04:52|
| 3|SYS_P27440_27| CREATE_TIME| 0|DATE'2023-09| 621191| 2024-11-17 21:04:48|
| 4|SYS_P27440_27| CREATE_TIME| 0|DATE'2023-10| 602119| 2024-11-17 21:04:46|
| 5|SYS_P27440_27| CREATE_TIME| 0|DATE'2023-11| 621000| 2024-11-17 21:04:50|
| 6|SYS_P27440_27| CREATE_TIME| 0|DATE'2023-12| 601147| 2024-11-17 21:04:46|
| 7|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-01| 620612| 2024-11-17 21:04:46|
| 8|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-02| 620328| 2024-11-17 21:04:51|
| 9|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-03| 581878| 2024-11-17 21:04:49|
| 10|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-04| 620846| 2024-11-17 21:04:49|
| 11|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-05| 599942| 2024-11-17 21:04:50|
| 12|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-06| 622363| 2024-11-17 21:04:48|
| 13|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-07| 601327| 2024-11-17 21:04:47|
| 14|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-08| 621217| 2024-11-17 21:04:51|
| 15|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-09| 622130| 2024-11-17 21:04:48|
| 16|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-10| 601122| 2024-11-17 21:04:47|
| 17|SYS_P27440_27| CREATE_TIME| 0|DATE'2024-12| 360419| 2024-11-17 21:04:47|
------------------------------------------------------------------------------------------------
##### COLUMN INFO : HR.T_PART
---------------------------------------------------------------------------------
| COLUMN_ID| COLUMN_NAME| COLUMN_TYPE| NULLABLE| DEFAULT_VAL|
---------------------------------------------------------------------------------
| 1| ID| VARCHAR2(20,0)| Y| NULL|
| 2| NAME| VARCHAR(50,0)| Y| NULL|
| 3| AGE| INT(4,0)| Y| NULL|
| 4| CREATE_TIME| DATE(3,0)| Y| NULL|
| 5| DEPTNO| VARCHAR2(20,0)| Y| NULL|
---------------------------------------------------------------------------------
####################################################################################################
##### INDEX INFO : HR.T2
CREATE OR REPLACE INDEX "IDX_T2_01" ON "HR"."T2"("DATA_OBJECT_ID" ASC) STORAGE(ON "TBS_HR", CLUSTERBTR) ;
----------------------------------------------------------------------------------------------------
| ID| INDEX_NAME| COLUMN_NAME| COLUMN_POSITION| DESCEND| TYPE| XTYPE| FLAG|
----------------------------------------------------------------------------------------------------
| 33634355| IDX_T2_01| DATA_OBJECT_ID| 1| ASC| BT| SEC| 0|
----------------------------------------------------------------------------------------------------
####################################################################################################
##### INDEX INFO : HR.T1
CREATE OR REPLACE INDEX "IDX_T1_01" ON "HR"."T1"("OWNER" ASC,"OBJECT_ID" ASC) STORAGE(ON "TBS_HR", CLUSTERBTR) ;
----------------------------------------------------------------------------------------------------
| ID| INDEX_NAME| COLUMN_NAME| COLUMN_POSITION| DESCEND| TYPE| XTYPE| FLAG|
----------------------------------------------------------------------------------------------------
| 33634386| IDX_T1_01| OWNER| 1| ASC| BT| SEC| 0|
| 33634386| IDX_T1_01| OBJECT_ID| 2| ASC| BT| SEC| 0|
----------------------------------------------------------------------------------------------------
CREATE OR REPLACE INDEX "IDX_T1_02" ON "HR"."T1"("DATA_OBJECT_ID" ASC,"STATUS" ASC) STORAGE(ON "TBS_HR", CLUSTERBTR) ;
----------------------------------------------------------------------------------------------------
| ID| INDEX_NAME| COLUMN_NAME| COLUMN_POSITION| DESCEND| TYPE| XTYPE| FLAG|
----------------------------------------------------------------------------------------------------
| 33634356| IDX_T1_02| DATA_OBJECT_ID| 1| ASC| BT| SEC| 0|
| 33634356| IDX_T1_02| STATUS| 2| ASC| BT| SEC| 0|
----------------------------------------------------------------------------------------------------
CREATE OR REPLACE INDEX "IDX_T1_ID" ON "HR"."T1"("OBJECT_ID" ASC,"OWNER" ASC) STORAGE(ON "TBS_HR", CLUSTERBTR) ;
----------------------------------------------------------------------------------------------------
| ID| INDEX_NAME| COLUMN_NAME| COLUMN_POSITION| DESCEND| TYPE| XTYPE| FLAG|
----------------------------------------------------------------------------------------------------
| 33634389| IDX_T1_ID| OBJECT_ID| 1| ASC| BT| SEC| 0|
| 33634389| IDX_T1_ID| OWNER| 2| ASC| BT| SEC| 0|
----------------------------------------------------------------------------------------------------
####################################################################################################
##### INDEX INFO : HR.T_PART
CREATE OR REPLACE INDEX "IDX_TP_ID" ON "HR"."T_PART"("ID" ASC) GLOBAL STORAGE(ON "TBS_HR", CLUSTERBTR) ;
----------------------------------------------------------------------------------------------------
| ID| INDEX_NAME| COLUMN_NAME| COLUMN_POSITION| DESCEND| TYPE| XTYPE| FLAG|
----------------------------------------------------------------------------------------------------
| 33634266| IDX_TP_ID| ID| 1| ASC| BT| 9| 0|
----------------------------------------------------------------------------------------------------
CREATE OR REPLACE INDEX "IDX_TP_ID_LOCAL" ON "HR"."T_PART"("ID" ASC,"NAME" ASC) STORAGE(ON "TBS_HR", CLUSTERBTR) ;
----------------------------------------------------------------------------------------------------
| ID| INDEX_NAME| COLUMN_NAME| COLUMN_POSITION| DESCEND| TYPE| XTYPE| FLAG|
----------------------------------------------------------------------------------------------------
| 33634288| IDX_TP_ID_LOCAL| ID| 1| ASC| BT| SEC| 0|
| 33634288| IDX_TP_ID_LOCAL| NAME| 2| ASC| BT| SEC| 0|
----------------------------------------------------------------------------------------------------
######################################################################################################################
##### TABLE STATS INFO : HR.T2
----------------------------------------------------------------------------------------------------------------------
| COL_NAME| COL_ID| COL_TYPE| T_TOTAL| N_SMAPLE| N_DISTINCT| N_NULL| N_BUCKETS| LAST_GATHERED|
----------------------------------------------------------------------------------------------------------------------
| OWNER| 1| VARCHAR2| 20| 20| 7| 0| 7| 2024-11-14 12:52:12|
| OBJECT_NAME| 2| VARCHAR2| 20| 20| 19| 0| 19| 2024-11-14 12:52:12|
| SUBOBJECT_NAME| 3| VARCHAR2| 20| 20| 20| 0| 20| 2024-11-14 12:52:12|
| OBJECT_ID| 4| NUMBER| 20| 20| 20| 0| 20| 2024-11-14 12:52:12|
| DATA_OBJECT_ID| 5| NUMBER| 20| 20| 20| 0| 20| 2024-11-14 12:52:12|
| OBJECT_TYPE| 6| VARCHAR2| 20| 20| 8| 0| 8| 2024-11-14 12:52:12|
| CREATED| 7| TIMESTAMP| 20| 20| 20| 0| 20| 2024-11-14 12:52:12|
| LAST_DDL_TIME| 8| TIMESTAMP| 20| 20| 20| 0| 20| 2024-11-14 12:52:12|
| TIMESTAMP| 9| TIMESTAMP| 20| 20| 20| 0| 20| 2024-11-14 12:52:12|
| STATUS| 10| VARCHAR2| 20| 20| 3| 0| 3| 2024-11-14 12:52:12|
| TEMPORARY| 11| VARCHAR2| 20| 20| 2| 1| 2| 2024-11-14 12:52:12|
| GENERATED| 12| VARCHAR2| 20| 20| 15| 0| 15| 2024-11-14 12:52:12|
| SECONDARY| 13| VARCHAR2| 20| 20| 18| 0| 18| 2024-11-14 12:52:12|
| NAMESPACE| 14| VARCHAR2| 20| 20| 18| 0| 18| 2024-11-14 12:52:12|
| EDITION_NAME| 15| VARCHAR2| 20| 20| 18| 0| 18| 2024-11-14 12:52:12|
----------------------------------------------------------------------------------------------------------------------
######################################################################################################################
##### TABLE STATS INFO : HR.T1
----------------------------------------------------------------------------------------------------------------------
| COL_NAME| COL_ID| COL_TYPE| T_TOTAL| N_SMAPLE| N_DISTINCT| N_NULL| N_BUCKETS| LAST_GATHERED|
----------------------------------------------------------------------------------------------------------------------
| OWNER| 1| VARCHAR2| 1000000| 1000000| 8| 0| 8| 2024-11-12 15:34:03|
| OBJECT_NAME| 2| VARCHAR2| 1000000| 1000000| 904860| 0| 300| 2024-11-12 15:34:08|
| SUBOBJECT_NAME| 3| VARCHAR2| 1000000| 1000000| 999460| 0| 300| 2024-11-12 15:34:12|
| OBJECT_ID| 4| NUMBER| 1000000| 1000000| 995061| 0| 300| 2024-11-12 15:34:12|
| DATA_OBJECT_ID| 5| NUMBER| 1000000| 1000000| 995061| 0| 300| 2024-11-12 15:34:15|
| OBJECT_TYPE| 6| VARCHAR2| 1000000| 1000000| 10| 0| 10| 2024-11-12 15:34:16|
| CREATED| 7| TIMESTAMP| 1000000| 1000000| 976879| 0| 300| 2024-11-12 15:34:19|
| LAST_DDL_TIME| 8| TIMESTAMP| 1000000| 1000000| 976940| 0| 300| 2024-11-12 15:34:22|
| TIMESTAMP| 9| TIMESTAMP| 1000000| 1000000| 980979| 0| 300| 2024-11-12 15:34:26|
| STATUS| 10| VARCHAR2| 1000000| 1000000| 3| 49931| 3| 2024-11-12 15:34:26|
| TEMPORARY| 11| VARCHAR2| 1000000| 1000000| 2| 49702| 2| 2024-11-12 15:34:26|
| GENERATED| 12| VARCHAR2| 1000000| 1000000| 62| 0| 62| 2024-11-12 15:34:26|
| SECONDARY| 13| VARCHAR2| 1000000| 1000000| 62| 0| 62| 2024-11-12 15:34:27|
| NAMESPACE| 14| VARCHAR2| 1000000| 1000000| 62| 0| 62| 2024-11-12 15:34:27|
| EDITION_NAME| 15| VARCHAR2| 1000000| 1000000| 62| 0| 62| 2024-11-12 15:34:27|
----------------------------------------------------------------------------------------------------------------------
######################################################################################################################
##### TABLE STATS INFO : HR.T_PART STATISTICS IS NULL.
##########################################################################################################################################
##### SQL PLAN INFO :
------------------------------------------------------------------------------------------------------------------------------------------
| DEP|SQL_PLAN |
------------------------------------------------------------------------------------------------------------------------------------------
| 0|#NSET2: [1729,1,265]; CPU_COST:0; IO_COST:1729 |
| 1| #PRJT2: [1729,1,265]; CPU_COST:0; IO_COST:1729 |
| 2| #AAGR2: [1729,1,265]; CPU_COST:0; IO_COST:1729 |
| 3| #HASH2 INNER JOIN: [1729,464236,265]; CPU_COST:0; IO_COST:1729 |
| 4| #NEST LOOP INDEX JOIN2: [1,5,217]; CPU_COST:0; IO_COST:1 |
| 5| #SLCT2: [1,5,78]; T2.STATUS = ''VALID''; CPU_COST:0; IO_COST:1 |
| 6| #CSCN2: [1,20,78]; INDEX33634353(T2); CPU_COST:0; IO_COST:1 |
| 5| #SSEK2: [1,1,30]; SCAN_TYPE(ASC); IDX_T1_ID(T1); SCAN_RANGE[(T2.OBJECT_ID,min),(T2.OBJECT_ID,max)); CPU_COST:0; IO_COST:1|
| 4| #PARALLEL: [1076,9378516,48]; SCAN_TYPE(FULL); CPU_COST:1076; IO_COST:0 |
| 5| #SSCN: [1076,9378516,48]; IDX_TP_ID(T_PART AS T3); CPU_COST:1076; IO_COST:0 |
------------------------------------------------------------------------------------------------------------------------------------------
####################################################################################################
##### SQL ET INFO :
----------------------------------------------------------------------------------------------------
| OP| TIME(MS)|PERCENT| RANK| SEQ|N_ENTER| MEM(KB)| DISK(KB)| HASH_CELLS| HASH_CONFLICT|
----------------------------------------------------------------------------------------------------
| PRJT2| 0| 0%| 11| 2| 4| 0| 0| 0| 0|
| AAGR2| 0| 0%| 10| 3| 3| 0| 0| 0| 0|
| IJI2| 0.01| 0%| 8| 5| 18| 0| 0| 0| 0|
| SLCT2| 0.01| 0%| 9| 6| 4| 0| 0| 0| 0|
| DLCK| 0.02| 0%| 7| 0| 2| 0| 0| 0| 0|
| NSET2| 0.05| 0%| 6| 1| 3| 0| 0| 0| 0|
| CSCN2| 0.48| 0%| 5| 7| 2| 0| 0| 0| 0|
| SSEK2| 7.57| 0.06%| 4| 8| 10| 0| 0| 0| 0|
| PLL| 25.5| 0.2%| 3| 9| 62526| 0| 0| 0| 0|
| HI3| 1282.91| 10%| 2| 4| 31270| 15022| 0| 5| 0|
| SSCN| 11514.45| 89.74%| 1| 10| 31263| 0| 0| 0| 0|
----------------------------------------------------------------------------------------------------
DMSQL 过程已成功完成
已用时间: 650.678(毫秒). 执行号:100403.
SQL>
其中，获取SQL缓存的执行计划会在trace目录下生成名为"DM_SQL_PLN_<日期>.trc"的文件。
PS：此工具并非官方推荐工具，欢迎下载测试，使用过程中如有问题或有好的想法和建议，欢迎评论区交流。
评论
后发表回复
isgoing
强啊
发布于
2025/03/13 14:54
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1