Yuan Yao, An Oracle ACE's Blog

SELECT *
FROM (
    SELECT
        a.sql_id,
        COUNT(*) AS cnt,
        TO_CHAR(100 * TRUNC(RATIO_TO_REPORT(COUNT(*)) OVER (), 4), 'FM990.99') || '%' AS "%",
        SUBSTR(t.sql_text, 1, 40) AS sql_text
    FROM
        v$active_session_history a
        LEFT JOIN v$sqlstats t ON a.sql_id = t.sql_id
    WHERE
        sample_time > SYSDATE - 30 / 60 / 24
        AND a.sql_id IS NOT NULL
    GROUP BY
        a.sql_id,
        SUBSTR(t.sql_text, 1, 40)
    HAVING
        COUNT(*) > 5
    ORDER BY
        COUNT(*) DESC
)
WHERE ROWNUM <= 10;

SQL_ID             CNT %        SQL_TEXT
f90zn75aphu4w     3352 97.38%   SELECT COUNT(DISTINCT (S_I_ID)) FROM ORD
16dhat4ta7xs9       15 0.43%    begin neword(:no_w_id,:no_max_w_id,:no_d
3kqrku32p6sfn       14 0.4%     MERGE /*+ OPT_PARAM('_parallel_syspls_ob
b13g21mgg8y98       14 0.4%     insert /* KSXM:TAKE_SNPSHOT */ into sys.
f3yfg50ga0r8n        8 0.23%    select obj# from obj$ where dataobj# = :
9sg6u8xys290z        7 0.2%     select count(*) num_enabled, sum(case op
730vdzhng6m6g        7 0.2%     update sys.scheduler$_job set  next_run_
gwcvcq87cn4sc        7 0.2%     INSERT INTO ORDERS (O_ID, O_D_ID, O_W_ID
aw9ttz9acxbc3        6 0.17%    BEGIN payment(:p_w_id,:p_d_id,:p_c_w_id,
c7fnaqcmbm0b5        6 0.17%    SELECT SUM(NUM_MAPPINGS+1) FROM smon_scn

10 rows selected.