The following topic just came up the other day whether you can check if a table was accessed in the past by using the DBA_HIST_SEG_STAT view (diagnostics pack licence necessary).
The idea is that you can get a more or less accurate result by checking the logical IO column of the view. My concern was that the accuracy of this information can vary depending on the internal maintenance task jobs. (statistics gathering, segment advisor)
Gather stats is fine since if the table content/size hasn't been changing recently the table will be excluded. But what about the segment advisor?
Let's run a quick test (12.1.0.2):
create table segadv_lio
as
select *
from all_objects, (select level l from dual connect by level <= 2);
Table created.
BALAZS@TESTDB
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
create or replace procedure run_sa
authid current_user
as
obj_id number;
begin
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_name => 'Manual_T' );
dbms_advisor.create_object (
task_name => 'Manual_T',
object_type => 'TABLE',
attr1 => user,
attr2 => 'SEGADV_LIO',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => 'Manual_T',
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task('Manual_T');
dbms_advisor.delete_task('Manual_T');
end;
/
Procedure created.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
col object_name for a30
col owner for a30
select obj.object_name,obj.owner,sg.LOGICAL_READS_DELTA,sg.snap_id
from DBA_HIST_SEG_STAT sg
join dba_objects obj
on sg.obj#=obj.object_id
where obj.owner='BALAZS'
order by snap_id;
OBJECT_NAME OWNER LOGICAL_READS_DELTA SNAP_ID
--------------- ------------- ------------------- ----------
SEGADV_LIO BALAZS 704 620
This is starting position, the table will now be analysed with the segment advisor.
SQL> exec run_sa
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_snapshot;
select obj.object_name,obj.owner,sg.LOGICAL_READS_DELTA,sg.snap_id
from DBA_HIST_SEG_STAT sg
join dba_objects obj
on sg.obj#=obj.object_id
where obj.owner='BALAZS'
order by snap_id;
OBJECT_NAME OWNER LOGICAL_READS_DELTA SNAP_ID
---------------- --------------- ------------------- ----------
SEGADV_LIO BALAZS 704 620
SEGADV_LIO BALAZS 6480 623
2 rows selected.
Trying to force physical reads
SQL> alter system flush buffer_cache;
System altered.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> exec run_sa
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
select obj.object_name,obj.owner,sg.LOGICAL_READS_DELTA,sg.snap_id
from DBA_HIST_SEG_STAT sg
join dba_objects obj
on sg.obj#=obj.object_id
where obj.owner='BALAZS'
order by snap_id;
OBJECT_NAME OWNER LOGICAL_READS_DELTA SNAP_ID
------------------- ---------------- ------------------- ----------
SEGADV_LIO BALAZS 704 620
SEGADV_LIO BALAZS 6480 623
SEGADV_LIO BALAZS 6480 625
The question remains open how the segment advisor algorithm works whether it checks when the table was last analysed and if it hasn't been touched for a while it may omits it from the segment advisor job.
