The hunt for the UNKNOWN SQL in Oracle

If you are looking at your AWR report or browsing thru performance views in your Oracle Enterprise Manager console, chances are, that some of those SQLs, eating at your CPU or I/O bandwidth are marked as UNKNOWN. This is frustrating, to say the least, but in cases when these mysterious queries are killing your database, it may become a matter of life and death to find out just what does the Oracle do.

Most commonly, these queries are LOB operations. That is, they represent reading and writing the actual CLOB or BLOB data by the appropriate oci functions. The question then stands: what table? And this is where this little post comes to the rescue.

We will start with the only thing we are able to get from the report--the SQL ID, which is a hashed fingerprint of our culprit. It looks something like "2v82uk48x55vt". Regular SQL queries can be viewed by querying the system view V$SQL (or GV$SQL in RAC environments).

SELECT SQL_TEXT
  FROM GV$SQL
 WHERE SQL_ID='2v82uk48x55vt';

This, however, will not work for those unknown SQLs. The query will come up empty. All hope is not lost yet, though. There are other places to look. One of them is the X$KGLOB table, which is part of the library cache.

SELECT KGLNAOBJ
  FROM X$KGLOB
 WHERE KGLOBT03 = '2v82uk48x55vt';

This query will yield a value, which on the first look, looks like gibberish. It will read something like "table_4_9_15f8c_0_0_". On the second look, this string containts one interesting part and that is the fourth underscore separated segment: 15f8c. This, in fact, is the hexadecimal representation of the object_id of the object involved in the operation. Now remains only to query the DBA_OBJECTS view to find out, which table is being operated on.

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  FROM DBA_OBJECTS
 WHERE OBJECT_ID=TO_NUMBER('15f8c','XXXXXXXX');

And there you have it, the schema and the name of the table, which was causing that unknown SQL in your stats.