Solved

How to use SQL Profile?

Posted on 2013-06-19
3
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

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 74

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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 article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 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