Sqlplus downlink prefetch features

Generally, each data block accessed by the database engine generates at least one logical read. Row prefetch is closely related to logical read. Row prefetch refers to when the client obtains data from the database
You can use a single row or multiple rows to return data. When multiple rows are used, multiple records are prefetch and stored in the client memory to avoid multiple subsequent requests to the data.
Various overhead (LIO, Pio, net Io ). Generally, the larger the number of prefetch rows, the smaller the overhead. When the threshold value is reached, the overhead will not change much.
I. Demonstration

1. Create a demo table <br/> Scott @ cnmmbo> select * from V $ version where rownum <2; </P> <p> banner <br/> statement <br/> Oracle Database 10g Release 10.2.0.3.0-64bit production <br/> Scott @ cnmmbo> Create Table T as select * from dba_objects; --> Create a demo table </P> <p> Scott @ cnmmbo> analyze table t compute statistics; --> Update Statistics </P> <p> Scott @ cnmmbo> exec show_space ('T'); <br/> UN Formatted blocks ..................... 0 <br/> fs1 blocks (0-25 )...................... 0 <br/> fs2 blocks (25-50 )..................... 0 <br/> fs3 blocks (50-75 )..................... 0 <br/> fs4 blocks (75-100 )..................... 0 <br/> full blocks ............................ 690 <br/> total blocks ............................ 768 --> the total number of blocks in the table segment is 768 <br/> Total Bytes ........................ ..... 6,291, 4 56 <br/> total Mbytes ............................ 6 <br/> unused blocks ........................... 58 --> the number of unused blocks is 58 <br/> unused bytes .......................... .. 475,136 <br/> last used ext fileid .................... 11 <br/> last used ext blockid ................... 39,561 <br/> last used block ......................... 70 </P> <p> PL/SQL procedure successfully completed. </P> <p> 2. Relationship between arraysize and consistent gets <Br/> Scott @ cnmmbo> show arraysize --> the size of arraysize defaults to 15 <br/> arraysize 15 <br/> Scott @ cnmmbo> set arraysize 2 --> modify arraysize the value is 2 <br/> Scott @ cnmmbo> set autotrace traceonly Stat; --> enable autotrace <br/> Scott @ cnmmbo> select * from T; </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 25390 consistent gets --> consiste NT gets is 25390 <br/> 0 physical reads <br/> 0 redo size <br/> 6596152 bytes sent via SQL * Net to client <br/> 275844 bytes encoded ed via SQL * Net from client <br/> 25034 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> Scott @ cnmmbo> set arraysize 15 --> modify the value of arraysize to 15 <br/> Scott @ cnmmbo> select * from t; </P> <p> Statistics <br/> -------------- Tables <br/> 0 recursive cballs <br/> 0 dB block gets <br/> 3992 consistent gets --> at this time, the consistent gets is 3992 <br/> 0 physical reads <br /> 0 redo size <br/> 2625967 bytes sent via SQL * Net to client <br/> 37199 bytes encoded ed via SQL * Net from client <br/> 3339 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> -- as shown in the preceding demonstration, different array size values are set to scan the entire table (no index exists on the table) different consistent gets are generated. <br/> -- when the arraysize value increases, the value of consistent gets is reduced. <br/> -- where does the consistent gets 3992 come from? </P> <p> Scott @ cnmmbo> set autotrace off; <br/> Scott @ cnmmbo> select 50066/15 + 768-58 from dual; --> number_of_rows/arraysize + total_blocks_read </P> <p> 50066/15 + 768-58 <br/> ------------- <br/> 4047.73333 </P> <p> -- calculated from the preceding we can see that if 4047 is higher than 3992, therefore, the consistent gets is close to the calculated value </P> <p> Scott @ cnmmbo> set autotrace traceonly; <br/> Scott @ cnmmbo> select count (*) from t; --> aggregate computing </P> <p> execution plan <br/> ------------------- ------------------------------------- <Br/> plan hash value: 2966233522 </P> <p> average <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ----------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 158 (1) | 00:00:02 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | table access full | T | 50066 | 158 (1) | 00:00:02 | <br/> Statistics </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 695 consistent gets --> the consistent gets is 695 <br/> 0 physical reads <br/> 0 redo size <br/> 517 bytes sent via SQL * Net to client <br /> 492 bytes encoded ed via S QL * Net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> -- the whole table scan is used. Why is the consistent read only 695? It is close to the number of blocks in the table. <br/> -- consider the number of blocks in the table segment: 768-58 = 710 <br/> -- note that aggregation is only a single call, and row prefetch is used in the SQL engine, each block generates a logical read and extracts all the rows from it </P> <p> Scott @ cnmmbo> set autotrace off; <br/> Scott @ cnmmbo> select num_rows, <br/> 2 blocks blks, <br/> 3 empty_blocks em_blks, <br/> 4 avg_space, <br/> 5 chain_cnt, <br/> 6 avg_row_len, <br/> 7 round (num_rows/blocks) as avg_rows_per_block, <br/> 8 last_analyzed lst_anly, <br/> 9 stale_stats <br/> 10 from dba_tab_statistics <br/> 11 where table_name = 'T' <br/> 12 and owner = 'Scott '; </P> <p> num_rows blks em_blks avg_space chain_cnt contains zookeeper sta <br/> ---------- ----------- --------- --- <br/> 50066 710 58 860 0 97 71 12-Jan-12 no </P> <p> Scott @ cnmmbo> select 71*710 from dual; </P> <p> 71*710 <br/> ---------- --> the value 50410 is close to 50066 <br/> 50410 </P> <p> 3. arraysize and total table scan <br/> Scott @ cnmmbo> set autotrace traceonly Stat; <br/> Scott @ cnmmbo> set arraysize 100 --> adjust the arraysize value to 100 <br/> Scott @ cnmmbo> select * from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 1185 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 2106796 bytes sent via SQL * Net to client <br/> 5992 bytes encoded ed via SQL * Net from client <br/> 502 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> Scott @ cnmmbo> set arraysize 200 --> adjust arraysize to 200 <br/> Scott @ cnmmbo> select * from t; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 940 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 2061046 bytes sent via SQL * Net to client <br/> 3242 bytes encoded ed via SQL * Net from client <br/> 252 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> Scott @ cnmmbo> set arraysize 2000 --> adjust the arraysize to 2000, the access method is full table scan <br/> Scott @ cnmmbo> select * from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 721 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 2019871 bytes sent via SQL * Net to client <br/> 767 bytes encoded ed via SQL * Net from client <br/> 27 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> Scott @ cnmmbo> set arraysize 4000 --> adjust the arraysize to 4000, the access method is full table scan <br/> Scott @ cnmmbo> select * from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 708 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 2017492 bytes sent via SQL * Net to client <br/> 624 bytes encoded ed via SQL * Net from client <br/> 14 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> Scott @ cnmmbo> set arraysize 20000 --> adjust the arraysize beyond the range <br/> SP2-0267: arraysize option 20000 out of range (1 through 5000) <br/> Scott @ cnmmbo> set arraysize 5000 --> adjust arraysize to the maximum value of 5000, the access method is full table scan <br/> Scott @ cnmmbo> select * from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 706 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 2017126 bytes sent via SQL * Net to client <br/> 602 bytes encoded ed via SQL * Net from client <br/> 12 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50066 rows processed </P> <p> -- from the preceding observations, we can see that when the arraysize not only increases, logical reads are reduced accordingly. When the arraysize reaches a certain value, the magnitude of logical reads decreases little </P> <p> Scott @ cnmmbo> set autotrace traceonly; <br/> Scott @ cnmmbo> show arraysize <br/> arraysize 1 <br/> Scott @ cnmmbo> select * from EMP; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 3956160932 </P> <p> -------------------------------------------------------------------------- <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> -------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 14 | 518 | 3 (0) | 00:00:01 | <br/> | 1 | table access full | EMP | 14 | 518 | 3 (0) | 00:00:01 | <br/> Statistics </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 14 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 2633 bytes sent via SQL * Net to client <br/> 558 bytes encoded ed via SQL * net from client <br/> 8 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 14 rows processed <br/> -- from the test above, we can see that when arraysize is 1 during full table scan, A record in the table will generate a consistent gets </P> <p> 3. arraysize and index scan <br/> Scott @ cnmmbo> alter table t add constraint t_pk primary key (object_id ); --> Add an index to a table </P> <p> Scott @ cnmmbo> analyze table t compute statistics; --> Update Statistics </P> <p> Scott @ cnmmbo> set autotrace traceonly; <br/> Scott @ cnmmbo> select count (*) from T; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 454320086 </P> <p> ---------------------------------------------------------------------- <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ---------------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 24 (0) | 00:00:01 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | index fast full scan | t_pk | 50066 | 24 (0) | 00:00:01 | <br/> Statistics </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 111 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 517 bytes sent via SQL * Net to client <br/> 492 bytes encoded ed via SQL * net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> -- according to the execution plan and buffers, the full index scan is performed, the size of consistent gets is 111. that is, the optimizer obtains the total number of rows based on index statistics, rather than based on tables. </P> <p> Scott @ cnmmbo> exec show_space ('t_pk', 'Scott ', 'index '); --> View the index segment usage <br/> unformatted blocks ..................... 0 <br/> fs1 blocks (0-25 )...................... 0 <br/> fs2 blocks (25-50 )..................... 1 <br/> fs3 blocks (50-75 )..................... 0 <br/> fs4 blocks (75-100 )..................... 0 <br/> full blocks ............................ 104 <br/> total blocks ............................ 120 --> the total number of index segments is 120 <br/> Total Bytes ............................. 983,040 <br/> total Mbytes ............................ 0 <br/> unused blocks ........................... 5 --> the number of idle parts is 5 <br/> unused bytes ............................ 40,960 <br/> last used ext fileid .................... 11 <br/> last used ext blockid ................... 39,745 <br/> last used block ......................... 3 </P> <p> PL/SQL procedure successfully completed. </P> <p> Scott @ cnmmbo> show arraysize <br/> arraysize 5000 <br/> Scott @ cnmmbo> select count (*) from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 111 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 517 bytes sent via SQL * Net to client <br/> 492 bytes encoded ed via SQL * Net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> Scott @ cnmmbo> set arraysize 2000 <br/> Scott @ cnmmbo> select count (*) from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 111 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 517 bytes sent via SQL * Net to client <br/> 492 bytes encoded ed via SQL * Net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> Scott @ cnmmbo> set arraysize 15 <br/> Scott @ cnmmbo> select count (*) from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 111 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 517 bytes sent via SQL * Net to client <br/> 492 bytes encoded ed via SQL * Net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> Scott @ cnmmbo> set arraysize 1 <br/> Scott @ cnmmbo> select count (*) from T; </P> <p> Statistics <br/> 0 recursive CILS <br/> 0 dB block gets <br/> 111 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 517 bytes sent via SQL * Net to client <br/> 492 bytes encoded ed via SQL * Net from client <br/> 2 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 1 rows processed </P> <p> -- from the test above, we can see that the aggregation after index scanning is irrelevant to arraysize.

Ii. Analysis
Assume that the query is published using select * from T. In this case, each data block in the table is 16 rows and the arraysize is set to 15 (default ),
Then
The first fetch reads the first 15 rows and 1st consistent gets is generated.
The second fetch is used to read the first row. At this time, 2nd consistent gets is generated, and the second row is read for 14 rows. At this time, 3rd consistent gets is generated.
The third fetch reads the second row and the second row. At this time, 4th consistent gets is generated, and the third row and 13 rows are read. At this time, 5th consistent gets is generated.
Push accordingly
Assume that each data block in the table is 10 rows, that is, the number of rows in the data block is smaller than the arraysize (10 <15)
The first fetch reads the first 10 rows and 1st consistent gets is generated.
Read the Second Five rows and 2nd consistent gets are generated.
The second fetch reads the Second Five rows and generates 3rd consistent gets
Read the third 10 rows. At this time, 4th consistent gets is generated.
The third fetch reads the fourth 10 rows and generates 5th consistent gets
Read the fifth row of the fifth row. At this time, 6th consistent gets is generated.
Push accordingly

When arraysize is set to n (15), Oracle fetch N (15) rows from buffer at a time and then returns the data to the client. Then Oracle fetch the second time from buffer.
Returns the next N (15) rows.
It can be seen that multiple consistent gets may be generated during fetch.

When a large arraysize is set, we can see from the demonstration that SQL * Net's sent and stored Ed are also reduced. That is, the number of round-trips in the request is significantly reduced.
Iii. Summary
1. The arraysize parameter is used to control the number of rows returned to the client for prefetch. The default value is 15.
2. The logical read is consistent gets + db block gets. to simplify the description, the logical read
3. When the row pre-value is set to 1, a logical read is generated for each row returned for a full table scan. If the pre-value of the row is greater than the number of rows in a single block storage, the logical read is close to the number of blocks.
4. The value of logical read decreases with the increase of arraysize. When the critical value is reached (that is, the value slightly exceeds the row bundle of a single block storage on the table), the change trend is not significant.
5. The sent and received values of SQL * Net decrease with the increase of arraysize.
6. Logical reading is usually performed more than once. For details, refer to the analysis at the second point.
7. In the case of aggregation (no index), the SQL engine uses row prefetch internally. That is, all rows are extracted each time the SQL engine accesses a block. Its logical reads are close to the number of blocks.
8. When indexes are used, consistent gets is irrelevant to arraysize.
Iv. More references

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor