Automatic Reoptimization Feature On 12c

Many times when we execute a statement execution plan gives us an estimate of the rows returned, but did not have in mind the actual rows, this was so at least until the Oracle 12c version. Now there is a new feature named AUTOMATIC REOPTIMIZATION, this basically learns from previous executions of a statement. The execution plan show us two columns, E-Rows column (The estimate number of rows) and A-Rows (The actual number of rows), using some algorithm, Oracle can change the execution plan based on these two values.

To find out when a query is a candidate (using the Oracle algorithm), we can look the column in IS_REOPTIMIZABLE at v$sql view, this will have value of “Y” if improved. However, to implement the change need perform a hard parse. For the next execution and to verify that the change is working, the execution plan show us in the section NOTE a comment like;

 

– cardinality feedback used for this statement

 

HTH – Antonio NAVARRO

 

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