• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » STATISTICS_LEVEL in Oracle

STATISTICS_LEVEL in Oracle

May 11, 2023 by techgoeasy Leave a Comment

The STATISTICS_LEVEL parameter was introduced in Oracle version 9 (9.2) to control all
major statistics collections or advisories in the database. The level of the setting affects the
a number of statistics and advisories that are enabled. These listed below are for 10g and above

How to set this parameter

This is a dynamic parameter and does not require any database restart

ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;

There is no impact of setting this parameter to the session level for the values BASIC and TYPICAL. But it can be set to ALL at the session level to gather timed OS statistics and plan execution statistics

We should not STATISTICS_LEVEL to ALL at the instance level, as this will cause some stability issues due to the heavy overhead associated with the collection of timed operating system statistics and row source execution statistics

How to get the current value of the parameter

show parameter statistics_level

How to get the activation level setting for each value

We can use the below query to get the activation level

SELECT statistics_name, session_status, system_status,
activation_level, session_settable
FROM v$statistics_level
ORDER BY statistics_name;

Let’s check for each setting

BASIC

ALTER SYSTEM SET statistics_level=basic;
SELECT statistics_name, session_status, system_status, activation_level, session_settable FROM v$statistics_level ORDER BY statistics_name;

TYPICAL

ALTER SYSTEM SET statistics_level=typical;
SELECT statistics_name, session_status, system_status, activation_level, session_settable FROM v$statistics_level ORDER BY statistics_name;

ALL

ALTER SYSTEM SET statistics_level=all; 
SELECT statistics_name, session_status, system_status, activation_level, session_settable FROM v$statistics_level ORDER BY statistics_name;

We can clearly see from the table, statistics_level=ALL will be valid for session-level also

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • STATISTICS_LEVEL in Oracle
  • Move SQL Profiles from One Database to Another 
  • How to compare statistics using stattab for a table in Oracle
  • How to compare statistics using history for a table in Oracle
  • How to create environment variables in Windows

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us
x
x