top query from awr(revised)

Previous query was blogged in http://orababy.blogspot.in/2013/07/simplification-of-use-of-oracle-10g11g.html  and http://orababy.blogspot.in/2013/08/find-top-resource-intensive-queries-and.html

Here is revised top query, this time presented with sqlplus formatting:

set feedback off
set pagesize 50000
set linesize 8000
set trimspool on
set long 65535
set verify off
set verify off
set serveroutput on size 1000000
alter session set "_optimizer_cartesian_enabled"=false;
alter session set optimizer_mode=RULE;
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS';
Set Heading On
Set Feedback On
PROMPT
PROMPT **Top 50 Sql last 7 Days(s)**
PROMPT
set pages 10000 linesize 500
set trimspool on
COLUMN parsing_schema_name JUSTIFY C FORMAT a15 HEADING 'Parsing|Schema'
COLUMN '%TOT%' JUSTIFY C FORMAT a6 HEADING '%Tot%'
COLUMN 'SEC/EXE' JUSTIFY C FORMAT 99999.999 HEADING 'Sec|Exec'
COLUMN sql_text JUSTIFY C FORMAT a95 word_wrap HEADING 'SQLText'
COLUMN EXECUTIONS JUSTIFY C format 9,999,999,999 HEADING Executions
COLUMN DISK_READS JUSTIFY C format 9,999,999,999 HEADING DiskReads
COLUMN BUFFER_GETS JUSTIFY C format 9,999,999,999 HEADING BufferGets
COLUMN ELAPSED_TIME JUSTIFY C format 9,999,999,999 HEADING ElapsedTime
COLUMN CPU_TIME JUSTIFY C format 9,999,999,999 HEADING CpuTime
COLUMN ROWS_PROCESSED JUSTIFY C format 9,999,999,999 HEADING
RowsProcessed
COLUMN RANK noprint


SELECT
DISTINCT sub.parsing_schema_name,
sub.sql_id,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),CHR(10),' '),CHR(9),' '),' ',' '),'FROM',CHR(10)||'FROM'),
'from',CHR(10)||'from'),'AND ',CHR(10)||'AND '),'and ',CHR(10)||'and '),'WHERE ',CHR(10)||'WHERE '),
'where ',CHR(10)||'where ') SQL_TEXT,
sub.EXECUTIONS,ROUND((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)||'%' "%TOT%",
sub.DISK_READS,ROUND((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)||'%' "%TOT%",
sub.BUFFER_GETS,ROUND((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)||'%' "%TOT%",
sub.ELAPSED_TIME,ROUND((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100)||'%' "%TOT%",
sub.CPU_TIME,ROUND((sub.CPU_TIME/sub2.CPU_TIME_TOTAL)*100)||'%' "%TOT%",
ROWS_PROCESSED,
sub.SEC_PER_EXEC "SEC/EXE",
ROUND((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+
ROUND((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+
ROUND((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+
ROUND((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) RANK
FROM DBA_HIST_SQLTEXT DHST,
(
SELECT DISTINCT
SQL_ID,
PARSING_SCHEMA_NAME,
ROUND(SUM(EXECUTIONS_DELTA)) AS EXECUTIONS,
ROUND(SUM(PARSE_CALLS_DELTA)) AS PARSE_CALLS,
ROUND(SUM(DISK_READS_DELTA)) AS DISK_READS,
ROUND(SUM(BUFFER_GETS_DELTA)) AS BUFFER_GETS,
ROUND(SUM(ROWS_PROCESSED_DELTA)) AS ROWS_PROCESSED,
ROUND(SUM(CPU_TIME_DELTA/1000000)) AS CPU_TIME,
ROUND(SUM(ELAPSED_TIME_DELTA/1000000)) ELAPSED_TIME,
ROUND(SUM(IOWAIT_DELTA)/1000000) AS IOWAIT,
SUM(ELAPSED_TIME_DELTA/1000000)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA)) SEC_PER_EXEC
FROM
dba_hist_snapshot
NATURAL join
dba_hist_sqlstat DHS
NATURAL join
dba_hist_sql_plan DHSP
WHERE
BEGIN_INTERVAL_TIME >= SYSDATE-7
AND
parsing_schema_name NOT IN ('SYS','SYSTEM')
AND
object_owner NOT IN ('SYS','SYSTEM')
GROUP BY
SQL_ID,PARSING_SCHEMA_NAME
) sub,
(
SELECT DECODE(ROUND(SUM(EXECUTIONS_DELTA)),0,1,ROUND(SUM(EXECUTIONS_DELTA))) AS EXECUTIONS_TOTAL,
DECODE(ROUND(SUM(DISK_READS_DELTA)),0,1,ROUND(SUM(DISK_READS_DELTA))) AS DISK_READS_TOTAL,
DECODE(ROUND(SUM(BUFFER_GETS_DELTA)),0,1,ROUND(SUM(BUFFER_GETS_DELTA))) AS BUFFER_GETS_TOTAL,
DECODE(ROUND(SUM(ELAPSED_TIME_DELTA/1000000)),0,1,ROUND(SUM(ELAPSED_TIME_DELTA/1000000))) AS ELAPSED_TIME_TOTAL,
DECODE(ROUND(SUM(CPU_TIME_DELTA/1000000)),0,1,ROUND(SUM(CPU_TIME_DELTA/1000000))) AS CPU_TIME_TOTAL
FROM
dba_hist_snapshot
NATURAL join
dba_hist_sqlstat DHS
NATURAL join
dba_hist_sql_plan DHSP
WHERE
BEGIN_INTERVAL_TIME >= SYSDATE-7
AND
parsing_schema_name NOT IN ('SYS','SYSTEM')
AND
object_owner NOT IN ('SYS','SYSTEM')
) sub2
WHERE DHST.sql_id = sub.sql_id
AND ROUND((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+
ROUND((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+
ROUND((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+
ROUND((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) > 4
AND ROWNUM < 51
AND sub.SEC_PER_EXEC >= .001
ORDER BY RANK DESC