asked on

How to use SQL Profile?

Hi, evaluating my SQL using Sql Tuning Advisor, now it is suggesting:

A potentially better execution plan was found for this statement.

- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'staName86912', task_owner => 'SYSTEM', replace => TRUE);

First how to understand, what exactly it is recommending and then how to implement too, as it's asking to do this:

execute dbms_sqltune.accept_sql_profile(task_name => 'staName86912', task_owner => 'SYSTEM', replace => TRUE);

OK ... if I executed this ... then what, please do guide/assist to understand, first how to understand this recommendation/advice and then how to implment this on our Sql, or order for it to better perform.

Do reply, if my question is not clear/confusing.

Thanks in advance.

Sean Stuber

A sql profile is real numbers gathered during the execution of a query.

If you accept the profile then those numbers are used by the optimizer when it needs to reexecute that query so it can make better decisions the next time.

For example...

Lets say your query generates a plan with a NESTED LOOP operation and the optimizer estimates it will loop 10 times and hence thinks that's an efficient operation and builds the plan that way.

When it executes the query the loop actually executes 1000 times and is a significant performance problem.

If you accept a profile for that query, when the optimizer is looking at the query it will see that it's previous estimate of 10 did not reflect the reality of 1000 and will therefore examine other paths looking for a different option like a MERGE or HASH join or possibly looping again but using a different driving table.

Using the profile does not require you to modify your query.

Mushfique Khan

ASKER

Thanks for the reply.

Does this mean that we don't need to do anything?

Basically in our scenario, developers wrote queries, which will be promoting to PROD on release night, they tested them on DEV and then give it to us to review/test/validate on DEV dbs.

Here we review and give/provide our suggestions/thoughts ... what to do in this case?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial