Re: Accessing ASH is slow

  • From: Martin Klier <usn@xxxxxxxxx>
  • To: mwf@xxxxxxxx
  • Date: Tue, 23 Jul 2013 11:25:05 +0200

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_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

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


Other related posts: