Who moved my cheese parameter?

On databases with multiple DBAs and a not too rigorous change control policy, database parameters get changed and it’s not always clear who made the change and when. Parameter changes are recorded in the instance alert log, but you might not save the alert log that far back. Auditing can tell you what user changed the parameter and when, but what if you don’t have auditing in place and you don’t keep your alert logs around very long?

How about use the AWR? Though the default retention is 8 days in one hour intervals, many databases have a much longer retention and snapshots are done more frequently too — I often see over a year of retention with snapshots every 15 minutes. If that’s the case, you can easily mine DBA_HIST_PARAMETER and at least narrow down when a given parameter was changed, in what PDB it was changed, and in what instance.

Querying DBA_HIST_PARAMETER

There are a couple of catches, though, and the query I’m using to mine that view should take those into account. Here is what DBA_HIST_PARAMETER looks like:

Name            Null?    Type          
--------------- -------- ------------- 
SNAP_ID         NOT NULL NUMBER        
DBID            NOT NULL NUMBER        
INSTANCE_NUMBER NOT NULL NUMBER        
PARAMETER_HASH  NOT NULL NUMBER        
PARAMETER_NAME  NOT NULL VARCHAR2(64)  
VALUE                    VARCHAR2(512) 
ISDEFAULT                VARCHAR2(9)   
ISMODIFIED               VARCHAR2(10)  
CON_DBID                 NUMBER        
CON_ID                   NUMBER      

Notice the columns for CON_ID and INSTANCE_NUMBER. You might change a parameter at the CDB level, and all PDBs will inherit it unless it’s modifiable at the PDB level and it was changed only for that PDB. Also, in a RAC environment you may change a parameter just for one instance for a specific container.

Finally, hidden parameters (that start with an underscore) are not going to be in this view unless they were set or changed for one or more containers.

Here are the requirements for this query:

• Show the begin and end time range in which a specified parameter was changed
• Prompt for the following values:
  • parameter name pattern (as part of LIKE)
  • begin date of interest
  • end date of interest
• Show all attributes of the parameter change for any one of these conditions:
  • A non-hidden or hidden parameter’s value was changed during the time range
  • A hidden parameter’s value was first set during the time range
  • A hidden or non-hidden parameter was set for just one instance in a RAC environment

Parameter change scenarios

Here are some examples where I make changes to various parameters in a RAC environment for two different PDBs. Here is the query:

select parameter_name, pa.instance_number, instance_name,
   cont_id, nvl(name,'CDB$ROOT') cont_name,
   value, old_value, pa.snap_id,
   to_char(begin_interval_time,'YYYY-MM-DD HH24:MI:SS') begin_int, 
   to_char(end_interval_time,'YYYY-MM-DD HH24:MI:SS') end_int
from
(
    select snap_id,parameter_name,instance_number,con_id cont_id,value,
       lag(value,1,'FIRST CHANGE')
          over (partition by parameter_name,dbid,instance_number,con_id
                order by snap_id,parameter_name,dbid,instance_number,con_id)
             old_value
    from dba_hist_parameter
) pa
join dba_hist_snapshot dhs
   on dhs.snap_id = pa.snap_id 
      and dhs.instance_number = pa.instance_number 
      and dhs.con_id=0
join gv$instance i
   on i.instance_number = dhs.instance_number
left join gv$pdbs p
   on p.inst_id = i.instance_number and p.con_id = pa.cont_id
where (value != old_value or old_value = 'FIRST CHANGE')
  and parameter_name like '%&par_name%'
  and begin_interval_time 
     between to_date('&start_dt','YYYY-MM-DD HH24:MI_SS') 
         and to_date('&end_dt','YYYY-MM-DD HH24:MI_SS')
order by instance_number;

The key to the query is the LAG analytic function which facilitates the comparison of adjacent sorted rows in DBA_HIST_PARAMETER to know when a particular parameter’s value has changed within the snapshot. If the parameter was set for the first time within the snapshot, I’ll know that by a previous value of “FIRST CHANGE” assigned in the LAG function itself.

In the first test case, I changed the parameter UNDO_RETENTION from 900 to 1000 on 9/1/2020 at 6:55 P.M. in the root, and by default, all three instances:

PARAMETER_NAME       INSTANCE_NUMBER INSTANCE_NAME  CONT_ID CONT_NAME    VALUE         OLD_VALUE  SNAP_ID BEGIN_INT           END_INT            
-------------------- --------------- ------------- -------- ------------ ---------     --------- -------- ------------------- -------------------
undo_retention                     1 xqa19c1              0 CDB$ROOT     1000          900          23912 2020-09-01 18:45:20 2020-09-01 19:00:06
undo_retention                     2 xqa19c2              0 CDB$ROOT     1000          900          23912 2020-09-01 18:45:20 2020-09-01 19:00:06
undo_retention                     3 xqa19c3              0 CDB$ROOT     1000          900          23912 2020-09-01 18:45:20 2020-09-01 19:00:06

As long as I know a general time range for the parameter change and the parameter itself, I will find it. In this example, I changed the parameter PGA_AGGREGATE_LIMIT to 31G (from 32G) on the PDB REL_CAND and only on instance 2 at 2:35 P.M. on 8/7/2020:

PARAMETER_NAME       INSTANCE_NUMBER INSTANCE_NAME  CONT_ID CONT_NAME    VALUE         OLD_VALUE     SNAP_ID BEGIN_INT           END_INT            
-------------------- --------------- ------------- -------- ------------ -----------   ------------ -------- ------------------- -------------------
pga_aggregate_limit                2 xqa19c2              9 REL_CAND     32505856000   FIRST CHANGE    21495 2020-08-07 14:30:11 2020-08-07 14:45:01
     

Since that parameter had never been set, the OLD_VALUE is NULL, which I map to “FIRST CHANGE” in the LAG function. The more often you create AWR snapshots the closer you can narrow down when the parameter was changed.

and Exadata!

The need for this view first came up during an Exadata performance investigation — while I could have run a series of AWR compare reports to see what parameters changed (DBA_HIST_PARAMETER is one of the sources for an AWR compare report), using this query is much more straightforward. The offloading performance of the Exadata system in question returned to normal after a series of performance issues, so I suspected that some cell layer parameters were to blame. I ran the query for any parameters containing the word “cell” and found the culprit at the beginning of May:

PARAMETER_NAME          INSTANCE_NUMBER INSTANCE_NAME  CONT_ID CONT_NAME    VALUE     OLD_VALUE   SNAP_ID BEGIN_INT           END_INT            
----------------------- --------------- ------------- -------- ------------ --------- ---------- -------- ------------------- -------------------
cell_offload_processing               1 PRDCDB1              0 CDB$ROOT     TRUE      FALSE         14243 2020-05-01 23:00:15 2020-05-02 00:00:26

cell_offload_processing               2 PRDCDB2              0 CDB$ROOT     TRUE      FALSE         14243 2020-05-01 23:00:15 2020-05-02 00:00:26

The parameter CELL_OFFLOAD_PROCESSING had been set back to TRUE right before midnight on 5/1/2020, and performance went back to normal. When it was set to FALSE is unknown, since the AWR snapshots were not retained before April.

Conclusions

Unless you create unique users for all of your DBAs and turn on auditing for ALTER SYSTEM, you won’t know who made a change to a parameter in which container, but at least you’ll know approximately when it was changed!