Solved

How to use SQL Profile?

Posted on 2013-06-19
3
512 Views
Last Modified: 2013-06-19
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.
0
Comment
Question by:mkhandba
  • 2
3 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39259874
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.
0
 

Author Comment

by:mkhandba
ID: 39259931
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?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39260139
The existence or absence of a suggested profile does NOT mean you need to do anything to the queries migrated to prod.

However, if your advisor in dev or prod suggests a profile you might want to accept it and see if it helps your performance.

If your dev and prod are not identical then the suggested profiles will likely be different or possibly only one of them will be suggested.

in other words - for your developers - no they don't have to do anything
for your dbas yes - they should look for profiles and accept whichever, if any, are helpful.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need a replacement data type in Oracle 6 66
Trigger usage 2 59
su - oracle could not open session 6 53
Sql Join Problem 2 33
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now