Solved

How to use SQL Profile?

Posted on 2013-06-19
3
510 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

762 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

14 Experts available now in Live!

Get 1:1 Help Now