Message-ID: <3645.137.229.7.101.1197500578.squirrel@fnmpa.email.uaf.edu>
In-Reply-To: <2230.137.229.7.101.1196492470.squirrel@fnmpa.email.uaf.edu>
References:  <2230.137.229.7.101.1196492470.squirrel@fnmpa.email.uaf.edu>
Date: Wed, 12 Dec 2007 14:02:58 -0900 (AKST)
Subject: Re: Meaning of v$sqlarea.users_opening
From: "Mark Anderson" <fnmpa@uaf.edu>
To: oracle-l@freelists.org
User-Agent: SquirrelMail/1.4.8-4.0.1.el4
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: fnmpa@uaf.edu

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


--
http://www.freelists.org/webpage/oracle-l