Home » RDBMS Server » Performance Tuning » SGA related queries (Oracle 10.1.0.2.0)
SGA related queries [message #559230] Sat, 30 June 2012 00:51 Go to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Dear Friends

Oracle Version 10.1.0.2.0
OS - Windows XP

I have a couple of questions...I started reading about SGA and am little confused .

My init.ora :

 java_pool_size=62914560
 large_pool_size=41943040
 shared_pool_size=125829120


SQL>  show parameter spfile;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
spfile                               string                           G:\ORACLE\PRODUCT\10.1.0\DB_1\
                                                                      DATABASE\SPFILEORCL.ORA 


SQL> show parameter sga;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
lock_sga                             boolean                          FALSE
pre_page_sga                         boolean                          FALSE
sga_max_size                         big integer                      1002438656
sga_target                           big integer                      838860800


Before we had manual memory management.
Later i made it automated by setting the sga_target and sga_max_size.
But i didn't make the settings for java pool , large pool and shared pool 0 in init.ora ...

Doubt 1 :
Should I make them zero in init.ora or it doesnot matter as spfile is used for instance startup in my case ?

Doubt 2 :

My SGA size is 800 MB.
Now :

SQL> select pool,sum(bytes) from v$sgastat group by pool;

POOL         SUM(BYTES)
------------ ----------
java pool      62914560
large pool     25181860
shared pool   205520896
              542381484


That blank column in pool column is for what ? Is it the Null pool (means the redo and data buffers )


SQL> select sum(bytes) from v$sgastat;

SUM(BYTES)
----------
 835998800


means approx 797.8 MB

where my SGA is 800MB ...does that mean I very little free space left in SGA ....do i need to do something or is it fine..

Sorry for asking a lot !!
But need clarifications to make my concepts clear.

Thanks and Regards.
Re: SGA related queries [message #559233 is a reply to message #559230] Sat, 30 June 2012 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Is your version really 10.1.0.2.0? Post result of "select * from v$version"
2/ init.ora parameters are irrelevant if you start with a spfile

Doubt 1: shared_poool_size parameter gives then the minimal size of shared pool ot the actual size. large_pool_size should be set if you use something that uses it (RMAN, parallel query...)

Doubt 2:
Quote:
That blank column in pool column is for what ?

Just execute "select * from v$sgastat where pool is null;" and you will see.

Quote:
But need clarifications to make my concepts clear


Just read Database Concepts, the memory structure are explained is details, if you have more questions after this read then come back with it.

Regards
Michel
Re: SGA related queries [message #559235 is a reply to message #559233] Sat, 30 June 2012 01:13 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Thanks Michel

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production


I read somewhere when we switch from manual to automatic memory management , we have to set the sga_target and also we need
to set the various pools = 0 as we dont need to set them individually.
Is this statement correct ??

Thanks and Regards
Charu
Re: SGA related queries [message #559237 is a reply to message #559235] Sat, 30 June 2012 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not need to, you may.
Please read the Database Concepts manual, it is explained in details in its chapter 8 Memory Architecture.

Regards
Michel
Re: SGA related queries [message #559238 is a reply to message #559237] Sat, 30 June 2012 01:50 Go to previous message
charuamit
Messages: 121
Registered: January 2009
Senior Member
okee thxs Michel Smile
Previous Topic: tools for monitoring the load of the db
Next Topic: Slow db performance on Oracle 11.2.0.3
Goto Forum:
  


Current Time: Thu Dec 18 22:05:13 CST 2014

Total time taken to generate the page: 0.11600 seconds