The STATISTICS_LEVEL parameter controls a bunch of features. In addition to the documentation, also the V$STATISTICS_LEVEL view provides a list of the ones it controls.
SQL> SELECT statistics_name, description, activation_level
2 FROM v$statistics_level
3 ORDER BY 3 DESC, 1;
STATISTICS_NAME DESCRIPTION ACTIVATION_LEVEL
-------------------------------------- ------------------------------------------------------------ ----------------
Active Session History Monitors active session activity using MMNL TYPICAL
Adaptive Thresholds Enabled Controls if Adaptive Thresholds should be enabled TYPICAL
Automated Maintenance Tasks Controls if Automated Maintenance should be enabled TYPICAL
Bind Data Capture Enables capture of bind values used by SQL statements TYPICAL
Buffer Cache Advice Predicts the impact of different cache sizes on number of TYPICAL
physical reads
Global Cache Statistics RAC Buffer Cache statistics TYPICAL
Longops Statistics Enables Longops Statistics TYPICAL
MTTR Advice Predicts the impact of different MTTR settings on number of TYPICAL
physical I/Os
Modification Monitoring Enables modification monitoring TYPICAL
PGA Advice Predicts the impact of different values of pga_aggregate_tar TYPICAL
get on the performance of memory intensive SQL operators
Plan Execution Sampling Enables plan lines sampling TYPICAL
SQL Monitoring Controls if SQL Monitoring should be enabled TYPICAL
Segment Level Statistics Enables gathering of segment access statistics TYPICAL
Shared Pool Advice Predicts the impact of different values of shared_pool_size TYPICAL
on elapsed parse time saved
Streams Pool Advice Predicts impact on Streams perfomance of different Streams TYPICAL
pool sizes
Threshold-based Alerts Controls if Threshold-based Alerts should be enabled TYPICAL
Time Model Events Enables Statics collection for time events TYPICAL
Timed Statistics Enables gathering of timed statistics TYPICAL
Ultrafast Latch Statistics Maintains statistics for ultrafast latches in the fast path TYPICAL
Undo Advisor, Alerts and Fast Ramp up Transaction layer manageability features TYPICAL
V$IOSTAT_* statistics Controls if I/O stats in v$iostat_ should be enabled TYPICAL
Plan Execution Statistics Enables collection of plan execution statistics ALL
Timed OS Statistics Enables gathering of timed operating system statistics ALL
Something that I learned only recently is that STATISTICS_LEVEL also controls cardinality feedback and adaptive cursor sharing. This fact, according to me, is neither (clearly) documented nor pointed out by the information provided by V$STATISTICS_LEVEL. In any case, when STATISTICS_LEVEL is set to BASIC at the system level both features are disabled. Interestingly, an ALTER SESSION SET STATISTICS_LEVEL = TYPICAL it is not enough to enable them… For adaptive cursor sharing it is possible to use the BIND_AWARE hint, though.
Note that I never advise to set STATISTICS_LEVEL at the system level to a value that is different from the default (TYPICAL). Probably for this reason I didn’t notice its impact for such a long time…
In any case I find it a bit disappointing that this information is not clearly stated somewhere. Or I’m the only one that was not aware?
I don’t think you are the only one. There is a lot of “black magic” around cardinality feedback, that is not documented.
Hi Chris,
that’s very interesting (and curious!), How did you find out (possibly by just experimenting)? What’s not clear to me from your post, is it just BASIC that disables those two, or do you need to set ALL to get them (I’d not worry about case 1 too much as we don’t have BASIC set on any dbs anyway)?
Also, I’d always thought about ALL as something you have to set to get the actual rows etc., but now I’ve stumbled about the timed os statistics – a quick search on the web seems to indicate that these, too, are not too well documented (what they are, how they are collected, what’s the impact etc) – or am I wrong here?
Best greetings
Sigrid
Hi Sigrid
> How did you find out (possibly by just experimenting)?
Somebody asked me during a class. I didn’t know the answer and, therefore, I did a test.
> is it just BASIC that disables those two
Yes, it’s just BASIC. By default (TYPICAL) the two features are enabled.
> or am I wrong here?
You are right. FWIW, I personally never used those statistics.
Cheers,
Chris
Dear Sir,
When are you planning to release the 11g updated version of TOP?
Hi
TOP covers 11.1. Since there are not sooooo many differences with 11.2, I would say that most of the content is relevant for 11.2 as well. That said, I am thinking about working on a second edition. But, if I do it, I will cover 12c as well. Hence, do not expect it before Q1 2013.
Cheers,
Chris