This discussion is archived
8 Replies Latest reply: Mar 21, 2013 7:58 AM by 977635 RSS

OEM shows SQL ID, but says:   ** SQL Text Not Available **

977635 Newbie
Currently Being Moderated
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.
  • 1. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    FreddieEssex Pro
    Currently Being Moderated
    The SQL has aged out the shared pool as you correctly identified.

    There is no time constraint on how long a query will stay in the shared pool. This will depend on a number of factors like how big your shared pool is and how many SQL queries are parsed etc.

    Have you tried running a AWR report for this time? I would have thought the SQL should be visible there?
  • 2. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    Jan-Marten Spit Explorer
    Currently Being Moderated
    "select sql_text from v$sqlarea where sql_id = '99mjdv0hkh5km';"

    if you are lucky, oracle captured this SQL (text) in DBA_HIST_SQLTEXT.
  • 3. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    977635 Newbie
    Currently Being Moderated
    In our case, our shared pool is managed by AMM, which our MEMORY_TARGET is set for 35G.
    If I check what our current usage is (by creating a dump from memory), it shows:

    __shared_pool_size=7936M
    shared_pool_reserved_size=1G

    Unfortunately, we have a lot of literals used in queries by our application, therefore, most statements are hard parsed.

    I have tried running an AWR report, andI can't even find the SQL ID in it.
    For that matter, I can't find any of the SQL IDs from the ASH report in the AWR report from the same time period.
    The ASH report is from 7:21am to 7:26am (this morning), and the AWR report is from 7:00am to 8:00am.
    I don't understand it unless the AWR is only reporting on certain queries that meet certain thresholds.
    But I'm actually looking and comparing from the "Complete List of SQL Text" in both reports. Very strange...
  • 4. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    977635 Newbie
    Currently Being Moderated
    Well, darn... nope, not in dba_hist_sqltext either.
    SQL> select  sql_text from  DBA_HIST_SQLTEXT where sql_id = '99mjdv0hkh5km';
    
    no rows selected
    But yet, in the ASH report, it shows: 99mjdv0hkh5km** SQL Text Not Available **
    And as I showed above, it is recorded in DBA_HIST_ACTIVE_SESS_HISTORY and shows it did a full table scan.
    In fact, it shows it ran at 21-MAR-13 07.24.50.524 AM this morning, but as I replied above, it is not included in the AWR report for that time period at all.
    Very strange....
  • 5. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    Dom Brooks Guru
    Currently Being Moderated
    And as I showed above, it is recorded in DBA_HIST_ACTIVE_SESS_HISTORY and shows it did a full table scan.
    In fact, it shows it ran at 21-MAR-13 07.24.50.524 AM this morning, but as I replied above, it is not included in the AWR report for that time period at all.
    Very strange....
    It's not necessarily strange at all.

    ASH samples every second into the memory buffer V$ACTIVE_SESSION_HISTORY and the sample of one in every 10 seconds is stored in the repository table DBA_HIST_ACTIVE_SESS_HISTORY.

    AWR captures the top N sql statements that occur during a snapshot interval.

    It's perfectly normal for statements that are captured in the ASH sample to not be in the Top N statements.

    The fact that one particular statement did a full table scan is not an indicator that it should figure in your top N.

    Having said all that, there are certain recursive/internal statements that do not get exposed via v$sql.
    But their metrics should normally be recorded in the parent sql's metrics.
    I've seen this with some internal LOB statements come to mind, also some stats calls.
    Also, sometimes if a statement is not in v$sql, you can get lucky with v$open_cursor.
  • 6. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    977635 Newbie
    Currently Being Moderated
    Thanks Dom. That's very interesting. I'll keep the v$open-cursor in mind for the future as well.
  • 7. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    Jan-Marten Spit Explorer
    Currently Being Moderated
    if your app is using literals, the SQL is simply gone from sqlarea before you know it.

    although it will certainly be there when it is running, so maybe you should look at the right moment :)

    the alternative is a 10046 trace to capture the SQL...
  • 8. Re: OEM shows SQL ID, but says:   ** SQL Text Not Available **
    977635 Newbie
    Currently Being Moderated
    Yes, that really explains it. Hmmm... I have tried to grab the sql as soon as i see it, but I always seem to miss it.

    I like the idea of doing a sql trace, (although with such an active database as ours, it is going to be a big messy file to search through). But that is probably the best suggestion and I should have thought of that. Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points