| Performance Prob with Group and Orader by clause [message #379844] |
Thu, 08 January 2009 02:16  |
dbcop
Messages: 37
Registered: September 2006
Location: india
|
Member |
|
|
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_sort_elimination_cost_ratio integer 10
nls_sort string
sort_area_retained_size integer 0
sort_area_size integer 2097152
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 12582912000
V$PGASTAT
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
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.?
Thanks
[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  |
rleishman
Messages: 3728
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 |
 |
Kevin Meade
Messages: 2103
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
nologging
as
select *
from ...
create table temp2
nologging
as
select *
from ...
order by ...
create table temp3
nologging
as
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
