I have the below SQL query which takes very long to run, but runs fast in TEST (Same data as PROD).
Our DBA added some index into the select statement to enhance performance, but still takes very long to finish.
So I am hoping someone would point out best practice that enhance performance.
SELECT /*+USE_NL(ALACTIVITY) INDEX(ALACTIVITY) */ ALACTIVITY.GUSTID as USER_ID, 'GUST' as RESPONSIBILITY_, to_char (ALACTIVITY.SDATE, 'MM-YYYY') as MONTH, count (ALACTIVITY.SUBJECT) as COUNT
FROM SKUSERALACTIVITY ALACTIVITY
WHERE ALACTIVITY.CITYID NOT IN (1,3,5) AND (ALACTIVITY.ID=0) AND (ALACTIVITY.SDATE >= to_date('2015-01-01','yyyy-mm-dd')) AND (ALACTIVITY.SDATE < to_date('2015-04-01','yyyy-mm-dd'))
Group by GUSTID, to_char (ALACTIVITY.SDATE, 'MM-YYYY')
UNION
SELECT /*+USE_NL(ALACTIVITY) INDEX(ALACTIVITY) */ ALACTIVITY.ACTORID as USER_ID, 'ACTOR' as RESPONSIBILITY_, to_char (ALACTIVITY.SDATE, 'MM-YYYY') as MONTH, count (ALACTIVITY.SUBJECT) as COUNT
FROM SKUSERALACTIVITY ALACTIVITY
WHERE ALACTIVITY.ACTIVITYTYPEID IN (2,4,6) AND (ALACTIVITY.EMPID=0) AND (ALACTIVITY.SDATE < to_date('2015-01-01','yyyy-mm-dd')) AND (ALACTIVITY.SDATE < to_date('2015-04-01','yyyy-mm-dd'))
Group by ACTORID, to_char (ALACTIVITY.SDATE, 'MM-YYYY')
UNION
SELECT /*+USE_NL(ALACTIVITY) INDEX(ALACTIVITY) */ ALACTIVITY.ACTORID as USER_ID, 'MANAGER' as RESPONSIBILITY_, to_char (ALACTIVITY.SDATE, 'MM-YYYY') as MONTH, count (ALACTIVITY.SUBJECT) as COUNT
FROM SKUSERALACTIVITY ALACTIVITY
WHERE ALACTIVITY.ACTIVITYTYPEID IN (12,13) AND (ALACTIVITY.EMPID=0) AND (ALACTIVITY.SDATE < to_date('2015-01-01','yyyy-mm-dd')) AND (ALACTIVITY.SDATE < to_date('2015-04-01','yyyy-mm-dd'))
Group by ACTORID, to_char (ALACTIVITY.SDATE, 'MM-YYYY')