ITPub博客
首页 博客 专栏 学院 社区 技术栈
发博

首页 > 数据库 > Oracle > [20210119]看执行计划可以使用hash_value.txt

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

原创 Oracle 作者：lfree 时间：2021-01-19 11:23:07 0 删除 编辑

[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/，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20210115]sqlnet.ora设置sqlnet.expire_time断开时oracle如何探测.txt
下一篇： [20210119]sqlplus 12c LOBPREFETCH.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    2855
  • 访问量
    6643914

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员