"v$open_cursor.sql_text showing "table_4_200_5e1

-- Thanks for the question regarding "v$open_cursor.sql_text showing "table_4_200_5e14_0_0_0 "", version 8.1.7

Submitted on 6-Apr-2002 23:29 Central time zone

Tom's latest followup |  | 

Last updated 12-Dec-2006 22:03

You Asked

I am trying to figure out why v$open_cursor.sql_text is showing things like 
"table_4_xxxx_x_x_x_x", the reason being that these entries in v$open_cursor has the most 
number of entries in v$open_cursor. database clients are JDBC Thin drivers against Oracle 
8.1.7.x on Solaris 8.

Here are some additional information:

* No middle tier. We are not using any App server / EJB. It is straight SQL via JDBC Thin 
driver.

* The Java client is not using any temporary tables. 

* The Java client is also not calling any PL/SQL procedures. It was already like that 
before I started working on this. 

I do use PL/SQL for the aggregation stuff ran via DBMS_JOB where we aggregate all the 
data on an hourly basis. The reason PL/SQL was used because I worked on it :) In any 
case, the user doing the aggregation is not the same user showing these weird entries in 
sql_text.


This is the query that I use ( I assume this is what you meant by "putting the entire 
query in there, not just the table name" ):

select user_name, count(1) as count, sql_text 
from sys.v_$open_cursor 
group by user_name, sql_text
order by count;

USER_NAME                           COUNT SQL_TEXT
------------------------------ ---------- 
------------------------------------------------------------
ORACLE                                  1 select user_name, count(1) as count, sql_text 
from sys.v_$op
SGMASTER                                1 SELECT PROPERTY_NAME,PROPERTY_VALUE FROM 
SERVICE_PROVIDER_PR
SGMASTER                                1 SELECT PROPERTY_NAME,PROPERTY_VALUE,PLATFORM_ID 
FROM SERVICE
SGMASTER                                1 select * from player where player_id=93357
SGMASTER                                1 select count(initiator_id) from Game where 
initiator_id=1138
SGMASTER                                1 Select  object_name ObjectName, object_type 
ObjectType  from
SGMASTER                                5 INSERT INTO player_session (session_id, 
creation_time, sessi
SGMASTER                                7 table_4_200_5ee2_0_0_0
SGMASTER                               12 table_4_2000_5ed8_0_0_0
SGMASTER                               12 table_4_200_5ed8_0_0_0
SGMASTER                               12 table_e_400_5ed8_2_0_0

USER_NAME                           COUNT SQL_TEXT
------------------------------ ---------- 
------------------------------------------------------------
SGMASTER                               17 table_4_2000_5e14_0_0_0
SGMASTER                               17 table_4_200_5f1b_0_0_0
SGMASTER                               17 table_e_400_5f1b_3_0_0
SGMASTER                               17 table_e_400_5e14_a_0_0
SGMASTER                               17 table_4_2000_5f1b_0_0_0
SGMASTER                               17 table_4_200_5e14_0_0_0


Since these tables are not really tables ( nor are they real objects in the sense that 
they are not in ALL_OBJECTS ), here is what I can just assume:

1) Parallel query execution -- I had a few tables / indexes that had a DEGREE of default. 
But even after ALTERing them to be NOPARALLEL ( and even restarting the clients using 
these tables ), the entries are still there.

2) Partitioned tables -- Although I have partitioned tables, these tables are owned by 
another user and are not accessed from the offending client.

3) (B)LOB -- Recalling that LOBs are moved out of the table ( but still in the tablespace 
) when the size gets to 4K or more, this is the most likely candidate, although the 
documentation does not specifically say how LOBs are named when stored outside of the 
table.
However, I also recall reading the LOBs are named with something like SYS$xxxxx.

4) Implicit cursors -- I doubt it, since the Java client hardly makes any PL/SQL calls. 
They are SELECTs, INSERTs, UPDATEs, DELETEs done using a PreparedStatement.

Is there also any way of showing the REAL sql that is generating these 
"table_4_xxx_x_x_x_x" entries in v$open_cursor?

 

and we said...

Ok -- in the never ending saga of "every day you learn something new" here we go (yes, I 
never really noticed them before as I don't use nested tables as a physical storage 
mechanism myself).

They have to do with implicit cursors surrounding nested table types!

Here is an example (done in a clean schema, I'm the only one logged on -- so the 
v$open_cursor query is "safe" -- no other sesssions owned by me)



ops$[e-mail]> connect /
Connected.
ops$[e-mail]> 
ops$[e-mail]> drop table t;
Table dropped.

ops$[e-mail]> drop type myTableType;
Type dropped.

ops$[e-mail]> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

that cleaned everything out for us...  Now, lets reconnect and see what happens

ops$[e-mail]> connect /
Connected.

ops$[e-mail]> select sql_text
  2    from v$open_cursor
  3    where user_name = user and sql_text like 'table%';

no rows selected

nothing there yet...

ops$[e-mail]> create or replace type myTableType as table of number
  2  /

Type created.

ops$[e-mail]> select sql_text
  2    from v$open_cursor
  3    where user_name = user and sql_text like 'table%';

SQL_TEXT
------------------------------------------------------------
table_1_0_10e_0_0_0

Ah-Hah -- there we go, we have that mysterious statement in there.  Its all about 
nested table types..

ops$[e-mail]> create table t ( x int, y myTableType ) nested table y 
store as y_tab;

Table created.

ops$[e-mail]> select sql_text
  2    from v$open_cursor
  3    where user_name = user and sql_text like 'table%';

SQL_TEXT
------------------------------------------------------------
table_1_0_10e_0_0_0
table_1_0_116_0_0_0
table_1_0_112_0_0_0

and now we have MORE of them..

ops$[e-mail]> insert into t values ( 1, myTableType( 1, 2, 3 ) );

1 row created.

ops$[e-mail]> select sql_text
  2    from v$open_cursor
  3    where user_name = user and sql_text like 'table%';

SQL_TEXT
------------------------------------------------------------
table_1_0_10e_0_0_0
table_1_0_116_0_0_0
table_1_0_112_0_0_0

ops$[e-mail]> 
ops$[e-mail]> select * from t;

         X
----------
Y
------------------------------------------------------------------------------------------
-----------------------------------------
         1
MYTABLETYPE(1, 2, 3)


ops$[e-mail]> select sql_text
  2    from v$open_cursor
  3    where user_name = user and sql_text like 'table%';

SQL_TEXT
------------------------------------------------------------
table_1_0_10e_0_0_0
table_1_0_116_0_0_0
table_1_0_112_0_0_0

ops$[e-mail]> 


now, instead of just creating them and all, lets see what would happen day to day:

ops$[e-mail]> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
ops$[e-mail]> connect /
Connected.
ops$[e-mail]> select sql_text
  2  from v$open_cursor
  3   where user_name = user and sql_text like 'table%';

no rows selected

ops$[e-mail]> save a rep
Wrote file a
ops$[e-mail]> select * from t;

         X
----------
Y
------------------------------------------------------------------------------------------
-----------------------------------------
         1
MYTABLETYPE(1, 2, 3)


ops$[e-mail]> @a
ops$[e-mail]> select sql_text
  2  from v$open_cursor
  3   where user_name = user and sql_text like 'table%'
  4  /

SQL_TEXT
------------------------------------------------------------
table_1_0_10e_0_0_0
table_1_0_116_0_0_0
table_1_0_112_0_0_0

so, they are a natural side effect of using nested tables.  Now, why are the "open" -- 
well, they use the same cursor sharing technique as PLSQL does.  In PLSQL when you close 
a cursor -- it'll still (most likely) be there in v$open_cursor which should really be 
called "v$MOST_LIKELY_STILL_open_cursor" -- the cursors for the nested table access are 
still there but -- they are replaceable -- they will go away if someone else needs that 
slot (eg: they do not count against your open_cursor init.ora setting)

So, thats the mystery here, they are implicit sql (magic sql apparently) to do with 
nested table types.  They will go away as their slots are needed, they are not "leaked" 
cursors (which is probably what you were looking for).

 

Reviews

But we don't use PL/SQL nor do we use nested tables??   April 7, 2002 - 10pm Central time zone  |  |  Reviewer: A reader from Sydney, Australia I have checked and I am pretty sure we do not use nested tables SQL> select distinct object_type from user_objects 2 3 SQL> / OBJECT_TYPE ------------------ INDEX LOB PROCEDURE SEQUENCE SYNONYM TABLE VIEW I know user SYS does, but we do not use them at all: SQL> select distinct object_type from all_objects where owner = 'SYS' 2 SQL> / OBJECT_TYPE ------------------ CONSUMER GROUP FUNCTION JAVA CLASS JAVA RESOURCE PACKAGE PROCEDURE TABLE TYPE VIEW Followup   April 8, 2002 - 8am Central time zone: What does: ops$[e-mail]> select owner, count(*) from dba_nested_tables group by owner; OWNER COUNT(*) ------------------------------ ---------- OPS$TKYTE 1 ops$[e-mail]> select owner, typecode, count(*) from dba_types group by owner, typecode; OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- APPS OBJECT 1 CTXSYS COLLECTION 1 CTXSYS OBJECT 4 OPS$TKYTE COLLECTION 1 SCOTT COLLECTION 1 SYS COLLECTION 10 SYS OBJECT 28 SYSTEM COLLECTION 1 SYSTEM OBJECT 1 TEST COLLECTION 1 TEST OBJECT 1 TYPES COLLECTION 1 BFILE 1 BINARY ROWID 1 ..... return (your query above is inconclusive -- nested tables are TABLES -- the object type is TABLE). You need not use plsql to use nested tables -- I did not use PLSQL at all in my example, only SQL. Also, if you use any number of features, you might be using them and not even be aware of it. interMedia, spatial, XML stuff -- lots of nested tables out there.  nested tables   April 8, 2002 - 2pm Central time zone  |  |  Reviewer: rinku from ca hi tom what are nested tables rinku Followup   April 8, 2002 - 4pm Central time zone: http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76976/adobjint.htm#452711 covers that.  Still no nested tables or collections   April 8, 2002 - 7pm Central time zone  |  |  Reviewer: A reader from Sydney Australia Hi Tom. Thanks for your replies. Unfortunately, I still have not figured out which object is actually causing this. ( See the results of my query below ). We are also not using any of those other features that may cause these entries in sql_text EXCEPT for Parallel Query and Table Partitioning. I have also checked and rechecked all of the Java code. SQL> select owner, count(*) from dba_nested_tables group by owner; 2 no rows selected SQL> select owner, typecode, count(*) from dba_types group by owner, typecode; 2 OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- SYS COLLECTION 10 SYS OBJECT 28 BFILE 1 BINARY ROWID 1 BLOB 1 CANONICAL 1 CFILE 1 CHAR 1 CLOB 1 CONTIGUOUS ARRAY 1 DATE 1 OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- DECIMAL 1 DOUBLE PRECISION 1 FLOAT 1 INTEGER 1 INTERVAL DAY TO SECOND 1 INTERVAL YEAR TO MONTH 1 LOB POINTER 1 NAMED COLLECTION 1 NAMED OBJECT 1 NUMBER 1 OCTET 1 OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- OID 1 PL/SQL BINARY INTEGER 1 PL/SQL BOOLEAN 1 PL/SQL COLLECTION 1 PL/SQL LONG 1 PL/SQL LONG RAW 1 PL/SQL NATURAL 1 PL/SQL NATURALN 1 PL/SQL PLS INTEGER 1 PL/SQL POSITIVE 1 PL/SQL POSITIVEN 1 OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- PL/SQL RECORD 1 PL/SQL REF CURSOR 1 PL/SQL ROWID 1 PL/SQL STRING 1 POINTER 1 RAW 1 REAL 1 REF 1 SIGNED BINARY INTEGER(16) 1 SIGNED BINARY INTEGER(32) 1 SIGNED BINARY INTEGER(8) 1 OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- SMALLINT 1 TABLE 1 TIME 1 TIME WITH TZ 1 TIMESTAMP 1 TIMESTAMP WITH LOCAL TZ 1 TIMESTAMP WITH TZ 1 UNSIGNED BINARY INTEGER(16) 1 UNSIGNED BINARY INTEGER(32) 1 UNSIGNED BINARY INTEGER(8) 1 VARCHAR 1 OWNER TYPECODE COUNT(*) ------------------------------ ------------------------------ ---------- VARCHAR2 1 VARYING ARRAY 1 57 rows selected. Followup   April 8, 2002 - 8pm Central time zone: do you use JMS on Oracle? AQ (advanced queues): ops$[e-mail]> select type_name from dba_types 2 where owner = 'SYS' and typecode = 'COLLECTION'; TYPE_NAME ------------------------------ AQ$_SUBSCRIBERS AQ$_RECIPIENTS AQ$_HISTORY AQ$_DEQUEUE_HISTORY_T AQ$_JMS_USERPROPARRAY DBMS_DEBUG_VC2COLL ODCICOLINFOLIST ODCIRIDLIST ODCIOBJECTLIST ODCIARGDESCLIST 10 rows selected. The types are all surrounding that. My question to you -- since this is recursive sql done by Oracle under the covers and is not really taking up a slot in the open_cursors -- why is this such a concern? It is normal, it is safe, it is not harmful...  Is it really a nested table ?   April 9, 2002 - 5am Central time zone  |  |  Reviewer: Le Crapaud from Paris Hello, I have found the same problem. In your example, you showed 'table_1_*' which was generated by nested table use. I have only 'table_4_*' (not 'table_1_*'). And we don't use nested tables. How can we know which particular object is causing this behavior ? Thanks, El Crapaud Followup   April 9, 2002 - 8am Central time zone: Ok, it looks like TABLE_1_* are nested tables. TABLE_4_* are lobs: [e-mail]> create table t ( x clob ); Table created. [e-mail]> select sql_text from v$open_cursor; SQL_TEXT ------------------------------------------------------------ select sql_text from v$open_cursor [e-mail]> insert into t values ( 'hello world' ); 1 row created. [e-mail]> select sql_text from v$open_cursor; SQL_TEXT ------------------------------------------------------------ select sql_text from v$open_cursor [e-mail]> select * from t; X -------------------------------------------------------------------------------- hello world [e-mail]> select sql_text from v$open_cursor; SQL_TEXT ------------------------------------------------------------ table_4_200_735d_0_0_0 select sql_text from v$open_cursor [e-mail]> it is a "lob" object.  Just Great !   April 9, 2002 - 8am Central time zone  |  |  Reviewer: Le crapaud from Paris  Thanks.   April 9, 2002 - 9pm Central time zone  |  |  Reviewer: John Salvo from Sydney Australia OK. So my initial asssumption is correct. LOBs are causing these table_4_***. Now I just need to find out what are those table_e_***. :)1 I know it is not harmful, but I need to know what these are. Followup   April 10, 2002 - 8am Central time zone: And that would appear to be a lob that was opened for writing to: [e-mail]> create table t ( x clob ); Table created. [e-mail]> insert into t values ( 'hello' ); 1 row created. [e-mail]> begin 2 for x in (select x from t for update) 3 loop 4 dbms_lob.writeappend( x.x, length( 'hey' ), 'hey' ); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. [e-mail]> select sql_text from v$open_cursor; SQL_TEXT ------------------------------------------------------------ select sql_text from v$open_cursor table_e_400_743b_1_0_0 [e-mail]>  To the original statment   April 12, 2002 - 5am Central time zone  |  |  Reviewer: Le Crapaud from Paris Tom, We know (thanks to you) 'table_4*' in v$open_cursor is due to a SELECT on a lob. Fine. But how can we extract this SELECT ? I tried to look at V$SQLTEXT but can't find anything. That would be very useful for us. Thanks in advance. Followup   April 12, 2002 - 8am Central time zone: I know of no way to tie that back to the original "select" -- that select doesn't even have to exist anymore -- o the app could close the cursor o the sql could be aged out of the shared pool Interestingly -- the hex number seems to be object_id of the table, consider: [e-mail]> create table t ( x clob, y clob ); Table created. [e-mail]> [e-mail]> insert into t values ( 'eh', 'he' ); 1 row created. [e-mail]> [e-mail]> select * from t; X -------------------------------------------------------------------------------- Y -------------------------------------------------------------------------------- eh he [e-mail]> [e-mail]> select sql_text from v$open_cursor; SQL_TEXT ------------------------------------------------------------ select sql_text from v$open_cursor table_4_200_5b22_0_0_0 11 rows selected. [e-mail]> select to_number( '5b22', 'xxxx' ) from dual; TO_NUMBER('5B22','XXXX') ------------------------ 23330 [e-mail]> select object_name, object_type from user_objects where object_id = 23330; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------ T TABLE [e-mail]> do that over and over -- the hex number changes and is always the object id of the base table.  cool, man !   April 12, 2002 - 9am Central time zone  |  |  Reviewer: Le Crapaud from Paris  Interesting   July 9, 2003 - 12am Central time zone  |  |  Reviewer: Doug from CT, USA Very interesting - I just saw some cursors in a DB like table_4_2000_e7a_0_0_0 table_e_400_e7a_7_0_0 table_4_200_e7a_0_0_0 and this thread helped me out in 2 minutes.  Very interesting!   October 28, 2003 - 3am Central time zone  |  |  Reviewer: Eric Mortensen from Oslo, Norway I see these 'table_4_*' entries in Toad all the time. Always wondered what they are, and now I know :-) Thanks!  extremely useful thread!   July 19, 2004 - 10am Central time zone  |  |  Reviewer: Mihail Daskalov from Sofia, Bulgaria  Concise and to the point   March 10, 2005 - 10am Central time zone  |  |  Reviewer: ssh from us  Why are implicit clob cursors not shared with nls_length_semantics=char ?   June 22, 2005 - 4am Central time zone  |  |  Reviewer: Vincent PARDOUX from Marseille, France Thank you very much for explaining what table_4_ cursors are. I have a high number of such cursors with the same name and a high number of reloads. I traced back the cause of clob cursors not being shared among sessions to parameter nls_length_semantics=char. To prove my point you can run the following script on a sample 9.2 database. With nls_length_semantics=char you will get one more line in v$db_object_cache each time you query the clob table from a different session. On the contrary, with nls_length_semantics=byte, the number of lines will remain 2. Note : After changing the parameter don't forget to restart the database. How can you explain this behavior ? What is the connection between this nls_length_semantics and clobs ? Thanks in advance. connect system/&pwd create table mytable(id number, txt clob); insert into mytable values(1,rpad('a',10000, 'a')); insert into mytable values(2,rpad('b',10000, 'b')); commit; column obj_id new_value obj_id select lower(trim(to_char(object_id, 'XXXXXX'))) obj_id from dba_objects where object_name = 'MYTABLE'; select * from v$db_object_cache where name like 'table\_4\_%\_&obj_id\_%' escape '\'; select * from mytable; select * from v$db_object_cache where name like 'table\_4\_%\_&obj_id\_%' escape '\'; connect system/&pwd select * from mytable; select * from v$db_object_cache where name like 'table\_4\_%\_&obj_id\_%' escape '\'; Followup   June 22, 2005 - 7am Central time zone: you are looking in the wrong view, v$sql would be where to look for child cursors. On my system, I end up with 2 rows in v$db_object_cache regardless of the setting. It is normal. It'll cycle out as needed.  v$sql   June 22, 2005 - 12pm Central time zone  |  |  Reviewer: Vincent PARDOUX from Marseille, France Sorry, but there is no trace of any implicit lob cursors with names like table_4_ in v$sql whereas there are some in v$open_cursor (1 line per cursor) and many by the same name in v$db_object_cache. What should the WHERE clause be like to find implicit lob cursors in v$sql ? By the way, which database version did you use ? Thanks in advance. Followup   June 23, 2005 - 1pm Central time zone: i used 9ir2 are you hitting an issue? using more memory? you are not "not sharing" cursors, that much is sure. help me see the "issue" you are having, not the symptoms.  clob cursors not shared   June 24, 2005 - 6am Central time zone  |  |  Reviewer: Vincent PARDOUX from Marseille, France The issue is a sudden rise of latch free waits on the library cache from zero to a cumulated 30 sec per sec when the application is tested with hundreds of users querying the clob column. This occur suddenly 15 min after a database startup or a shared pool flush. At this stage the shared pool is less than 20% used. After 1 hour under this load the number of reloads on the SLQ AREA is over a million and this figure is 100% consistent with the cumulated number of reloads on "table_4_..." cursors. Thanks to you I know what these cursors are and I know that the cause is LOB cursors not being shared. I am very surprised that my simple test case doesn't work on your database. Although nls_length_semantics can be changed dynamicaly, I can only see the change on cursor sharing after a restart of the database (maybe that's the reason why you didn't reproduce the problem). I have opened a TAR on this issue so that an analyst can "see" the problem on my database through a web conference. In the meantime any suggestion will be very much appreciated. Thanks again.  table_X_200_d9_0_0_0   February 3, 2006 - 3pm Central time zone  |  |  Reviewer: Neeraj Nagpal from LA, CA Tom, I have a very large query (meant to be executed one time only), which selects data from a large table, by doing a full table scan (FTS is OK because I don't have any indexes on the selected columns and never will have after running this query), it then -- goes out and does an inner join on itself and then stores the output of the query into a table. The table Properties has about 100 Million rows in it. So, if you see the execution plan of the query, it does the FTS on the table twice and then does a merge join. NOW MY QUESTION: while monitoring this job I noticed two work tables created by Oracle, with names like table_4_2000_d9_0_0_0,(I guess to do a merge join ??). Could you please let me know what these tables are for? Can I some how see what's in these tables?? I am not using any nested tables or LOB's any where, although this table is a hash partitioned table with 50 partitions and 32 subpartitions in them. INSERT INTO duplicate_zips (fips_state_county1, fips_state_county2, std_zip_code, datetime_last_update ) SELECT /*+ FIRST_ROWS */ t1.fips_state_county AS fips1, t2.fips_state_county AS fips2, t2.zip_code_5 zip2, SYSDATE FROM properties t1, properties t2 WHERE t1.zip_code_5 = t2.zip_code_5 AND t1.fips_state_county <> t2.fips_state_county AND t1.zip_code_5 <> '00000' AND t2.zip_code_5 <> '00000' Execution Plan ---------------------------------------------------------- 0 null SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1549687 Ca null rd=327301595671 Bytes=7200635104762) 1 0 MERGE JOIN (Cost=1549687 Card=327301595671 Bytes=720063510 null 4762) 2 1 SORT (JOIN) (Cost=774844 Card=92345063 Bytes=1015795693) null 3 2 PARTITION RANGE (ALL) null 4 3 PARTITION HASH (ALL) null 5 4 TABLE ACCESS (FULL) OF 'PROPERTIES' (Cost=421431 C null ard=92345063 Bytes=1015795693) 6 1 FILTER null 7 6 SORT (JOIN) null 8 7 PARTITION RANGE (ALL) null 9 8 PARTITION HASH (ALL) null 10 9 TABLE ACCESS (FULL) OF 'PROPERTIES' (Cost=421431 null Card=92345063 Bytes=1015795693) SQL> select sid,sql_text from v$open_cursor where user_name = user and sql_text like 'table%' and sid = 42 order by sid SQL> / SID SQL_TEXT ---------- ------------------------------------------------------------ 42 table_4_200_d9_0_0_0 42 table_4_2000_d9_0_0_0 Please let me know if you need more info about this. Thanks For your help, Neeraj Followup   February 3, 2006 - 5pm Central time zone: ctl-f for strange entries  LOBs   February 3, 2006 - 7pm Central time zone  |  |  Reviewer: A reader  Tom, If I understood it correctly, then these strange table entries pertain to the LOB tables. And, like I said in my previous post, I am not using any LOB's or Nested tables anywhere. Please suggest. Thanks, Neeraj Followup   February 5, 2006 - 11pm Central time zone: perhaps elsewhere in your session you are - they do not need to have come from THAT sql statement you are looking at. suggestion: if this is reproducible, do your work with sql_trace=true and you might find the sql in the resulting trace file to be very useful to see everything that is happening in your session (include our own recursive sql that is executed)  Are lob retrivers never shared   December 11, 2006 - 7pm Central time zone  |  |  Reviewer: Mathias from Denver, CO We're finding many LOB cursors for the table_4 cursors. Each connection gets two. Is this typical or should they be shared? If we reconnect from a process, then it gets two more. These are special, but should they not be shared between sessions? The version is 10g. Followup   December 11, 2006 - 7pm Central time zone: every session needs its own "cursors", cursors are "private" sql is shared, cursors - very much not so.  Does this mean v$sql_shared_cursor   December 11, 2006 - 8pm Central time zone  |  |  Reviewer: Mathias from Denver, CO Does this mean that growth in the v$sql_shared_cursor with two per session that retrieves LOBs is to be expected? They get flagged with auth_check_mismatch and language_mismatch. I believe the reason for two is that we use anonymous PL*SQL to retrieve as well as direct call from PRO*C. Does that make sense or is it something we should be concerned with? Followup   December 12, 2006 - 6am Central time zone: are you seeing them in v$sql?  SQL not in V$SQL   December 12, 2006 - 9am Central time zone  |  |  Reviewer: Mathias from Denver, CO No these "SQL" ids for LOB retrivers does not show up in V$SQL. I thought it was due to it not being "real" SQL, but a background function. They show up here and we have around 2500 most of the time as processes reconnects every now and then. The theory that was presented was that this reflected parsing and that was why our library cache use is out of control. I'm not sure as it doesn't seem like these 2500 would be enough to take over a 24 CPU server. Something happens that makes the library cache go super active, but we have not been able to find out what and why. Do you think the entries in v$sql_shared_cursor is a dead end or is it something worth looking more at? As cursors are not shared, what does this view do? Can you explain what the language_mismatch and auth_check_mismatch means? The language one is interpreted by some in our group to refer to NLS. I don't think it does, but we have not found any documentation for it. Followup   December 12, 2006 - 10pm Central time zone: they are open lobs - we need the handles in the program. they are not really "shared cursors".  To Mathias: LOB pseudo-cursors not shared?   December 13, 2006 - 12am Central time zone  |  |  Reviewer: Roderick  You may want to open an SR with Oracle Support and see if you might be running into bug 4238592.    April 16, 2009 - 1pm Central time zone  |  |  Reviewer: Kerry Osborne from Texas Thanks Tom. I know this is a pretty old post, but I found it very useful. I had a sql_id showing up in ASH that was responsible for a lot of waits. Couldn't find the sql_text to go with it. Found the entry for it in V$OPEN_CURSOR (table_4...) which led me to this post. This discussion gave me the clue I needed.  I was able to find the original SQL statement which was responsible for the LOB access, by the way. I posted about it here if anyone is interested in more details:  The short answer is that you should be able to find the original sql_id in the PREV_SQL_ID field in V$SESSION, assuming you can find a session that last executed the sql_id of interest. Something like: select prev_sql_id from v$session where sql_id = 'sql_id_for_lob_access' Then of course you can get the text from the normal shared pool views (v$sqlarea,v$sql, v$sqltext, etc...).