Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2232297
  • 博文数量: 310
  • 博客积分: 6853
  • 博客等级: 准将
  • 技术积分: 2833
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-04 16:41
文章分类

全部博文(310)

文章存档

2013年(17)

2012年(42)

2011年(76)

2010年(71)

2009年(99)

2007年(2)

2006年(1)

2005年(2)

分类: Oracle

2010-06-03 15:53:49

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

 

Reviews   
4 stars 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$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
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. 

3 stars 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. 

4 stars 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$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...

 

4 stars 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:


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. 

5 stars Just Great !   April 9, 2002 - 8am Central time zone
 |  | 
Reviewer: Le crapaud from Paris


5 stars 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:

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> 

 

4 stars 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:

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. 

5 stars cool, man !   April 12, 2002 - 9am Central time zone
 |  | 
Reviewer: Le Crapaud from Paris


4 stars 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.  


5 stars 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! 


5 stars extremely useful thread!   July 19, 2004 - 10am Central time zone
 |  | 
Reviewer: Mihail Daskalov from Sofia, Bulgaria


4 stars Concise and to the point   March 10, 2005 - 10am Central time zone
 |  | 
Reviewer: ssh from us


5 stars 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. 

5 stars 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. 

5 stars 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. 


4 stars 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 

2 stars 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) 

5 stars 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. 

5 stars 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? 

5 stars 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". 

5 stars 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. 


4 stars   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...). 
阅读(1442) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~