Solved

How to use SQL Profile?

Posted on 2013-06-19
3
515 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

785 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