RE: Accessing ASH is slow

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <usn@xxxxxxxxx>
  • Date: Tue, 23 Jul 2013 08:16:50 -0400


Glad you found a solution.

Regarding practices: You've engaged in a valid firefighting practice, and
you recognize it as such. You were forced into using this valid firefighting
practice by a customer site that did not facilitate using a better practice.
(Most people do not in fact have a DBA datawarehouse, as I have been
unsuccessful in evangelizing the effectiveness of this practice in economic

Now perhaps it is a nicety of American English, but the reverse of "bad" is
"good" or "better." The word "best" means nothing better exists. It is a
useful word for things like dog shows where it is inherently limited to a
specific event. But I do see your smilely face after your comment, so I know
you're kidding. Perhaps it would be a best practice to wipe out the use of
the couplet "best practice!" ;{)  <- that's me winking and smiling under my

Checking for inadvertent invalidation of the usability of existing indexes
due to NLS_ parameters is a damn good practice and it was clearly an
effective practice in this case. Thanks for posting it to the list!


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Martin Klier
Sent: Tuesday, July 23, 2013 5:25 AM
To: mwf@xxxxxxxx
Subject: Re: Accessing ASH is slow

Hi Mark,

thanks for your reply.

You are absolutely right with wasting prod resources for DBA research on
mass data. Trouble is, that coming on-site for helping quickly does not
always allow to build up infrastructure, or even duplicate data. And I am
fully aware that I am talking about contrary aims here: Quickness and
refusing to do the best way to make it go.

But in fact, you told me a best practice, nevertheless. :)

In this special case, I found a very interesting reason for the slowness.
And it was my own booby trap... If you are interested:

v$active_session_history joins v$ash and v$kewash by various predicates,
including column NEDD_AWR_SAMPLE of both tables. In my case, NLS_SORT was
set to BINARY_CI and NLS_COMP was set to LINGUISTIC. So the comparision was
changed from "S.NEED_AWR_SAMPLE=A.NEED_AWR_SAMPLE" (which could have been
supported by a fixed index) to
which causes a full access to the table.

Gnahhhh. :)


Mark W. Farnham schrieb:

> I personally think it is a bad practice to consume production RDBMS 
> cycles doing ad hoc analysis of metrics.
Usn's IT Blog for Oracle and Linux

Usn's IT Blog for Oracle and Linux



Other related posts: