DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws;  s=s1024; d=yahoo.com;  h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type;  b=pgaFNP4HOlNiBRXV6YybQluKQBJRWBRBzFBtPB1ZJXnuLaWTPebnRL5SmX4CwfdXx+CQNp+qClIKCLnuIkQdqWMXFI5q9p3FvwWjzQ8AHlW49/tFyBPo1NmmkACtut5NpjPZrkWeldBx6r5KmVnHPYn74ZWJwqIdl7Fc5BqfPO0=;
Message-ID: <116008.81574.qm@web32001.mail.mud.yahoo.com>
Date: Tue, 2 Feb 2010 15:46:47 -0800 (PST)
From: Kellyn Pedersen <kjped1313@yahoo.com>
Subject: Re: Minimize Performance Hit on Sort...Help!
To: greg@structureddata.org
Cc: oracle Freelists <oracle-l@freelists.org>
In-Reply-To: <a9c093441002021220q59b4e445j3f36d06d14df2060@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-81090712-1265154407=:81574"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: kjped1313@yahoo.com
--0-81090712-1265154407=:81574
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Greg,
I am still spending time on the sorting issue in parallel.=A0 I do believe =
part of the main issue is in the design and the quantity of data that is be=
ing sorted and the joins involved in sorting.=A0 I've only been here=A0five=
 months, but redesign from wide tables to a star schema is something that I=
've been spending time with our lead BI developer on.=A0 Our "step through"=
 design is much=A0improved and tuned, but it still hits these wide tables.=
=A0 The view involved here is legacy code that=A0with the current design, t=
here isn't much improvement that I can see to offer or no one is willing to=
 compromise and use the correct process to sort all this, (that they need t=
o sort all this?=A0 Who is going to go through the millions and millions of=
 rows of data that is being output to the SAS data set?=A0 I have no idea..=
.)=A0=20
The previous architects thought that they could run the business on tables =
with 100's of columns wide and trillions of rows deep, then select, sort an=
d present the data anyway they want.
Prune partitions?=A0 Who needs to prune partitions?=A0 We keep everything!=
=A0 Saturate disk I/O?=A0 Unheard of!=A0 Now they say I've striped too much=
 and the server=A0bus is showing stress... :)
OK, Kellyn is getting off her soapbox...=A0 I know, I know, fix the design,=
 fix the code, otherwise I'm just bandaiding and duct tapin'... :(


Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
=A0
"Go away before I replace you with a very small and=A0efficient shell scrip=
t..."

--- On Tue, 2/2/10, Greg Rahn <greg@structureddata.org> wrote:


From: Greg Rahn <greg@structureddata.org>
Subject: Re: Minimize Performance Hit on Sort...Help!
To: kjped1313@yahoo.com
Cc: "oracle Freelists" <oracle-l@freelists.org>
Date: Tuesday, February 2, 2010, 1:20 PM


I would comment that sorting large amounts of data parallel IS a good
thing - that is the whole premise behind parallel computing.=A0 It's
just that the sort operators have to be well placed.=A0 If your parallel
sort is not performing, then the obvious question would be why (what
does the ASH/AWR data show it is waiting on?)

When you say it "comes back" does that mean starts to return rows, or
it completes?=A0 If a sort (order by) is requested, then no rows can be
returned to the client until they are ordered, where as w/o an order
by they can be returned immediately.

Can you post the execution plans for the order by and w/o order by?

On Tue, Feb 2, 2010 at 8:57 AM, Kellyn Pedersen <kjped1313@yahoo.com> wrote=
:
>
> I'm at a total brain block on this one-=A0 Query with a order by that MUS=
T be done in Oracle=A0as the file is an output to a dataset in SAS.=A0 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 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;

--
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l


=0A=0A=0A      
--0-81090712-1265154407=:81574
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<table cellspacing=3D"0" cellpadding=3D"0" border=3D"0" ><tr><td valign=3D"=
top" style=3D"font: inherit;"><DIV>Hi Greg,</DIV>
<DIV>I am still spending time on the sorting issue in parallel.&nbsp; I do =
believe part of the main issue is in the design and the quantity of data th=
at is being sorted and the joins involved in sorting.&nbsp; I've only been =
here&nbsp;five months, but redesign from wide tables to a star schema is so=
mething that I've been spending time with our lead BI developer on.&nbsp; O=
ur "step through" design is much&nbsp;improved and tuned, but it still hits=
 these wide tables.&nbsp; The view involved here is legacy code that&nbsp;w=
ith the current design, there isn't much improvement that I can see to offe=
r or no one is willing to compromise and use the correct process to sort al=
l this, (that they need to sort all this?&nbsp; Who is going to go through =
the millions and millions of rows of data that is being output to the SAS d=
ata set?&nbsp; I have no idea...)&nbsp; </DIV>
<DIV>The previous architects thought that they could run the business on ta=
bles with 100's of columns wide and trillions of rows deep, then select, so=
rt and present the data anyway they want.</DIV>
<DIV>Prune partitions?&nbsp; Who needs to prune partitions?&nbsp; We keep e=
verything!&nbsp; Saturate disk I/O?&nbsp; Unheard of!&nbsp; Now they say I'=
ve striped too much and the server&nbsp;bus is showing stress... :)</DIV>
<DIV>OK, Kellyn is getting off her soapbox...&nbsp; I know, I know, fix the=
 design, fix the code, otherwise I'm just bandaiding and duct tapin'... :(<=
BR><BR></DIV>
<DIV>Kellyn Pedersen</DIV>
<DIV>Multi-Platform DBA</DIV>
<DIV>I-Behavior Inc.</DIV>
<DIV><A title=3D"View public profile" href=3D"http://www.linkedin.com/in/ke=
llynpedersen" target=3D_blank rel=3Dnofollow name=3DwebProfileURL>http://ww=
w.linkedin.com/in/kellynpedersen</A></DIV>
<DIV><A href=3D"http://www.dbakevlar.blogspot.com/" target=3D_blank rel=3Dn=
ofollow>www.dbakevlar.blogspot.com</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>"Go away before I replace you with a very small and&nbsp;efficient she=
ll script..."</DIV><BR><BR>--- On <B>Tue, 2/2/10, Greg Rahn <I>&lt;greg@str=
uctureddata.org&gt;</I></B> wrote:<BR>
<BLOCKQUOTE style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: rgb(=
16,16,255) 2px solid"><BR>From: Greg Rahn &lt;greg@structureddata.org&gt;<B=
R>Subject: Re: Minimize Performance Hit on Sort...Help!<BR>To: kjped1313@ya=
hoo.com<BR>Cc: "oracle Freelists" &lt;oracle-l@freelists.org&gt;<BR>Date: T=
uesday, February 2, 2010, 1:20 PM<BR><BR>
<DIV class=3DplainMail>I would comment that sorting large amounts of data p=
arallel IS a good<BR>thing - that is the whole premise behind parallel comp=
uting.&nbsp; It's<BR>just that the sort operators have to be well placed.&n=
bsp; If your parallel<BR>sort is not performing, then the obvious question =
would be why (what<BR>does the ASH/AWR data show it is waiting on?)<BR><BR>=
When you say it "comes back" does that mean starts to return rows, or<BR>it=
 completes?&nbsp; If a sort (order by) is requested, then no rows can be<BR=
>returned to the client until they are ordered, where as w/o an order<BR>by=
 they can be returned immediately.<BR><BR>Can you post the execution plans =
for the order by and w/o order by?<BR><BR>On Tue, Feb 2, 2010 at 8:57 AM, K=
ellyn Pedersen &lt;<A href=3D"http://us.mc320.mail.yahoo.com/mc/compose?to=
=3Dkjped1313@yahoo.com" ymailto=3D"mailto:kjped1313@yahoo.com">kjped1313@ya=
hoo.com</A>&gt; wrote:<BR>&gt;<BR>&gt; I'm at a total brain block on this
 one-&nbsp; Query with a order by that MUST be done in Oracle&nbsp;as the f=
ile is an output to a dataset in SAS.&nbsp; 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...)<BR>&gt;<BR=
>&gt; The simple query with the order by-, (without the order by, the data =
will come back in seconds...)<BR>&gt;<BR>&gt; select smap.* from CENSUS_0_4=
8 smap order by ibhid, ibid;<BR><BR>--<BR>Regards,<BR>Greg Rahn<BR><A href=
=3D"http://structureddata.org/" target=3D_blank>http://structureddata.org</=
A><BR>--<BR><A href=3D"http://www.freelists.org/webpage/oracle-l" target=3D=
_blank>http://www.freelists.org/webpage/oracle-l</A><BR><BR><BR></DIV></BLO=
CKQUOTE></td></tr></table><br>=0A=0A=0A=0A      
--0-81090712-1265154407=:81574--
--
http://www.freelists.org/webpage/oracle-l