[20240325]expand_sql_text dba_hist_sysstat(12c).txt

来源：这里教程网时间：2026-03-03 19:46:34 作者：

[20240325]expand_sql_text dba_hist_sysstat(12c).txt --//前几天测试dba_hist_sysdate的底层视图定义里面包含提示. --//测试一条sql语句包含dba_hist_sysstat 使用expand_sql_text的展开情况. 1.环境: SYS@test> @ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 2.测试: SYS@test> @ v2 dba_hist_sysstat Show SQL text of views matching "dba_hist_sysstat"... V_OWNER VIEW_NAME TEXT ------- ---------------- ------------------------------------------------------------------------------------------------ SYS DBA_HIST_SYSSTAT select "SNAP_ID","DBID","INSTANCE_NUMBER","STAT_ID","STAT_NAME","VALUE","CON_DBID","CON_ID" from AWR_ROOT_SYSSTAT SYS@test> @ v2 AWR_ROOT_SYSSTAT Show SQL text of views matching "AWR_ROOT_SYSSTAT"... V_OWNER VIEW_NAME TEXT ------- ---------------- ------------------------------------------------------------------------------------------------ SYS AWR_ROOT_SYSSTAT select /*+ leading(sn s nm) use_hash(sn s) */ s.snap_id, s.dbid, s.instance_number, s.stat_id, nm.stat_name, value, decode(s.con_dbid, 0, s.dbid, s.con_dbid), con_dbid_to_id(decode(s.con_dbid, 0, s.dbid, s.con_dbid)) con_id from WRM$_SNAPSHOT sn, WRH$_SYSSTAT s, WRH$_STAT_NAME nm where s.stat_id = nm.stat_id and s.dbid = nm.dbid and s.snap_id = sn.snap_id and s.dbid = sn.dbid and s.instance_number = sn.instance_number and sn.status = 0 --//底层视图AWR_ROOT_SYSSTAT包含提示leading(sn s nm) use_hash(sn s) SYS@test> select max(snap_id),max(dbid) from dba_hist_snapshot; MAX(SNAP_ID) MAX(DBID) ------------ ---------- 588 2286984624 SYS@test> SELECT stat_name,value FROM dba_hist_sysstat WHERE snap_id = 588 AND instance_number = 1 AND dbid = 2286984624 AND stat_name in ('sorts (rows)'); STAT_NAME VALUE -------------------- ---------- sorts (rows) 764924 SYS@test> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 3920320955 3bkv493nuqqdv 1 88507 2701875554 e9ab59bb 2024-03-24 20:59:21 16777217 SYS@test> @ expand_sql_text.sql 3bkv493nuqqdv SELECT "A1"."STAT_NAME" "STAT_NAME","A1"."VALUE" "VALUE" FROM (SELECT "A2"."SNAP_ID" "SNAP_ID","A2"."DBID" "DBID","A2"."INSTANCE_NUMBER" "INSTANCE_NUMBER","A2"."STAT_NAME" "STAT_NAME","A2"."VALUE" "VALUE" FROM (SELECT /*+ LEADING ("A5" "A4" "A3") USE_HASH ("A4") USE_HASH ("A5") */ "A4"."SNAP_ID" "SNAP_ID","A4"."DBID" "DBID","A4"."INSTANCE_NUMBER" "INSTANCE_NUMBER","A3"."STAT_NAME" "STAT_NAME","A4"."VALUE" "VALUE",CON_DBID_TO_ID(DECODE("A4"."CON_DBID",0,"A4"."DBID","A4"."CON_DBID")) "CON_ID" FROM "SYS"."WRM$_SNAPSHOT" "A5","SYS"."WRH$_SYSSTAT" "A4","SYS"."WRH$_STAT_NAME" "A3" WHERE "A4"."STAT_ID"="A3"."STAT_ID" AND "A4"."DBID"="A3"."DBID" AND "A4"."SNAP_ID"="A5"."SNAP_ID" AND "A4"."DBID"="A5"."DBID" AND "A4"."INSTANCE_NUMBER"="A5"."INSTANCE_NUMBER" AND "A5"."STATUS"=0) "A2") "A1" WHERE "A1"."SNAP_ID"=588 AND "A1"."INSTANCE_NUMBER"=1 AND "A1"."DBID"=2286984624 AND "A1"."STAT_NAME"='sorts (rows)' PL/SQL procedure successfully completed. --//格式化如下: SELECT "A1"."STAT_NAME" "STAT_NAME","A1"."VALUE" "VALUE" FROM (SELECT "A2"."SNAP_ID" "SNAP_ID", "A2"."DBID" "DBID","A2"."INSTANCE_NUMBER" "INSTANCE_NUMBER", "A2"."STAT_NAME" "STAT_NAME", "A2"."VALUE" "VALUE" FROM (SELECT /*+ LEADING ("A5" "A4" "A3") USE_HASH ("A4") USE_HASH ("A5") */ "A4"."SNAP_ID" "SNAP_ID", "A4"."DBID" "DBID","A4"."INSTANCE_NUMBER" "INSTANCE_NUMBER", "A3"."STAT_NAME" "STAT_NAME","A4"."VALUE" "VALUE", CON_DBID_TO_ID(DECODE("A4"."CON_DBID", 0,"A4"."DBID","A4"."CON_DBID")) "CON_ID" FROM "SYS"."WRM$_SNAPSHOT" "A5","SYS"."WRH$_SYSSTAT" "A4", "SYS"."WRH$_STAT_NAME" "A3" WHERE "A4"."STAT_ID" = "A3"."STAT_ID" AND "A4"."DBID" = "A3"."DBID" AND "A4"."SNAP_ID" = "A5"."SNAP_ID" AND "A4"."DBID" = "A5"."DBID" AND "A4"."INSTANCE_NUMBER" = "A5"."INSTANCE_NUMBER" AND "A5"."STATUS" = 0) "A2") "A1" WHERE "A1"."SNAP_ID" = 588 AND "A1"."INSTANCE_NUMBER" = 1 AND "A1"."DBID" = 2286984624 AND "A1"."STAT_NAME" = 'sorts (rows)' --//存在提示LEADING ("A5" "A4" "A3") USE_HASH ("A4") USE_HASH ("A5"),与前面的写法有一些不同. --//也就是通过这样的方式也可以看出语句里面包含提示. 3.12c下看执行计划没有hint_report功能. SYS@test> SELECT stat_name,value FROM dba_hist_sysstat WHERE snap_id = 588 AND instance_number = 1 AND dbid = 2286984624 AND stat_name in ('sorts (rows)'); STAT_NAME VALUE -------------------- ---------- sorts (rows) 764924 SYS@test> @ dpc '' hint_report '' PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------- Error: format 'all allstats last peeked_binds cost partition note -projection -outline hint_report' not valid for DBMS_XPLAN.DISPLAY_CURSOR() --//12c版本执行计划就看不到Hint Report的相关信息. --//执行计划看到内容如下: Plan hash value: 1931519367 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | | | 1 |00:00:00.01 | 1628 | | | | |* 1 | HASH JOIN | | 1 | 1 | 126 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 1628 | 1448K| 1448K| 1504K (0)| |* 2 | HASH JOIN | | 1 | 1 | 81 | 1 (0)| 00:00:01 | | | 1788 |00:00:00.01 | 1604 | 1572K| 1572K| 713K (0)| |* 3 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 1 | 16 | 1 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | | |* 4 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | 1 | | 0 (0)| | | | 1 |00:00:00.01 | 1 | | | | | 5 | PARTITION RANGE SINGLE | | 1 | 1 | 65 | 0 (0)| | 2 | 2 | 1788 |00:00:00.01 | 1602 | | | | | 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| WRH$_SYSSTAT | 1 | 1 | 65 | 0 (0)| | 2 | 2 | 1788 |00:00:00.01 | 1602 | | | | |* 7 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 1 | 1 | | 0 (0)| | 2 | 2 | 1788 |00:00:00.01 | 16 | | | | |* 8 | TABLE ACCESS FULL | WRH$_STAT_NAME | 1 | 1 | 45 | 1 (0)| 00:00:01 | | | 1 |00:00:00.01 | 24 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5C160134 3 - SEL$5C160134 / SN@SEL$3 4 - SEL$5C160134 / SN@SEL$3 6 - SEL$5C160134 / S@SEL$3 7 - SEL$5C160134 / S@SEL$3 8 - SEL$5C160134 / NM@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$5C160134" "NM"@"SEL$3") USE_HASH(@"SEL$5C160134" "NM"@"SEL$3") IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$5C160134") MERGE(@"SEL$335DD26A" >"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$335DD26A") MERGE(@"SEL$3" >"SEL$2") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") INDEX_RS_ASC(@"SEL$5C160134" "SN"@"SEL$3" ("WRM$_SNAPSHOT"."DBID" "WRM$_SNAPSHOT"."SNAP_ID" "WRM$_SNAPSHOT"."INSTANCE_NUMBER")) INDEX_RS_ASC(@"SEL$5C160134" "S"@"SEL$3" ("WRH$_SYSSTAT"."DBID" "WRH$_SYSSTAT"."SNAP_ID" "WRH$_SYSSTAT"."INSTANCE_NUMBER" "WRH$_SYSSTAT"."STAT_ID" "WRH$_SYSSTAT"."CON_DBID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5C160134" "S"@"SEL$3") LEADING(@"SEL$5C160134" "SN"@"SEL$3" "S"@"SEL$3" "NM"@"SEL$3") USE_HASH(@"SEL$5C160134" "S"@"SEL$3") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("S"."STAT_ID"="NM"."STAT_ID" AND "S"."DBID"="NM"."DBID") 2 - access("S"."SNAP_ID"="SN"."SNAP_ID" AND "S"."DBID"="SN"."DBID" AND "S"."INSTANCE_NUMBER"="SN"."INSTANCE_NUMBER") 3 - filter("SN"."STATUS"=0) 4 - access("SN"."DBID"=2286984624 AND "SN"."SNAP_ID"=588 AND "SN"."INSTANCE_NUMBER"=1) 7 - access("S"."DBID"=2286984624 AND "S"."SNAP_ID"=588 AND "S"."INSTANCE_NUMBER"=1) 8 - filter(("NM"."STAT_NAME"='sorts (rows)' AND "NM"."DBID"=2286984624)) Note ----- - this is an adaptive plan --//SN@SEL$3 =>WRM$_SNAPSHOT --//S@SEL$3 =>WRH$_SYSSTAT --//NM@SEL$3 =>WRH$_STAT_NAME --//执行计划比较好的连接顺序是 LEADING(@"SEL$5C160134" "SN"@"SEL$3" "NM"@"SEL$3" "S"@"SEL$3" ) --//看到执行计划如下: Plan hash value: 529202186 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 15 (100)| | | | 1 |00:00:00.03 | 49 | | | | |* 1 | HASH JOIN | | 1 | 1 | 85 | 15 (0)| 00:00:01 | | | 1 |00:00:00.03 | 49 | 1082K| 1082K| 406K (0)| | 2 | PART JOIN FILTER CREATE | :BF0000 | 1 | 1 | 61 | 8 (0)| 00:00:01 | | | 1 |00:00:00.01 | 25 | | | | | 3 | NESTED LOOPS | | 1 | 1 | 61 | 8 (0)| 00:00:01 | | | 1 |00:00:00.01 | 25 | | | | |* 4 | TABLE ACCESS BY INDEX ROWID| WRM$_SNAPSHOT | 1 | 1 | 16 | 1 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | | |* 5 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | 1 | | 0 (0)| | | | 1 |00:00:00.01 | 1 | | | | |* 6 | TABLE ACCESS FULL | WRH$_STAT_NAME | 1 | 1 | 45 | 7 (0)| 00:00:01 | | | 1 |00:00:00.01 | 23 | | | | | 7 | PARTITION RANGE SINGLE | | 1 | 1788 | 42912 | 7 (0)| 00:00:01 |KEY(AP)|KEY(AP)| 1788 |00:00:00.03 | 24 | | | | |* 8 | TABLE ACCESS FULL | WRH$_SYSSTAT | 1 | 1788 | 42912 | 7 (0)| 00:00:01 | 2 | 2 | 1788 |00:00:00.01 | 24 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5C160134 4 - SEL$5C160134 / SN@SEL$3 5 - SEL$5C160134 / SN@SEL$3 6 - SEL$5C160134 / NM@SEL$3 8 - SEL$5C160134 / S@SEL$3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$5C160134") MERGE(@"SEL$335DD26A" >"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$335DD26A") MERGE(@"SEL$3" >"SEL$2") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") INDEX_RS_ASC(@"SEL$5C160134" "SN"@"SEL$3" ("WRM$_SNAPSHOT"."DBID" "WRM$_SNAPSHOT"."SNAP_ID" "WRM$_SNAPSHOT"."INSTANCE_NUMBER")) FULL(@"SEL$5C160134" "NM"@"SEL$3") FULL(@"SEL$5C160134" "S"@"SEL$3") LEADING(@"SEL$5C160134" "SN"@"SEL$3" "NM"@"SEL$3" "S"@"SEL$3") USE_NL(@"SEL$5C160134" "NM"@"SEL$3") USE_HASH(@"SEL$5C160134" "S"@"SEL$3") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("S"."DBID"="NM"."DBID" AND "S"."SNAP_ID"="SN"."SNAP_ID" AND "S"."STAT_ID"="NM"."STAT_ID" AND "S"."DBID"="SN"."DBID" AND "S"."INSTANCE_NUMBER"="SN"."INSTANCE_NUMBER") 4 - filter("SN"."STATUS"=0) 5 - access("SN"."DBID"=2286984624 AND "SN"."SNAP_ID"=588 AND "SN"."INSTANCE_NUMBER"=1) 6 - filter(("NM"."STAT_NAME"='sorts (rows)' AND "NM"."DBID"=2286984624)) 8 - filter(("S"."SNAP_ID"=588 AND "S"."INSTANCE_NUMBER"=1 AND "S"."DBID"=2286984624)) 68 rows selected. --//实际上id=8,不用全表扫描,因为前面WRH$_STAT_NAME已经知道stat_id,这样仅仅通过索引主键就可以定位. index(@"SEL$5C160134" "S"@"SEL$3") LEADING(@"SEL$5C160134" "SN"@"SEL$3" "NM"@"SEL$3" "S"@"SEL$3" ) USE_nl(@"SEL$5C160134" "S"@"SEL$3") --//加入以上提示执行计划如下,这才是最佳的执行计划,上个星期思路太乱了,实际上仅仅涉及3个表,很容易通过提示获得最佳的执行计 --//划. Plan hash value: 340881634 ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | | | 1 |00:00:00.01 | 30 | | 1 | NESTED LOOPS | | 1 | 1 | 85 | 10 (0)| 00:00:01 | | | 1 |00:00:00.01 | 30 | | 2 | NESTED LOOPS | | 1 | 1 | 85 | 10 (0)| 00:00:01 | | | 1 |00:00:00.01 | 29 | | 3 | NESTED LOOPS | | 1 | 1 | 61 | 8 (0)| 00:00:01 | | | 1 |00:00:00.01 | 26 | |* 4 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 1 | 16 | 1 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | |* 5 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | 1 | | 0 (0)| | | | 1 |00:00:00.01 | 1 | |* 6 | TABLE ACCESS FULL | WRH$_STAT_NAME | 1 | 1 | 45 | 7 (0)| 00:00:01 | | | 1 |00:00:00.01 | 24 | | 7 | PARTITION RANGE SINGLE | | 1 | 1 | | 1 (0)| 00:00:01 | 2 | 2 | 1 |00:00:00.01 | 3 | |* 8 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 1 | 1 | | 1 (0)| 00:00:01 | 2 | 2 | 1 |00:00:00.01 | 3 | | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| WRH$_SYSSTAT | 1 | 1 | 24 | 2 (0)| 00:00:01 | 2 | 2 | 1 |00:00:00.01 | 1 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- --//实际上如果早知道oracle在底层视图写死提示,问题很容易解决.可以简单的加入 IGNORE_OPTIM_EMBEDDED_HINTS --//很奇怪使用这个提示不行 OPT_PARAM('_optimizer_ignore_hints','true'). SYS@test> alter session set "_optimizer_ignore_hints"=true; Session altered. --//12c下这个参数还是隐含参数,19c已经不是了. 4.附上expand_sql_text.sql代码: set long 20000 set serveroutput on declare L_sqltext clob := null; l_version varchar2(3) := null; l_sql clob := null; l_result clob := null; begin select regexp_replace(version,'\..*') into l_version from v$instance; select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1'; if l_version = '11' then l_sql := 'begin dbms_sql2.expand_sql_text( :a,:b ); end;'; elsif l_version >= '12' then l_sql := 'begin dbms_utility.expand_sql_text(:a,:b); end;'; end if; execute immediate l_sql using in l_sqltext,out l_result; dbms_output.put_line(l_result); end; / set serveroutput off

编辑推荐：

返回列表

[20240325]expand_sql_text dba_hist_sysstat(12c).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推