Home » RDBMS Server » Performance Tuning » Window Sort
Window Sort [message #329324] Wed, 25 June 2008 01:46 Go to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Guys,

Is window sort good or bad ?
Please expalin with an example?

Arvind
Re: Window Sort [message #329334 is a reply to message #329324] Wed, 25 June 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good or bad regarding to what?

Regards
Michel
Re: Window Sort [message #329339 is a reply to message #329324] Wed, 25 June 2008 02:48 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
When I see in my Plan I see the Window sort is happening.
Iam not whether its for a good thing to be there in the query or not!! Or is there any best sort which can be used other than WINDOW SORT or WINDOW SORT PUSHED RANK.
Ideally is Window sort best or is any thing else can be forced to ?


Arvind
Re: Window Sort [message #329343 is a reply to message #329339] Wed, 25 June 2008 02:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You get a WINDOW SORT as a result of using Analytic Functions (OVER clause). Remove the function, the WINDOW SORT disappears.

Ross Leishman
Re: Window Sort [message #329352 is a reply to message #329324] Wed, 25 June 2008 03:15 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Yeah that can be done!! But the Function cannot be removed as application demands it.Please let me know the best possibilities in this case.

Thanks,
Arvind
Re: Window Sort [message #329354 is a reply to message #329352] Wed, 25 June 2008 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know the best possibilities in this case.


Once again, the best possibilities for what?

Regards
Michel
Re: Window Sort [message #329355 is a reply to message #329324] Wed, 25 June 2008 03:22 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Best possibility when I Cannot ignore "Analytic Functions (OVER clause)".
Re: Window Sort [message #329371 is a reply to message #329355] Wed, 25 June 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Best possibility when I Cannot ignore "Analytic Functions (OVER clause)".

I still don't understand the question.
Best than what?
You use analytic function, then you have windows sort operation, what is the problem with this?

Regards
Michel
Re: Window Sort [message #329374 is a reply to message #329324] Wed, 25 June 2008 04:24 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
I meant Application demands the analytic function and that can't be avoided at any case. But the cost at the Window Join is very high and iam unable to reduce taht.

Any suggestions in the plan here mostly at that key area "Window ...."

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2240 | 743K| | 84967 (1)| 00:17:00 |
| 1 | SORT ORDER BY | | 2240 | 743K| 1640K| 84967 (1)| 00:17:00 |
|* 2 | HASH JOIN RIGHT OUTER | | 2240 | 743K| | 84802 (1)| 00:16:58 |
|* 3 | VIEW | | 4480 | 266K| | 42401 (1)| 00:08:29 |
| 4 | WINDOW SORT | | 4480 | 192K| | 42401 (1)| 00:08:29 |
|* 5 | TABLE ACCESS BY INDEX ROWID | XXX_MAGNET_XXX | 4480 | 192K| | 42399 (1)| 00:08:29 |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 7 | BITMAP OR | | | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 9 | INDEX RANGE SCAN | CRYO_FUNC1 | | | | 89 (0)| 00:00:02 |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 11 | SORT ORDER BY | | | | | | |
|* 12 | INDEX RANGE SCAN | PQR_MAGNET_ABC | | | | 40 (0)| 00:00:01 |
|* 13 | VIEW | | 2240 | 610K| | 42401 (1)| 00:08:29 |
|* 14 | WINDOW SORT PUSHED RANK | | 2240 | 107K| | 42401 (1)| 00:08:29 |
|* 15 | TABLE ACCESS BY INDEX ROWID | XXX_MAGNET_XXX | 2240 | 107K| | 42400 (1)| 00:08:29 |
| 16 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 17 | BITMAP OR | | | | | | |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 19 | INDEX RANGE SCAN | CRYO_FUNC1 | | | | 89 (0)| 00:00:02 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 21 | SORT ORDER BY | | | | | | |
|* 22 | INDEX RANGE SCAN | VVV_MAGNET_VVV | | | | 40 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------


Thanks,
Arvind
Re: Window Sort [message #329412 is a reply to message #329374] Wed, 25 June 2008 06:34 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing to do from the information you gave.
By the way, please read 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 to verify.

Regards
Michel
Previous Topic: How tuning this query ? (merged)
Next Topic: High Frequency of Redo Log Files
Goto Forum:
  


Current Time: Fri Dec 09 00:14:45 CST 2016

Total time taken to generate the page: 0.10392 seconds