[20210119]看执行计划可以使用hash_value.txt

来源：这里教程网时间：2026-03-03 16:22:59 作者：

[20210119]看执行计划可以使用hash_value.txt --//一直以为使用dbms_xplan.display_cursor看执行计划使用sql_id,实际上也可以使用hash_value查看.做一个测试: 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: SCOTT@book> create table dula as select * from dual ; Table created. SCOTT@book> select sysdate from dual --lKxJJcPEUTjApATJphcLUMjsJnnVVGZV; SYSDATE ------------------- 2021-01-19 11:03:43 SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1752296241 8ppsmutn73utj 0 6871eb31 SCOTT@book> select sysdate from dula --AxznlSHtpUlGnoJCxeekPkMeydsUmtiQ; SYSDATE ------------------- 2021-01-19 11:06:47 SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1752296241 dyq9z09n73utj 0 6871eb31 --//注:以上2条sql语句HASH_VALUE=1752296241相同. SCOTT@book> @ dpc 1752296241 '' PLAN_TABLE_OUTPUT -------------------------------------- HASH_VALUE 1752296241, child number 0 -------------------------------------- An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Plan hash value: 3414513622 ------------------------------------------------------------------- | Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 2 (100)| | | 0 | SELECT STATEMENT | | | 3 (100)| | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DULA | 1 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DUAL@SEL$1 1 - SEL$1 / DULA@SEL$1 Note ----- - dynamic sampling used for this statement (level=2) - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level HASH_VALUE 1752296241, child number 0 -------------------------------------- An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows Plan hash value: 3414513622 ------------------------------------------------------------------- | Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 2 (100)| | | 0 | SELECT STATEMENT | | | 3 (100)| | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DULA | 1 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DUAL@SEL$1 1 - SEL$1 / DULA@SEL$1 Note ----- - dynamic sampling used for this statement (level=2) - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 56 rows selected. --//注意看显示输出报错,可以发现冲突后,2个显示信息混合在一起输出. --//dula表没有分析. - dynamic sampling used for this statement (level=2). --//使用sql_id就不会出现这样的问题. SCOTT@book> @ dpc dyq9z09n73utj '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dyq9z09n73utj, child number 0 ------------------------------------- select sysdate from dula --AxznlSHtpUlGnoJCxeekPkMeydsUmtiQ Plan hash value: 3414513622 ------------------------------------------------------------------- | Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 3 (100)| | | 1 | TABLE ACCESS FULL| DULA | 1 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DULA@SEL$1 Note ----- - dynamic sampling used for this statement (level=2) - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 25 rows selected. --//理论讲hash_value值还是很容易出现冲突的. 3.再来看看sql_id冲突的情况: select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib; SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1992264959 ayr58apvbz37z 0 76bf8cff select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq; SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1992264959 ayr58apvbz37z 0 76bf8cff --//如果在生产系统遇到语句不同,sql_id相同,真的可以买cai piao了. SCOTT@book> @ dpc ayr58apvbz37z '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID ayr58apvbz37z, child number 0 ------------------------------------- An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Plan hash value: 1817786178 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 58 (100)| | | | | | 0 | SELECT STATEMENT | | | | 55 (100)| | | | | |* 1 | COUNT STOPKEY | | | | | | | | | |* 1 | COUNT STOPKEY | | | | | | | | | |* 2 | HASH JOIN | | 9 | 4437 | 58 (0)| 00:00:01 | 2440K| 2440K| 1533K (0)| |* 2 | HASH JOIN | | 11 | 6336 | 55 (0)| 00:00:01 | 2440K| 2440K| 1660K (0)| | 3 | INDEX FULL SCAN | I_USER2 | 94 | 376 | 1 (0)| 00:00:01 | | | | | 3 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | 0 (0)| | | | | | 4 | NESTED LOOPS | | 9 | 3366 | 57 (0)| 00:00:01 | | | | | 4 | NESTED LOOPS | | 11 | 4334 | 55 (0)| 00:00:01 | | | | |* 5 | HASH JOIN RIGHT OUTER | | 9 | 3294 | 48 (0)| 00:00:01 | 2440K| 2440K| 1587K (0)| |* 5 | HASH JOIN RIGHT OUTER | | 11 | 3729 | 55 (0)| 00:00:01 | 2440K| 2440K| 1587K (0)| | 6 | INDEX FULL SCAN | I_USER2 | 94 | 376 | 1 (0)| 00:00:01 | | | | | 6 | INDEX FULL SCAN | I_USER2 | 94 | 376 | 1 (0)| 00:00:01 | | | | | 7 | NESTED LOOPS OUTER | | 9 | 2331 | 47 (0)| 00:00:01 | | | | | 7 | NESTED LOOPS OUTER | | 11 | 2475 | 54 (0)| 00:00:01 | | | | |* 8 | HASH JOIN RIGHT OUTER | | 9 | 2259 | 38 (0)| 00:00:01 | 2616K| 2616K| 1461K (0)| |* 8 | HASH JOIN | | 11 | 2387 | 45 (0)| 00:00:01 | 1483K| 1483K| 1581K (0)| | 9 | TABLE ACCESS FULL | TS$ | 9 | 27 | 5 (0)| 00:00:01 | | | | | 9 | TABLE ACCESS FULL | USER$ | 94 | 1598 | 3 (0)| 00:00:01 | | | | | 10 | NESTED LOOPS OUTER | | 9 | 1404 | 33 (0)| 00:00:01 | | | | | 10 | NESTED LOOPS | | 11 | 1265 | 42 (0)| 00:00:01 | | | | |* 11 | HASH JOIN | | 9 | 1305 | 24 (0)| 00:00:01 | 1483K| 1483K| 1584K (0)| | 11 | NESTED LOOPS | | 11 | 1265 | 42 (0)| 00:00:01 | | | | | 12 | TABLE ACCESS FULL | USER$ | 94 | 1598 | 3 (0)| 00:00:01 | | | | | 12 | NESTED LOOPS OUTER | | 11 | 869 | 20 (0)| 00:00:01 | | | | | 13 | NESTED LOOPS | | 9 | 576 | 21 (0)| 00:00:01 | | | | | 13 | NESTED LOOPS OUTER | | 11 | 814 | 19 (0)| 00:00:01 | | | | | 14 | NESTED LOOPS | | 9 | 576 | 21 (0)| 00:00:01 | | | | |* 14 | HASH JOIN | | 11 | 693 | 8 (0)| 00:00:01 | 2616K| 2616K| 1463K (0)| |* 15 | TABLE ACCESS FULL | IND$ | 9 | 252 | 3 (0)| 00:00:01 | | | | | 15 | TABLE ACCESS FULL | TS$ | 9 | 27 | 5 (0)| 00:00:01 | | | | |* 16 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | | | |* 16 | TABLE ACCESS FULL | TAB$ | 91 | 2730 | 3 (0)| 00:00:01 | | | | |* 17 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 36 | 2 (0)| 00:00:01 | | | | | 17 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 | | | | | 18 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 | | | | |* 18 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| | | | | |* 19 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| | | | | |* 19 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 | | | | |* 20 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 1 (0)| 00:00:01 | | | | |* 20 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 | | | | |* 21 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 1 (0)| 00:00:01 | | | | |* 21 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 36 | 2 (0)| 00:00:01 | | | | |* 22 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 1 (0)| 00:00:01 | | | | |* 23 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)| | | | | ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$7ABE1C4F 1 - SEL$7ABE1C4F 3 - SEL$7ABE1C4F / IU@SEL$2 3 - SEL$7ABE1C4F / KSPPCV@SEL$2 6 - SEL$7ABE1C4F / ITU@SEL$2 6 - SEL$7ABE1C4F / CU@SEL$2 9 - SEL$7ABE1C4F / TS@SEL$2 9 - SEL$7ABE1C4F / U@SEL$2 12 - SEL$7ABE1C4F / U@SEL$2 15 - SEL$7ABE1C4F / I@SEL$2 15 - SEL$7ABE1C4F / TS@SEL$2 16 - SEL$7ABE1C4F / O@SEL$2 16 - SEL$7ABE1C4F / T@SEL$2 17 - SEL$7ABE1C4F / O@SEL$2 17 - SEL$7ABE1C4F / S@SEL$2 18 - SEL$7ABE1C4F / S@SEL$2 18 - SEL$7ABE1C4F / S@SEL$2 19 - SEL$7ABE1C4F / S@SEL$2 19 - SEL$7ABE1C4F / CO@SEL$2 20 - SEL$7ABE1C4F / ITO@SEL$2 20 - SEL$7ABE1C4F / O@SEL$2 21 - SEL$7ABE1C4F / IO@SEL$2 21 - SEL$7ABE1C4F / O@SEL$2 22 - SEL$7ABE1C4F / CX@SEL$2 23 - SEL$7ABE1C4F / KSPPI@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=9) 1 - filter(ROWNUM<=10) 2 - access("IO"."OWNER#"="IU"."USER#") 2 - access("KSPPI"."INDX"="KSPPCV"."INDX") 5 - access("ITO"."OWNER#"="ITU"."USER#") 5 - access("CX"."OWNER#"="CU"."USER#") 8 - access("I"."TS#"="TS"."TS#") 8 - access("O"."OWNER#"="U"."USER#") 11 - access("U"."USER#"="O"."OWNER#") 14 - access("T"."TS#"="TS"."TS#") 15 - filter(BITAND("I"."FLAGS",4096)=0) 16 - access("O"."OBJ#"="I"."OBJ#") 16 - filter(BITAND("T"."PROPERTY",1)=0) 17 - filter(BITAND("O"."FLAGS",128)=0) 18 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#") 19 - access("I"."TS#"="S"."TS#" AND "I"."FILE#"="S"."FILE#" AND "I"."BLOCK#"="S"."BLOCK#") 19 - access("T"."BOBJ#"="CO"."OBJ#") 20 - access("I"."INDMETHOD#"="ITO"."OBJ#") 20 - access("O"."OBJ#"="T"."OBJ#") 21 - access("I"."BO#"="IO"."OBJ#") 21 - filter(BITAND("O"."FLAGS",128)=0) 22 - access("T"."DATAOBJ#"="CX"."OBJ#") 23 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled') Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level ..//太长仅仅贴出一段. --//可以发现也是2个执行计划混在一起输出. SELECT sql_id,hash_value,to_char(hash_value,'xxxxxxxx'),sql_text,executions FROM v$sql WHERE sql_text ='select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib' OR sql_text ='select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq'; SQL_ID HASH_VALUE TO_CHAR(H SQL_TEXT EXECUTIONS ------------- ---------- --------- ------------------------------------------------------------ ---------- ayr58apvbz37z 1992264959 76bf8cff select owner, index_name from dba_indexes where rownum<=9 -- 1 BaERRzEYqyYphBAvEbIrbYYDKkemLaib ayr58apvbz37z 1992264959 76bf8cff select owner, table_name from dba_tables where rownum<=10 -- 1 XhiidvehudXqDpCMZokNkScXlQiIUkUq SELECT sql_id,hash_value,to_char(hash_value,'xxxxxxxx'),sql_text,executions FROM v$sqlarea WHERE sql_text ='select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib' OR sql_text ='select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq'; SQL_ID HASH_VALUE TO_CHAR(H SQL_TEXT EXECUTIONS ------------- ---------- --------- ------------------------------------------------------------ ---------- ayr58apvbz37z 1992264959 76bf8cff select owner, index_name from dba_indexes where rownum<=9 -- 2 BaERRzEYqyYphBAvEbIrbYYDKkemLaib --//v$sqlarea仅仅看到1条语句.

编辑推荐：

返回列表

[20210119]看执行计划可以使用hash_value.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推