Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Discovering if a System level Parameter has changed its value (and when it happened)

with 4 comments

Quite often I learn of a system where “nobody changed anything” and suddenly the system is experiencing some strange behavior. Then after diligent investigation it turns out someone changed a little parameter at the System level, but somehow disregarded mentioning it since he/she thought it had no connection to the unexpected behavior.  As we all know, System parameters are big knobs that we don’t change lightly, still we often see “unknown” changes like the one described.

Script below produces a list of changes to System parameter values, indicating when a parameter was changed and from which value into which value. It does not filter out cache re-sizing operations, or resource manager plan changes. Both would be easy to exclude, but I’d rather see those global changes listed as well.

Note: This script below should only be executed if your site has a license for the Oracle Diagnostics pack (or Tuning pack), since it reads from AWR.

WITH
all_parameters AS (
SELECT snap_id,
       dbid,
       instance_number,
       parameter_name,
       value,
       isdefault,
       ismodified,
       lag(value) OVER (PARTITION BY dbid, instance_number, parameter_hash ORDER BY snap_id) prior_value
  FROM dba_hist_parameter
)
SELECT TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI') end_time,
       p.snap_id,
       p.dbid,
       p.instance_number,
       p.parameter_name,
       p.value,
       p.isdefault,
       p.ismodified,
       p.prior_value
  FROM all_parameters p,
       dba_hist_snapshot s
 WHERE p.value != p.prior_value
   AND s.snap_id = p.snap_id
   AND s.dbid = p.dbid
   AND s.instance_number = p.instance_number
 ORDER BY
       s.begin_interval_time DESC,
       p.dbid,
       p.instance_number,
       p.parameter_name
/

Sample output follows, where we can see a parameter affecting Degree of Parallelism was changed. This is just to illustrate its use. Enjoy this new free script! It is now part of edb360.
Screen Shot 2015-03-25 at 19.15.26

Advertisements

Written by Carlos Sierra

March 25, 2015 at 7:25 pm

4 Responses

Subscribe to comments with RSS.

  1. I have a script which gives similar output. http://www.vishalgupta.com/sqlscripts/awr_parameter.sql

    Vishal Gupta

    March 25, 2015 at 7:54 pm

  2. Thanks for this new script, Carlos! I have been using script awr_parm_changes.sql, by Tim Gorman, since some time ago for this research. If tuning pack is not available, and if you have installed statspack environment, you can also use sp_parm_changes.sql for the identical behaviour.

    Unfortunately I could not find these scripts anymore on Tim Gorman repositories online.

    lcdsantos

    March 26, 2015 at 11:32 am

  3. Thanks for this SQL Carlos.

    jkstill

    March 26, 2015 at 3:48 pm


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: