Parallel Queries:

/* AWR Query: Fetch SQL queries with parallel hint from DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT */

SELECT DISTINCT

hst.sql_id,

hst.sql_text,

hss.executions_total AS executions,

hss.elapsed_time_total / 1e6 AS elapsed_time_secs,

hss.disk_reads_total AS disk_reads,

hss.buffer_gets_total AS buffer_gets,

hss.cpu_time_total / 1e6 AS cpu_time_secs,

hsn.begin_interval_time,

hsn.end_interval_time

FROM

dba_hist_sqltext hst

JOIN

dba_hist_sqlstat hss

ON hst.sql_id = hss.sql_id

JOIN

dba_hist_snapshot hsn

ON hss.snap_id = hsn.snap_id

AND hss.instance_number = hsn.instance_number

WHERE

UPPER(hst.sql_text) LIKE '%/*+ PARALLEL%'

AND hsn.begin_interval_time >= SYSDATE - 7 -- Last 7 days, adjust as needed

ORDER BY

hsn.begin_interval_time DESC;

/* ASH Query (Historical): Fetch SQL queries with parallel hint from DBA_HIST_ACTIVE_SESS_HISTORY */

/* ASH Query (Historical): Fetch SQL queries with parallel hint from DBA_HIST_ACTIVE_SESS_HISTORY, including username */

SELECT DISTINCT

ash.sql_id,

(SELECT sql_text

FROM dba_hist_sqltext hst

WHERE hst.sql_id = ash.sql_id

AND ROWNUM = 1) AS sql_text,

(SELECT username

FROM dba_users u

WHERE u.user_id = ash.user_id

AND ROWNUM = 1) AS username,

COUNT(DISTINCT ash.session_id) AS parallel_sessions,

MIN(ash.sample_time) AS first_seen,

MAX(ash.sample_time) AS last_seen

FROM

dba_hist_active_sess_history ash

WHERE

(ash.event LIKE 'PX%' OR ash.event LIKE 'parallel%')

AND ash.session_type = 'FOREGROUND'

AND ash.program LIKE '%(P%)' -- Parallel slave processes, e.g., P000, P001

AND ash.sample_time >= SYSDATE - 1 -- Last 1 day, adjust as needed

AND EXISTS (

SELECT 1

FROM dba_hist_sqltext hst

WHERE hst.sql_id = ash.sql_id

AND UPPER(hst.sql_text) LIKE '%/*+ PARALLEL%'

)

GROUP BY

ash.sql_id,

ash.user_id

ORDER BY

last_seen DESC;

/* ASH Query (Real-Time): Fetch SQL queries with parallel hint from V$ACTIVE_SESSION_HISTORY */

SELECT DISTINCT

ash.sql_id,

(SELECT sql_text

FROM v$sqltext hst

WHERE hst.sql_id = ash.sql_id

AND hst.piece = 0

AND ROWNUM = 1) AS sql_text,

COUNT(DISTINCT ash.session_id) AS parallel_sessions,

MIN(ash.sample_time) AS first_seen,

MAX(ash.sample_time) AS last_seen

FROM

v$active_session_history ash

WHERE

(ash.event LIKE 'PX%' OR ash.event LIKE 'parallel%')

AND ash.session_type = 'FOREGROUND'

AND ash.program LIKE '%(P%)' -- Parallel slave processes, e.g., P000, P001

AND ash.sample_time >= SYSDATE - 1 -- Last 1 day, adjust as needed

AND EXISTS (

SELECT 1

FROM v$sqltext hst

WHERE hst.sql_id = ash.sql_id

AND UPPER(hst.sql_text) LIKE '%/*+ PARALLEL%'

)

GROUP BY

ash.sql_id

ORDER BY

last_seen DESC;