| Improve in-memory sort performance [message #350270] |
Wed, 24 September 2008 09:43  |
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 
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  |
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 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
