The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 243 other followers

Explaining the number of Consistent Gets

Posted by Harald van Breederode on November 6, 2009

Last week I received an email from a friend, who wishes to remain anonymous, with the question why Oracle needed 8 consistent gets to perform a full table scan on a table where all the rows are stored in just one data block. There are several possibilities that can cause this and that is what this posting is all about: Explaining the number of Consistent Gets.

Verifying the claim

Let me first demonstrate that the claim made by my friend is indeed true. I start by setting the STATISTICS_LEVEL parameter to ALL to enable the collection of “Plan Execution Statistics” needed by DBMS_XPLAN to be able to report the number of consistent gets. Next I execute a query to fetch the data followed by a call to DBMS_XPLAN to get and format the execution plan.

SQL> alter session set statistics_level = all;

Session altered.

SQL> select * from foo;

        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 foo

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| FOO  |      1 |     64 |00:00:00.01 |       8 |
---------------------------------------------------------------------------

We can clearly see that there are 64 rows in table FOO 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 = 'FOO';

    BLOCKS
----------
         1

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

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. I recall from memory that Oracle needs 3 consistent gets to perform a full table scan on an empty table, but before jumping to conclusions I better verify my memory”.

SQL> create table bar as select * from foo where 1=0;

Table created.

SQL> select * from bar;

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 bar

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| BAR  |      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 foo;

        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 foo

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| FOO  |      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 foo;

        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 foo

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| FOO  |      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 foo;

   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 foo

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| FOO  |      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 foo;

        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 foo

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| FOO  |      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.

A little quiz to wrap-up

The final example shows yet another number of consistent gets for retrieving the same 64 rows using an arraysize of 70:

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

        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 foo

Plan hash value: 1245013993

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

If you can explain why there are 24 consistent gets instead of just 4 please leave a comment below and I will let you know if you are correct ;-)
-Harald

About these ads

17 Responses to “Explaining the number of Consistent Gets”

  1. Vincent said

    Hi,

    Thanks for your post, this is very interesting.

    But I don’t completely get the read-consistency and aggregate trap examples.
    About the read-consistency example, as shown previously in your post, using an arraysize of 70 Oracle performs only 4 (not 5 !) consistent gets to fulfill the request.
    Therefore, in this example I expected that Oracle performed 4 + 64 (undo records) = 68 consistent gets instead of 69. Where does this 69th extra consistent get come from ?

    Conversely in the aggregate trap example, I expected that Oracle performed, whatever the chosen arraysize, 4 consistent gets instead of 3. Indeed, there are 3 “empty table” consistent gets and, as to do the sum Oracle obviously needs to read the single data block of the table, it should therefore perform a fourth consistent get.
    Why Oracle doesn’t perform (or show us ?) that fourth consistent get ? Maybe I misunderstand what a consistent get is. :-(

    Thanks in advance,
    Vincent

    • Harald van Breederode said

      Hi Vincent,

      Thanx for your feedback. I’ll try to answer your questions:

      The 69th consistent get is to undo the changes made to the data block header. In this header data structures resides which are known as ITL’s and their function is to link the changed rows to the undo segment holding the previous values. All rows inside one data block that are changed by the same transaction share one ITL. Because all 64 rows are stored in one data block there is one ITL in use causing one extra consistent get. If the updated rows were stored in two data blocks there would be two ITL’s involved causing two extra consistent gets. Or if the 64 rows were updated by two transactions there would also be two ITL’s involved also resulting in two extra consistent gets.

      The missing 4th consistent get in the aggregate trap is caused by the way SQL*Plus fetches data. Strangely enough SQL*Plus always seems to start with a fetch for one row followed by fetches of arraysize number of rows until there is nothing more to fetch. So in the case where SQL*Plus fetches 64 rows using an arraysize of 70 it results in two consistent gets. In the case where the sum function fetches the rows from the full table scan, SQL*Plus start by fetching one row from the sum function and it gets notified that there is nothing more to fetch resulting in one consistent get less.

      A consistent get is basicly accessing a data block that is first made read consistent. Consistent gets have a n-to-n relationship with a fetch operation.
      -Harald

  2. One way to force more consistent gets is to update the rows to force row migration, so that even though the original stub is left behind in the only block with row piece starts, the bulk of some rows is in another block.

    Another way is to add a bunch more rows and delete rows only from blocks at the beginning of the table until all except the demo block are empty (and the demo block is not the first block). I think you can use this method to force an arbitrary number of blocks to be gotten by a full table scan before the first row is reached.

    Is there yet another way?

    • Harald van Breederode said

      Hi Mark,

      Both are possible techniques to force the number of consistent gets to go up, but both are not what I did. I didn’t touch the existing 64 rows at all.
      -Harald

  3. Okay, I’ll hope the czar of the society against guessing isn’t watching and I’ll try one more:

    Did you insert some more rows in a different session and it had to read the blocks to determine your session wasn’t supposed to see the contents?

    I’m also not sure without testing what happens if you insert rows in your own session and then roll them back. Sigh. Isn’t it a real achievement that Oracle CONSISTENTLY gets the right answer?

  4. Narendra said

    Harald,

    Nice post.
    When I tried running your scripts, the DBA_TABLES always reported BLOCKS as 4 whereas COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)) reported 1. Not sure what is the reason for difference. However, the DBMS_XPLAN.DISPLAY_CURSOR reported buffers as 4, with arraysize 70.
    Now, bit confused with your question, why there are 24 consistent gets instead of just 5. shouldn’t it be why there are 24 consistent gets instead of just 4 since you have set the arraysize to 70.
    Now, I was able to get the figure of 24 when I added 2010 rows into foo table from another session but did not commit. After this when I executed the SELECT * from foo in the first session, the subsequent DBMS_XPLAN.DISPLAY_CURSOR reported 24 buffers, instead of 4. Could that be the case?
    p.s. As you mentioned in a reply, I did not “touch” existing 64 rows at all. :)

    • Harald van Breederode said

      Hi Narendra,

      Thanx for catching the typo (5 instead of 4). I’ve updated the original post which now read correct.

      The reason why you get 4 blocks instead of 1 could be caused by the tablespace space management type. My table resides in a freelist managed tablespace and yours is probably stored in an ASSM tablespace. I did this on purpose to get consistent behaviour. Things become somewhat complex when rows get stored in an.ASSM managed table and I didn’t want it to get in the way for this discussion. Is your table indeed in an ASSM tablespace?
      Just as Mark you are close to what I did to the table, but I didn’t use a second session to make it happen.
      -Harald

  5. Narendra said

    Just to add some details to my earlier response.
    Oracle atabase version: 10.2.0.4 64-bit on Solaris.
    Also, I had added 2010 rows (from another session) in 3 batches of 1000, 1000 and 10.
    When I tried to add all 2010 rows in a single batch, the consistent gets were not 24. So it appears to be some internal (??) mechanism used by Oracle that decides when to advance the High Water Mark.
    BTW, the increase in consistent gets is due to Oracle advancing High Water Mark of the table as a result of adding new rows (INSERT from another session). I could not get a reproducible number of rows that would result in exact figure of 24. I guess it depends upon how you insert, row size etc.

    • Harald van Breederode said

      Hi Narendra,

      You are correct that the increased number of consistent gets is caused by a bumped high-water mark. I didn’t insert and rolled back the new rows in another session though. I simply did it in the same session. The exact number of rows that are needed to get the extra 20 consistent gets depends on the physical table structure: block size, pctfree and column data type (and a few other attributes). In my case I increased the number of rows from 64 to 10000.
      -Harald

  6. Narendra said

    Harald,

    Yes. I am using ASSM tablespace. Now as for your quote
    I didn’t insert and rolled back the new rows in another session though. I simply did it in the same session., then the obvious
    way to do it from single session is doing the INSERTs (followed by ROLLBACK) from an autonomous transaction (in same session). Am I correct?

    • Harald van Breederode said

      Narendra,

      No it has nothing to do with an anonymous transaction, I just used a regular transaction and rolled it back. While inserting the rows, the high-water mark gets pushed up. Upon transaction rollback the rows are deleted but the HWM remains at its new position.
      -Harald

  7. Narendra said

    Harald,

    Not sure why but here is what I noticed.
    While running the example, I was frequently doing the following to reset the HWM (so that I can start from the scratch; number of buffers as 4)

    ALTER TABLE foo MOVE;
    EXEC dbms_stats.gather_table_stats(user, 'FOO');

    What I could not understand is why is the stats collection needed after the ALTER TABLE..MOVE? Without stats collection, the number of buffers reported (by DBMS_XPLAN.DISPLAY_CURSOR) were 8 and never went back to 4. My understanding is the outcome of DBMS_XPLAN.DISPLAY_CURSOR reports facts and not the guesses (based on statistics). In that case, how did the stats collection affect the actual work done?

    • Harald van Breederode said

      Hi Narendra,

      I don’t know what is causing this when you try it. You are correct that dbms_xplan reports the actual number of consistent gets in the buffers column. The only explanation I have is that somehow your arraysize gets reset back to 15.

      If you omit the call to dbms_stats there will be no statistics and Oracle10g/11g will use dynamic sampling during the parse call. However the effects of dynamic sampling are not reported by dbms_xplan.
      -Harald

  8. [...] Harald van Breederode-Explaining the number of Consistent Gets [...]

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

 
Follow

Get every new post delivered to your Inbox.

Join 243 other followers

%d bloggers like this: