Find SQL query using adaptive execution plan in Oracle

Check SQL Query using adaptive execution plan in Oracle

select sql_id, child_number, sql_text, IS_RESOLVED_ADAPTIVE_PLAN from v$sql
where Is_RESOLVED_ADAPTIVE_PLAN = 'Y';

IS_RESOLVED_ADAPTIVE_PLAN column in V$SQL view:

This column shows whether all of the adaptive parts of a plan have been resolved to the final plan. Once the plan is resolved, the plan hash value and the plan displayed by DBMS_XPLAN will not change through the end of execution. The values for this column are:

  • NULL: If the plan is not adaptive
  • Y: If the plan is fully resolved
  • N: If the plan is not yet fully resolved

Reference: https://docs.oracle.com/database/121/REFRN/GUID-2B9340D7-4AA8-4894-94C0-D5990F67BE75.htm#REFRN30246

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.