Re: Minimize Performance Hit on Sort...Help!
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: greg@xxxxxxxxxxxxxxxxxx
- Date: Tue, 2 Feb 2010 15:46:47 -0800 (PST)
Hi Greg,
I am still spending time on the sorting issue in parallel. I do believe part
of the main issue is in the design and the quantity of data that is being
sorted and the joins involved in sorting. I've only been here five months, but
redesign from wide tables to a star schema is something that I've been spending
time with our lead BI developer on. Our "step through" design is much improved
and tuned, but it still hits these wide tables. The view involved here is
legacy code that with the current design, there 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 to sort all this? Who is going to go through
the millions and millions of rows of data that is being output to the SAS data
set? I have no idea...)
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 and present
the data anyway they want.
Prune partitions? Who needs to prune partitions? We keep everything!
Saturate disk I/O? Unheard of! Now they say I've striped too much and the
server bus is showing stress... :)
OK, Kellyn is getting off her soapbox... 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
"Go away before I replace you with a very small and efficient shell script..."
--- On Tue, 2/2/10, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
Subject: Re: Minimize Performance Hit on Sort...Help!
To: kjped1313@xxxxxxxxx
Cc: "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>
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. It's
just that the sort operators have to be well placed. 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? 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@xxxxxxxxx> wrote:
>
> 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 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
