OraFAQ Forum: Performance Tuning » Initialization parameters value

Home » RDBMS Server » Performance Tuning » Initialization parameters value (Oracle 10g, 10.2.0.4.0, Solaris 10)

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

Initialization parameters value [message #489009]

Wed, 12 January 2011 21:13

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Good day!

I'm just wondering why my SQLPlus queries for db_cache_size & shared_pool_size resulted in values of 0 for both, while it had values of 106MB & 380MB respectively declared in init.ora?

How can I synchronized these values?

Thanks in advance.

Report message to a moderator

Re: Initialization parameters value [message #489010 is a reply to message #489009]

Wed, 12 January 2011 21:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can I synchronized these values?
synchronize which values to what exactly?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

query one or more of the views below
V_$MEMORY_TARGET_ADVICE
V_$MTTR_TARGET_ADVICE
V_$PGA_TARGET_ADVICE
V_$PGA_TARGET_ADVICE_HISTOGRAM
V_$PX_BUFFER_ADVICE
V_$SGA_TARGET_ADVICE
V_$SHARED_POOL_ADVICE
V_$STREAMS_POOL_ADVICE
V_$JAVA_POOL_ADVICE
V_$DB_CACHE_ADVICE

Report message to a moderator

Re: Initialization parameters value [message #489013 is a reply to message #489010]

Wed, 12 January 2011 21:41

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Thanks for your prompt reply!

What I'm referring is that the init.ora values against the show paramaters (value) command in SQLPlus. Why is that they're not synchronized?

Report message to a moderator

Re: Initialization parameters value [message #489014 is a reply to message #489013]

Wed, 12 January 2011 22:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What doe Reading The Fine Manual reveal about these parameters?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

How can anyone reproduce what you report?

I don't know what you have.
I don't know what you do.
I don't know what you see.
use COPY & PASTE so we can see what you do & how Oracle responds.

Report message to a moderator

Re: Initialization parameters value [message #489019 is a reply to message #489014]

Wed, 12 January 2011 22:43

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Excerpts from initcrmpos.ora ...

crmpos.__db_cache_size=285212672
crmpos.__shared_pool_size=218103808
crmpos.__java_pool_size=8388608

Using SQLPlus ...

SQL> show parameter DB_CACHE_SIZE
NAME TYPE VALUE
--------------------------------- ----------- ------------------
db_cache_size big integer 0

SQL> show parameter SHARED_POOL_SIZE
NAME TYPE VALUE
--------------------------------- ----------- ------------------
shared_pool_size big integer 0

SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
--------------------------------- ----------- ------------------
java_pool_size big integer 0

Why is it that it displays zero when I queried values for db_cache_size, shared_pool_size & java_pool_size using SQLPlus when it was clearly declared in the initcrmpos.ora their corresponding values of 285212672, 218103808 & 8388608?

Report message to a moderator

Re: Initialization parameters value [message #489021 is a reply to message #489019]

Wed, 12 January 2011 22:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> sho parameter spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /u01/app/oracle/product/11.2.0
						 /dbhome_1/dbs/spfilev112.ora

Report message to a moderator

Re: Initialization parameters value [message #489028 is a reply to message #489021]

Wed, 12 January 2011 23:15

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Thanks for the info. I will update it later, but what went wrong here?

$ view spfilecrmpos.ora
"spfilecrmpos.ora" [Read only] 28 lines, 3584 characters (2331 null)
^G^B^B^DÝ+¸s^crmpos.__db_cache_size=109051904
crmpos.__java_pool_size=12582912
crmpos.__large_pool_size=4194304
crmpos.__shared_pool_size=390070272

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /appl1/home/oracle/10.2/dbs/sp
filecrmpos.ora
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0

Report message to a moderator

Re: Initialization parameters value [message #489029 is a reply to message #489019]

Wed, 12 January 2011 23:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

__* values are dynmaic values and should not be in an init.ora only in a spfile.
They are NOT values YOU give they are values Oracle computed.
YOU give 0 and this is what you see in "show parameter".

I bet you give *max_size or *target parameter(s), from them and the workload Oracle computes the other parameters.

Regards
Michel

Report message to a moderator

Re: Initialization parameters value [message #489048 is a reply to message #489028]

Thu, 13 January 2011 01:33

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Hi - you need to read up on the priority of parameter files. This is what you need to check: if both a static (or "init") file and a dynamic ("spfile") file exist, then which file will be used? Make sure you also work out how to instruct Oracle to use the init file, instead of the spfile fie.
As Michel says, the double under-score parameters are not manually entered parameters, but values generated by Oracle from memory, when someone issued the command CREATE PFILE FROM SPFILE; Read up on that, too.
And one more thing, do not try to edit the spfile! The reason will become clear when you read about it.
Cheers, John.

Report message to a moderator

Previous Topic:	Session tracing for shared servers
Next Topic:	SGA

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:41:42 CDT 2024