Consistently Inconsistent Consistent Gets

16032010

March 16, 2010

You might be aware that the number of consistent gets performed during the execution of a SQL statement will vary as the execution plan for a SQL statement changes. You might be aware that the number of consistent gets for a SQL statement will vary depending on the block size used by the database. You might be aware that the number of consistent gets will vary depending on the clustering factor of the indexes used by the SQL statement change (assuming that the table rows are actually accessed). You might be aware that moving a table and rebuilding its indexes could cause the number of consistent gets for a SQL statement to change. But were you aware that there are other reasons for the number of consistent gets to change? Let’s set up a simple test table in the database that has 1,000,000 rows and an index on the primary key column:

In Oracle Database, for instance release 11.1.0.6 on 64 bit Linux, we craft a silly SQL statement that uses the test table. The silly SQL statement’s goal is just to drive up the number of consistent gets to allow us to peg the server’s CPU. This is the silly SQL statement that essentially joins table T5 to itself multiple times:

In the above SQL statement I have forced through a hint a nested loop join between the various aliases for the table T5 to force the use of the index, rather than letting the optimizer use full table scans and hash joins. Let’s see how the array fetch size affects the number of consistent gets for the above SQL statement. Articles on other blogs have shown demonstrations of the same effect of changing the array fetch size, but stay with me, there is a twist. The script:

We will throw out the first execution with the array fetch size set at 1 (the client computer is using the 11.1.0.7 client) so that the recursive call does not throw off the timing. The summarized output of the above script follows:

As you can see from the above, when the fetch array size was set to 1, SQL*Plus actually operated as if the fetch array size was set to 2. As the fetch array size increased, the execution time for the query decreased until the fetch array size of 5,000 was attempted. Accompanying the decrease in the execution time is a decrease in the number of consistent gets, bytes sent across the network, and the number of round trips. Nice, so where is the twist, is it just that the time increased when the fetch array size was increased to 5,000?

Maybe we should repeat the test on Oracle Database 11.2.0.1, which also used the default 8KB block size and runs on 64 bit Linux. These are the summarized results, excluding the output that did not change significantly from the test run on Oracle 11.1.0.6:

When the fetch array size was specified as 1, the number of consistent gets dropped from 12,554,387 to 516,378 when run on Oracle 11.2.0.1, yet the execution time is almost identical to that achieved on 11.1.0.6. When the fetch array size was specified as 5,000, the number of consistent gets dropped from 8,559,626 to 16,578, yet the execution time was almost identical to that achieved on 11.1.0.6. Quite a substantial decrease in the number of consistent gets from one release to another.

Anyone want to take an educated guess as to what caused the decrease in the number of consistent gets?

Take a guess before scrolling down.

…

Here is the DBMS_XPLAN output for Oracle 11.1.0.6 for the SQL statement:

Actions

Information

9 responses

Revisiting blocks would be my guess. If Oracle can get all the information it needs in a single fetch, it only needs to grab the block once. With a small ARRAYSIZE it will need to go back to the same block several times.

Thanks for stopping by and providing a comment. I was thinking the same thing regarding the changes to the ARRAYSIZE setting. My Faulty Quotes blog article about the buffer cache hit ratio metric references this document and this OTN thread where Jonathan Lewis described how Oracle is able to grab a block and pin it when the fetch array size is increased, thus reducing the consistent gets statistic.

When I first saw Oracle 11.2.0.1’s consistent gets statistic for this query, I immediately thought that maybe Oracle 11.2.0.1 had greatly improved the block pinning behavior. I was surprise, shocked, maybe even a little impressed when I saw that the 8 way self-join was collapsed into a single full table scan.

Thank you for sharing the formula. That formula is either very close or exactly right – I cannot tell at the moment because I am showing that the table in this database contains 16217 blocks, which based on the formula you provided, suggests that the actual number of blocks in the original test table should be about 16,378 (this is possible, but I cannot check right now).

For those that decide to test the values shown for the fetch array size of 1, keep in mind that the article states: “As you can see from the above, when the fetch array size was set to 1, SQL*Plus actually operated as if the fetch array size was set to 2.”

Thanks for providing the link – I agree, his case #2 explains very clearly what happened during my test run on 11.2.0.1. I thought that the feature was called table elimination (where a parent table could be eliminated if a foreign key constraint is present and no columns from the parent are returned), but the 10053 trace shows that it is correctly referred to as join elimination.

It is impressive to see that this join elimination works with self joins, but I wonder if it would still work if there were different WHERE clause predicates for each of the aliases – for instance an EMPLOYEES table that lists the employee’s supervisor in one column, so a self join would be needed to determine for all of the employees, which employees they supervise. It might be something interesting to explore.

Hi Charles,
I read your interesting test that I came immediately to mind
a post by Christian Antognini (where to take part on and also mentioned by Gary)
that the “join elimination”. I ran your test
my DB version 11.2.0.1.0, getting your own results

I do not get significant benefits by increasing further ArraySize(in my case 200), in fact if I set arraysize at a number greater than the number of rows stored in Each single table blocks, the number of logical reads is close to the number of the table's
blocks… right?

Thanks for contributing your test case in this blog article. It is interesting that removing the unique contraint affects the execution plan.

My test showed that there was only about a 7 second time difference between the fetch array size of 200 and the fetch array size of 2000 (01:29.87 – 01:22.25) on Oracle 11.2.0.1. On Oracle 11.1.0.6 the difference was only about 2.5 seconds (01:24.48 – 01:21.96). I noticed that my times were a bit faster than were your times – I wonder if network performance might be a limiting factor that causes your execution time to level off with a fetch array size of 200. If the client computer was also the server computer (no network involved) it could be that you reached the server’s memory throughput/transfers per second threshold.

I am not sure what the correct answer is for your question. The answer might depend on the execution plan – whether there are nested loop joins, hash joins, etc. By reviewing my summarized test case output, the number of consistent gets perform continued to decrease as the array fetch size increased.

You might take a look at the comment made in this thread by Jonathan Lewis:http://forums.oracle.com/forums/thread.jspa?threadID=973560&start=0&tstart=0
“If the query really is nothing but a UNION ALL of five tablescans, then what it the array fetch size ? If the array fetch size is large enough the number of block visits would be similar to the number of physical block reads. If the arraysize is one then the number of block visits could be close to the number of rows fetched as a block has to be released at the end of each fetch and the reacquired for the next fetch.”

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: