Home » RDBMS Server » Performance Tuning » Select with Union Performance issue (Oracle 11g)
Select with Union Performance issue [message #648709] Tue, 01 March 2016 08:40 Go to next message
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.

  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')
Re: Select with Union Performance issue [message #648710 is a reply to message #648709] Tue, 01 March 2016 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 26204
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
Re: Select with Union Performance issue [message #648711 is a reply to message #648710] Tue, 01 March 2016 08:58 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
4) Find out why those hints are there and have whomever added them explain why.
Re: Select with Union Performance issue [message #648740 is a reply to message #648711] Wed, 02 March 2016 02:59 Go to previous message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
First thing to do is replace the UNION with UNION ALL. UNION does a distinct which is a waste of time with those selects.
Previous Topic: Performance issue enq: TX index contention
Next Topic: Size of SYSAUX is getting bigger
Goto Forum:
  


Current Time: Sun Oct 21 03:45:43 CDT 2018