Pseudo cursors and invisible SQL

Do you ever wonder why Oracle Enterprise Manager and AWR reports show the SQL text for some SQL IDs as not available?

The DBA forums and discussion groups mention that it might be because the SQLs have already been flushed from the shared pool or wonder how do you get and confirm the SQL ID for a query without seeing the text of the query?

The truth is however slightly different. These are Oracle internal or so called pseudo cursors which are just structures providing direct access database objects by avoiding the overhead of writing an explicit SELECT statement. These structures access directly most of the time the data dictionary tables, CLOBs and BLOBs. Sometimes even NCHAR and NVARCHAR2.

This is the reason why these internal cursors show in OEM and AWR with the legend “SQL Text Not Available”. There is just no SQL.

They are used internally within the database and cannot be described. At least not completely. However, here is what you can do as suggested by MOS 1298471.1:

create or replace view h$pseudo_cursor as
select Pseudo_cursor, sql_id,obj_id hex_obj_id
     ,obj# object_id, u.name owner, o.name object_name
from (select distinct 
             KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
        ,substr(KGLNAOBJ
               ,instr(KGLNAOBJ,'_',1,3)+1
               ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id 
       ,(case when 
         replace(translate(substr(upper(KGLNAOBJ)
                                 ,instr(KGLNAOBJ,'_',1,3)+1
                                 ,instr(KGLNAOBJ,'_',1,4)
                                  -instr(KGLNAOBJ,'_',1,3)-1)
                          ,'0123456789ABCDEF','................')
                ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
            from x$kglob) k
   , obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
   and o.owner#=u.user#

Now, we can try to find out more about our invisible SQL:

col hex_obj_id  format a10
col pseudo_cursor format a30
col owner format a20
col object_name for A22
select * from h$pseudo_cursor where sql_id='grv54uyqkw2yc';

The second query shows us the pseudo PL/SQL used by Oracle internally. Notice the missing value for SQL ID!

Now, here is a question: how to tune pseudo cursors? If you run STA (SQL Tuning Advisor), the error message you get will be “The SQL has aged out of the cursor cache”. OEM will notify you with “The SQL statement is not available”:

So the bottom line is: if you see messages about SQL Text not being available, it is most likely due to Oracle trying to access internally LOBs or its own tables.

Reducing “library cache: mutex X” concurrency with dbms_shared_pool.markhot

Mutexes or mutual exclusion algorithms are lighter and more granular concurrency mechanism than latches and are used in concurrent programming to avoid the simultaneous use of a common resource.

Oracle started using mutexes from 10g for certain operations in the library cache but from 11g all library cache latches were replaced by mutexes which lead to the common to all DBAs “library cache: mutex X” concurrency event:

The irony here is that the reason for obtaining a mutex is to ensure that certain operations are properly managed for concurrency but the management itself leads to concurrency. These are the top 3 differences between mutexes and latches:

– A mutex can protect a single structure, latches often protect many structures
– A mutex get is about 30-35 instructions in the algorithm, compared to 150-200 instructions for a latch get
– A mutex is around 16 bytes in size, compared to 112-200 bytes for a latch

Last month, I found a very interesting and detailed article by Anju Garg on latches and mutexes. It is worth reading.

The aim of this post is to suggest on how to proceeded if you hit the “library cache: mutex X” concurrency event which simply means that a library cache operation is being performed and Oracle is trying to get the library cache mutex in eXclusive mode.

Do the following:

1. Run a query against the x$kglob table to find the names of the objects being pinned most of the time. x$kglob is the resource structure table for library cache locks and pins. Jagjeet’s note gives a very good example.

select * from (
 select case when (kglhdadr = kglhdpar) then 'Parent' else 'Child '||kglobt09 end cursor,
 kglhdadr ADDRESS, substr(kglnaobj,1,20) NAME, kglnahsh HASH_VALUE, kglobtyd TYPE, 
 kglobt23 LOCKED_TOTAL, kglobt24 PINNED_TOTAL,kglhdexc EXECUTIONS, kglhdnsp NAMESPACE
 from x$kglob -- where kglobtyd != 'CURSOR'
order by kglobt24 desc)
where rownum <= 20;

2. If you have issues with PL/SQL packages and procedures, use dbms_shared_pool.markhot() to mark them as hot. As suggested by Andrey Nikolaev, Oracle creates multiple copies of marked objects in the library cache, but frequently uses only one of them. That is why, in order to achieve uniform distribution, mark the PL/SQL objects as hot immediately after the instance restart.

The documentation for the dbms_shared_pool.markhot procedure can be found here.

3. If the issue is sequence related, then either verify with Oracle that it is not a bug, get rid of the sequence if possible or at least modify its parameters (cache, order, etc.).

As you can see from Dom Brook’s article, issues are often package related. Have a look at my screenshot from ASH. The data source is V$ACTIVE_SESSION_HISTORY.

You may review on Metalink “Waitevent: library cache: mutex X” (Document ID 727400.1) for the list of known Oracle bugs but for all I have seen, sometimes patches do not help, so finding a workaround is all the DBA is left with. An ASH report will be very helpful:

If you see “library cache: mutex X” as a top event, note that ADDM will first find that there is contention for latches related to the shared pool that were consuming significant
database time (no surprise). For the top SQL statements, ADDM will claim something like “Waiting for event library cache: mutex X in wait class ‘Concurrency’ accounted for 97% of the database time spent in processing the SQL statement with SQL_ID d2svmdafangyq.” Look then at the “Rationale” sections and hunt for the names of the PL/SQL packages 🙂