-
Hi Fellows,
I have Two Questions, I could not figure it out, Please verify.
1) You all know we use Dynamic performance views and even synonyms and Global Views. Is there a way that I can find out the actual underlying tables for those performance views, Like V$Open_Cursors, V$session, V$Transaction etc etc... There are many But I need to see the Actual Tables that these views Defined on.
2) In V$open_Cursor, It display me even those statements that were used while ago. Our DB is using Java application and we have Limited to 400 Open Cursors. Can any one please tell me when does the Oracle Closes those cursors as every open Cursor is counted towards the Limited Number of Open Cursors.
Like Select Sysdate From Dual;
This will have a open cursor, when will it be closed. Please elaborate this.
Thanks in Advance.
-
For point 1), the following process may help:
a) find the name of the view to which the synonym points
e.g.
select table_owner, table_name from dba_synonyms
where synonym_name = 'V$SESSION';
TABLE_OWNER TABLE_NAME
------------------------------ ---------------
SYS V_$SESSION
b) get the text for the view definition
select text from dba_views
where owner = 'SYS'
and view_name = 'V_$SESSION';
TEXT
--------------------------------------------------------------------------------
select "SADDR","SID","SERIAL#","AUDSID","PADDR","USER#","USERNAME","COMMAND","OW
NERID","TADDR","LOCKWAIT","STATUS","SERVER","SCHEMA#","SCHEMANAME","OSUSER","PRO
CESS","MACHINE","TERMINAL","PROGRAM","TYPE","SQL_ADDRESS","SQL_HASH_VALUE","PREV
_SQL_ADDR","PREV_HASH_VALUE","MODULE","MODULE_HASH","ACTION","ACTION_HASH","CLIE
NT_INFO","FIXED_TABLE_SEQUENCE","ROW_WAIT_OBJ#","ROW_WAIT_FILE#","ROW_WAIT_BLOCK
#","ROW_WAIT_ROW#","LOGON_TIME","LAST_CALL_ET","PDML_ENABLED","FAILOVER_TYPE","F
AILOVER_METHOD","FAILED_OVER" from v$session
Sorry about the line wraps etc. but I'm sure you get the idea. Note also that the view selects from v$session - this is not recursive - as has been discussed at length in recent threads in this forum.
As for point 2), I am much less sure on this point, but I believe that unless the cursor is explicitly closed, it can be held open for the duration of the session that opened it. The best bet in any case is to ensure that the developers explicitly close all cursors that they open.
HTH
David.
[Edited by Dave_A on 04-02-2001 at 09:08 AM]
-
Well if you see v$fixed_table you can see the tables that build v$ Views however as you can see from the result those tables are pretty useless unless you get an internal guide from Oracle or from wherever you can since those table names are abbreavited. Why do you need to see these tables??? Even the attributes of these tables are abbreviated...
As for you second question, to check for your open cursor you should check with
select * from v$sysstat where statistic#=3
These will show how many cursors you have open in that precise instance. Sometimes v$open_cursor is kind of tricky since it stores the cached cursors as well (which are not actually open but cached)
We use Java application as well, WEB based and I was having the same worry like you
Also from my experience with Web Based application, those
select sysdate from dual j0nks are used by the application server connection pool to detect dead connections or similar, in our case WebLogic
[Edited by pando on 04-02-2001 at 09:22 AM]
-
Thanks Pando,
But You see there is no Difference in the Number of open Cursors, either you see from V$open_cursor or from V$sysstat ... Value is the same...
Even if the User Commits, it keeps there as in open Cursor. One thing for sure that all the open Cursors counted Towards the max Number of Open Cursors allowed in Parameter File.
Any idea, after how long the Oracle closes them, Developers close their Cursors but they still appear as Open.
I wanted to see the Underlying Tables to get the Bigger Picture, I know that those Views or Synonyms provide all necessary Info.... Any Idea where to Find.
Thanks again. Thanks David for Your Input.
-
Well, once a session issues a SQL stmt it seems like that cursor is left open until the session is disconnected (I know how to close an explicit cursor in PL/SQL but I have no clue how to close an inplicit cursor in SQL)
If you want an example of how v$open_cursor works you could check
http://asktom.oracle.com/pls/ask/f?p...D:553222846752
I had a nice mailing with Tom last week regarding this issue about open cursors and chached cursors. If you want I can pass the answers he gave me... Not 100% convincing to me but the only answers I could get
-
Yes,
I checked the Response regarding the Open Cursors, but I did not answer the Question.
Any way, the result from his query is always 1, Which does not seem to be true at any given Time.
select a.value, b.name from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
This Returns the Value one even the Value in v$sysstat is more than that and even in v$open_Cursor there are few Statements that seems to be Open.
Thanks
Sorry Dave the Way you said to get the Table name did not help me ... can you please try with V$OPEN_CURSOR... This seem to me Recursive.
-
btw the open_cursor in init.ora is per session , this open cursors issue is really confusing, I have asked Oracle support as well and they cant answer very well neither
-
Hi,
Here goes to get the table name and view definition:
1 select table_owner, table_name from dba_synonyms
2* where synonym_name = 'V$OPEN_CURSOR'
SQL> /
TABLE_OWNER TABLE_NAME
------------------------------ -----------------------
SYS V_$OPEN_CURSOR
So the view that the public synonym V$OPEN_CURSOR points to is the V_$OPEN_CURSOR view, owned by SYS. You can describe this view:
SQL> desc sys.v_$open_cursor
Name Null? Type
------------------------------- -------- ----
SADDR RAW(4)
SID NUMBER
USER_NAME VARCHAR2(30)
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_TEXT VARCHAR2(60)
To get the underlying table, query dba_views for the SQL text used to define the view:
SQL> select text from dba_views
2 where owner = 'SYS'
3 and view_name = 'V_$OPEN_CURSOR';
TEXT
--------------------------------------------------------------------------------
select "SADDR","SID","USER_NAME","ADDRESS","HASH_VALUE","SQL_TEXT" from v$open_c
ursor
From this, we can see that the view is based on the fixed table V$OPEN_CURSOR, owned by SYS. Because this is a fixed table, we can not simply use desc sys.v$open_cursor because we get the 'object does not exist' error message. Fixed tables are visible as the SYS user though, so if we connect as SYS (OK, I did it from connect internal in svrmgrl) and describe the table we get:
SVRMGR> desc sys.v$open_cursor;
Column Name Null? Type
------------------------------ -------- ----
SADDR RAW(4)
SID NUMBER
USER_NAME VARCHAR2(30)
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_TEXT VARCHAR2(60)
Although the original view has the same definition as the underlying table in this case, there is no recursion involved. The last descrive is of the underlying fixed table, where the first describe was on the view based on this table.
For a more detailed (and clearer) explanation on the apparent (but non-existant) recursion, see this thread http://www.dbasupport.com/forums/sho...?threadid=8006 where Jurij explains it very well.
HTH
David.
-
Dave I think OracleDBA is more interested in X$ tabels not the fixed views, X$ are the underlaying tables for V$ fixed views
-
In that case, I apologise for misunderstanding the question. The thread reference http://www.dbasupport.com/forums/sho...?threadid=8006 is still valid tho...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|