Home » RDBMS Server » Performance Tuning » Performance Prob with Group and Orader by clause (Oracle DB version on AIX )
Performance Prob with Group and Orader by clause [message #379844] Thu, 08 January 2009 02:16 Go to next message
Messages: 37
Registered: September 2006
Location: india
I have a situation where

The queries on a particular table is taking abnormal time to execute.To mention only the queries having order by and group by clause is taking much time although other queries which does not have these clauses execute much faster.

The queries involve group by and order by clause.

Here are the following parameters

------------------------------------ ----------- ------------------------------
_sort_elimination_cost_ratio integer 10
nls_sort string
sort_area_retained_size integer 0
sort_area_size integer 2097152

------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 12582912000


---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 1.2583E+10 bytes
aggregate PGA auto target 8954781696 bytes
global memory bound 104857600 bytes
total PGA inuse 4989884416 bytes
total PGA allocated 6531011584 bytes
maximum PGA allocated 1.6104E+10 bytes
total freeable PGA memory 1128660992 bytes
PGA memory freed back to OS 9.2238E+12 bytes
total PGA used for auto workareas 2346791936 bytes
maximum PGA used for auto workareas 1.0825E+10 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 1.7629E+13 bytes
extra bytes read/written 9.8064E+12 bytes
cache hit percentage 64.25 percent

Trace information shows a full table scan occurs . The table has been reorganized to remove the fragmentation.

I found the sorts on disk is increasing with respect to memory sorts.Will it be advisable to increase sort area size although I have a pga aggregate target set to approx 11 GB.?


[Updated on: Thu, 08 January 2009 02:37]

Report message to a moderator

Re: Performance Prob with Group and Orader by clause [message #380012 is a reply to message #379844] Thu, 08 January 2009 19:57 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Queries without GROUP BY or ORDER BY only apeear fast because they start return rows as soon as they are found.

When you GROUP or ORDER, Oracle must find ALL matching rows before ANY can be returned. It looks like it takes MUCH longer, but it's really only a bit longer.

Look at this article for a method to benchmark different queries.

Ross Leishman
Re: Performance Prob with Group and Orader by clause [message #380397 is a reply to message #380012] Sun, 11 January 2009 18:04 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
If you want to know how long it is taking to get and move data, do CREATE TABLE AS for each variation. For example:

set timing on

create table temp1
select *
from ...

create table temp2
select *
from ...
order by ...

create table temp3
select *
from ...
group by ...

You get the idea. This will tell you what the big differences are if any. Of course, this is not a perfect method either. Because you are writing data to a table, the number of rows involved can scew your results. The point it, you need to invent a test to show you what you really want to know.

Good luck, Kevin

[Updated on: Sun, 11 January 2009 18:04]

Report message to a moderator

Previous Topic: Need help in Optimizing this query
Next Topic: Speed of query
Goto Forum:

Current Time: Sat Jul 22 12:20:27 CDT 2017

Total time taken to generate the page: 0.14452 seconds