数据库中plan_hash_value有什么用
这篇文章给大家分享的是有关数据库中plan_hash_value有什么用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。
plan_hash_value
判断sql的访问路径是否改变的主要方法是:v$sql.plan_hash_value的值是否改变。如果不同的sql语句含有相同的
实验：
---创建表dept
SQL> create table dept as select * from scott.dept;
Table created.
---执行2条sql查询dept表
SQL> select deptno,dname from dept where deptno=10;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
SQL> select deptno,dname from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
---通过v$sql查询关于dept的sql的address,hash_value,child_number,plan_hash_value,sql_text
col SQL_TEXT for a100
select address,hash_value,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%DEPT%';
ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
---------------- ---------- ------------ --------------- ----------------------------------------------------------------------------------------------------
000000008B589B40 3749466620 0 315352865 SELECT NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT 1 AS C1, 1 AS C
2 FROM "SYS"."DEPT" "DEPT") SAMPLESUB
0000000061211A40 2958346034 0 2657262937 select ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER, TIMESTAM
P, OPERATION, OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, O
BJECT_NAME, OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER,
ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TA
G, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST
, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOC
K_NAME, REMARKS, OTHER_XML from GV
$SQL_PLAN where inst_id = USERENV('Insta
nce')
000000008B5D3908 3410315986 0 903671040 select address,hash_value,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%DEP
T%'
000000008B626668 3145184715 0 315352865 SELECT NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT 1 AS C1, CASE WHEN "DEPT"."DEPTNO"=10 THEN 1 ELSE 0 END AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLES
UB
---通过v$sql_plan查询执行计划
SQL> SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address ='000000008B589B40' and HASH_VALUE='3749466620';
OPERATION OPTIONS OBJECT_NAME COST
------------------------------------------------------------ ------------------------------------------------------------
SELECT STATEMENT 2
SORT AGGREGATE
TABLE ACCESS FULL DEPT 2
SQL>
SQL> SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address ='000000008B626668' and HASH_VALUE='3145184715';
OPERATION OPTIONS OBJECT_NAME COST
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----------
SELECT STATEMENT 2
SORT AGGREGATE
TABLE ACCESS FULL DEPT 2
查询的执行计划完全一样
--对表dept做修改，增加索引
SQL> create index idx_dept_deptno on dept(deptno);
Index created.
---再次查看
SQL> select address,hash_value,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%DEPT%';
ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
---------------- ---------- ------------ --------------- ----------------------------------------------------------------------------------------------------
000000008B589B40 3749466620 0 315352865 SELECT NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT 1 AS C1, 1 AS C
2 FROM "SYS"."DEPT" "DEPT") SAMPLESUB
0000000061211A40 2958346034 0 2657262937 select ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER, TIMESTAM
P, OPERATION, OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, O
BJECT_NAME, OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER,
ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TA
G, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST
, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOC
K_NAME, REMARKS, OTHER_XML from GV
$SQL_PLAN where inst_id = USERENV('Insta
nce')
000000008B5D3908 3410315986 0 903671040 select address,hash_value,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%DEP
T%'
000000008E0A74E0 1470990285 0 0 LOCK TABLE "DEPT" IN SHARE MODE NOWAIT
000000008B626668 3145184715 0 315352865 SELECT NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT 1 AS C1, CASE WHEN "DEPT"."DEPTNO"=10 THEN 1 ELSE 0 END AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLES
UB
----再次执行上述相同的2条sql
SQL> select deptno,dname from dept where deptno=10;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
SQL> select deptno,dname from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
---查询v$sql下的address,hash_value,child_number,plan_hash_value,sql_text
SQL> select address,hash_value,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%DEPT%';
ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
---------------- ---------- ------------ --------------- ----------------------------------------------------------------------------------------------------
000000008B589B40 3749466620 0 315352865 SELECT NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT 1 AS C1, 1 AS C
2 FROM "SYS"."DEPT" "DEPT") SAMPLESUB
0000000061211A40 2958346034 0 2657262937 select ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER, TIMESTAM
P, OPERATION, OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, O
BJECT_NAME, OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER,
ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TA
G, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST
, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOC
K_NAME, REMARKS, OTHER_XML from GV
$SQL_PLAN where inst_id = USERENV('Insta
nce')
000000008B5D3908 3410315986 0 903671040 select address,hash_value,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%DEP
T%'
000000008E0A74E0 1470990285 0 0 LOCK TABLE "DEPT" IN SHARE MODE NOWAIT
000000008B626668 3145184715 0 315352865 SELECT NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT 1 AS C1, CASE WHEN "DEPT"."DEPTNO"=10 THEN 1 ELSE 0 END AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLES
UB
00000000613ACE30 1756886759 0 2570986044 SELECT NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0
) FROM (SELECT 1
AS C1, 1 AS C2, 1 AS C3 FROM "SYS"."DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=10 AND ROWNUM <= 2500) SAMPL
ESUB
6 rows selected.
---再次查看执行计划
SQL> SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address ='000000008B589B40' and HASH_VALUE='3749466620';
OPERATION OPTIONS OBJECT_NAME COST
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----------
SELECT STATEMENT 2
SORT AGGREGATE
TABLE ACCESS FULL DEPT 2
SQL> SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address ='000000008B626668' and HASH_VALUE='3145184715';
OPERATION OPTIONS OBJECT_NAME COST
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----------
SELECT STATEMENT 2
SORT AGGREGATE
TABLE ACCESS FULL DEPT 2
SQL> SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address ='00000000613ACE30' and HASH_VALUE='1756886759';
OPERATION OPTIONS OBJECT_NAME COST
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----------
SELECT STATEMENT 1
SORT AGGREGATE
VIEW 1
COUNT STOPKEY
INDEX RANGE SCAN IDX_DEPT_DEPTNO 1
最终发现执行计划已经改变，所以可以根据plan_hash_value值是否变化可知该sql语句的执行计划是否改变
通过v$sql视图的plan_hash_value值可以很方便的知道，该sql语句的执行计划是否改变，
通常我们的做法是分别将v$sql,v$sql_hash做两份快照，隔段时间后再做段快照，然后将其进行比较，查看是否有sql的执行计划已经改变。
感谢各位的阅读！关于“数据库中plan_hash_value有什么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/52.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
