Insights

Insights are statements about patterns observed in historical SQL performance data that are consistent with certain types of issues or interesting events. These Insights are computed on a daily basis, and in particular, they are not statements of fact, but indirect signals that are consistent with certain types of facts.

For instance, the plan change insight looks for a sequence of observations with one plan hash followed by a sequence of observations with another plan hash, for a given SQL_ID on a daily basis. This is consistent with the common case of the query optimizer using a single plan for the SQL_ID and then switching to another plan. It is imperfect because the data collected only records the plan in use at time of collection, every 30 minutes. Many plans may have been used that were not recorded, but that is not the common case.

Insights are grouped by scope:

Fleet level insights
Database level insights
SQL level insights

Insight Regions and Widgets

All three primary dashboards have a multi-widget region called Insights:

Figure 6-7 Insights Widget

These widget regions allow you to quickly scan and check for insights in a single place, with a simple standardized visual communication widget. Within these insights the color indicators show the following statuses:

Grey: Insight not detected.
Blue: Insight detected, not likely problematic.
Orange: Insight detected, could be problematic.

Insight Use Cases

Table 6-4 Fleet Level Insights

Insight	Use case	Analysis and output	Notes
Plan changes	Are any databases having more than a few plan changes? How many?	Count of databases with plan change insights over the time period.	Insight tile status indicator: Blue when > 0. Grey when = 0 Absence of plan changes should indicate stability in workload if execution rates remain similar.
Invalidation storms	Have any databases experienced extensive recompilation of SQL/PLSQL due to object invalidation? How many?	Count of databases with at least one day in the period where the invalidation storm insight was true.	Insight tile status indicator: Blue when > 0. Grey when = 0 Excessive and unexpected levels of SQL invalidations may be event related and can be CPU intensive. Chronically high levels of invalidations may induce unnecessary load on the database,
Cursor sharing duplicates	Do any databases have applications not sharing cursors and wasting CPU? How many?	Count of databases where cursor sharing insight was true during the time period.	Insight tile status indicator: Blue when > 0. Grey when = 0
Degrading SQL	Do any databases have SQL that may be degrading over time? How many?	Count of databases having at least one SQL_ID where the degrading SQL insight was true over the period.	Insight tile status indicator: Orange when > 0. Grey when = 0

Table 6-5 Database Level Insights

Insight	Use cases	Analysis and output	Notes
Degrading trend	Does the database have any SQL that show degrading performance over the time period How many?	Count of number of SQL_ID with degrading performance trend insight over the time period.	Computed per database (all instances combined) Insight tile status indicator: Orange when > 0. Grey (not detected) when = 0
Improving trend	Does the database instance have any SQL that show improving performance over the time period? How many?	Count of number of SQL_ID with improving performance trend insight over the time period.	Computed per database (all instances combined) Insight tile status indicator: Blue when > 0. Grey (not detected) when = 0
Degraded plan changes	Have database instances seen any plan changes that degraded performance in the time period? How many?	Count of number of SQL_ID with single plan change insight judged to be degraded over the time period	Total estimated extra seconds per day saved is computed for ranking this database against others. Insight tile status indicator: Orange when > 0. Grey (not detected) when = 0 NOTE: plan changes are per instance
Improved plan changes	Have database instances seen any plan changes that improved performance in the time period? How many?	Count of number of SQL_ID with single plan change insight judged to be improved over the time period	Total estimated seconds saved per day is computed for ranking this database against others. Insight tile status indicator: Blue when > 0. Grey (not detected) when = 0 NOTE: plan changes are per instance
Invalidation storm	Has there been widespread invalidation and recompilation of SQL in the database instance during the time period?	Count of number of days in which at least 20% of the total SQL collected had also been invalidated on that day.	This is a true database level insight as it concerns all SQL collected from the database instances per day. Insight tile status indicator: Blue when > 0. Grey (not detected) when = 0
New SQL	Did the database do any new work in the time period? Which SQL and how much work?	Count of the number of SQL_ID with initial collection timestamp in the time period.	SQL may not be truly new to the database, just new to Ops Insights. Insight tile status indicator: Blue when > 0. Grey (not detected) when = 0

Table 6-6 SQL Level Insights

Insight	Use case	Analysis and output	Notes
Performance trend	Did SQL execution latency vary consistently up or down over time during the period? The execution plans may be sensitive to expanding or contracting data sizes.	Signals true for any SQL_ID where linear regression over aggregate average latencies observed in the time period explains at least ½ the variance (R2 >0.5) in aggregate average latency, AND the percentage change in the linear regression line exceeds +/- 20%	Minimum 5 observations per SQL_ID required for the regression. Positive regression slope indicates a degrading trend. Negative regression slope indicates improving trend. Insight tile status indicator: Orange = degrading Blue = improving Grey = not detected The 20% limit needs to be considered in context of time period selected. Computed per database (instances combined).
Performance variability	Did SQL show large variation in aggregate average latencies?	Signals true for a SQL_ID when the range in aggregate average latencies divided by the overall aggregate average latency is greater than 25%.	Minimum 5 observations required. Insight tile status indicator: Orange when > 25% Grey when = 0 0 < Blue < 25% More meaningful for longer running and less frequently executed SQL.
Single plan change	Did SQL show a plan change on the database during the period? Did the plan change impact performance? How much?	Indicator that the selected SQL ID incurred single daily plan change(s) during time period. The last plan change is used to determine degradation or improvement.	Minimum 5 observations required. Higher average latency for new plan indicates performance regression. Lower average latency for new plan indicates performance improvement. Insight tile status indicator: Orange = performance regression Blue = performance improvement Grey = not detected Plan information is sampled and not complete. Computed per instance as different instances can have different plans.
Multiple plans	Did SQL execute under multiple plans on the database over the time period?	Signals true when count of number of distinct plan hash values for SQL_ID exceeds 1.	Plan information is sampled and not complete, so there could be more plans than those collected. Will signal with single plan change. Insight tile status indicator: Blue = plant count >1 Grey = plan count = 1 Computed per database (instances combined).
Cursor sharing duplicates	Are there SQL that could share parsing and optimization save for the non-use of bind variables? How much excess CPU is wasted?	Signals true when the count of distinct SQL_ID sharing same force matching signature is > 1.	Computed per database instance. Insight tile status indicator: Blue = duplicates > 0 Grey = duplicates = 0 Only includes SQL_ID with executions = 1. Estimated excess parse CPU is computed as SUM(average hard parse time) – AVG(average hard parse time) on the basis that hard parsing is all CPU and the average hard parse time is not an average since executions is 1 for all the SQL. The above is likely a very low estimate of total CPU lost because many duplicates that executed will not have been collected.

Oracle Cloud Infrastructure Documentation