Program and service_name using the ASH data

If you have a Diagnostic and Tuning Pack or an Enterprise license, you can track sessions activity in DB with Active Session History (ASH) data. Here are examples of aggregate SQL queries on ASH views, to use it replace sql_id, dbid, begin_snap_id and and_snap_id proper values.

SELECT A.INST_ID, S.NAME SERVICE_NAME, A.PROGRAM, COUNT(*) FROM GV$ACTIVE_SESSION_HISTORY A, GV$SERVICES S WHERE A.INST_ID = S.INST_ID AND A.SERVICE_HASH = S.NAME_HASH
AND A.SQL_ID='SQL_ID'
GROUP BY A.INST_ID, S.NAME, A.PROGRAM;

SELECT AH.INSTANCE_NUMBER, S.NAME SERVICE_NAME, AH.PROGRAM, COUNT(*) FROM DBA_HIST_ACTIVE_SESS_HISTORY AH, GV$SERVICES S WHERE AH.DBID = DBID#
AND AH.SNAP_ID BETWEEN BEGIN_SNAP_ID# AND END_SNAP_ID#
AND AH.INSTANCE_NUMBER = S.INST_ID AND AH.SERVICE_HASH = S.NAME_HASH
AND AH.SQL_ID='SQL_ID'
GROUP BY AH.INSTANCE_NUMBER, S.NAME, AH.PROGRAM;