[20240325]expand_sql_text dba_hist_sysstat(12c).txt
[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
--//前几天测试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
