Shulou(Shulou.com)05/31 Report--

This article is to share with you about the use of plan_hash_value in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Plan_hash_value

The main way to determine whether the access path of sql has changed is whether the value of v$sql.plan_hash_value has changed. If different sql statements contain the same

Experiment:

-create table dept

SQL > create table dept as select * from scott.dept

Table created.

-execute 2 sql queries on the dept table

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

-query the address,hash_value,child_number,plan_hash_value,sql_text of dept's sql through v$sql

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

NVL (SUM (C)) NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0)

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

NVL (SUM (C)) NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0)

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

-query the execution plan through v$sql_plan

SQL > SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address = '0000008B589B40' 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 = '0000008B626668' and HASH_VALUE='3145184715'

OPERATION OPTIONS OBJECT_NAME COST

-

SELECT STATEMENT 2

SORT AGGREGATE

TABLE ACCESS FULL DEPT 2

The query execution plan is exactly the same.

-- modify the table dept to add an index

SQL > create index idx_dept_deptno on dept (deptno)

Index created.

-check again

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

NVL (SUM (C)) NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0)

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 00 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

NVL (SUM (C)) NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0)

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

-execute the same 2 sql again

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

-query address,hash_value,child_number,plan_hash_value,sql_text under v$sql

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

NVL (SUM (C)) NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0)

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 00 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

NVL (SUM (C)) NO_PARALLEL_INDEX (SAMPLESUB) NO_SQL_TUNE * / NVL (SUM (C1), 0)

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 SELECT operation, options, object_name, cost FROM v$sql_plan WHERE address = '0000008B589B40' 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 = '0000008B626668' 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 = '000000613ACE30' 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

Finally, it is found that the execution plan has changed, so we can know whether the execution plan of the sql statement has changed according to whether the plan_hash_ value value has changed.

From the plan_hash_ value of the v$sql view, you can easily know whether the execution plan of the sql statement has changed.

Usually what we do is to take two snapshots of sql _

Thank you for reading! This is the end of the article on "what is the use of plan_hash_value in the database?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.