Test the effect of ARRAYSIZE of SQLPLUS on Performance

Arraysize defines the number of rows returned to the SQLPLUS client at a time. After scanning the arraysize row, stop scanning, return data, and continue scanning.

This process is the SQL * Net roundtrips to/from client in the statistics.

Because arraysize is 15 rows by default, there is a problem, because the number of records in a block is generally more than 15 rows, So if you scan by 15 rows, therefore, each scan requires one more data block, and one data block may be scanned multiple times.

Duplicate scans increase consistent gets and physical reads. Increasing physical reads makes it easy to understand that the more scans, the more likely physical reads will be.

Consistent gets, which is the number of reads from undo. To ensure data consistency, Oracle modifies the data block after a long query and does not submit the data block, oracle builds CR Blocks Based on Undo. This CR block can be understood as the status of data blocks at a certain time. In this way, the queried data is consistent.

The more blocks that need to be scanned repeatedly, the more CR blocks that need to be built. The more opportunities to read Undo, the more consistent gets will be.

If the data is interrupted every time it is transmitted to the client, the data will be scanned again, which increases the logical read. Therefore, adjusting the arraysize can reduce the number of data transfers and reduce the number of logical reads.

Therefore, if the arraysize parameter is too low, it will affect the number of times physical reads, consistent gets, and SQL * Net roundtrips to/from client. --- This section is taken from DAVE's blog.

Experiment conclusion: The experiment table is created by dba_objects. By setting the arraysize to 1, 15, and 200, you can use the final summary table to set the arraysize to 200 for better query performance.

Specifically, the execution time of SQL statements is greatly reduced, and the total number of bytes received from the client through Oracle Net is greatly reduced, the total number of bytes sent by SQL * Net and received from the client is greatly reduced-the scale-down ratio is close to the arraysize ratio.

Therefore, when using the SQLPLUS client to query big data and SPOOL output, you can consider setting the arraysize to a larger value to improve performance. If this parameter is set permanently, set arraysize 15 can be written to $ ORACLE_HOME/sqlplus/admin/glogin. SQL.

1. Use the ARRAYSIZE of SQLPLUS for testing by default 15.

BYS @ bys1> create tabele test2 as select * from dba_objects;
BYS @ bys1> alter system flush shared_pool;
System altered.
BYS @ bys1> alter system flush buffer_cache;
System altered.

BYS @ bys1> set arraysize 15 because I have changed it, I manually change it to the default 15

This setting is only useful in the current SESSION. If you need to set it permanently, you can write set arraysize 15 in $ ORACLE_HOME/sqlplus/admin/glogin. SQL.

BYS @ bys1> set autotrace traceonly stat
BYS @ bys1> select * from test2;

72465 rows selected.

Elapsed: 00:00:02. 12

Statistics
----------------------------------------------------------
606 recursive cballs
0 db block gets
5882 consistent gets
1052 physical reads
0 redo size
8036433 bytes sent via SQL * Net to client
53549 bytes encoded ed via SQL * Net from client
4832 SQL * Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
72465 rows processed

Summary: execution time: 02.12 seconds, 606 recursive calls, 5882 consistent reads, 1052 physical reads, 8036433 bytes sent, 53549 bytes received, and 4832 round trips

For more details, please continue to read the highlights on the next page:

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

Oracle arraysize and fetch size parameters and performance optimization

1 2 Next Page