1. 先建立区分Good和Garbage SQL的视图

运行一次以上的sql定义为Good
只运行一次的定义为Garbage
根据业务特点，可以调整Good SQL和Bad SQL的区分标准，即调整标准的运行次数a.executions

REM
REM View to sort SQL into GOOD and GARBAGE
REM
CREATE OR REPLACE VIEW sql_garbage AS
SELECT
  b.username users,
  SUM(a.sharable_mem+a.persistent_mem) Garbage,
  TO_NUMBER(null) good
FROM
  sys.v_$sqlarea a, dba_users b
WHERE
  (a.parsing_user_id = b.user_id and a.executions<=1)
GROUP BY b.username
UNION
SELECT DISTINCT
  b.username users,
  TO_NUMBER(null) garbage,
  SUM(c.sharable_mem+c.persistent_mem) Good
FROM
  dba_users b, sys.v_$sqlarea c
WHERE
  (b.user_id=c.parsing_user_id and c.executions>1)
GROUP BY b.username;

2. 察看每个用户的good sql和garbage sql的比例

REM
REM Report on SQL Area Reuse by user
REM
column garbage format 9,999,999,999 heading 'Non-Shared SQL'
column good format 9,999,999,999 heading 'Shared SQL'
column good_percent format 999.99 heading 'Percent Shared'
set feedback off
break on report
compute sum of garbage on report
compute sum of good on report
compute avg of good_percent on report
@title80 'Shared Pool Utilization'
--spool rep_out&dbsql_garbage
select
  a.users,
  a.garbage,
  b.good,
  (b.good/(b.good+a.garbage))*100 good_percent
from
  sql_garbage a, sql_garbage b
where a.users=b.users
  and a.garbage is not null
  and b.good is not null
/
--spool off
set feedback off
clear columns
clear breaks
clear computes

执行结果如下: