Shulou(Shulou.com)06/01 Report--

ORACLE sql trace

AUTOTRACE command

1 SET AUTOTRACE OFF this is the default value, that is, turn off Autotrace

2 SET AUTOTRACE ON produces result sets and explains plans and lists statistics

3 SET AUTOTRACE ON EXPLAIN displays result sets and explains that plans do not display statistics

4 SETAUTOTRACE TRACEONLY displays explanatory plans and statistics, although you execute this statement, you will not see the result set

5 SET AUTOTRACE TRACEONLY STATISTICS displays statistics only

SQL > set autotrace on

SQL > select * from dual

2

D

-

X

Execution Plan

Plan hash value: 272002086

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

| | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0) | 00:00:01 |

Statistics

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

407 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Meaning of each statistical information

The number of block read by db block gets from buffer cache

The number of block of undo data read by consistent gets from buffer cache

The number of block read from disk by physical reads

The size of the redo generated by redo size DML

The amount of sorting performed by sorts (memory) in memory

The amount of sorting performed by sorts (disk) on disk

Db block gets is to obtain the buffer cache under current mode.

Consistent gets takes the buffer cache under consistent mode, where consistent read needs undo block to construct consistent read blocks.

The official documents are as follows

Current mode

A current mode get, also called a db block get, is a retrieval of a block as it currently appears in the buffer cache.

Current mode, also known as db block get, is the search for the current block in buffer cache.

For example, if an uncommitted transaction has updated two rows in a block, then a current mode get retrieves the block with these uncommitted rows.

For example, if an uncommitted thing modifies two lines of a block, the current mode retrieves the block, including two uncommitted lines

The database uses db block gets most frequently during modification statements, which must update only the current version of the block.

Databases usually use db block get when using modify statements, which must modify the version of the current block.

Consistent mode

A consistent read get is a retrieval of a read-consistent version of a block. This retrieval may use undo data.

Consistent read reads a consistent version of a block, which usually uses a undo block.

For example, if an uncommitted transaction has updated two rows in a block, and if a query in a separate session requests the block

For example, an uncommitted transaction modifies two lines of a block, and another session queries the block

Then the database uses undo data to create a read-consistent version of this block (called a consistent read clone) that does not include the uncommitted updates.

The database uses undo to create a consistent read version of the block (called a consistent read block clone) that does not contain uncommitted update.

Typically, a query retrieves blocks in consistent mode.

Typically, queries are retrieved in consistent read mode

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.