Histograms
Without histograms, the
optimizer assumes the distribution of values in a column to be uniform.
There are several types of histograms:
- Frequency
- Height balanced
- Top-frequencey (12c +)
- Hybrid (12c +)
- ?
drop table tq84_histogram_test;
create table tq84_histogram_test (
nm number,
tx varchar2(100)
);
insert into tq84_histogram_test values ( 5, 'A');
insert into tq84_histogram_test values ( 5, 'B');
insert into tq84_histogram_test values ( 5, 'C');
insert into tq84_histogram_test values (100, 'D');
insert into tq84_histogram_test values (100, 'E');
insert into tq84_histogram_test values (100, 'F');
insert into tq84_histogram_test values (104, 'G');
insert into tq84_histogram_test values (104, 'H');
insert into tq84_histogram_test values (104, 'I');
Query histogram:
select
own.name owner,
obj.name obj_name,
col.name col_name,
-----------------
hgr.bucket,
hgr.endpoint,
hgr.ep_repeat_count,
hgr.epvalue_raw, hgr.epvalue,
hgr.savtime,
dense_rank() over (partition by own.name, obj.name, col.name order by hgr.savtime desc) savtime_rank
from
sys.obj$ obj join
sys.user$ own on obj.owner# = own.user# join
sys.col$ col on obj.obj# = col.obj# left join
"_OPTSTAT_HISTGRM_HISTORY_DEC" hgr on col.obj# = hgr.obj# and
col.col# = hgr.intcol#
where
own.name = 'RENE' and
obj.name = 'TQ84_HISTOGRAM_TEST' and
col.name = 'NM';
Automatic gathering of statistics
By default, Oracle automatically gathers statistics for
database objects where statistics are missing or stale during a predefined maintance window (10 PM through 2 AM on weekdays and 6 AM through 2 AM on weekends).
The maintainance window can be changed using
Disable automatic statistics:
begin
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => null,
window_name => null
);
end;
/
Query some characteristics about the automatic gathering:
select * from dba_autotask_task where client_name = 'auto optimizer stats collection';
select * from dba_autotask_client where client_name = 'auto optimizer stats collection';
select * from dba_scheduler_programs where program_name = 'GATHER_STATS_PROG';
select * from dba_scheduler_window_groups where window_group_name = 'MAINTENANCE_WINDOW_GROUP';
select * from dba_scheduler_wingroup_members where window_group_name = 'MAINTENANCE_WINDOW_GROUP';
select * from dba_scheduler_windows where resource_plan = 'DEFAULT_MAINTENANCE_PLAN';