Re: v$sgastat question

A few additional exploratory questions:

Are you using Shared Server (aka Multi Threaded Server) connection model? Are you using RMAN?

If the answer to both the questions is no, then you are wating the memory by setting the large_pool_size. I don't know when you took the snapshot of the query, but if you took that after the database has been up for a while, it looks like the answer is no to both the questions.

If you are not using java then limit your java pool. There is a bare minimum it needs to start the database; not sure how much. In databases (8.1.7.4), I have set it at 32768, but could be less.

HTH Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, January 08, 2003 4:00 PM

> Hi:
>
> I am trying to learn how to make use v$sgastat info in our oracle 8173 db.
>
> Here is what I set for the instance:
>
> shared_pool_size 268435456
> shared_pool_reserved_size 13421772
> large_pool_size 614400
> java_pool_size 1024000
>
> I also got v$sgastat info:
>
> SQL> select * from v$sgastat;
>
> POOL NAME BYTES
> ----------- -------------------------- ----------
> fixed_sga 73888
> db_block_buffers 409600000
> log_buffer 1024000
> shared pool free memory 20906472
> shared pool miscellaneous 14755144
> shared pool event statistics per sess 399280
> shared pool db_files 72496
> shared pool transactions 112392
> shared pool fixed allocation callback 960
> shared pool KGK heap 6056
> shared pool PX subheap 10252
>
> POOL NAME BYTES
> ----------- -------------------------- ----------
> shared pool db_block_hash_buckets 931096
> shared pool db_block_buffers 6800000
> shared pool table columns 24328
> shared pool sessions 247940
> shared pool State objects 137424
> shared pool PL/SQL MPCODE 1623420
> shared pool trigger defini 2152
> shared pool type object de 31300
> shared pool ktprhtnew3 1760480
> shared pool SYSTEM PARAMETERS 64168
> shared pool PL/SQL DIANA 913464
>
> POOL NAME BYTES
> ----------- -------------------------- ----------
> shared pool dictionary cache 4276152
> shared pool PLS non-lib hp 2096
> shared pool DML locks 60480
> shared pool trigger inform 300
> shared pool KQLS heap 2055708
> shared pool library cache 90144620
> shared pool PX msg pool 64496
> shared pool sql area 132286312
> shared pool table definiti 4096
> shared pool processes 79600
> shared pool message pool freequeue 124552
>
> POOL NAME BYTES
> ----------- -------------------------- ----------
> shared pool KGFF heap 56060
> shared pool pl/sql source 5076
> large pool free memory 614400
> java pool free memory 1024000
>
> 37 rows selected.
>
> Is there anything here obvious a potential problem? Is there something
that
> can be improved based on the above info?
>
> TIA
>
> Guang Mei
>
> _________________________________________________________________
> Help STOP SPAM: Try the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: zlmei_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).