Home » RDBMS Server » Performance Tuning » Improve in-memory sort performance (Oracle 10g on Linux)
Improve in-memory sort performance [message #350270] Wed, 24 September 2008 09:43 Go to next message
kpalania
Messages: 2
Registered: September 2008
Junior Member
Hi,
I have a query that goes against multiple views and tables and does a few joins, and ultimately does a "group by". With everything else being the same, if I remove the "group by" clause and run the query, it takes about 0.04 seconds. With the "group by", it takes about 0.72 seconds. There is a big difference Sad

I've verified a number of things -
* There are no disk sorts happening. They are all in-memory sorts.
* If I do a temporary insert into another global temporary table and then do a group by on those results, it takes no time. But, ofcourse, I run into an ORA-08103 error sporadically and that is a whole new discussion (about existing Oracle database issues with Ref Cursors and Global Temporary tables). So, that ceases to be an alternative.
* However, it is clear that doing a group by on data from a physical table is way faster than doing it otherwise. Perhaps, the in-memory sort doesn't come into play there?
* I've ensured that the sort_area_size, sort_area_retained_size and pga_aggregate_target values are about right.

If I bump up the PGA_AGGREGRATE_VALUE to over 440M, it slows it down further and the query takes about 2.4 seconds to run.

I am not posting the query here as it is very specific to our environment and I actually don't believe it has anything to do with that query specifically. This is a generic issue, perhaps tied to some Oracle parameters that I need to tweak.

Finally, the resultset contains about 400K rows and the group by is performed on it. What is the expected time to do this sort of a thing? I am guessing 0.7 seconds is still way too long.
Any thoughts?
Re: Improve in-memory sort performance [message #350286 is a reply to message #350270] Wed, 24 September 2008 10:30 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
first question.
without group by it takes 0.04s? of what? 0.04s past by you see first 200 rows?
use
create table 'table_name' as

to load all record set of 'group by' query and without 'group by' query into the table and then post run time results.
Re: Improve in-memory sort performance [message #350292 is a reply to message #350286] Wed, 24 September 2008 10:47 Go to previous messageGo to next message
kpalania
Messages: 2
Registered: September 2008
Junior Member
The query with the "group by" returns about 12K rows, with each row having 2 columns - "field1" and "group_by_count". There are about 155K rows in total so if I were to do a rollup() on the 'group by', it would return 155K.

If I simply ran the query to return the 155 rows, the first row starts returning after the 0.03rd second, and takes the usual amount of time to print the rest of the rows. However, if I include the "group by" or simply an "order by", it takes 0.7 seconds or more.

Also, increasing the PGA_AGGREGRATE_TARGET to over 440M resulting in this number being bumped upto 2.4 seconds. Just need to find a way to make a positive difference by altering some Oracle initialization parameters..
Re: Improve in-memory sort performance [message #350525 is a reply to message #350292] Thu, 25 September 2008 05:15 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's fast without the GROUP BY because it is returning rows before the entire result set has been identified. When you GROUP BY, the entire result set must be retrieved, loaded into memory, sorted and grouped, before a single row is returned.

If you want to compare the real difference between methods, read this article.

Ross Leishman
Previous Topic: Stroring Hierarchical Data(XML) into relational Table
Next Topic: Index Creation with Parallel Option
Goto Forum:
  


Current Time: Sun Dec 04 23:09:35 CST 2016

Total time taken to generate the page: 0.07953 seconds