[askdba] Re: lot of time taking on qa than production

  • From: drumil narayan <drumilnarayan@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Mon, 11 Aug 2008 17:39:33 +0530 (IST)

thanks to all..
after analysing the indexes the performance was dramatically gained...
thanks once again..drumil



----- Original Message ----
From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
To: askdba@xxxxxxxxxxxxx
Sent: Friday, 8 August, 2008 8:05:51 PM
Subject: [askdba] Re: lot of time taking on qa than production

The stats on the tables and indexes. 
-------------------------------------------------
Sent from my BlackBerry Wireless Handheld 
________________________________
From: drumil narayan <drumilnarayan@xxxxxxxxx>
Date: Fri, 8 Aug 2008 20:04:27 +0530 (IST)
To: <askdba@xxxxxxxxxxxxx>
Subject: [askdba] lot of time taking on qa than production

Our production and qadatabases are same in structure and hardware.
 
following query is taking approx 2 minutes to fetch about 4568878 where as am 
firing the same query on qa it is still running.
no of indexs and columns are identical on the both the databases..where should 
i look for..
 
 
select m.sic_code sic_code from
zips z,
addresses a,
memberships m,
territorys t 
where m.MEM_SOURCE != 'MCCOY NOM' 
and m.mem_type = 'GOV'
and ((m.CANCEL_TYPE IS NULL OR m.CANCEL_TYPE IN('REINSTATE','EXTENSION') OR 
m.CANCEL_TYPE LIKE 'PO%') OR SYSDATE - m.CANCEL_DT < 366) 
and m.mem_id = a.mem_id
and a.addr_type ='BUS'
and a.zip_code = z.zip_code
and substr(lpad(to_char(abs(z.zip_code)),9,'0'),1,5) = t.zip_code
and z.state=t.state
and z.county=t.county_code
 
thanks
________________________________
Did you know? You can CHAT without downloading messenger. Click here


      Connect with friends all over the world. Get Yahoo! India Messenger at 
http://in.messenger.yahoo.com/?wm=n/

Other related posts: