Announcement

Collapse
No announcement yet.

[INSTRUCTIONS] Default Settings for New OTM DB

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [INSTRUCTIONS] Default Settings for New OTM DB

    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:
    Code:
    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:
    Code:
    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:
    Code:
    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
    Chris Plough
    twitter.com/chrisplough
    MavenWire

  • #2
    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.

    Comment

    Working...
    X