Re: Accessing ASH is slow

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_SAMPLE,'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