Re: Meaning of v$sqlarea.users_opening
- From: "Mark Anderson" <fnmpa@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Dec 2007 14:02:58 -0900 (AKST)
I can focus my earlier question somewhat. The continuously climbing value
of sum(250*users_opening) over time is because of a small number of very
large and increasing values of v$sqlarea.users_opening:
TEST> select * from (select users_opening, sql_text from v$sqlarea order
by users_opening desc) where rownum < 5
USERS_OPENING SQL_TEXT
------------- ------------------------------------------------------------
20612 begin bwgkjpay.P_Java_Client_Get_Request(:1,:2,:3,:4); end;
3559 SELECT sys_context(:b2,:b1) from sys.dual
3439 begin dbms_session.reset_package; end;
2019.5 SELECT SYSDATE FROM DUAL
TEST> select count(*) from v$session where username is not null;
COUNT(*)
----------
49
v$sqlarea.users_opening is defined in the Database Reference as
USERS_OPENING NUMBER Number of users that have any of the child cursors
open
Q1: What is the meaning of a fractional value of users_opening in the
fourth row of the results?
Q2: How can ~21000 users have a child cursor open for the SQL in the first
row of results when there are only 49 users logged in?
Thank you,
Mark Anderson
University of Alaska
> I wish to use the "Shared Pool Minimium Size Calculator" script in
> Metalink Note 105813.1 to find out how the shared pool of an Enterprise
> 9.2.0.8.0 database is divided up between objects like packages and views,
> SQL, and a third component that I don't understand very well, per-user
> per-cursor memory. The Calculator computes per-user per-cursor memory as
>
> select sum(250*users_opening) from v$sqlarea;
>
> I run the Calculator script every ten minutes through the week. The values
> of the first two components start low when the instance is restarted after
> its weekly cold backup and shortly level off. The per-user per-cursor
> memory value climbs continuously through the week.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
