Re: Minimize Performance Hit on Sort...Help!
Date: Tue, 2 Feb 2010 15:46:47 -0800 (PST)
Message-ID: <116008.81574.qm_at_web32001.mail.mud.yahoo.com>
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_at_structureddata.org> wrote:
From: Greg Rahn <greg_at_structureddata.org>
Subject: Re: Minimize Performance Hit on Sort...Help!
To: kjped1313_at_yahoo.com
Cc: "oracle Freelists" <oracle-l_at_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. 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_at_yahoo.com> 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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 02 2010 - 17:46:47 CST