Re: are hints still necessary in 9i?

on 10/10/2003 8:10 AM Mark Townsend said the following:

>> information, or do they store the same information once
>> for every use of a particular table in each different SQL
>> statement ? I suspect it is the latter.
>
> I'm still checking, but do profiles store generic correlation
>
> My understanding is the same as yours - the latter. It's the statement
> that is profiled. Note that profiles could have other information
> other than correlation info - for instance, I understand that how the
> data is consumed is also considered, which may result in a profile
> containing information to the effect of a first rows hint. I have yet
> to understand how this is detected. Seems pretty damn clever to me.

SQL tuning could do - Partial execution, Dynamic sampling, and in this case how it detects to use "first rows" or "all rows" is by looking at the SQL's execution statistics from AWR (Automatic Workload Repository - that takes a snapshot of the entire database every 30 mins and retains information upto 7 days, by default). Just like V$SQL, AWR also has (#executions, #fetches) stats on Top SQL statements.

>> feeling that you are telling me I really can run the advisor
>> in pseudo-real time to update profiles and make execution
>> paths change dynamically every 30 minutes if I want to.
>
> I haven't put the bits together yet, but I have an unpleasant
>
> Sure, if you want to. I'm not too sure there would be much benefit to
> that, however.
>
>> Bye-bye deterministic trouble-shooting ! Why was performance
>> slow yesterday afternoon - I don't know, the execution plans
>> were different.
>>
> I think, but will need to verify, that you can historically view past
> execution plans as well.

Yes, you can. AWR also stores the execution plan for the Top SQL stmts.  From AWR, one can find the execution plan, execution statistics, whether or not profiles were attached to a particular SQL, and also the 'recommendation' history (when were SQL tune run on that particular SQL, what were the recommendations given by SQL tune during every run) as much as 7 days in the past, by default.

> The whole point of the ADDM stuff is that the question "why was
> performance slow yesterday" will hopefully go away. With ADDM, either
> 1) It wasn't, or
> 2) You already know.
>
> That's the design goal at least. YMMV.
>
>> But it brings me back to the original point,
>> which is that Outlines (can) also guarantee you the correct
>> execution path for a given SQL statement, but there is a
>> cost associated with USING them inappropriately - will
>> Profiles have the same (potential) problem.
>
> I absolutely certain inappropriate use will introduce unnecessary cost
> :-)

As I look at all this, here is a key place where various automatic manageability features come together. If you have a SQL that has a profile attached to it, and due to enormous changes in the underlying data, the profile is infact hurting the SQL's performance, then, obviously, you can either drop the current profile and/or recreate a new one. This is where ADDM brings pieces of the puzzle together - the minute the SQL starts executing badly, the next automatic ADDM analysis (happens every 30 mins) is going to detect that the SQL is expensive and recommend you to run SQL tune on it. Another run of SQL Tuning advisor on that SQL, will fix the bad profile for you, just the way you want it to be fixed.

-V Received on Fri Oct 10 2003 - 13:01:17 CDT