Prompted by a recent question on the MOSC community forum (link needs support account) I thought I’d dust off this little script (that I wrote nearly 20 years ago for Oracle 9.2) so see if it still worked, needed any new columns, or added extra rows in 21c.
The script is just a simple report of v$statistics_level, which reports the various real-time statistics collections and advisors that could be enabled, the view that holds associated results (where relevant) and the “activation_level” – in effect telling you whether or not it is necessary to set the statistics_level to “all” before you can enable a particular collection.
The results are not as helpful as you might hope, however, and may result in a mild panic attack if you are prone to getting worried about licensing requirements.
rem
rem Script: statistics_level.sql
rem Author: Jonathan Lewis
rem Dated: July 2002
rem Purpose: Simple script to pick up 9.2 statistics activation details
rem
column statistics_name format a40
column statistics_view_name format a24
column description format a64 word_wrapped
column system_status heading "Sys"
column session_Status heading "Ses"
column session_settable heading "Set"
break on activation_level skip 1
set linesize 160
set pagesize 90
set trimspool on
spool statistics_level
select
activation_level,
statistics_name,
statistics_view_name,
system_status,
session_status,
session_settable,
description
from
v$statistics_level
order by
activation_level,
-- length(description) desc,
statistics_name
;
clear breaks
spool off
By default the script has to be run by the SYS user, and you’ll note that I’ve omitted the con_id column, which always seemed to be zero whether I query from the root or from a pluggable database on my 21.3 instance.
Here are the results I got from the root container with the statistics_level set to typical:
ACTIVAT STATISTICS_NAME STATISTICS_VIEW_NAME Sys Ses Set DESCRIPTION
------- ---------------------------------------- ------------------------ -------- -------- --- ----------------------------------------------------------------
ALL Plan Execution Statistics V$SQL_PLAN_STATISTICS DISABLED DISABLED YES Enables collection of plan execution statistics
Timed OS Statistics DISABLED DISABLED YES Enables gathering of timed operating system statistics
TYPICAL Active Session History V$ACTIVE_SESSION_HISTORY ENABLED ENABLED NO Monitors active session activity using MMNL
Adaptive Thresholds Enabled ENABLED ENABLED NO Controls if Adaptive Thresholds should be enabled
Automated Maintenance Tasks ENABLED ENABLED NO Controls if Automated Maintenance should be enabled
Automatic DBOP Monitoring V$SQL_MONITOR ENABLED ENABLED YES Controls if automatic DBOP Monitoring should be enabled
Bind Data Capture V$SQL_BIND_CAPTURE ENABLED ENABLED NO Enables capture of bind values used by SQL statements
Buffer Cache Advice V$DB_CACHE_ADVICE ENABLED ENABLED NO Predicts the impact of different cache sizes on number of
physical reads
Column Tracking Level ENABLED ENABLED YES Sets Up Column Tracking Level
Global Cache Statistics ENABLED ENABLED NO RAC Buffer Cache statistics
Longops Statistics V$SESSION_LONGOPS ENABLED ENABLED NO Enables Longops Statistics
MTTR Advice V$MTTR_TARGET_ADVICE ENABLED ENABLED NO Predicts the impact of different MTTR settings on number of
physical I/Os
Modification Monitoring ENABLED ENABLED NO Enables modification monitoring
OLAP row load time precision ENABLED ENABLED YES Sets precision of olap row load time statistics
Object Activity Tracking ENABLED ENABLED YES Sets Up Object Activity Tracking (OATS)
PGA Advice V$PGA_TARGET_ADVICE ENABLED ENABLED NO Predicts the impact of different values of pga_aggregate_target
on the performance of memory intensive SQL operators
Plan Execution Sampling V$ACTIVE_SESSION_HISTORY ENABLED ENABLED YES Enables plan lines sampling
SQL Monitoring V$SQL_MONITORING ENABLED ENABLED YES Controls if SQL Monitoring should be enabled
Segment Level Statistics V$SEGSTAT ENABLED ENABLED NO Enables gathering of segment access statistics
Shared Pool Advice V$SHARED_POOL_ADVICE ENABLED ENABLED NO Predicts the impact of different values of shared_pool_size on
elapsed parse time saved
Streams Pool Advice V$STREAMS_POOL_ADVICE ENABLED ENABLED NO Predicts impact on Streams perfomance of different Streams pool
sizes
Threshold-based Alerts ENABLED ENABLED NO Controls if Threshold-based Alerts should be enabled
Time Model Events V$SESS_TIME_MODEL ENABLED ENABLED YES Enables Statics collection for time events
Timed Statistics ENABLED ENABLED YES Enables gathering of timed statistics
Ultrafast Latch Statistics ENABLED ENABLED NO Maintains statistics for ultrafast latches in the fast path
Undo Advisor, Alerts and Fast Ramp up V$UNDOSTAT ENABLED ENABLED NO Transaction layer manageability features
V$IOSTAT_* statistics ENABLED ENABLED NO Controls if I/O stats in v$iostat_ should be enabled
27 rows selected.
The key thing to note from this output is that there are only two statistics collections that are enabled by setting statistics_level to all, the “Plan Execution Statistics” and the “Timed OS Statistics”. Make the change and you’ll see these two statistics reported as ENABLED. If you look behind the scenes you’ll also find that the parameter “timed_os_statistics” has changed from 0 to 60 (and v$sysstat now starts reporting values for the statistics with names like ‘%OS%’) and the hidden parameter “_rowsource_execution_statistics” has changed from false to true (and when you execute new queries and use the option format=>’allstats [last]’ in calls to dbms_xplan.display_cursor you get execution stats appearing in every line of the resulting execution plan).
Guideline: do not set statistics_level to all at the system level, the overheads can be significant. You might want to set it occasionally for a single session for a brief interval to investigate a performance problem – bearing in mind that setting the parameter might actually introduce a whole new performance problem.
User-friendly: NOT
The question on the forum that prompted this note was asking if there were licensing implications of setting the statistics_level, in particular whether there was any requirement to license the diagnostic and performance packs. I didn’t reply to the question – someone else took it on – but the answer is no.
If you look at the output above, though you’ll see that it reports both “Active Session History” and “Plan Execution Sampling” as ENABLED – when I know that I’ve set the parameter control_management_pack_access to none. And when I query v$active_session_history the rowcount is always zero – so it’s not enabled.
Without going through the manuals, checking the dynamic performance views in the output above, and looking for related parameters (e.g. view V$MTTR_TARGET_ADVICE and parameter fast_start_mttr_target) I can’t really be sure what it means to say that an entry in the output is “Enabled”.
I wonder if my query to check whether v$active_session_history was populated will have set the feature audit to say that I’ve used the active session history!
Footnote
There have been a couple of changes over time in this view. In 12.2, for example, I noted a statistic called “Global Cache CPU Statistics” which wasn’t present in 21.3; conversely in 21.3 I noted a statistic “Object Activity Tracking” that wasn’t present in 12.2
You could disable most of the stats collections and advisors at the system level by setting the statistics level to “basic” – but (a) you might find that the attempt to do so raised Oracle errors if you haven’t previously disabled some of the default mechanism e.g:
ORA-32017: failure in updating SPFILE
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
and (b) you will probably find one day that 20/20 hindsight tells you that really could have used some of the advisors to find out why your production system is misbehaving.
[…] A quick check on what diagnostic statistics are available (Nov 2021) […]
Pingback by Performance catalogue | Oracle Scratchpad — January 28, 2022 @ 5:00 pm GMT Jan 28,2022 |