Dear Experts,
RDBMS - 11.1.0.7, we stumbled upon an issue with database showing "Huge Concurrency Waits" (Based on OEM monitoring)
Concurrency waits are high when too many sessions of a specific SQL ID are active. Concurrency waits go down after business hours when number of sessions of that SQL ID reduce.
We have the SQL ID, but have hard time figuring out the underlying SQL Text (OEM, V$VIEWS everywhere it displays SQL Text as "UNKNOWN").
We found this SQL ID has something to do with one of the application LOB table based on the below query. The object name has hexadecimal number "496a8" when that converted to decimal number gives the object id of the table, queried that object ID from dba_tables, and I see the LOB table name. Please see below.
Any ideas on how to fetch the SQL Text for this SQL ID, these SQL's run instantly, not sure if tracing helps....any ideas please?
SQL> select kglnaobj from x$kglob where kglobt03 = 'a2w4u58vjs7tw';
KGLNAOBJ
--------------------------------------------------------------------------------
table_e_a_496a8_3_0_
table_e_a_496a8_3_0_
SQL> select object_name from dba_objects where data_object_id=300712;
OBJECT_NAME
--------------------------------------------------------------------------------
CUBE_SCOPE
Thanks for your help !!!