The table created by the above contains 1,000,000 rows with an index on column C1. Column C1 is defined as a VARCHAR2, but I placed numbers into the column. Now for the first part of the test, where I request that Oracle display the execution plan for a SQL statement without actually executing the SQL statement:

The first execution plan displayed is incorrect. I have a utility (my Toy Project for Performance Tuning) which retrieves all plans for a SQL_ID using DBMS_XPLAN and also checks V$SQL_SHARED_CURSOR and V$SQL_BIND_METADATA. This is the output:

Notice the reason for child #2 being created “The optimizer environment does not match the existing child cursor”.

I then disconnected from the database instance and reconnected to reset the OPTIMIZER_INDEX_COST_ADJ parameter to the system default. One more test, gather statistics forcing current plans to be invalidated (there is usually a delay when statistics are gathered before plans accessing those objects are invalidated):

I had originally planned to mention the Oracle 11g introduced adaptive cursor sharing, which is another source of different plans for the same SQL statement. So, in summary some of the sources of the plan changing:
* Parsing a SQL statement with the bind variable values not defined
* Parsing a SQL statement with the bind variables defined as the wrong type (or specified as a different type)
* Statistics collection forcing the invalidation of cursors, which forces a new hard parse on the next execution (on 10g and above, statistics collection does not cause an immediate invalidation of cursors accessing objects for which statistics were collected)
* Changes in the statistics, or even lack of change in the statistics with supplied values in the SQL statement appearing to be increasingly above or below the maximum and minimum values for the columns.
* Bind peeking, where different bind variable values were specified on each hard parse
* Different optimizer environment for the session executing the SQL statement
* Adaptive cursor sharing in 11g

Actions

Information

6 responses

I shall be taking some time to read all the posts. As usual, very impressive and useful so need time to grasp them all. Just wanted to give a feedback about the posts that at some places, the code is not coming properly. Its coming in a very light color, making it difficult to read.

Thanks so much once again for starting the blog, really so much happy!

Thanks for the feedback Aman. I am not yet sure that I like this WordPress template. It seems that a lot of the DBMS_XPLAN output will not fit. I am still trying to learn how to configure WordPress. With Internet Explorer 8 the code sections appear in a fairly large, black, Courier type font, at least on my computer.

The theme which Jonathan uses over his blog, that’s a good one for the code posting. Also the one which I use over my blog is okay I believe but I am not sure that’s there for the wordpress.com . Try using the template of JL’s blog.

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: