What is “Consistent Gets” ?


SQL> alter session set statistics_level = all;

Session altered.

we have one table Tab1 containing 64 rows.

SQL> select * from Tab1;

C1
———-
1
….
….
64

64 rows selected.

SQL> select * from table(dbms_xplan.display_cursor( –
> format=>’basic -rows iostats last -rows’));

PLAN_TABLE_OUTPUT
——————————————————————————–
EXPLAINED SQL STATEMENT:
————————
select * from Tab1

Plan hash value: 1245013993

—————————————————————————
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 64 |00:00:00.01 | 8 |
| 1 | TABLE ACCESS FULL| TAB1 | 1 | 64 |00:00:00.01 | 8 |
—————————————————————————

We can clearly see that there are 64 rows in table TAB1 and that Oracle performed 8 consistent gets to fulfill my request for data. The question is why 8 and not something less assuming all 64 rows are indeed stored in the same data block. Making assumptions is risky, so, lets verify the one data block assumption:

SQL> select blocks from dba_tables where table_name = ‘TAB1′;

BLOCKS
———-
1

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from Tab1;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
—————————————————
1
The above shows that according to the data dictionary all data is indeed stored in one data block, but this information could be stale. Hence the verification on the actual data itself. We now know for sure that all rows are indeed stored in one data block. But there is more to a table than data blocks and maybe that is causing the somewhat high number of consistent gets we saw earlier. Oracle needs 3 consistent gets to perform a full table scan on an empty table, so to be sure let’s verify.

SQL> create table tab2 as select * from Tab1 where 1=0; (Empty table)

Table created.

SQL> select * from tab2;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor( –
> format=>’basic -rows iostats last -rows’));

PLAN_TABLE_OUTPUT
——————————————————————————–
EXPLAINED SQL STATEMENT:
————————
select * from tab2

Plan hash value: 4224476444

—————————————————————————
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS FULL| TAB2 | 1 | 0 |00:00:00.01 | 3 |
—————————————————————————
The above confirms that Oracle indeed used three consistent gets to scan the empty table. This leaves us with 5 consistent gets to fetch the 64 rows. The question remains why 5 gets?

The arraysize

When a database client executes a query it starts fetching rows until there is nothing more to fetch. This fetching is performed in batches and the number of rows to fetch in one batch is known as the arraysize or fetchsize depending on the programming environment. The arraysize can be displayed in SQL*Plus using the ‘show arraysize’ command as shown below:

SQL> show array
arraysize 15
By default SQL*Plus uses an arraysize of 15, which means that each fetch performed requests a batch of 15 rows. Thus if we are about to fetch 64 rows we need 5 batches. This is the key to the answer why Oracle needed 8 consistent gets to scan the 64 row table, 5 are used to fetch the actual rows and the other 3 were already shown.

We can easily show the effect of arraysize by raising it from 15 to 35 using the ‘set arraysize’ command. This will reduce the number of batches from 5 to 2, resulting in a total of 5 consistent gets as demonstrated below:.

SQL> set array 35
SQL> select * from Tab1;

C1
———-
1
….
….
64

64 rows selected.

SQL> select * from table(dbms_xplan.display_cursor( –
> format=>’basic -rows iostats last -rows’));

PLAN_TABLE_OUTPUT
——————————————————————————–
EXPLAINED SQL STATEMENT:
————————
select * from Tab1

Plan hash value: 1245013993

—————————————————————————
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 64 |00:00:00.01 | 5 |
| 1 | TABLE ACCESS FULL| TAB1 | 1 | 64 |00:00:00.01 | 5 |
—————————————————————————
If we double the arraysize from 35 to 70 all rows will be fetched in just one batch resulting in the lowest possible number of consistent gets (in this example).

SQL> set array 70
SQL> select * from Tab1;

C1
———-
1
….
….
64

64 rows selected.

SQL> select * from table(dbms_xplan.display_cursor( –
> format=>’basic -rows iostats last -rows’));

PLAN_TABLE_OUTPUT
——————————————————————————–
EXPLAINED SQL STATEMENT:
————————
select * from Tab1

Plan hash value: 1245013993

—————————————————————————
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 64 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS FULL| TAB1 | 1 | 64 |00:00:00.01 | 4 |
The arraysize is quite important when it comes to performance tuning and a good discussion about it can be found in Making Friends written by fellow OakTable member Cary Millsap.

The above answered the question from my friend. Before wrapping up there are a few things to watch out for when it comes to explaining the number of consistent gets.

The aggregate trap

The first thing to be aware of is what I call the “aggregate trap”. The arraysize controls the batch size between the Oracle database server and its client, and does not affect SQL operations which are running completely inside the server. The classic example is an SQL aggregation function as shown below:

SQL> select sum(c1) from Tab1;

SUM(C1)
———-
2080

SQL> select * from table(dbms_xplan.display_cursor( –
> format=>’basic -rows iostats last -rows’));

PLAN_TABLE_OUTPUT
——————————————————————————–
EXPLAINED SQL STATEMENT:
select sum(c1) from Tab1

Plan hash value: 1342139204

—————————————————————————-
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS FULL| TAB1 | 1 | 64 |00:00:00.01 | 3 |
—————————————————————————-
Although Oracle performed a full table scan on the 64 row table it only needed 3 consistent gets. Because the SUM function ran entirely inside the Oracle server process, which required no interaction with the client, the arraysize has no impact.

The Read Consistency Trap

Another thing to watch out for is known as the Read Consistency Trap, which causes the number of consistent gets to go up whenever the Oracle database server has to apply undo records to a data block to make it read consistent. Each undo record that gets applied increases the number of consistent gets by one as illustrated below:

SQL> show array
arraysize 70
SQL> select * from Tab1;

C1
———-
1
….
….
64

64 rows selected.

SQL> select * from table(dbms_xplan.display_cursor( –
> format=>’basic -rows iostats last -rows’));

PLAN_TABLE_OUTPUT
——————————————————————————–
EXPLAINED SQL STATEMENT:

select * from Tab1

Plan hash value: 1245013993

—————————————————————————
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 64 |00:00:00.01 | 69 |
| 1 | TABLE ACCESS FULL| TAB1 | 1 | 64 |00:00:00.01 | 69 |
—————————————————————————
Suddenly there are 69 consistent gets instead of 5, as shown previously, using an arraysize of 70. The extra 64 consistent gets are caused by applying 64 undo records to make the data block read consistent. The 64 undo records were created in another session by updating each row in a PL/SQL loop (one row at a time) without issuing a COMMIT.

(Caution: all tables resides in a freelist managed tablespace and not ASSM tablespace.  this is on purpose to get consistent behaviour. Things become somewhat complex when rows get stored in an.ASSM managed table .)

References: https://prutser.wordpress.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s