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

Editor to share with you how to get the method in the database, check the execution order, statistical information, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article. Let's learn about it!

Methods commonly used by individuals to obtain execution plans

1.select * from table (dbms_xplan.display_cursor ('vSecretsql.sqldistributivsql.CHILDLCNUMBERreachable advanced.');-- only execute the plan

2.set autotrace traceonly exp-only execution plan

The difference between 1 and 2 is that when SQL has bound variables, 1 is more accurate (after all, sql executes the execution plan first and then obtains the execution plan), 2 may not be accurate, and in other cases the execution plan obtained by the two is basically the same.

Summary of implementation plan indicators and statistical information indicators:

If 1.sql executes many times without changing the arraysize, the consistent gets will not change each time.

When 2.sql changes arraysize once, the consistent gets changes each time, but the CPU_COST recorded in v$sql_plan does not change, indicating that v$sql_plan.CPU_COST is generated by the SQL corresponding to CHILD_NUMBER when it is executed for the first time.

3. Cost (% CPU) in the execution plan equals v$sql_plan.COST instead of v$sql_plan.CPU_COST

4. When it is only a select (not select for update), the db block gets is 0

5. Logical reads (consistent gets+db block gets) are related to the size of data rows processed in each batch. The larger the rows of data processed in each batch, the smaller the logical read. So one way to reduce logical reads is to add arraysize.

6. The Cost (% CPU) in the execution plan is only related to physics, so there is no way to modify arraysize to reduce physical reading, that is, Cost (% CPU) remains unchanged.

The explanation of each indicator of statistical information can be found in the official document on Statistics Descriptions.

Recursive calls:Number of recursive calls generated at both the user and system level

Db block gets:Number of times a CURRENT block was requested (logical reads generated by DML).

Consistent gets:Number of times a consistent read was requested for a block (logical reads generated by select).

Physical reads:Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

Redo size:Total amount of redo generated in bytes

Bytes sent via SQL*Net to client:Total number of bytes sent to the client from the foreground processes (can be understood as the number of bytes of the query result of sql)

Bytes received via SQL*Net from client:Total number of bytes received from the client over Oracle Net Services

SQL*Net roundtrips to/from client:Total number of Oracle Net Services messages sent to and received from the client (the number of interactions between the client and the database server, related to arraysize (number of row prefetches) and rows processed, equal to rows processed/arraysize)

Sorts (memory): Number of sort operations that were performed completely in memory and did not require any disk writes

Sorts (disk): Number of sort operations that required at least one disk write

Rows processed:Total number of rows that the parsed SQL statement returns (the number of rows of query results that can be understood as sql)

Look at the way to implement the plan:

1. From top to bottom, the first step without child nodes should be executed first.

2. For sibling nodes, apply the first point, that is, the upper nodes are executed first.

3. After all the sibling nodes have finished execution, execute the parent node.

The principle of execution order is to find the first juxtaposed column from top to bottom, from top to bottom, from right to left.

From top to bottom: there are generally multiple nodes in the execution plan, with nodes of the same level (or juxtaposition), priority execution at the top and post execution at the bottom.

From right to left: there are also multiple child nodes under a node, starting with the rightmost child node.

The order of execution plan is 3, 2, 5, 4, 1, 0

The order of execution plan is 3, 5, 4, 2, 6, 1, 0

The order of execution plan is 4, 3, 6, 5, 2, 8, 7, 1, 0

The order of execution plan is 3, 5, 6, 4, 1, 0

Four ways to get the execution plan

1. Using explain plan for and DBMS_XPLAN.DISPLAY

SQL > explain plan for select * from te123

SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)

Or

SQL > SELECT id,operation,options,object_name,position from plan_table

two。 Use autotrace

Set autotrace on

1. Execute sql

2. Display the result set of sql

3. Show the execution plan

4. Display the statistics after the execution of sql

-

Set autotrace traceonly

Or

Set autotrace traceonly exp stat

1. Execute sql

2. Show the execution plan

3. Display the statistics after the execution of sql

-

Set autotrace traceonly exp | explain

1. Display the execution plan (select will not execute sql, that is, V$SQL.EXECUTIONS will not increase, but insert, update, delete will execute SQL, that is, V$SQL.EXECUTIONS will increase)

-

Set autotrace traceonly stat | statistics

1. Execute sql

2. Display the statistics after the execution of sql

3. Use dbms_xplan.display_cursor

Select * from table (dbms_xplan.display_cursor ('vpromosql.sqldistributivce. CHILDPE number reign advanced.')

In the description of the function display_cursor in the official document, there is no advanced parameter value, only BASIC, TYPICAL and ALL, but in practice, it is found that the parameter value of advanced shows more content than these parameter values.

4. Use v$sql_plan

V$sql_plan can also query the SQL execution plan of an object.

SQL > select id,operation,options,object_name,object_owner from v$sql_plan where object_name='TABLE_NAME'

The following experiments show that increasing arraysize can reduce logical reads (in which the Elapsed time is no longer reduced when it reaches a certain stage), but physical reads do not decrease, that is, Cost (% CPU) remains unchanged.

SQL > set timing on

SQL > set arraysize 15

SQL > select * from T1

1024000 rows selected.

Elapsed: 00:00:11.22

Execution Plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 512K | 41m | 1785 (1) | 00:00:22 |

| | 1 | TABLE ACCESS FULL | T1 | 512K | 41m | 1785 (1) | 00:00:22 |

Statistics

0 recursive calls

0 db block gets

93297 consistent gets

12933 physical reads

0 redo size

96936855 bytes sent via SQL*Net to client

751345 bytes received via SQL*Net from client

68268 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1024000 rows processed

SQL > set arraysize 1500

SQL > select * from T1

1024000 rows selected.

Elapsed: 00:00:07.43

Execution Plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 512K | 41m | 1785 (1) | 00:00:22 |

| | 1 | TABLE ACCESS FULL | T1 | 512K | 41m | 1785 (1) | 00:00:22 |

Statistics

0 recursive calls

0 db block gets

26518 consistent gets

12933 physical reads

0 redo size

88150935 bytes sent via SQL*Net to client

7921 bytes received via SQL*Net from client

684 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1024000 rows processed

SQL > set arraysize 5000

SQL > select * from T1

1024000 rows selected.

Elapsed: 00:00:07.65

Execution Plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 512K | 41m | 1785 (1) | 00:00:22 |

| | 1 | TABLE ACCESS FULL | T1 | 512K | 41m | 1785 (1) | 00:00:22 |

Statistics

0 recursive calls

0 db block gets

26043 consistent gets

12933 physical reads

0 redo size

88088795 bytes sent via SQL*Net to client

2663 bytes received via SQL*Net from client

206 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1024000 rows processed

SQL >

The following proves that the Cost (% CPU) in the execution plan is equal to v$sql_plan.COST

SQL > set autotrace traceonly

SQL > select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID select sql_id,OPERATION,OPTIONS,OBJECT_NAME,OPTIMIZER,ID,COST,CARDINALITY,BYTES,CPU_COST,IO_COST from v$sql_plan where sql_id='cndu66r2wpa63' and CHILD_NUMBER=0

SQL_ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER ID COST CARDINALITY BYTES CPU_COST IO_COST

Cndu66r2wpa63 SELECT STATEMENT FIRST_ROWS 0 5

Cndu66r2wpa63 TABLE ACCESS BY INDEX ROWID RECORDLIST 1 5 10 1600 42307 5

Cndu66r2wpa63 INDEX RANGE SCAN IX_RECORDLIST_MAINID 2 4 5778 30486 4

As follows, if sql executes many times without changing the arraysize, the consistent gets will not change each time.

SQL > set timing on

SQL > set linesize 200

SQL > set autotrace traceonly

SQL > select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID

SQL >

SQL > select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID

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.