[20210119]看執行計劃可以使用hash_value.txt
[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條語句.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2750981/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 看執行計劃是否正確
- DBMS_XPLAN.DISPLAY_CURSOR()看執行計劃
- MySQL 5.7 優化不能只看執行計劃MySql優化
- 執行計劃-1：獲取執行計劃
- 使用hint改變執行計劃
- oracle執行計劃的使用(EXPLAIN)OracleAI
- 使用sql profile固定執行計劃SQL
- 執行計劃
- 【顯示執行計劃】在普通使用者下使用set autot 檢視執行計劃
- 報錯的語句也可以產生執行計劃
- Oracle執行計劃Explain Plan 如何使用OracleAI
- 使用PL/SQL檢視執行計劃SQL
- 使用OUTLINE固定執行計劃
- MySQL Cases-執行計劃向Oracle看齊（FORMAT=tree）MySqlOracleORM
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 使用SPM和STA進行固定執行計劃
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- sql 執行計劃SQL
- ORACLE執行計劃Oracle
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 使用EXPLAIN PLAN來檢視執行計劃AI
- 使用Oracle Hint提示來更改執行計劃Oracle
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- mysql explain 執行計劃MySqlAI
- MySQL執行計劃解析MySql
- 分割槽 執行計劃
- 執行計劃繫結
- SQL Server執行計劃SQLServer