SELECT
*
FROM
(
SELECT
p.OBJECT_OWNER,
p.SQL_ID,
S.RUNTIME_MEM,
S.BUFFER_GETS,
S.SORTS,
S.DISK_READS,
S.PARSE_CALLS,
S.SHARABLE_MEM,
S.EXECUTIONS,
ROUND(S.DISK_READS/DECODE(S.EXECUTIONS,0,1,S.EXECUTIONS),0) as DISK_READS_PER_EXEC,
ROUND(S.BUFFER_GETS/DECODE(S.EXECUTIONS,0,1,S.EXECUTIONS),0) as BUF_GETS_PER_EXEC,
ROUND(S.PARSE_CALLS/DECODE(S.EXECUTIONS,0,1,S.EXECUTIONS),1) as PARSE_CALLS_PER_EXEC,
S.SQL_FULLTEXT,
S.LAST_LOAD_TIME,
ROW_NUMBER() OVER(PARTITION BY P.SQL_ID ORDER BY P.SQL_ID) AS ROWNUMS
FROM V$SQL_PLAN P ,V$SQLAREA S
WHERE
S.SQL_ID=P.SQL_ID
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
AND P.OBJECT_OWNER='LCAM_DEVELOP'
ORDER BY S.SQL_TEXT,P.SQL_ID
) A WHERE A.ROWNUMS=1
AND DISK_READS_PER_EXEC>0
AND BUF_GETS_PER_EXEC>0
ORDER BY A.DISK_READS_PER_EXEC DESC,BUF_GETS_PER_EXEC DESC
;
