Re: EM Grid Control-Sql Advisor

Ghassan,

STA also mentions "Restructure SQL" as one of its' recommendations, and that keeps me in a job ;-)

Dhimant - Read up on SQL Tuning Advisor from the Oracle 10gR2 Performance Tuning Guide. This really doesn't say much, but there are a number of good books which go into fairly great detail on the specifics of the questions you have (hint, hint!) You should also look at the 10gR2 PL/SQL Inbuilt types and packages manual. The DBMS_SQLTUNE package is the one to read up on.

-- 
John Kanagaraj <><
DB Soft Inc
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

On 9/21/07, Dhimant Patel <drp4kri_at_gmail.com> wrote:


>

> To me, "implement" means implement and so I was too afraid to press

> "Implement" button. I decided to press that button after reading suggestions

> from Job and Ghassan - yes in next screen it does provide a matrix with

> possible strategies.

>

> I wish it would say something other than "Implement" on that button

> though.

>

>

>

> Thanks for the suggestion!

> -DP.

>

>

>

> On 9/21/07, Ghassan Salem <salem.ghassan_at_gmail.com> wrote:

> >

> > sql tuning adv never changes the SQL, it usually gives 3 types of

> > recommendations:

> >

> >    1. add indexes

> >    2. gather stats

> >    3. profile: this is a bunch of hints giving the CBO more accurate

> >    stats about the SQL (e.g . cardinalities of some columns, ....)

> >

> > It may give advices of type 1 and 3, and you can choose to implement

> > either or all of the advices that it gives.

> >

> > rgds

> >

> > On 9/20/07, Dhimant Patel <drp4kri_at_gmail.com > wrote:

> > >

> > > Hi All,

> > >

> > > *In short: *

> > > Under 10g grid control, after running sql tuning advisor, I could see

> > > only old and new explain plans and a button with "Implement" but no

> > > description of

> > > what tuning advisor is going to do in order to ac hive new explain

> > > plan. I want to be able to see if

> > > 1> tuning advisor plans to modify the query AND/OR

> > > 2> add indexes/hints etc. So that I  can undo if need to manually.

> > >

> > > At present I don't see this information is offered anywhere.

> > >

> > >

> > > *In Long:*

> > >

> > > I have an expensive query on which I ran SQL Tuning advisor under grid

> > > control. Now looking under

> > >    Database Instance: <Name>

> > > <http://fire.reis.com:1158/em/console/database/instance/sitemap?event=doLoad&target=reisprod.fire.reis.com&type=oracle_database&pageNum=1>

> > >  >   Advisor Central<http://fire.reis.com:1158/em/console/database/instance/advisorTasks?event=reload&target=reisprod.fire.reis.com&type=oracle_database&dbPageNum=1>

> > >   >  *SQL Tuning Results:<Query_Name>* >  *Recommendations for SQL

> > > ID:4wx9fm17gfhy8434 *

> > > I could see following sections.

> > > SQL Text: Underneath my sql query.

> > > Select Recommendation:

> > > I have buttons with "Original Explain Plan" "Implement"  and a table

> > > with last column "New Explain Plan" with link to new and old explain plan

> > > compared.

> > >

> > >

> > >

> > > How can I obtain this info?

> > >

> > >

> > > -Thanks,

> > > DP.

> > >

> > >

> >

> >

>


--
http://www.freelists.org/webpage/oracle-l