| Regarding sort optimsation (merged) [message #273855] |
Fri, 12 October 2007 00:21  |
jobsushil
Messages: 7
Registered: October 2007
Location: India
|
Junior Member |
|
|
Hi,
I am having SQL,Which is of the following structure
select
p2.a,p2.b,count(p2.c)
FROM
(select p1.a,p1.b,p1.c
FROM
(select
max(a),b,c
from
Table 1,Table 2
where
1.a=2.a
and 1.b=2.b
group by
b,c
)P1
)P2
group by
p2.a,p2.b;
The records getting processed is about 2 million.
The query is running for a long time. When i checked the 'Long Ops'in session browser of Toad, i found that the sorting of output is taking more time.
what can i do to reduce the time in sorting?
Or is there some way i can rewrite the SQL?
Job
[Updated on: Fri, 12 October 2007 00:25] Report message to a moderator |
|
|
|
|
|
| Re: Optimization of sorting time [message #273872 is a reply to message #273869] |
Fri, 12 October 2007 01:23  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
The formatted output should look like this:
SELECT p2.a
, p2.b
, COUNT (p2.c)
FROM (SELECT p1.a
, p1.b
, p1.c
FROM (SELECT MAX (t1.a)
, t1.b
, t2.c
FROM Table1 t1
, Table2 t2
WHERE t1.a = t2.a AND t1.b = t2.b
GROUP BY t1.b
, t2.c) P1) P2
GROUP BY p2.a
, p2.b;
MHE
[Updated on: Fri, 12 October 2007 01:25] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Optimization of sorting time [message #274078 is a reply to message #273855] |
Sat, 13 October 2007 08:48 |
gintsp
Messages: 118
Registered: February 2007
|
Senior Member |
|
|
Rewrite your SQL to get les sorts 
And if you need them anyway then the most common solution is to increase your hash_area_size and sort_area_size for your session. You need also set workarea_size_policy = manual for that. If you are using workarea_size_policy = auto, that means Oracle no more than ~5% of pga_aggregate_target can use for sorting/hashing in your particular session, so increasing pga_aggregate_target will affect your queries less. Of course after increasing sort/hash area sizes for your session and running your queries don't forget to set them back to normal.
Gints Plivna
[Edit MC: url to poster site removed. If you have an answer on your site, post a link to this answer. If you don't, post your site url in your profile and in Marketplace]
[Updated on: Sat, 13 October 2007 10:25] by Moderator Report message to a moderator |
|
|
|
| Help needed regarding sorting of output [message #274417 is a reply to message #273855] |
Tue, 16 October 2007 00:43 |
jobsushil
Messages: 7
Registered: October 2007
Location: India
|
Junior Member |
|
|
Hi,
I am running a query which is taking too much time for sorting the output.
How can reduce the time taken in sort operation?
How can i check whether it is a disk sort or in memory and the amount of memory used?
How can i increase the sort_area_size and hash_area_size?
Kindly help me out
regards
Job
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
