rem
rem	Script:		get_sql_ops_last_7d.sql
rem	Author:		Jeff Moss
rem	Dated:		January 2007
rem	Purpose:	Gets the SQL Operations for the last 7 days 
rem                      from the AWR ASH data
rem
rem	Versions tested 
rem		10.2.0.2
rem

WITH ash AS
(
SELECT TRUNC(ash.sample_time) sample_day
,      (CASE WHEN ash.sql_opcode = 47
             THEN 'PL/SQL'
             WHEN ash.sql_opcode IN(1)
             THEN 'DDL'
             WHEN ash.sql_opcode IN(2,6,7,189)
             THEN 'DML'
             WHEN ash.sql_opcode IN(50)
             THEN 'Explain Plan'
             WHEN ash.sql_opcode IN(3)
             THEN 'Query'
             ELSE 'No Statement ID; In object type:
'||NVL(o.object_type,'Not Specified')
              END) statement_type
,      ROW_NUMBER() OVER(PARTITION BY ash.sql_id,ash.sql_child_number
ORDER BY ash.sample_time DESC) rn
FROM   dba_hist_snapshot s 
,      dba_hist_active_sess_history ash
,      dba_objects o
WHERE  s.snap_id = ash.snap_id(+)
AND    s.dbid = ash.dbid(+)
AND    s.instance_number  = ash.instance_number(+)
AND    ash.plsql_entry_object_id = o.object_id(+)
AND    TRUNC(ash.sample_time) BETWEEN TRUNC(SYSDATE-6) AND
TRUNC(SYSDATE+1) -- all within last 7 days
)
SELECT sample_day
,      statement_type
,      COUNT(1)
FROM   ash
WHERE  rn = 1
GROUP BY ROLLUP(sample_day)
,        ROLLUP(statement_type)
ORDER BY sample_day
,        statement_type
/