Home » RDBMS Server » Performance Tuning » Regarding sort optimsation (merged)
Regarding sort optimsation (merged) [message #273855] Fri, 12 October 2007 00:21 Go to next message
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 #273869 is a reply to message #273855] Fri, 12 October 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck and provide the requested information.

Regards
Michel
Re: Optimization of sorting time [message #273872 is a reply to message #273869] Fri, 12 October 2007 01:23 Go to previous messageGo to next message
Maaher
Messages: 7062
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 #273877 is a reply to message #273855] Fri, 12 October 2007 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the purpose of P2 if it is the same as P1?

Regards
Michel
Re: Optimization of sorting time [message #273906 is a reply to message #273877] Fri, 12 October 2007 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this not just the same as
SELECT   MAX (t1.a)
        , t1.b
        ,COUNT(t2.c)
FROM     Table1 t1
        ,Table2 t2
WHERE    t1.a = t2.a AND t1.b = t2.b
GROUP BY t1.b
Re: Optimization of sorting time [message #274078 is a reply to message #273855] Sat, 13 October 2007 08:48 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Rewrite your SQL to get les sorts Wink
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 Go to previous messageGo to next message
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
Re: Help needed regarding sorting of output [message #274423 is a reply to message #274417] Tue, 16 October 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to start a new topic for the same question.
Continue on the previous and provide the requested information.

Regards
Michel
Re: Help needed regarding sorting of output [message #274594 is a reply to message #274423] Tue, 16 October 2007 09:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did you try my rewrite of the query?
Previous Topic: sql performance issue.
Next Topic: autotrace
Goto Forum:
  


Current Time: Sun Dec 11 06:06:56 CST 2016

Total time taken to generate the page: 0.07646 seconds