Add a new Db Parameter

[Former Member]

Hi All,

The System is Netweaver 04. It is a BW System.

Database is Oracle 9.2.0.6.0 on Win Server 2003 server.

OSS Note 632556 recommends me to set the following parameters in my BW system.

The following parameters which start with an underscore viz. do not exist in my Database profile parameters(Transaction Code DB26),

BTREE_BITMAP_PLANS

ELIMINATECOMMON_SUBEXPR

OPTIMIZERJOIN_SEL_SANITY_CHECK

OPTIMIZEROR_EXPANSION

OPTIMPEEK_USER_BINDS

PGAMAX_SIZE

SORTELIMINATION_COST_RATIO

Can you please tell me how to create these new database parameters in my system? Can we create new database parameters & is it recommended or allowed to create new parameters in the system? I know how these parameters effect the system performance.

Thank you all,

Regards,

Bob.

[Former Member]

Hi Bob,

You can add it manually from database side.

If you are using SPFILE and don't know the command to add the parameter, you can use this way.

1. CMD> sqlplus /nolog (CMD> is your command prompt, so dont type it)

2. SQL> connect /as sysdba (SQL> is your sqlplus command prompt, dont type it)

3. SQL> create pfile from spfile

4. edit the <drive>:/oracle/<SID>/920/database/init<SID>.ora and add the paramter

begin with *.

ex: *.__B_TREE_BITMAP_PLANS=<value>

5. SQL> shutdown immediate

6. SQL > create spfile from pfile;

7. SQL > startup

But if you want to use the command to add the parameter, here the command

SQL> alter system set "_B_TREE_BITMAP_PLANS"=<value> scope=spfile;

Regards,

Fendi Suyanto

[Former Member]

Hi Bob,

Netweaver'04 of BW using spfile by default is what I am aware of. Refer to SAP Note 596423 - Events and SPFILE which explains how to set events in SPFILE:

The best way to set events in the SPFILE is to use SQL statements:

SQL> ALTER SYSTEM SET

EVENT='10181 trace name context forever, level 10',

'10183 trace name context forever, level 1'

COMMENT='my comment, user, date'

SCOPE=SPFILE;

System altered.

To activate the events, you must restart the instance.

Cheers,

Nisch

[former_member204746]

it depends on if you use Pfile or SPFILE. to know:

sqlplus "/ as sysdba"

show parameter pfile

if value is empty, this means you use init[SID].ora

then, edit this file by adding manually entries using a text editor.

then shutdown Oracle and restart it.

[Former Member]

First of all upgrade to Oracle Patchset 9.2.0.8, and install the latest minipatch (currently 9.2.0.8.11, 9.2.0.8.12 coming soon).

If not upgrading, you may (will?) experience all those problems we already have solved at our customers within the last 3 years.

Underscore parameters are undocumented parameters which are not visible by "show parameter" command or "select name from v$parameter".

Therefore you have to set them manually in the init<SID>.ora file.

regards

Peter

[Former Member]

Hi Peter,

Normally we apply SAP recommended interim patches on top of a patchset like 9.2.0.8 but i'm not sure what you're referring to hear by minipatch?

Can you give me the minipatch 9.2.0.8.11 number ..i mean pxxxx number?

Can you pls. clarify?

[former_member204746]

minipatch is used on Windows only. On unix, this is different and you should use MOPATCH.

[fidel_vales]

Hi,

In Windows you cannot apply individual or interim patches ( it is not possible to relink the oracle executable, for example )

For this reason Oracle put together several interim patches into what is called a "minipatch" ( name a little confusing as it should be "mini patch set" )

The patch has to be installed with Opatch.

On the note

539921 Current patch set for Oracle 9.2.0

you have the location on the SAP Service Marketplace where you can download it.

The patch number is p6263319 for 32 bit and p6263328 for 64 bit