Re: Index vs. table scans in statspack reports

Pete Finnigan <plsql_at_petefinnigan.com> wrote:

>Hi Rick,
>
>Not sure if it is relevant in your case as it sounds like you may be
>limited to system wide changes only but you may want to take a look at
>Cary Millsaps new book "optimizing oracle performance" published by
>O'Reilly. It looks at how to identify the right business processes to
>tune rather than system wide reports and then he shows how to obtain the
>correct data to analyse using the wait interface and.... well anyway you
>get the picture.

I willingly limit myself to administration tuning. If I ever prove to be able to tune any specific query, I would be immediately overwhelmed by (amateur) developers who think that the Admin is responsible for tuning their queries anyway. But I have no clue about how people access tables, I wouldn't dare to do any change of the schema (something would break) and I can't afford the time to support them.

But: There are ways for the Admin to "secretly" adapt things so that queries would run as if they were optimized - without touching them. For example, using stored outlines: I would link the right execution plan to the query. Or switching tracing on, gathering trace files, processing these files with tkprof using the record= option and get "replayable" queries to see what is being done with the data; if a pattern is recognized, the Admin could for example build materialized views and switch query_rewrite on, give the users the appropriate privilege, and he will fly without knowing why.

So in any case, such a book would be valuable. I might order it if I don't forget to.

Thanks
Rick Denoire Received on Fri Nov 28 2003 - 18:33:23 CST