Reducing the size of the UGA

Reducing the size of the UGA

Post by mark_sim.. » Sun, 31 Dec 1899 09:00:00



Is there an easy way to reduce the size of the UGA - the bit of the PGA
residing in the shared pool of the SGA when MTS is being used. The
application I start grabs about 2.5 Mb of UGA.

To what extent is the size of the UGA determined by:
(1) the operating system (HPuX 11)
(2) Oracle (v7.3.4) via INIT.ORA values such as OPEN_CURSORS and
SORT_AREA_SIZE
(3) the application itself.

We're trying to allow more users onto the machine but are running out
of memory because each application is grabbing so much of the SGA.

Thanks, Mark (not a DBA)

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Reducing the size of the UGA

Post by Mark D Powel » Sun, 31 Dec 1899 09:00:00




> Is there an easy way to reduce the size of the UGA - the bit of the
PGA
> residing in the shared pool of the SGA when MTS is being used. The
> application I start grabs about 2.5 Mb of UGA.

> To what extent is the size of the UGA determined by:
> (1) the operating system (HPuX 11)
> (2) Oracle (v7.3.4) via INIT.ORA values such as OPEN_CURSORS and
> SORT_AREA_SIZE
> (3) the application itself.

> We're trying to allow more users onto the machine but are running out
> of memory because each application is grabbing so much of the SGA.

> Thanks, Mark (not a DBA)

What is your init.ora setting for sort_area_size?  Every session
weather it ends up sorting or not reservers sort_area_size of memory.
With MTS this comes out of the shared pool.  If you are an OLTP then
your sort_area_size should not need to be larger than 128K.  You can
actually find this number in the ver 8 manuals; I think DBA Admin.

I do not know about the Open cursors parameter, but I believe that it
should not be a problem if your application is written such that it
makes re-use of its code in the SGA; that is you use bind variables in
place of constants in your SQL and avoid dynamic SQL since these
require re-pharsing with each execution.
--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. DB size increased after reducing fields size

Hello, I have this problem:

my DB (SQL2k) is about 5mb (4,4 +0,6 free) with about 6000 records in
a table and a 2000 in another one, plus some other minor tables.

I cleaned the tables, reducing all the nvarchar fields size that I had
previously set to 50 or more to 20, 10 ... ecc ecc
Then compacted the DB.

Now DB is bigger, 5,7 (5,0 +0,7). Tried again and the DB now is even
more bigger.
How can I recompact data? because compact in the Enterprise manager
seems not to work.

I can post the schema of the DB if it can help, but is very simple,
and I'm sure I've only forgot something.

Thanks!

2. Navigating Record Sets

3. ADP File Size reduced very much - but why?

4. Is it wrong to notify people of open systems?

5. Trying to reduce size of MASSIVE log file

6. Cannot start Monitor Server (ASE 11.9.2/Solaris 2.7), please advise

7. database size has been suddenly reduced

8. Need help with printing from report

9. Reducing the Size of Archive Logs

10. reduce database / file size

11. Can a device size be reduced?

12. DBCC command to reduce database size

13. How to reduce the size of the log?