Consistent Gets During a Hard Parse – a Test Case to See One Possible Cause

7102010

October 7, 2010

A recent OTN thread caught my attention. The original poster noticed that when generating a TKPROF summary from a 10046 trace file that the parse call showed a significant number of consistent gets when compared to the fetch and execute calls. The TKPROF summary looked something like this:

The TKPROF summary showed that there were 3,599 consistent gets during the four times that this SQL statement was hard parsed, while there were only 36 consistent gets performed during the four executions and fetches. The displayed Row Source Operation execution plan shows that 9 consistent gets were performed, and because that number is 1/4 of the value displayed by the TKPROF summary (the SQL statement was executed four times), that likely indicates that the OP is running Oracle Database 11.1.0.6 or greater, which by default outputs the STAT lines (TKPROF Row Source Operation lines) to the trace file after every execution.

How could this SQL statement perform an average of 900 consistent gets per hard parse, and only an average of 9 consistent gets to retrieve the rows from the two tables listed in the SQL statement? This is probably a good excuse to build a test case to try out a couple of ideas. A couple of good suggestions were offered in the OTN thread regarding what may cause consistent gets during a hard parse, but 900 consistent gets? The OP mentioned that this problem is happening in a development database, and that may be a key clue. What if the OP creates a couple of tables with a couple of indexes, and then loads data into the tables just before executing his SQL statement? If the OPTIMIZER_MODE is set to the deprecated values of RULE or CHOOSE we could very well see one result for the number of consistent gets, which differs from what happens when the OPTIMIZER_MODE is set to a non-deprecated value. What if the OP does not collect statistics on the tables and indexes, and those tables and indexes either do not survive until 10 PM or the DBA has disabled the automatic stale statistics collection job that typically starts around 10 PM? What if the memory allocated to the SGA is much smaller than what is needed? What if… (fill in the blank)?

Let’s build a quick test case. First, we will make certain that no one has adjusted the default value for dynamic sampling:

The above creates two simple tables, each with two indexes. Note that the indexes are created before the tables contain any rows, so statistics are not automatically collected for the indexes when they are created. Now to insert the rows:

Now let’s take a look in the trace files (see this three part blog article series for help with decoding 10046 trace files), starting with the PARSE_TEST1 trace file (note that I manually line wrapped the dep=1 SQL statements):

In the above, you will notice that the parse call for our SQL statement performed 159 consistent gets. If you add up the number of consistent gets performed by the dep=1 SQL statements that immediately preceed our SQL statement (71 + 5 + 71 + 8), you can see where 155 consistent gets were performed during the hard parse. The first STAT line shows that the SQL statement actually required 355 consistent gets and no physical reads (SQL*Plus showed that 514 consistent gets and 15 physical reads were performed, and if you look closely at the dep=1 SQL statement you can see where the 15 physical block reads were performed). 355 + 155 = 510, which is just less than the 514 consistent gets reported by SQL*Plus, so we could look further up in the trace file to find the remaining 4 consistent gets.

This time you can see that 172 consistent gets were performed during the hard parse. If we add up the consistent gets just before our SQL statement appeared in the trace file (71 + 10 + 71 + 16), we can account for 168 of the 172 consistent gets during the parse of our SQL statement. Note that the SQL_ID for the SQL statement appeared in the trace file (‘0m07kq3jktxwn’ for the last trace file), so we could do something like this to quickly confirm that dynamic sampling happened during the hard parse without looking in the 10046 trace file:

The note at the bottom of each execution plan shows that dynamic sampling happened during the hard parse. Note also that there are two child cursors with the same execution plan. One was created when the 10046 trace was active, and the other was created after the 10046 trace was disabled.

We could then extend this test case by collecting table AND index statistics, and then re-execute the test script to compare the results. So, what are the other possible causes for consistent gets during a hard parse? Start with my test case and see if you are able to show the source of the consistent gets that are output by SQL*Plus or a TKPROF summary.

Actions

Information

2 responses

Sir,
In the same thread Jonathan Lewis replies that this consistent gets are due to access to buffers for dictioanry tables as there is no information in row cache for the statement.
What do you think?

I could very well be wrong, but I think that Jonathan Lewis’ comment was intended to answer a person’s question about how accesses to the database’s data dictionary could result in physical block reads. He stated: “The data dictionary tables (tab$, ind$, col$ etc.) are just tables like any other table – and blocks from those tables get into the buffer cache.” And then he added a couple of clarification points. He was answering a follow-up question with that reply, rather than the original question that started the OTN thread. I do not think that his comment was intended to suggest that lookups of the data dictionary would/could cause the average of 900 consistent gets per hard parse of the simple SQL statement.

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: