Skip to Main Content
  • Questions
  • v$open_cursor.sql_text showing "table_4_200_5e14_0_0_0 "

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 06, 2002 - 11:29 pm UTC

Last updated: December 12, 2006 - 10:03 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

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 Tom 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$tkyte@ORA817DEV.US.ORACLE.COM> connect /
Connected.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop type myTableType;
Type dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM> connect /
Connected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM> create or replace type myTableType as table of number
2 /

Type created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y myTableType ) nested table y store as y_tab;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, myTableType( 1, 2, 3 ) );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

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


ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM>


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

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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$tkyte@ORA817DEV.US.ORACLE.COM> connect /
Connected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select sql_text
2 from v$open_cursor
3 where user_name = user and sql_text like 'table%';

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> save a rep
Wrote file a
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

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


ops$tkyte@ORA817DEV.US.ORACLE.COM> @a
ops$tkyte@ORA817DEV.US.ORACLE.COM> 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).



Rating

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

But we don't use PL/SQL nor do we use nested tables??

A reader, April 07, 2002 - 10:25 pm UTC

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

 

Tom Kyte
April 08, 2002 - 8:30 am UTC

What does:


ops$tkyte@ORA817DEV.US.ORACLE.COM> select owner, count(*) from dba_nested_tables group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
OPS$TKYTE                               1

ops$tkyte@ORA817DEV.US.ORACLE.COM> select owner, typecode, count(*) from dba_types group by owner, typecode;

OWNER                          TYPECODE                         COUNT(*)
------------------------------ ------------------------------ ----------
APPS                           OBJECT                                  1
CTXSYS                         COLLECTION                              1
CTXSYS                         OBJECT                                  4<b>
OPS$TKYTE                      COLLECTION                              1
SCOTT                          COLLECTION                              1
SYS                            COLLECTION                             10</b>
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

rinku, April 08, 2002 - 2:04 pm UTC

hi tom
what are nested tables
rinku

Still no nested tables or collections

A reader, April 08, 2002 - 7:42 pm UTC

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.



 

Tom Kyte
April 08, 2002 - 8:08 pm UTC

do you use JMS on Oracle?  AQ (advanced queues):


ops$tkyte@ORA817DEV.US.ORACLE.COM> 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 ?

Le Crapaud, April 09, 2002 - 5:15 am UTC

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

Tom Kyte
April 09, 2002 - 8:00 am UTC

Ok, it looks like TABLE_1_* are nested tables.

TABLE_4_* are lobs:


a@ORA817DEV.US.ORACLE.COM> create table t ( x clob );

Table created.

a@ORA817DEV.US.ORACLE.COM> select sql_text from v$open_cursor;

SQL_TEXT
------------------------------------------------------------
select sql_text from v$open_cursor

a@ORA817DEV.US.ORACLE.COM> insert into t values ( 'hello world' );

1 row created.

a@ORA817DEV.US.ORACLE.COM> select sql_text from v$open_cursor;

SQL_TEXT
------------------------------------------------------------
select sql_text from v$open_cursor

a@ORA817DEV.US.ORACLE.COM> select * from t;

X
--------------------------------------------------------------------------------
hello world

a@ORA817DEV.US.ORACLE.COM> select sql_text from v$open_cursor;

SQL_TEXT
------------------------------------------------------------
table_4_200_735d_0_0_0

select sql_text from v$open_cursor

a@ORA817DEV.US.ORACLE.COM>

it is a "lob" object.

Just Great !

Le crapaud, April 09, 2002 - 8:39 am UTC


Thanks.

John Salvo, April 09, 2002 - 9:47 pm UTC

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.

Tom Kyte
April 10, 2002 - 8:02 am UTC

And that would appear to be a lob that was opened for writing to:

a@ORA817DEV.US.ORACLE.COM> create table t ( x clob );
Table created.

a@ORA817DEV.US.ORACLE.COM> insert into t values ( 'hello' );
1 row created.

a@ORA817DEV.US.ORACLE.COM> 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.

a@ORA817DEV.US.ORACLE.COM> select sql_text from v$open_cursor;

SQL_TEXT
------------------------------------------------------------
select sql_text from v$open_cursor
table_e_400_743b_1_0_0

a@ORA817DEV.US.ORACLE.COM>



To the original statment

Le Crapaud, April 12, 2002 - 5:54 am UTC

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.



Tom Kyte
April 12, 2002 - 8:45 am UTC

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:

a@ORA9I.WORLD> create table t ( x clob, y clob );

Table created.

a@ORA9I.WORLD>
a@ORA9I.WORLD> insert into t values ( 'eh', 'he' );

1 row created.

a@ORA9I.WORLD>
a@ORA9I.WORLD> select * from t;

X
--------------------------------------------------------------------------------
Y
--------------------------------------------------------------------------------
eh
he


a@ORA9I.WORLD>
a@ORA9I.WORLD> 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.

a@ORA9I.WORLD> select to_number( '5b22', 'xxxx' ) from dual;

TO_NUMBER('5B22','XXXX')
------------------------
23330

a@ORA9I.WORLD> select object_name, object_type from user_objects where object_id = 23330;

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
T TABLE

a@ORA9I.WORLD>



do that over and over -- the hex number changes and is always the object id of the base table.

cool, man !

Le Crapaud, April 12, 2002 - 9:23 am UTC


Interesting

Doug, July 09, 2003 - 12:25 am UTC

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!

Eric Mortensen, October 28, 2003 - 3:52 am UTC

I see these 'table_4_*' entries in Toad all the time. Always wondered what they are, and now I know :-)

Thanks!

extremely useful thread!

Mihail Daskalov, July 19, 2004 - 10:21 am UTC


Concise and to the point

ssh, March 10, 2005 - 10:28 am UTC


Why are implicit clob cursors not shared with nls_length_semantics=char ?

Vincent PARDOUX, June 22, 2005 - 4:16 am UTC

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 '\';


Tom Kyte
June 22, 2005 - 7:04 am UTC

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

Vincent PARDOUX, June 22, 2005 - 12:04 pm UTC

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.

Tom Kyte
June 23, 2005 - 1:27 pm UTC

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

Vincent PARDOUX, June 24, 2005 - 6:31 am UTC

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

Neeraj Nagpal, February 03, 2006 - 3:03 pm UTC

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
 

Tom Kyte
February 03, 2006 - 5:28 pm UTC

LOBs

A reader, February 03, 2006 - 7:16 pm UTC

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

Tom Kyte
February 05, 2006 - 11:51 pm UTC

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

Mathias, December 11, 2006 - 7:42 pm UTC

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.

Tom Kyte
December 11, 2006 - 7:54 pm UTC

every session needs its own "cursors", cursors are "private"

sql is shared, cursors - very much not so.

Does this mean v$sql_shared_cursor

Mathias, December 11, 2006 - 8:38 pm UTC

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?

Tom Kyte
December 12, 2006 - 6:56 am UTC

are you seeing them in v$sql?

SQL not in V$SQL

Mathias, December 12, 2006 - 9:55 am UTC

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.

Tom Kyte
December 12, 2006 - 10:03 pm UTC

they are open lobs - we need the handles in the program. they are not really "shared cursors".

To Mathias: LOB pseudo-cursors not shared?

Roderick, December 13, 2006 - 12:30 am UTC

You may want to open an SR with Oracle Support and see if you might be running into bug 4238592.

Kerry Osborne, April 16, 2009 - 1:35 pm UTC

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:

http://kerryosborne.oracle-guy.com/2009/04/hidden-sql-why-cant-i-find-my-sql-text/

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...).

ORA-942 11gR2 BASELINE

Marcus Oliveira, June 28, 2012 - 12:11 pm UTC

Hi guys,

This table_4_whatever gives error ORA-942 in conjunction with SQL BASELINES in Oracle 11gR2 (11.2.0.3.2).

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=3hbmrh43j1qmt) -----
table_4_d_d0_0_0_0

select to_number( 'd0', 'xxxx' ) from dual;
> 208

select object_name, object_type from user_objects where object_id = 208;

> OBJECT_NAME OBJECT_TYPE
> SQLOBJ$DATA TABLE

Would you know something about it ?

Marcus

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.