对于一个oracle DBA， 虽然研究达梦数据库长达2小时，但一点都不影响使用，查看SQL的执行计划是DBA的必备技能，准备研究一下达梦查看SQL执行计划的几种方法，如explain, 10053,trace, autotrace,plndump 等系列测试。以oracle DBA视觉学习达梦。
SQL> select * from v$version;
行号 BANNER
---------- ---------------------------------
1 DM Database Server 64 V8
2 DB Version: 0x7000c
3 03134283890-20220720-165295-10045
已用时间: 1.355(毫秒). 执行号:545.
SQL>
[dmdba@oel7db1 ~]$ disql anbob/anbob_1234
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 7.069(ms)
disql V8
SQL>
[dmdba@oel7db1 ~]$ sh dm_connect.sh
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 16.982(ms)
disql V8
SQL>
SQL> create table test1(id int);
操作已执行
已用时间: 39.087(毫秒). 执行号:505.
SQL> insert into test1 select rownum from dual connect by rownum<=100; 影响行数 100 已用时间: 72.014(毫秒). 执行号:506. SQL> explain plan for select * from test;
explain plan for select * from test;
explain plan for select * from test;
*
第 1 行, 第 27 列[for]附近出现错误[-2007]:
大意了，不是oracle语法，试mysql或pg的语法.
EXPLAIN方法
SQL> explain select * from test1;
1 #NSET2: [1, 100, 12]
2 #PRJT2: [1, 100, 12]; exp_num(2), is_atom(FALSE)
3 #CSCN2: [1, 100, 12]; INDEX33555470(TEST1)
已用时间: 0.732(毫秒). 执行号:0.
SQL> explain for select * from test1;
行号 PLAN_ID PLAN_NAME CREATE_TIME LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE ROW_NUMS BYTES
---------- ----------- --------- -------------------------- ----------- --------- -------- ------------- --------- ---------- -------------------- -----------
COST CPU_COST IO_COST FILTER JOIN_COND ADVICE_INFO PSTART PSTOP
-------------------- -------------------- -------------------- ------ --------- ----------- ----------- -----------
1 1 NULL 2023-08-08 20:49:10.000000 0 NSET2 NULL NULL NULL NULL 100 12
1 0 0 NULL NULL NULL 0 0
2 1 NULL 2023-08-08 20:49:10.000000 1 PRJT2 NULL NULL NULL NULL 100 12
1 0 0 NULL NULL NULL 0 0
3 1 NULL 2023-08-08 20:49:10.000000 2 CSCN2 TEST1 INDEX33555470 NULL NULL 100 12
1 0 0 NULL NULL NULL 0 0
已用时间: 3.073(毫秒). 执行号:507.
SQL> explain select * from test1 where id=2;
1 #NSET2: [1, 2, 12]
2 #PRJT2: [1, 2, 12]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [1, 2, 12]; TEST1.ID = 2
4 #CSCN2: [1, 100, 12]; INDEX33555470(TEST1)
10053 event
我习惯在命令行工具，提前写一些SQL脚本，提升效率，发现在达梦一样可以执行SQL文件，只是”@”变成了”`”
SQL> desc v$dm_ini 行号 NAME TYPE$ NULLABLE ---------- ------------- ------------ -------- 1 PARA_NAME VARCHAR(128) Y 2 PARA_VALUE VARCHAR(256) Y 3 MIN_VALUE VARCHAR(256) Y 4 MAX_VALUE VARCHAR(256) Y 5 DEFAULT_VALUE VARCHAR(256) Y 6 MPP_CHK CHAR(1) Y 7 SESS_VALUE VARCHAR(256) Y 8 FILE_VALUE VARCHAR(256) Y 9 DESCRIPTION VARCHAR(256) Y 10 PARA_TYPE VARCHAR(200) Y 10 rows got 已用时间: 2.819(毫秒). 执行号:546. SQL> host vi sp.sql select para_name,para_value from v$dm_ini where para_name like '%&1%'; SQL> @p.sql a 2 ; 错误的语句 SQL> ALTER SESSION 0 SET EVENTS '10053 trace name context forever'; 操作已执行 已用时间: 0.863(毫秒). 执行号:530. SQL> select * from test1 where id=100; 行号 ID ---------- ----------- 1 100 已用时间: 3.151(毫秒). 执行号:531. SQL> ALTER SESSION 0 SET EVENTS '10053 trace name context off'; 操作已执行 SQL> `sp TRACE SQL> select para_name,para_value from v$dm_ini where para_name like '%&1%'; 原值 1:select para_name,para_value from v$dm_ini where para_name like '%&1%'; 新值 1:select para_name,para_value from v$dm_ini where para_name like '%TRACE%'; 行号 PARA_NAME PARA_VALUE ---------- --------------- --------------------------------- 1 AUTOTRACE_LEVEL 0 2 FILE_TRACE 0 3 COMM_TRACE 0 4 ERROR_TRACE 0 5 UDP_TRACE_MODE 0 6 TRACE_PATH /home/dm8/dmdbms/data/anbob/trace SQL> host [dmdba@oel7db1 ~]$ cd /home/dm8/dmdbms/data/anbob/trace [dmdba@oel7db1 trace]$ ls DM1_0808_2057_140217059188864.trc [dmdba@oel7db1 trace]$ vi DM1_0808_2057_140217059188864.trc
10053 trace file
*** Plan before optimized:
project: (0);
select: (1); (TEST1.ID = 100)
base table: (TEST1, FULL SEARCH) (0);
<<<<< selectivity estimate of table TEST1 >>>>>
*** stdesc 1: column = ID, scan_type = EQU, key = (100)
stat_info(1059,0,'C')= {
#Valid = 'N',
#Type = '-',
#Card = 100,
#NDV = 33,
#Nulls = 1,
#LP = 9000,
#LVLS = 3,
#CLUF = 0,
#NK = 0,
#NS = 0}
---> st = 0.02500
>>>>> total: 100, estimate match rows: 2, st: 0.02500; -- st_other: 1.000, n_stdesc: 1
---------------- single table access path probe for TEST1 ----------------
*** path 1: INDEX33555470 (FULL search), cost: 0.10070
>>> best access path: INDEX33555470 (FULL search), cost: 0.10070
*** BEST PLAN FOR THIS STATEMENT ***
project: (0.10070, 2, 2);
select: (0.10070, 2, 2); (TEST1.ID = 100)
base table: (TEST1, INDEX33555470, FULL SEARCH) (0.10070, 100, 0);
autotrace on
SQL> set autotrace on SQL> select * from test1 where id=3; 0 | CSCN2 | TEST1 | INDEX33555470 已用时间: 0.952(毫秒). 执行号:0.
v$视图
SQL> desc v$sqltext
行号 NAME TYPE$ NULLABLE
---------- ---------- ------------- --------
1 SQL_ADDR VARBINARY(8) Y
2 SQL_ID INTEGER Y
3 N_EXEC INTEGER Y
4 HASH_VALUE INTEGER Y
5 CMD_TYPE VARCHAR(16) Y
6 SQL_TEXT VARCHAR(7168) Y
7 SQL_NTH INTEGER Y
8 HASH VARBINARY(8) Y
9 LINK_ADDR VARBINARY(8) Y
SQL> desc v$sql_plan
行号 NAME TYPE$ NULLABLE
---------- --------------------- ------------- --------
1 PLN_ADDR VARBINARY(8) Y
2 HASH_VALUE INTEGER Y
3 SQL_ID INTEGER Y
4 PLN_TYPE VARCHAR(16) Y
5 SQLSTR VARCHAR(1000) Y
6 RT_METHOD VARBINARY(8) Y
7 SVPNT VARCHAR(128) Y
8 N_LIT_PARAS INTEGER Y
9 N_CLNT_PARAS INTEGER Y
10 N_COLS INTEGER Y
11 SEL_UPDATABLE CHAR(1) Y
12 N_NDCTS INTEGER Y
13 N_SUBPLNS INTEGER Y
14 N_SUBPGS INTEGER Y
15 PRE_COMMIT CHAR(1) Y
16 IS_RECURSIVE CHAR(1) Y
17 BPARAM_CAN_OPT CHAR(1) Y
18 NDCT_VERSION INTEGER Y
19 CAN_REUSE CHAR(1) Y
20 HAS_SQL CHAR(1) Y
21 HASH VARBINARY(8) Y
22 SCHID INTEGER Y
23 USER_ID INTEGER Y
24 OBJ_ID INTEGER Y
25 RS_CAN_CACHE CHAR(1) Y
26 RS_CAN_CLT_CACHE CHAR(1) Y
27 RS_MUTEX VARBINARY(8) Y
28 N_TABLES INTEGER Y
29 LINK_ADDR VARBINARY(8) Y
30 PHD_TIME DATETIME(6) Y
31 OPTIMIZER VARCHAR(128) Y
32 TABLEID VARCHAR(256) Y
33 SQLCACHE BIGINT Y
34 RET_CMD SMALLINT Y
35 STMT_TYPE INTEGER Y
36 MEM_SIZE BIGINT Y
37 RS_CAN_CACHED_IN_RULE CHAR(1) Y
38 NLS_SORT_TYPE INTEGER Y
39 BINDED CHAR(1) Y
39 rows got
SQL> select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,sqlcache from v$sql_plan where sqlstr like '%test1%';
行号 PLN_ADDR HASH_VALUE SQL_ID SQLSTR
---------- ------------------ ----------- ----------- ------------------------------------------------------------------------------------------------
SQLCACHE
--------------------
1 0x00007F86C4BB1870 -1692871538 56 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,sqlcache from v$sql_plan where sqlstr like '%test1%';
140216839140856
2 0x00007F86C4BAF870 669255808 55 select CACHE_ITEM,OBJ_ID,HASH_VALUE,SQLSTR from v$cachepln where sqlstr like '%test1%';
140216839140344
3 0x00007F86C4B5B870 -1277887457 53 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,obj_id from v$sql_plan where sqlstr like '%test1%';
140216839139832
4 0x00007F86C4B27870 1316724171 51 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR from v$sql_plan where sqlstr like '%test1%';
140216839138808
5 0x00007F86C4AFD870 -441824365 48 select * from v$sql_plan where sqlstr like '%test1%';
140216839137784
6 0x00007F86C6EA3870 1020817542 42 select * from test1 where id=100;
140216839136248
7 0x00007F86C6E33870 11508228 33 select * from test1 where id=2;
140216835708408
8 0x00007F86C6B69870 1256601298 15 insert into test1 select rownum from dual connect by rownum<=100; 140216835705336 SQL> select a.*,b.owner,b.OBJECT_NAME from v$sql_plan a,dba_objects b where a.tableid=b.object_id and a.sql_id=42;
行号 PLN_ADDR HASH_VALUE SQL_ID PLN_TYPE SQLSTR RT_METHOD SVPNT N_LIT_PARAS N_CLNT_PARAS N_COLS SEL_UPDATABLE N_NDCTS N_SUBPLNS N_SUBPGS PRE_COMMIT
---------- ------------------ ----------- ----------- -------- --------------------------------- ------------------ ----- ----------- ------------ ----------- ------------- ----------- ----------- ----------- ----------
IS_RECURSIVE BPARAM_CAN_OPT NDCT_VERSION CAN_REUSE HAS_SQL HASH SCHID USER_ID OBJ_ID RS_CAN_CACHE RS_CAN_CLT_CACHE RS_MUTEX N_TABLES LINK_ADDR
------------ -------------- ------------ --------- ------- ------------------ ----------- ----------- ----------- ------------ ---------------- ------------------ ----------- ------------------
PHD_TIME OPTIMIZER TABLEID SQLCACHE RET_CMD STMT_TYPE MEM_SIZE RS_CAN_CACHED_IN_RULE NLS_SORT_TYPE BINDED OWNER OBJECT_NAME
-------------------------- --------- ------- -------------------- ----------- ----------- -------------------- --------------------- ------------- ------ ------ -----------
1 0x00007F86C6EA3870 1020817542 42 SQL select * from test1 where id=100; 0x00007F86C6EA3B98 NULL 0 0 1 Y 3 0 0 N
N N 0 Y Y 0x0000000000000000 150994945 50331649 0 N N 0x0000000000000000 1 0x00007F86C6E89870
2023-08-08 21:09:04.000000 COST 1059 140216839136248 160 7 24176 Y 0 N SYSDBA TEST1
SQL> desc V$CACHEITEM
行号 NAME TYPE$ NULLABLE
---------- ----------- ------------- --------
1 ADDRESS BIGINT Y
2 TYPE$ VARCHAR(8188) Y
3 OVERFLOW CHAR(1) Y
4 IN_POOL CHAR(1) Y
5 DISABLED CHAR(1) Y
6 N_FIXED INTEGER Y
7 TS_VALUE INTEGER Y
8 ITEM_SIZE BIGINT Y
9 N_HIT INTEGER Y
10 N_DIS_FIXED INTEGER Y
SQL> select CACHE_ITEM,OBJ_ID,HASH_VALUE,SQLSTR from v$cachepln
2 where sqlstr like '%test1%';
行号 CACHE_ITEM OBJ_ID HASH_VALUE SQLSTR
---------- -------------------- ----------- ----------- --------------------------------------------------------------------------------------------
1 140216802932848 0 669255808 select CACHE_ITEM,OBJ_ID,HASH_VALUE,SQLSTR from v$cachepln where sqlstr like '%test1%';
2 140216802588784 0 -1277887457 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,obj_id from v$sql_plan where sqlstr like '%test1%';
3 140216802375792 0 1316724171 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR from v$sql_plan where sqlstr like '%test1%';
4 140216802203760 0 -441824365 select * from v$sql_plan where sqlstr like '%test1%';
5 140216839583856 0 1020817542 select * from test1 where id=100;
6 140216839125104 0 11508228 select * from test1 where id=2;
7 140216836200560 0 1256601298 insert into test1 select rownum from dual connect by rownum<=100;
v$cachepln
SQL> select CACHE_ITEM,OBJ_ID,HASH_VALUE,SQLSTR from v$cachepln where sqlstr like '%test1%'; 行号 CACHE_ITEM OBJ_ID HASH_VALUE SQLSTR ---------- -------------------- ----------- ----------- ------------------------------------------------------------------------------------------------ 1 140216805455984 0 291712662 select CACHE_ITEM,OBJ_ID,HASH_VALUE,SQLSTR from v$cachepln where sqlstr like '%test1%'; 2 140216804661360 0 -1730619283 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,sqlcache from v$sql_plan where sqlstr like '%test1%'; 3 140216802941040 0 -1692871538 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,sqlcache from v$sql_plan where sqlstr like '%test1%'; 4 140216802932848 0 669255808 select CACHE_ITEM,OBJ_ID,HASH_VALUE,SQLSTR from v$cachepln where sqlstr like '%test1%'; 5 140216802588784 0 -1277887457 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR,obj_id from v$sql_plan where sqlstr like '%test1%'; 6 140216802375792 0 1316724171 select PLN_ADDR,HASH_VALUE,SQL_ID,SQLSTR from v$sql_plan where sqlstr like '%test1%'; 7 140216802203760 0 -441824365 select * from v$sql_plan where sqlstr like '%test1%'; 8 140216839583856 0 1020817542 select * from test1 where id=100; 9 140216839125104 0 11508228 select * from test1 where id=2; 10 140216836200560 0 1256601298 insert into test1 select rownum from dual connect by rownum<=100; SQL> alter session set events 'immediate trace name plndump level 140216839583856 , dump_file ''sqlt140216839136248'''; 操作已执行 已用时间: 9.146(毫秒). 执行号:563. -- 没找到trace SQL> alter session set events 'immediate trace name plndump level 140216839583856 , dump_file ''/tmp/sqlt140216839136248'''; 操作已执行 已用时间: 35.423(毫秒). 执行号:565. -- 没生成trace SQL> alter session set events 'immediate trace name plndump level 140216839583856 , dump_file ''/home/dm8/dmdbms/data/anbob/trace/sqlt140216839136248'''; 操作已执行 已用时间: 25.863(毫秒). 执行号:566. SQL> host [dmdba@oel7db1 ~]$ cd /home/dm8/dmdbms/data/anbob/trace/ [dmdba@oel7db1 trace]$ ls DM1_0808_2057_140217059188864.trc dm20230808_0000.trc sqlt140216839136248
Note：
如果没写路径和写/tmp 执行没有报错，但没有生成trace。也可能没有权限。
MONITOR_SQL_EXEC
SQL> `p TRACE SQL> select name ,type,value from v$parameter where name like '%&1%'; 原值 1:select name ,type,value from v$parameter where name like '%&1%'; 新值 1:select name ,type,value from v$parameter where name like '%TRACE%'; 行号 NAME TYPE VALUE ---------- --------------- --------- --------------------------------- 1 AUTOTRACE_LEVEL SESSION 0 2 FILE_TRACE IN FILE 0 3 COMM_TRACE SYS 0 4 ERROR_TRACE SYS 0 5 UDP_TRACE_MODE IN FILE 0 6 TRACE_PATH READ ONLY /home/dm8/dmdbms/data/anbob/trace SQL> alter session set 'MONITOR_SQL_EXEC'=1; DMSQL 过程已成功完成 已用时间: 0.694(毫秒). 执行号:570. SQL> select * from test1 where id=101; 未选定行 SQL> ET(571); 行号 OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS HASH_CONFLICT ---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- 1 DLCK 1 0.26% 5 0 2 0 0 2 PRJT2 2 0.51% 4 2 2 0 0 3 SLCT2 7 1.79% 3 3 3 0 0 4 CSCN2 16 4.09% 2 4 2 0 0 5 NSET2 365 93.35% 1 1 2 0 0 已用时间: 117.034(毫秒). 执行号:572.
DBMS_SQLTUNE
SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>571); 行号 DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=571) ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 SQL Monitoring Report SQL Text ------------------------------ select * from test1 where id=101; Global Information ------------------------------ Status : DONE (ALL ROWS) Session : SYSDBA (140217059188864:7) SQL ID : 80 SQL Execution ID : 571 Execution Started : 2023-08-08 22:40:34 Duration : 0.000419s Program : disql Global Stats ========================================================= | Affected | Bytes | Bytes | Physical | Logical | | Rows | Allocate | Free | Read(page) | Read(page) | ========================================================= | 0 | 0 | 0 | 0 | 1 | ========================================================= SQL Plan ------------------------------ 1 #NSET2: [1, 2, 12] 2 #PRJT2: [1, 2, 12]; exp_num(2), is_atom(FALSE) 3 #SLCT2: [1, 2, 12]; 4 #CSCN2: [1, 100, 12]; INDEX33555470(TEST1) SQL Plan Monitoring Details =================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | =================================================================================================================== | 0 | DLCK | | | | 0.000001 | +0.000404 | 2 | | 0.26 | | | 1 | NSET2 | | 2 | 1 | 0.000365 | +0.000040 | 2 | | 93.35 | | | 2 | PRJT2 | | 2 | 1 | 0.000002 | +0.000039 | 2 | | 0.51 | | | 3 | SLCT2 | | 2 | 1 | 0.000007 | +0.000039 | 3 | | 1.79 | | | 4 | CSCN2 | TEST1 | 100 | 1 | 0.000016 | +0.000039 | 2 | 100 | 4.09 | | =================================================================================================================== 已用时间: 1.231(毫秒). 执行号:574.
— over —