RE: Accessing ASH is slow
Date: Mon, 22 Jul 2013 08:11:40 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E887A3DC2D54_at_NADCWPMSGCMS10.hca.corpad.net>
Whenever I run into slow performance against fixed tables (not often but it does happen) I'll run it through DBMS_SQLTUNE to see if the Oracle built-in profiler can give me some insight. Often in these cases, dbms_sqltune will find a profile that is much better but it is always a puzzle to me why the optimizer didn't use that plan to begin with in regard to fixed tables.
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier
Sent: Monday, July 22, 2013 7:52 AM
To: ORACLE-L
Subject: 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:11:40 CEST