Explanation of autotrace traceonly stat statistics in SQLPLUS

BYS @ bys1> set arraysize 200
BYS @ bys1> set autotrace traceonly stat
BYS @ bys1> select * from test2 order by 3;
72465 rows selected.

Elapsed: 00:00:00. 57

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1037 consistent gets
0 physical reads
0 redo size
2909118 bytes sent via SQL * Net to client
4401 bytes encoded ed via SQL * Net from client
364 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
72465 rows processed

Recursive cballs	Recursive call: recursive call at the user and system level. Oracle databases maintain tables for internal processing. When the Oracle database needs to change these tables, it generates an internal SQL statement internally, which in turn produces a recursive call.
Db block gets	The number of read blocks in the current read mode, which is relatively small and special and non-consistent. For example, for data dictionary data acquisition, in DML, the current read mode is used to change or delete data. Number of reads through update/delete/select for update
Consistent gets	Consistent read: The number of reads in the consistent read mode, including the number of reads from the rollback segment.
Physical reads	The total number of data blocks read from the disk. This number is equal to the value of "physical direct reading", plus all reads to the buffer cache.
Redo size	Total redo bytes
Bytes sent via SQL * Net to client	Number of bytes sent to the client through SQL * Net
Bytes encoded ed via SQL * Net from client	Number of bytes received from the client through SQL * Net
SQL * Net roundtrips to/from client	Total number of SQL * Net send and receive round trips from the client
Sorts (memory)	The number of sorting operations is completely in memory, and no disk write is required.
Sorts (disk)	Number of sorting operations, which must be written to at least one disk
Rows processed	Number of rows processed during the operation

 

1. DB Block Gets (number of blocks in the current request) The number of blocks in the current mode is the number of blocks to be extracted in the operation, rather than the number of blocks generated in the case of consistent read.

Under normal circumstances, the block extracted by a query is the data block that exists at the start of the query. The current block is the data block that exists at this time, instead of the number of data blocks before or after this time point.

2. Consistent Gets (the data block required for Consistent read of data requests in the Buffer of the rollback segment) the concept here is how many chunks need to be processed in the consistent read state when processing this operation. The main reason for these chunks is that during your query, other sessions operate on data blocks and modify the blocks to be queried. However, because our queries are called before these modifications, therefore, you need to query the pre-image of the data block in the rollback segment to ensure data consistency. In this way, consistent reads are generated.

3. Physical Reads (Physical read) is the number of data blocks read from the disk. The main reason is: 1. These blocks do not exist in the database cache. 2. Full table scan. 3. Disk sorting.

The relationship between the three can be roughly summarized as: Logical read refers to the number of data blocks read from the memory of Oracle. Generally, logic io (logical reads) = 'consistent gets' + 'db block gets'

If the required data block cannot be found in the memory, it needs to be obtained from the disk, so 'phsical reads' is generated '.

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)