A quick query to find top TEMP abusers in case of a query within a package

I was looking into my query library the other day and the existing query for historical temp tablespace usage I had was not satisfying my needs in case of search for a top temp abuser. I turned out to be a bug caused by DBMS_FEATURE_USAGE_INTERNAL. 



SELECT h.sql_id,
  h.TOP_LEVEL_SQL_ID,
  MAX(round(temp_space_allocated/power(1024,3),2)) MAX_USER_TEMP_GB ,
  NVL(TO_CHAR(SUBSTR(s.sql_Text, 0,3900)),'Check the TOP_LEVEL_SQL_ID: select * from SYS.DBA_HIST_SQLTEXT where sql_id='''||TOP_LEVEL_SQL_ID||''';  -- OR --  no sql_text was captured')  
FROM DBA_HIST_ACTIVE_SESS_HISTORY h
LEFT JOIN SYS.DBA_HIST_SQLTEXT s
ON h.sql_id=s.sql_id
WHERE h.sample_time > sysdate-1
  and TEMP_SPACE_ALLOCATED > (10*power(1024,3))
GROUP BY h.sql_id,
  h.TOP_LEVEL_SQL_ID,
  TO_CHAR(SUBSTR(s.sql_Text, 0,3900))
ORDER BY 3 DESC nulls last;

Can historical table access be determined based on DBA_HIST_SEG_STAT statistics?

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.