RE: Accessing ASH is slow

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 terms.)

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 moustache.

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!

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier
Sent: Tuesday, July 23, 2013 5:25 AM
To: mwf_at_rsiz.com
Cc: 'ORACLE-L'
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
NLSSORT(S.NEED_AWR_SAMPLE,'nls_sort=''BINARY_CI''')=NLSSORT(A.NEED_AWR_SAMPL E,'nls_sort=''BINARY_CI''')
which causes a full access to the table.

Gnahhhh. :)

Regards
Martin

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
http://www.usn-it.de


-- 
Usn's IT Blog for Oracle and Linux
http://www.usn-it.de

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


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