Oracle executes plans and gets data rows

An execution plan is a series of commands that tell Oracle what access method to use for each Table object and what join and join order to join multiple tables together. Each step in the execution plan produces a row source and then joins with another row source until all objects are accessed and joined.

The query plan in Oracle will complete the steps of parsing, binding, executing, and extracting.

Each time Oracle accesses a block of data, Oracle extracts the data rows from the block and returns them to the client in a single loop. The number of rows returned at a time is a configurable value called the column size. The column size is the number of possible rows in a network loop that can be transmitted at one time. The setting of the column size can be changed programmatically. In Sql*plus, the default size is 15, and you can change the size of the array by using the Set arraysize n command. The default value for JDBC is 10. The following shows how the number of logical reads of the same query is reduced by changing the column size. The logical reads are marked with the consistent gets:

Set ArraySize 15

Set Autotrace traceonly Statistics

SELECT * from Order_items;

Set ArraySize 45

The effect is as follows: