[INSTRUCTIONS] Default Settings for New OTM DB

[chrisplough]

All,

Based on the OTM Admin Guide and some advice from an old G-Log friend (who did at ton of the DB development and performance work for GC3!), the following non-standard parameters are recommended for use with your new OTM v5.5 database

Note: I always recommend reviewing changes like this with a DBA that you trust. Please don't make these blindly and realize that the MAY NOT WORK FOR YOU.

Oracle 10gR2 - 10.2.0.3:

sga_target=2147483648
pga_aggregate_target=536870912
log_buffer=163840
sort_area_size=1048576
sort_area_retained_size=1048576
optimizer_mode='CHOOSE'
optimizer_index_cost_adj=50
optimizer_index_caching=50
statistics_level='ALL'
query_rewrite_enabled=TRUE
query_rewrite_integrity='trusted'
workarea_size_policy='AUTO'

Some of these may override your current settings, so ensure that you check your existing init file and replace/update any settings as necessary. To create a new init file, based on your current configuration, login to your DB as SYSDBA and run:

CREATE PFILE FROM SPFILE;

The shutdown your DB, and edit your init file. To apply these settings after editing your init file (my preferred way, but there are several others) - login to your DB as SYSDBA and run the following:

STARTUP PFILE='/u02/app/ora10g/product/10.2/tmsprd/dbs/initTMSPRDDB10G.ora'
CREATE SPFILE FROM PFILE;

Restart your DB one more time, just to be sure, and you're done.

Hope this helps!

--Chris

[Kristof Stevens]

Re: [INSTRUCTIONS] Default Settings for New OTM DB

Currently we are using optimize Mode CHOOSE in OTM, but it seems the CHOOSE mode is desupported with Oracle 10g.
I created a new index on a high cost SQL and compared the optimize mode CHOOSE with FIRST_ROWS_10 or _100.Even with the nex index creation and with CHOOSE the SQL results in a table access full scan, costs 50984
When I changed into FIRST_ROWS_10 or 100, cost 72.

The cost reduced from 50984 to 72...Couldn't we consider to change our optimize mode from CHOOSE into FIRST_ROWS_10? What will be the impact into OTM?

If we are going to change database mode to first_rows, then there is maybe need to change mode with "alter session" to all_rows when running reports or big batch jobs.

Thx,

Kristof.