溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關(guān)數(shù)據(jù)庫(kù)中plan_hash_value有什么用的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。
plan_hash_value
判斷sql的訪問(wèn)路徑是否改變的主要方法是:v$sql.plan_hash_value的值是否改變。如果不同的sql語(yǔ)句含有相同的
實(shí)驗(yàn)：
---創(chuàng)建表dept
SQL> create table dept as select * from scott.dept;
Table created.
---執(zhí)行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
---通過(guò)v$sql查詢關(guān)于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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('paralle
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('paralle
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C
2),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
---通過(guò)v$sql_plan查詢執(zhí)行計(jì)劃
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
查詢的執(zhí)行計(jì)劃完全一樣
--對(duì)表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('paralle
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('paralle
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C
2),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
----再次執(zhí)行上述相同的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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('paralle
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C
2),0) FROM (SELECT /*+ NO_PARALLEL("DEPT") FULL("DEPT") NO_PARALLEL_INDEX("DEPT") */ 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 /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('paralle
l_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C
2),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
00000000613ACE30 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") */ 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.
---再次查看執(zhí)行計(jì)劃
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
最終發(fā)現(xiàn)執(zhí)行計(jì)劃已經(jīng)改變，所以可以根據(jù)plan_hash_value值是否變化可知該sql語(yǔ)句的執(zhí)行計(jì)劃是否改變
通過(guò)v$sql視圖的plan_hash_value值可以很方便的知道，該sql語(yǔ)句的執(zhí)行計(jì)劃是否改變，
通常我們的做法是分別將v$sql,v$sql_hash做兩份快照，隔段時(shí)間后再做段快照，然后將其進(jìn)行比較，查看是否有sql的執(zhí)行計(jì)劃已經(jīng)改變。
感謝各位的閱讀！關(guān)于“數(shù)據(jù)庫(kù)中plan_hash_value有什么用”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
