OraFAQ Forum: Performance Tuning » SGA related queries

Home » RDBMS Server » Performance Tuning » SGA related queries (Oracle 10.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SGA related queries [message #559230]

Sat, 30 June 2012 00:51

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.

Report message to a moderator

Sat, 30 June 2012 01:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Sat, 30 June 2012 01:13

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

Report message to a moderator

Sat, 30 June 2012 01:48

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Sat, 30 June 2012 01:50

charuamit
Messages: 121
Registered: January 2009

Senior Member

okee thxs Michel Smile

Report message to a moderator

Previous Topic:	tools for monitoring the load of the db
Next Topic:	Slow db performance on Oracle 11.2.0.3

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 13:46:36 CST 2025