“STATISTICS” THE VITALS OF ORACLE DATABASE

“STATISTICS” THE VITALS OF ORACLE DATABASE

Statistics are the receptors or the data collector similar to a human body. Eyes, ears, nose, tongue etc are the receptors in a human body which senses and collects information with the stimulus and sensory mechanism like smell, light, heat etc and sends the signal to brain in form of electrical impulses.

Nervous system controls and coordinates the activities of our body with the help of the information gathered from organs. So lets focus on oracle. Humans who live in present enjoy the current moment and who donot, worry thinking about the past. We dont go deeper into human biology however its good to imagine.

Similar to how a human body and mind works, Oracle collects the present information about the database in the form of statistics for each and every changes like DML (Insert, Update and Delete) which happen in the database. If appropriate statistics are not feeded to Oracle, then Oracle think in terms of past as present using the old information which leads to sub optimal plan. Thus the performance of a single SQL or batch may be slow causing end user impact.

A DBA should know what happens in his database which he work daily. So its important to understand the internal jobs and how they work in collecting the statistics.

  • When the stats job is scheduled?
  • What are the settings or default configurations of stats job?
  • Whether the auto job collect stats properly or manual intervention required?
  • Why does a last day stats job fail?
  • Why stats are stale even after stats collection?
  • Is the schedule time sufficient for job to complete?
  • What is the right time to collect stats according to the incoming workload?
  • What are the objects which become stale frequently?
  • How often there are changes (DML) on the objects?
  • Does stats become a cause for any performance slowness? ….

Ask these above questions, when you determine a stats requirements for database

As statistics is vital for a database to live in present, they must be up to date to get good performance results

Basically there are three ways to gather stats

  • Manual – This is not always possible
  • Automatic statistics internal job – This is good but may fail sometimes due to timeout
  • Automate the statistics using shell script or PLSQL in cronjob

Types of statistics

  • System statistics
  • Table
  • Column
  • Index

Data dictionary stores the optimizer statistics in shared pool(SGA) and system tablespace

Statistics can be

  • Copied from a different database
  • Exported and imported to different database
  • Gathered
  • Locked to avoid further statistics change

In this article, we will see how to identify the auto optimizer stats job and its behaviour

To check the auto optimizer stats job,

SQL> set lines 200 pages 500
colSQL>  CLIENT_NAME for a20
col TASK_NAME for a15
SQL> SQL> col OPERATION_NAME for a20
col ATTRIBUTES for a20
col TASK_PRIORITY for 999
select client_name,
        task_name,
         operation_name,
          status,
           attributes,
            task_priority
from dba_autotask_task
where task_name like '%stats%';SQL> SQL> SQL>   2    3    4    5    6    7    8

CLIENT_NAME          TASK_NAME       OPERATION_NAME       STATUS   ATTRIBUTES           TASK_PRIORITY
-------------------- --------------- -------------------- -------- -------------------- -------------
auto optimizer stats gather_stats_pr auto optimizer stats ENABLED  VOLATILE, SAFE TO KI             2
 collection          og               job                          LL

The parameter values can be set either globally or locally specific to an object using DBMS_STATS.SET_*_PREFS procedure

To get the existing default values of the stats parameters, we can use get_prefs procedure which is very handy. Use the below script to get all the values for parameters of stats.

SQL> select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')||'' as "STATS_PARAM" from dual
 union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE')||'' from dual
 union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT')||'' as "STATS_PARAM" from dual
 union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE')||'' from dual
 union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')||'' from dual
 union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY')||'' from dual
 union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL')||'' from dual
 union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL')||'' from dual
 union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS')||'' from dual
 union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT')||'' from dual
 union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS')||'' from dual
 union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT')||'' from dual;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23

STATS_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTOSTATS_TARGET =====> AUTO
CASCADE =====> DBMS_STATS.AUTO_CASCADE
CONCURRENT =====> OFF
DEGREE =====> NULL
ESTIMATE_PERCENT =====> DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY =====> AUTO
INCREMENTAL =====> FALSE
INCREMENTAL_LEVEL =====> PARTITION
INCREMENTAL_STALENESS =====>
METHOD_OPT =====> FOR ALL COLUMNS SIZE AUTO
OPTIONS =====> GATHER
STALE_PERCENT =====> 10

12 rows selected.

For individual objects or entities, we can set the preferences manually using the below procedures

SET_TABLE_PREFS
SET_SCHEMA_PREFS
SET_DATABASE_PREFS
SET_GLOBAL_PREFS

ESTIMATE_PERCENT AND METHOD_OPT

These two parameters plays a crucial role in determining the samples of calculating statistics(ESTIMATE_PERCENT) and histograms for columns(METHOD_OPT)

If stale stats for a table needs to be identified, then stale_stats column should have a value ‘YES’ . These tables need the statistics to be updated in order to get optimal execution plans for a SQL statement

SQL> set lines 200 pages 1000
col PARTITION_NAME for a10
col TABLE_NAME for a15
col INDEX_NAME for a10
col CLUSTERING_FACTOR for 999999
select dts.TABLE_NAME,
             dts.PARTITION_NAME,
               dts.SAMPLE_SIZE as SS,
                 dts.AVG_ROW_LEN as ARL,
                   dts.BLOCKS,
                     di.NUM_ROWS,
                       di.CLUSTERING_FACTOR as CF,
                         di.INDEX_NAME,
                           dts.CHAIN_CNT as CC
from dba_tab_statistics dts
inner join dba_indexes di on ( dts.table_name = di.table_name )
where dts.STALE_STATS='YES'
order by 7 desc;SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13

TABLE_NAME      PARTITION_         SS        ARL     BLOCKS   NUM_ROWS         CF INDEX_NAME         CC
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SCHEDULER$_EVEN                  1212        264         58                       SYS_IL0000          0
T_LOG                                                                             007903C000
                                                                                  16$$

WRH$_SYSSTAT    WRH$_SYSST       7068         30         35      49476      40884 WRH$_SYSST          0
                A_41072869                                                        AT_PK
                76_277

WRH$_LATCH      WRH$_LATCH       4620         54         43      32340      26568 WRH$_LATCH          0
                _410728697                                                        _PK
                6_277

In 19c database, there is one default gather stats auto program to gather stats during a scheduler window on off business hours

SQL> select task_name,count(*) from dba_autotask_task where task_name like '%stat%' group by task_name;

TASK_NAME                                                          COUNT(*)
---------------------------------------------------------------- ----------
gather_stats_prog                                                         1

Below detail shows the auto stats job execution during previous days

SQL> set lines 200 pages 1000
col OPERATION for a25
col TARGET for a10
SQL> SQL> SQL> col STATUS for a10
col JOB_NAME for a20
col CPU for 999999
col DURATION for 999999
col START_TIME for a15
col END_TIME for a15
select  doo.OPERATION,
                 to_char(doo.START_TIME,'dd-mm-yy hh24:mi') "START",
                  to_char(doo.END_TIME,'dd-mm-yy hh24:mi') "END",
                   doo.STATUS,
                    doo.JOB_NAME,
                extract(SECOND from dsjrd.RUN_DURATION) "DURATION",
                 extract(SECOND from dsjrd.CPU_USED) "CPU"
from dba_optstat_operations doo
inner join dba_scheduler_job_run_details dsjrd on ( doo.JOB_NAME = dsjrd.JOB_NAME)
where doo.TARGET = 'AUTO'
order by doo.ID asc;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11

OPERATION                 START          END            STATUS     JOB_NAME             DURATION     CPU
------------------------- -------------- -------------- ---------- -------------------- -------- -------
gather_database_stats (au 03-01-22 22:00 03-01-22 22:00 COMPLETED  ORA$AT_OS_OPT_SY_301       31      24
to)

gather_database_stats (au 13-01-22 22:00 13-01-22 22:00 COMPLETED  ORA$AT_OS_OPT_SY_321       13      44
to)

gather_database_stats (au 31-01-22 22:00 31-01-22 22:01 COMPLETED  ORA$AT_OS_OPT_SY_341       21      36
to)

gather_database_stats (au 01-02-22 22:00 01-02-22 22:00 COMPLETED  ORA$AT_OS_OPT_SY_361        3      36
to)

To check the auto stats job history

SQL> set lines 200 pages 1000
col CLIENT_NAME for a20
col JOB_NAME for a20
SQL> SQL> SQL> col JOB_STATUS for a10
col WDURATION for 999999
col JDURATION for 999999
select CLIENT_NAME,
       to_char(WINDOW_START_TIME,'dd-mm-yy hh24:mi') "WSTART",
       extract(SECOND from WINDOW_DURATION) "WDURATION",
       JOB_NAME,
       JOB_STATUS,
       to_char(JOB_START_TIME,'dd-mm-yy hh24:mi') "JSTART",
       extract(SECOND from JOB_DURATION) "JDURATION"
from dba_autotask_job_history
where CLIENT_NAME like '%stat%'
order by 4;SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10

CLIENT_NAME          WSTART         WDURATION JOB_NAME             JOB_STATUS JSTART         JDURATION
-------------------- -------------- --------- -------------------- ---------- -------------- ---------
auto optimizer stats 03-01-22 22:00        49 ORA$AT_OS_OPT_SY_301 SUCCEEDED  03-01-22 22:00        31
 collection

auto optimizer stats 13-01-22 22:00        29 ORA$AT_OS_OPT_SY_321 SUCCEEDED  13-01-22 22:00        13
 collection

auto optimizer stats 31-01-22 22:00        55 ORA$AT_OS_OPT_SY_341 SUCCEEDED  31-01-22 22:00        21
 collection

auto optimizer stats 01-02-22 22:00         0 ORA$AT_OS_OPT_SY_361 SUCCEEDED  01-02-22 22:00         3
 collection

Leave a Reply

%d bloggers like this: