Fw: Minimize Performance Hit on Sort...Help!

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Tue, 2 Feb 2010 09:03:12 -0800 (PST)
Message-ID: <862747.58545.qm_at_web32005.mail.mud.yahoo.com>

OK, so don't hit the tab bar twice, it will send the email before you are done!! :) I've finished my request and filled in the data, sorry folks for the spam! Any help, probably something blaring on an issue here that I have been looking straight at and the sinus infection I'm battling has moved onto my brain, so I appreciate it! :) ~Kellyn

• On Tue, 2/2/10, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:

From: Kellyn Pedersen <kjped1313_at_yahoo.com> Subject: Minimize Performance Hit on Sort...Help! To: "oracle Freelists" <oracle-l_at_freelists.org> Date: Tuesday, February 2, 2010, 9:57 AM

I'm at a total brain block on this one-  Query with a order by that MUST be done in Oracle as the file is an output to a dataset in SAS.  The query is through a view and I would prefer to avoid parallel, as this makes the sorting problem worse, (sorting large amounts in parallel is just NOT a good thing...) 
 

The view-

SELECT /*+(index(i INDV_IDX03) parallel(o 4) use_hash(i g INDV_IDX03) */ i .ibid,
i.ind_mod_key,
i.ibhid,
DECODE (i.geo_census_id,
0, 'N',
NVL2 (g.geo_census_tract, 'B', 'Z'))

AS cmatch,
g.zipcode,
g.zpwhite,
g.zpblack,
g.zpasian,
g.zporace,
g.zphisp,
g.zpmale,
g.zpfeml,
g.zpag01,
g.zpag02,
g.zpag03,
g.zpag04,
g.zpag05,
g.zpag06,
g.zpag07,
g.zpag08,
g.zpag09,
g.zpmhher,
g.zpfhher,
g.zpchild,
g.zh1524,
g.zh2534,
g.zh3544,
g.zh4554,
g.zh5564,
g.zh6574,
g.zh7584,
g.zh85up,
g.zhp01,
g.zhp02,
g.zhp03,
g.zhp04,
g.zhp05,
g.zfmar,
g.zfmch,
g.zfnfm,
g.zfnmch,
g.zfnff,
g.zfnfch,
g.zfc0002,
g.zfc0305,
g.zfc0611,
g.zfc1213,
g.zfc1417,
g.zpnm,
g.zpmar,
g.zpwid,
g.zpdiv,
g.zplogn,
g.zplosp,
g.zploie,
g.zploap,
g.zpfb,
g.zpfbcit,
g.zpnocit,
g.zpmtc,
g.zpmtp,
g.zpwah,
g.zptrv1,
g.zptrv2,
g.zptrv3,
g.zptlh6,
g.zptlh1,
g.zptlh2,
g.zptlh3,
g.zptlh4,
g.zptlh5,
g.zpsch01,
g.zpsch02,
g.zpsch03,
g.zpsch04,
g.zpsch05,
g.zpsch06,
g.zpedu1,
g.zpedu2,
g.zpedu3,
g.zpedu4,
g.zpedu5,
g.zpvet,
g.zhfull,
g.zfhww,
g.zpocc1,
g.zpocc2,
g.zpocc3,
g.zpocc4,
g.zpocc5,
g.zpocc6,
g.zhinco1,
g.zhinco2,
g.zhinco3,
g.zhinco4,
g.zhinco5,
g.zhinco6,
g.zhinco7,
g.zhinco8,
g.zhinco9,
g.zhinc10,
g.zhinc11,
g.zmsal99,
g.zmsel99,
g.zmint99,
g.zmsoc99,
g.zmsec99,
g.zmret99,
g.zpbe1,
g.zpbe2,
g.zpae1,
g.zpae2,
g.zphe1,
g.zphe2,
g.zhbi1,
g.zhbi2,
g.zhbi3,
g.zhai1,
g.zhai2,
g.zhai3,
g.zhhi1,
g.zhhi2,
g.zhhi3,
g.zhmm,
g.zhff,
g.zhhup,
g.zpgcrn,
g.zpgcry,
g.zplfr,
g.zplit,
g.zplpt,
g.zplge,
g.zplhe,
g.zplgr,
g.zplru,
g.zplpl,
g.zplsl,
g.zplps,
g.zplin,
g.zplch,
g.zpljp,
g.zplko,
g.zplsa,
g.zplvn,
g.zplta,
g.zplar,
g.zplaf,
g.zpsanc,
g.zpfaar,
g.zpfaen,
g.zpfacz,
g.zpfadu,
g.zpfafr,
g.zpfage,
g.zpfagr,
g.zpfair,
g.zpfait,
g.zpfano,
g.zpfapl,
g.zpfaru,
g.zpfasi,
g.zpfasc,
g.zpfaaf,
g.zpfasw,
g.zpfawi,
g.zppbneu,
g.zppbweu,
g.zppbseu,
g.zppbeeu,
g.zppbeas,
g.zppbcas,
g.zppbsea,
g.zppbafr,
g.zppbcrb,
g.zppbcam,
g.zppbsam,
g.zuurb,
g.zurur,
g.zuoo,
g.zuro,
g.zuoowh,
g.zuoobl,
g.zuooas,
g.zuoohs,
g.zunr1,
g.zunr2,
g.zunr3,
g.zunr4,
g.zuhu1,
g.zuhu2,
g.zuhu3,
g.zuhu4,
g.zuhu5,
g.zuage1,
g.zuage2,
g.zuage3,
g.zuage4,
g.zulr1,
g.zulr2,
g.zulr3,
g.zulr4,
g.zulr5,
g.zugas,
g.zuele,
g.zuoil,
g.zuofu,
g.zubed1,
g.zubed2,
g.zubed3,
g.zubed4,
g.zutel,
g.zuveh1,
g.zuveh2,
g.zuveh3,
g.zuveh4,
g.zuplumb,
g.zukitch,
g.zurent1,
g.zurent2,
g.zurent3,
g.zurent4,
g.zurent5,
g.zurent6,
g.zugr01,
g.zugr02,
g.zugr03,
g.zuval01,
g.zuval02,
g.zuval03,
g.zuval04,
g.zuval05,
g.zuval06,
g.zuval07,
g.zmvall99,
g.zudebt,
g.zusmrtg,
g.zumrtg0,
g.zumrtg1,
g.zumrtg2,
g.zumrtg3,

g.zumrtg4,
g.zminc99
FROM individual_dim i, geo_census_dim g
WHERE g.geo_census_id = i.geo_census_id
AND EXISTS
(SELECT /*+ use_hash(o,i) */
*
FROM order_sum o
WHERE o.ibid = i.ibid
AND o.recency_key = 48
AND o.member_id = 0);
 
 

The simple query with the order by-, (without the order by, the data will come back in seconds...)
 

select smap.* from CENSUS_0_48 smap order by ibhid, ibid;
 

Work area usage by this query with the order by included: Operation pga  memory  temp
SORT (v2) 511 1024 2403
GROUP BY (SORT) 1 95 1907
 

Is there a better way to sort this data inside the database?  Inside the view?  Something wrong with the view itself?  These were written a long time ago and I see the group by and the sort as the contributing factor here...  It's a LOT of data, about 95 million records... :(

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com
 

"Go away before I replace you with a very small and efficient shell script..."

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 02 2010 - 11:03:12 CST