This discussion is archived
5 Replies Latest reply: Sep 25, 2013 1:41 PM by olwin RSS

[OCP exam] doubt about Automatic SQL Tuning Advisor

olwin Newbie
Currently Being Moderated

Hi,

 

Sorry for my poor english.


I am currently preparing for the OCP  11g exam.

For that, I have bought some training tools  to help me prepare the exam.

 

But, in the chapter related to the SQL tuning advisor, some explanation really confuse me .

 

Extract :

<<

the SQL automatic tuning  job runs every night during the maintenance window.

By default, the job makes SQL profile recommendations, tests them, and automatically implements them if they provide better performance improvement)

>>

 

I was persuaded it was the opposite.

 

I check several databases/documentation, and it's seems to be always the opposite.

By default,new sql profile are tested but not implemented automatically (you have to set a parameter ACCEPT_SQL_PROFILES TO TRUE to activate this option)

 

But the exam is validated on oracle 11.2.0.1, and I only have 11.2.0.2/11.2.0.3 db available.

So may be oracle changed the value of the default parameter in the new releases.

 

Can someone confirm if I am right or not ?

  • 1. Re: [OCP exam] doubt about Automatic SQL Tuning Advisor
    jgarry Guru
    Currently Being Moderated

    Study guides and the exams themselves have been known to be wrong.  You might ask this on the Certification

  • 2. Re: [OCP exam] doubt about Automatic SQL Tuning Advisor
    Aman.... Oracle ACE
    Currently Being Moderated

    AFAIK(and I know very less) , you are right! The automatic implementation is not a default thing.

     

    Aman....

  • 3. Re: [OCP exam] doubt about Automatic SQL Tuning Advisor
    rainiermorilla Newbie
    Currently Being Moderated

    I would rather accept what is in the documentation:


    "If a SQL profile is recommended, the database tests the new profile by executing the SQL statement both with and without the profile. If the performance improvement improves at least threefold, then the database accepts the SQL profile, but only if the ACCEPT_SQL_PROFILES task parameter is set to TRUE. Otherwise, the automatic SQL tuning reports merely report the recommendation to create a SQL profile." Automatic SQL Tuning


    "If automatic implementation of SQL profiles is enabled (the default is disabled), then the database implements any SQL profiles that promise a great performance benefit. The implementation occurs at tuning time so that the database can immediately benefit from the new plan. You can enable or disable automatic implementation by using the SET_AUTO_TUNING_TASK_PARAMETER API to set the ACCEPT_SQL_PROFILES parameter." DBMS_AUTO_SQLTUNE

  • 4. Re: [OCP exam] doubt about Automatic SQL Tuning Advisor
    JohnWatson Guru
    Currently Being Moderated

    What training materials are you using? In the Oracle Press All In One Exam Guide that I wrote with a colleague, we say ïn chapter 25 where the SQL Tuning Advisor is discussed:

     

    "Optionally, you can specify that the analysis performed during the maintenance window automatically implements recommended SQL profiles".

     

    If it is a mistake we made somewhere else, we would like to know! No matter how much care goes into editing, some errors always slip through, and have to be listed in errata published later..

    But as far as the exams are concerned, I am sure you will have no problems given the attention to detail that you are showing.

  • 5. Re: [OCP exam] doubt about Automatic SQL Tuning Advisor
    olwin Newbie
    Currently Being Moderated

    JohnWatson wrote:


    What training materials are you using?

     

    A practice exam proposed by a society called "Transcender"

     

    JohnWatson wrote:

     

    In the Oracle Press All In One Exam Guide that I wrote with a colleague, we say ïn chapter 25 where the SQL Tuning Advisor is discussed:

     

    "Optionally, you can specify that the analysis performed during the maintenance window automatically implements recommended SQL profiles".

     

     

    My bad ! I have bought the smaller version of that book in complement (only the 1z0-053 exam).

     

    Last night, I have read this chapter several times, looking for an answer, I don't know how I did to miss this sentence

    (chapter 11, first paragraph of the "Use the SQL tuning advisor" section)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points