Dontcheff

The Newest init.ora Parameter in the Oracle Database

In Database tuning, DBA, Init.ora, Oracle database on November 6, 2011 at 14:18

Altogether there are 347 init.ora parameters in Oracle Database 11.2.0.3. Looking into all documented and undocumented parameters in 11.2.0.3, we witness a high number: 2752.

11.2.0.3 does not sparkle with any fancy features and no one is expecting it to but there is one new init.ora parameter added by Oracle: awr_snapshot_time_offset.

Why is it needed? The answer is very clear in case you have a server with several databases running on it. Consolidation has been fashionable for the past decade and many scattered corporative databases have perched on the same computer.

You might have observed those spikes on the top of every hour when all the AWR snapshots are taken. How do you avoid this, i.e., say you have 12 databases on the server and you want snapshots taken on hour bases but within 5 minutes difference for every database?

Quite simply with awr_snapshot_time_offset by setting it for n*300 (seconds) for every n:th database (n = 1..12).

The documentation of awr_snapshot_time_offset gives additional details for the parameter.

And finally, here is how one can search for the values of certain parameters, for example Bloom filter related:

select a.ksppinm name, b.ksppstvl value 
from x$ksppi a, x$ksppcv b 
where a.indx = b.indx 
and a.ksppinm like '%bloom%';
Advertisements
  1. […] – Exadata – Enterprise Manager Cloud Control – Edition-Based Redefinition – Advanced Compression – Oracle Database Appliance – Automatic SQL Tuning – Total Recall – Real Application Testing – One of these init.ora parameters: db_ultra_safe, result_cache_mode, optimizer_capture_sql_plan_baselines, awr_snapshot_time_offset […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: