“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 , processed by brain and react to the situation around as per the collected information. 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