Wednesday, November 27, 2013

Database Statistics (DBMS_STATS) in ORACLE

Purpose: Helps to gather statistics of system (IO, CPU), Database, Table, Columns, Indexes.
                This package will be executed with the privileges of current_user.

How to setup DBMS_STATS (Stats Pack) package:

Run script [ORACLE_HOME]\rdbms\admin\spcreate.sql to create the package.
You'll need to know three pieces of information before running the spcreate.sql script. They are:
• The password you would like to use for the PERFSTAT schema that will be created
• The default table space you would like to use for PERFSTAT
• The temporary table space you would like to use for PERFSTAT

Note: Use spdrop.sql to remove the user and installed views prior to attempting another install of StatsPack. The StatsPack installation will create a file called spcpkg.lis. You should review this file for any possible errors that might have occurred.


There are three ways to collect statistics using dbms_stats package.
·         Automatic optimizer statistics collection
·         Mixed
·         Manual

How to gather Statistics using dbms_stats:

exec dbms_stats.gather_database_stats;
exec dbms_stats.gather_schema_stats('SCOTT');
exec dbms_stats.gather_table_stats ('SCOTT', 'EMP');
exec dbms_stats.gather_index_stats ('SCOTT', 'EMP_PK');
exec dbms_stats.gather_dictionary_stats;

Delete statistics using dbms_stats:

exec dbms_stats.delete_database_stats;
exec dbms_stats.delete_schema_stats ('SCOTT');
exec dbms_stats.delete_table_stats ('SCOTT','EMP');
exec dbms_stats.delete_column_stats ('SCOTT', 'EMP', 'EMPNO');
exec dbms_stats.delete_index_stats ('SCOTT', 'EMP_PK');
exec dbms_stats.delete_dictionary_stats;
EXEC DBMS_STATS.delete_system_stats;

Data Dictionary Views:

·         DBA_TABLES
·         DBA_TAB_STATISTICS
·         DBA_TAB_PARTITIONS
·         DBA_TAB_SUB_PARTITIONS
·         DBA_TAB_COLUMNS
·         DBA_TAB_COL_STATISTICS
·         DBA_PART_COL_STATISTICS
·         DBA_SUBPART_COL_STATISTICS
·         DBA_INDEXES
·         DBA_IND_STATISTICS
·         DBA_IND_PARTITIONS
·         DBA_IND_SUBPARTIONS
·         DBA_TAB_HISTOGRAMS
·         DBA_PART_HISTOGRAMS
·         DBA_SUBPART_HISTOGRAMS



Q1: SQL query to find the last time table is analyzed?
A1:  select last_analyzed
 from dba_tables
where owner = 'OMSOWN'
and table_name = 'OEORDER_HEADER'

Q2: SQL to find whether the table is partitioned or not? If partitioned, then display the names of the partitions.
A2: select partitioned
 from dba_tables
where owner = 'OMSOWN'
and table_name = 'OEORDER_HEADER'

select * from
dba_tab_partitions
where table_owner = 'OMSOWN'
and table_name = 'OEORDER_HEADER'


System Stats:
Gather_system_stats gathers statistics related to the performance of I/O and CPU.

-- Manually start and stop to sample a representative time (several hours) of system activity.
EXEC DBMS_STATS.gather_system_stats('start');
EXEC DBMS_STATS.gather_system_stats('stop');

-- Sample from now until a specific number of minutes.
DBMS_STATS.gather_system_stats('interval', interval => 180);

Q3: Write a query to display current system statistics

A3:  SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';













EXEC DBMS_STATS.gather_fixed_objects_stats; -- To gather statistics on the X$ tables
EXEC DBMS_STATS.delete_fixed_objects_stats; -- To delete statistics

Locking Stats:

To prevent statistics being overwritten, you can lock the stats at schema, table or partition level.

EXEC DBMS_STATS.lock_schema_stats('SCOTT');
EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');

If you need to replace the stats, they must be unlocked.

EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');

Transferring Stats:

It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data.
First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA.

EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
This table can then be transfered to another server using your preferred method (Export/Import, SQL*Plus COPY etc.) and the stats imported into the data dictionary as follows.

EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

Setting Stats Manually:

The DBMS_STATS package provides several procedures for manually setting statistics.

SET_SYSTEM_STATS
SET_TABLE_STATS
SET_COLUMN_STATS
SET_INDEX_STATS

The current stats can be returned using the following procedures.

GET_SYSTEM_STATS
GET_TABLE_STATS
GET_COLUMN_STATS
GET_INDEX_STATS
Be careful when setting stats manually. Possibly the safest approach is to get the current values, amend them as required, then set them. An example of setting column statistics is shown below.

SET SERVEROUTPUT ON
DECLARE
  l_distcnt  NUMBER;
  l_density  NUMBER;
  l_nullcnt  NUMBER;
  l_srec     DBMS_STATS.StatRec;
  l_avgclen  NUMBER;
BEGIN
  -- Get current values.
  DBMS_STATS.get_column_stats (
    ownname => 'SCOTT',
    tabname => 'EMP',
    colname => 'EMPNO',
    distcnt => l_distcnt,
    density => l_density,
    nullcnt => l_nullcnt,
    srec    => l_srec,
    avgclen => l_avgclen);

  -- Amend values.
  l_srec.minval := UTL_RAW.cast_from_number(7369);
  l_srec.maxval := UTL_RAW.cast_from_number(7934);

  -- Set new values.
  DBMS_STATS.set_column_stats (
    ownname => 'SCOTT',
    tabname => 'EMP',
    colname => 'EMPNO',
    distcnt => l_distcnt,
    density => l_density,
    nullcnt => l_nullcnt,
    srec    => l_srec,
    avgclen => l_avgclen);
END;
/


Optimizer statistics include the following:
·         Table statistics
o    Number of rows
o    Number of blocks
o    Average row length
·         Column statistics
o    Number of distinct values (NDV) in column
o    Number of nulls in column
o    Data distribution (histogram)
o    Extended statistics
·         Index statistics
o    Number of leaf blocks
o    Levels
o    Clustering factor
·         System statistics
o    I/O performance and utilization
o    CPU performance and utilization
Automatic Optimizer Statistics Collection:
          If this option enabled, the database automatically collects optimizer statistics for tables with absent or stale statistics.

Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROC prioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/


When to Use Manual Statistics:

1.       When large bulk loads performed on a table (generally 10% or more of total records changed)
2.       When tables deleted or truncated and rebuild during the course of the day

How to determining the Stale Statistics:

                When the STATISTICS_LEVEL parameter is set to TYPICAL or ALL then monitoring is enabled by default to determine whether a given database objects needs new database statistics or not.
Oracle provides a table monitoring facility which tracks the approximate number of rows inserted, updated & deleted.
Query the view USER_TAB_MODIFICATIONS







Note: Analyze statement, dbms_utility.analyze_schema, dbms_utility.analyze_database are maintaining for backward compatibility.


For More Details -> Link1     Link2

No comments:

Post a Comment