Did you find this post useful? Does your organization need Oracle services? We can help.

Much of this morning went into investigating strange ADDM reports on a two-node Oracle RAC database. For some reason, there were statistically improbable differences between impact percentages that have persisted over a month; in this case, instance 2 would consistently have wildly higher impacts for some optimizer-related activities. For example:

My first line of research was to review connection strings on the custom applications and reporting tools that have been put in place. I assumed that someone had skipped the SCAN address or specified a DSN with SID, not service name. There must have been some reporting SQL that changed or was poorly auto-generated, causing the optimizer load to differ. However, this investigation led to no results.

Next, I tried to trace the issue back through the basics. The differences were primarily in optimizer activity, so I wanted to look at a time series plot of optimizer_cost in dba_hist_sqlstat by instance.

SELECT dhss.instance_number as instance_number,
dhss.snap_id as snap_id,
AVG(dhss.optimizer_cost) as average_cost,
SQRT(VARIANCE(dhss.optimizer_cost)) as stddev_cost
FROM dba_hist_sqlstat dhss
GROUP BY dhss.instance_number, dhss.snap_id
ORDER BY dhss.snap_id, dhss.instance_number;

This got some pretty strange results. I plotted the output in R with ggplot using the lines below.

Instance two has had strictly higher loess-smoothed average optimizer costs, even during snapshots in which the average cost was nearly zero. It’s almost like they were using entirely different optimizers. Then it hit me – what if some parameters that affected the optimizer were not synchronized between the databases? How could I quickly check?

Lo and behold, optimizer_mode, optimizer_index_cost_adj, optimizer_index_caching, and workarea_size_policy differed between instance 1 and instance 2. Time to get this fix in to change control review and help get this system back up to full steam.

Did you find this post useful? Does your organization need Oracle services? We can help.