OraFAQ Forum: Performance Tuning » Select with Union Performance issue

Home » RDBMS Server » Performance Tuning » Select with Union Performance issue (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select with Union Performance issue [message #648709]

Tue, 01 March 2016 08:40

emyk
Messages: 9
Registered: February 2010
Location: usa

Junior Member

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.

Code: [Select all] [Show/ hide]

  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')

Report message to a moderator

Re: Select with Union Performance issue [message #648710 is a reply to message #648709]

Tue, 01 March 2016 08:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator