I'm a bit confused on this and was hoping someone can help.
I'm using DBConsole for an Oracle 11.2 database (on Solaris 10).
I see really high momentary "*+spikes+*" in top activity at approx. 20 to 30 minute intervals.
When I try to figure out which SQL is causing the spike, I slide the shaded section to the area containing the spike and look at all the SQL statements during that period.
First I attempt to look at the SQL statements by highlighting, or clicking on the entries in the SQL ID column under Top SQL (at the bottom left side of the page).
Then, I copy and paste the statements in my SQL Plus session and see if it causes any kind of spike that I monitor in another OEM window looking at the top activities.
Usually, I don't even get a blip on the radar, so I'm not finding the offending SQL statement which causes the spikes.
Sometimes, however, when I hover over a SQL ID, it doesn't show the SQL statement and instead just shows the words: [SQL ID].
Next, I will run an ASH report for the 5 minute window of the shaded box containing the spike.
When I look at the "Complete List of SQL Text" in the ASH report, I again find some entry or entries that just say: ** SQL Text Not Available **
So, then I try to select sql_text from v$sqlarea where sql_id = '.....' (whatever that sql_id is), and I get no rows found.
e.g.,
SQL> select sql_text from v$sqlarea where sql_id = '99mjdv0hkh5km';
no rows selected
For that matter, when I try other sql_id's, I don't get them either and I assume it is because they are already aged out of v$sqlarea.
If I check the v$sqlarea for current statements, they work fine.
So, first thing, what controls length of time (size of pool) for v$sqlarea?
Next, I selected from DBA_HIST_ACTIVE_SESS_HISTORY, and it shows the information related to the SQL ID, but of course, it does not show the sql text.
1* select * from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id = '99mjdv0hkh5km'
SQL> /
---------------------------------------------------------------------------------------------------------------------------------------------------
19242 1311231241 1 68759345
21-MAR-13 07.24.50.524 AM 394 2337 FOREGROUND
16 65 99mjdv0hkh5km Y 0 3
SELECT 6.2052E+18 99mjdv0hkh5km
3 2792326754 4
TABLE ACCESS
FULL 16777216 21-MAR-2013 07:24
Notice above the FULL TABLE SCAN. This is what I'm trying to dig down into which I suspect is the culprit of causing the spikes.