RE: Accessing ASH is slow
Date: Mon, 22 Jul 2013 15:07:09 +0200
Message-ID: <BACE06B587FB3C47AC31296417C11434064C4EF91D22_at_thnms004.TheHumanNetwork.local>
Hi,
did you try collecting fixed object statistics?
RUN: exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Herald
www.superconsult.nl
Van: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] namens Martin Klier [usn_at_usn-it.de] Verzonden: maandag 22 juli 2013 14:52
Aan: ORACLE-L
Onderwerp: Accessing ASH is slow
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
--
http://www.freelists.org/webpage/oracle-l--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 22 2013 - 15:07:09 CEST