Meaning of v$sqlarea.users_opening
- From: "Mark Anderson" <fnmpa@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 30 Nov 2007 22:01:10 -0900 (AKST)
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.
I thought I understood that the query above returns a value of 250 bytes
for each concurrent user for each cursor that the user has open. But the
continuously rising value returned by the query suggests that the database
is experiencing some combination of a continuously rising number of
database connections and/or a continuously rising number of open cursors
per connection. That should not be the case in this database. Is my
understanding of v$sqlarea.users_opening incorrect? Doesn't it represent
the number of open cursors for a given shared SQL?
N.B. The underlying intent here, in case that helps, is to deliberately
undersize the shared pool of a test database to "just large enough" so
that load tests will quickly place it under stress, but we will not be
hampered by continuous ORA-4031s.
Thank you,
Mark Anderson
University of Alaska
--
//www.freelists.org/webpage/oracle-l
Other related posts:
