Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 19, 2017 - 6:45 pm UTC

Last updated: May 10, 2023 - 4:43 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi - we are doing some data conversion of our database associated with a vendor product. This means migrating from one version of the vendor schema to another. so remapping the data. During a performance run, one of the SQLs was taking longer to run. So I ran the SQL Tuning Advisor. It came back with a recommended SQL profile with 99.9% benefit. I applied it and could obviously see that it was processing much faster. The difference between the 2 was a plan change. The second one was using parallel execution. Now my question is - can we just leave it with the profile ? will this persist between database restarts ? when we go to actually run this in prod, do we just migrate the sql profiles or should we find a way to get the plan that the SQL profile would have done. whats the best way to do this ? is there any harm in leaving the sql profiles on for that SQL ?

and Connor said...

Hi Sirg,

Let me begin to address your questions with a brief explanation of what a SQL profile is.

The Optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the Optimizer can lead to a sub-optimal plan. A SQL profile contains auxiliary information (a subset of hints) that mitigates these problems. When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan, without constraining it to any specific plan.

Is a SQL profile persisted across Database Restarts?

Yes, the SQL profile will be persisted across restarts, as it is stored in the data dictionary. You can see all existing SQL profiles in a database using the following query,

COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20

SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM   DBA_SQL_PROFILES;


Should we just migrate the SQL profile to production?

Yes, you can easily move a SQL profile from the test system to the production system as we described in an early post https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:952492100346078754

Is there any harm in leaving the SQL profiles on for this statement?

The answer to this question is it depends on the behavior you want.

As I explained earlier a SQL profile is a subset of hint that help the Optimizer to correct cardinality mistakes. It does not freeze the execution plan. If something changes with the underlying tables, indexes, or statistics it’s possible that the execution plan will change, even though a SQL profile exists for the statement.

For some folks this possibility of a plan change is too scary and they use a SQL plan baseline instead to guarantee they get the same plan every time regardless of any changes to the statistics or the schema. More information on SQL plan baselines and how they differ from SQL profiles can be found on the Optimizer https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines

Is it worth finding a way to get the optimizer to automatically generate the plan that the SQL profile gets?

Again the answer here is it depends.

If you are happy with the existing plan and you believe this statement is a one off (no other statement behaves like it) and the statistics on the underlying schema objects won’t change much, it may not be worth the effort to investigate.

However, if multiple SQL statements may be encountering the same problem or would benefit from fixing the root cause then it could be worth investigating the problem.

The first step in your investigation should be to figure out how the SQL profile is influencing the execution plan for the SQL statement. To do this you will need to look at the outline for the SQL statements with and without the profile.

The outline is the complete list of Optimizer hints required to reproduce a specific execution plan. You can view the outline for a SQL statement using the Display functions in the DBMS_XPLAN package, as follows

Explain plan for
 Select prod_id, sum(amount_sold)
 From   SALES
  Where  channel_id=5
  Group by prod_id;

Explained.

select * from table(dbms_xplan.display(null,null,'BASIC +outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 3604305554

--------------------------------------
| Id  | Operation      | Name  |
--------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH GROUP BY      |      |
|   2 |   PARTITION RANGE ALL|      |
|   3 |    TABLE ACCESS FULL | SALES |
--------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$1")
      FULL(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TO_NUMBER("CHANNEL_ID")=5)

30 rows selected.


By comparing the two outlines you will get a sense on where to start your investigation. And depending on what causes the Optimizer to get an incorrect plan (missing statistics, correlation between columns in a table, correlations between join column, complex expressions, etc.) you may be able to find an alternative approach to fixing the problem.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

That helps !!!!

A reader, April 25, 2017 - 12:22 am UTC

Thanks a lot Maria. That helps !! Appreciate your time and effort

Difference Link is No Longer Valid

A reader, May 05, 2023 - 1:24 pm UTC


Connor McDonald
May 10, 2023 - 4:43 am UTC

thanks - the link has been corrected

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.