RE: Accessing ASH is slow

  • From: <Christopher.Taylor2@xxxxxxxxxxxx>
  • To: <usn@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 22 Jul 2013 08:11:40 -0500

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.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Martin Klier
Sent: Monday, July 22, 2013 7:52 AM
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 

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
Usn's IT Blog for Oracle and Linux



Other related posts: