Sql tuning advisor(accepting profile)

Question

I have created sql tuning task for some query and executed it.After generating report,It recommends me to run the following command:

execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_task_name',
                                        replace => TRUE);

But! Of course I want to know what does this profile do?! and after searching this question on the internet I found the following query:

select
    rat.attr1
from
  sys.WRI$_ADV_TASKS    tsk,
  sys.WRI$_ADV_RATIONALE  rat
where
  tsk.name = 'tuning_task_name'
and  rat.task_id   = tsk.id

And the result of this query is the following:

alt text

I don't understand what does it do :( And also I want to convert this profile hints into the more readable (/+ hint/) sql hints?

score 1 · Answer 1 · answered Oct 11 '17 at 05:44

Yes, you saw those outline hints.

They will be forced to be used when oracle optimizer parsing this SQL. Hence, the generated execution plan is affected by those hints.

If you want to use similar hints in you SQL, you just need simply copy them into you SQL where follows SELECT, commented by /+/. Be aware the Query Block names, they will be changed in different SQL.

Here is a full list of Oracle SQL hints for your reference.

http://www.hellodba.com/reader.php?ID=208&lang=EN http://www.hellodba.com/reader.php?ID=220&lang=EN

Hope this helps

score 0 · Answer 2 · answered Nov 30 '10 at 06:49

Based on page 24 of this PDF it looks like these hints are mostly telling the optimizer to adjust the cardinality estimates for different objects. WHY the profiler is doing this I'm not sure; maybe the statistics are bad or maybe it's making fake statistics to force a better plan? But perhaps a better way to understand the profile is to compare the explain plan before and after.

Sql tuning advisor(accepting profile)

2 Answers2