Accessing ASH is slow
- From: Martin Klier <usn@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Jul 2013 14:52:21 +0200
Dear listers,
accessing v$active_session_history is quite slow when fetching data from
a wide (time) window. What's best practice to get ash queries back with
decent performance?
For example, to find all entries for a SQL_ID from last 24h:
select * from gv$active_session_history
where SAMPLE_TIME>sysdate -1
and sql_id='f29fxwd5kh2pq';
It's ending up with two full table scans on X$ASH and X$KEWASH, plus
awfully calculated cardinality. I dared and created table stats with
histograms there, and cardinality was calculated to realistic values.
But it seems that the two tables don't have any index to improve the
access path. My daring went not far enough to create objects in SYS. :)
So what du YOU do when your access to performance repos is too slow, due
to the sheer mass of collected data? (I don't like to duplicate the
view/table - I'm not currently solving a problem, I'm working on a
concept that I can come up with as soon as a customer system need this
analysis. I simply can't waste the time+space then...)
Thanks a lot in advance
Martin
--
Usn's IT Blog for Oracle and Linux
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
