数据库中plan_hash_value有什么用

这篇文章给大家分享的是有关数据库中plan_hash_value有什么用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

plan_hash_value判断sql的访问路径是否改变的主要方法是:v$sql.plan_hash_value的值是否改变。如果不同的sql语句含有相同的实验：---创建表deptSQL> 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 ACCOUNTINGSQL> 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_textcol 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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(parallel_execution_enabled, false) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 1 AS C1, 1 AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLESUB0000000061211A40 2958346034            0      2657262937 select ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS,          CHILD_NUMBER,  TIMESTAMP, OPERATION,                                         OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME,                    OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER,ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY,           BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,             OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE,                          ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(parallel_execution_enabled, false) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 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                                                                                                                                                  2SORT                                                         AGGREGATETABLE ACCESS                                                 FULL                                                         DEPT                                    2SQL> 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                                                                                                                                                  2SORT                                                         AGGREGATETABLE 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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(parallel_execution_enabled, false) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 1 AS C1, 1 AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLESUB0000000061211A40 2958346034            0      2657262937 select ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS,          CHILD_NUMBER,  TIMESTAMP, OPERATION,                                         OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME,                    OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER,ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY,           BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,             OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE,                          ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_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  NOWAIT000000008B626668 3145184715            0       315352865 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(parallel_execution_enabled, false) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 1 AS C1, CASE WHEN "DEPT"."DEPTNO"=10 THEN 1 ELSE 0 END AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLES                                                         UB----再次执行上述相同的2条sqlSQL> select deptno,dname from dept where deptno=10;    DEPTNO DNAME---------- --------------        10 ACCOUNTINGSQL> 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_textSQL> 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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(parallel_execution_enabled, false) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 1 AS C1, 1 AS C                                                         2 FROM "SYS"."DEPT" "DEPT") SAMPLESUB0000000061211A40 2958346034            0      2657262937 select ADDRESS, HASH_VALUE, SQL_ID, PLAN_HASH_VALUE, CHILD_ADDRESS,          CHILD_NUMBER,  TIMESTAMP, OPERATION,                                         OPTIONS, OBJECT_NODE, OBJECT#, OBJECT_OWNER, OBJECT_NAME,                    OBJECT_ALIAS, OBJECT_TYPE, OPTIMIZER,ID, PARENT_ID, DEPTH, POSITION, SEARCH_COLUMNS, COST, CARDINALITY,           BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,             OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE,                          ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_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  NOWAIT000000008B626668 3145184715            0       315352865 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(parallel_execution_enabled, false) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 1 AS C1, CASE WHEN "DEPT"."DEPTNO"=10 THEN 1 ELSE 0 END AS C2 FROM "SYS"."DEPT" "DEPT") SAMPLES                                                         UB00000000613ACE30 1756886759            0      2570986044 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param(parallel_execution_enabled, false) NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") INDEX("DEPT" IDX_DEPT_DEPTNO) NO_PARALLEL_INDEX("DEPT") */ 1AS C1, 1 AS C2, 1 AS C3  FROM "SYS"."DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=10 AND ROWNUM <= 2500) SAMPL                                                         ESUB6 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                                                                                                                                                  2SORT                                                         AGGREGATETABLE ACCESS                                                 FULL                                                         DEPT                                    2SQL> SELECT operation, options, object_name, cost  FROM v$sql_plan  WHERE address =000000008B626668 and HASH_VALUE=3145184715;OPERATION                                                    OPTIONS                                                      OBJECT_NAME                          COST------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----------SELECT STATEMENT                                                                                                                                                  2SORT                                                         AGGREGATETABLE ACCESS                                                 FULL                                                         DEPT                                    2SQL> SELECT operation, options, object_name, cost  FROM v$sql_plan  WHERE address =00000000613ACE30 and HASH_VALUE=1756886759;OPERATION                                                    OPTIONS                                                      OBJECT_NAME                          COST------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----------SELECT STATEMENT                                                                                                                                                  1SORT                                                         AGGREGATEVIEW                                                                                                                                                              1COUNT                                                        STOPKEYINDEX                                                        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有什么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！