Home » RDBMS Server » Performance Tuning » Performance Due to Buffer Sort (Oracle 10.2.0.2.0)  () 1 Vote
Performance Due to Buffer Sort [message #351268] Tue, 30 September 2008 02:54 Go to next message
senaka
Messages: 5
Registered: September 2008
Junior Member
Can some one explain the reason for geeting buffer sort statment in the explain plain with view VW_SQ_1
Re: Performance Due to Buffer Sort [message #351272 is a reply to message #351268] Tue, 30 September 2008 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To sort a buffer that contains intermediate results.

Regards
Michel
Re: Performance Due to Buffer Sort [message #351280 is a reply to message #351268] Tue, 30 September 2008 03:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You get a BUFFER SORT in Explain Plan for some types of Parallel Queries. Certainly Partial Partition-wise joins can use a BUFFER SORT, I don't know if others can.

There are some examples in the Oracle Performance Tuning manual that show how one Explain Plan contains a BUFFER SORT and another - also a Partial Partition-wise join - does not. But it does not adequately explain (in a way that I can understand) why the two are different and what exactly they are doing differently.

If I were to GUESS (and that's what this is) I would say that it occurs when you perform a Partial Partition-Wise Join with a RANGE PARTITIONED table and a non-partitioned table. There seems to be some issue with the join that requires the parallel query buffers on the non-partitioned table to be sorted. I don't understand it.

I would appear from the examples that HASH partitioned (or sub-partitioned) tables do NOT require a BUFFER SORT when joining on the hash partition key.

Sorry I can't be more definitive.

Ross Leishman
Re: Performance Due to Buffer Sort [message #351485 is a reply to message #351268] Wed, 01 October 2008 01:49 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Also, if you have a parallel query that is specifying a parallel hint on only 1 table of a 2 table join, you will get a buffer sort as the join is performed by a serial process. you need to run the same level of parallel on both tables so that the join itself can be run in parallel and hence avoid the buffer sort.

Thsi applies to HASH joins.

[Updated on: Wed, 01 October 2008 01:52]

Report message to a moderator

Re: Performance Due to Buffer Sort [message #351494 is a reply to message #351485] Wed, 01 October 2008 02:31 Go to previous messageGo to next message
senaka
Messages: 5
Registered: September 2008
Junior Member
Thanks for your infromation.
Still I am confusing about the result.
The attached documentation will explain the query and the explain plan.

  • Attachment: orafaq.pdf
    (Size: 24.16KB, Downloaded 1328 times)
Re: Performance Due to Buffer Sort [message #351608 is a reply to message #351268] Wed, 01 October 2008 12:45 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
The problem is not the buffer sort, it is the nested loops access, and the FULL table scan on the INVENTORIES object for each row.
Re: Performance Due to Buffer Sort [message #351666 is a reply to message #351608] Thu, 02 October 2008 04:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The cartesian join would also be a fair candidate.

As would the user defined function User_Allowed_Site_aPi.Authorized()

Ross Leishman

[Updated on: Thu, 02 October 2008 04:38]

Report message to a moderator

Re: Performance Due to Buffer Sort [message #351669 is a reply to message #351666] Thu, 02 October 2008 04:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And here is the SQL formatted using the SQL Formatter on this site. Always handy to format if you want people to help.
SELECT *
FROM   Shop_Ord
WHERE  Contract IN (SELECT Contract
                    FROM   Site_Public
                    WHERE  Contract = User_Allowed_Site_aPi.Authorized(Contract))
       AND Order_Code_db IN ('M',
                             'F',
                             'P')
       AND (EXISTS (SELECT 1
                    FROM   Shop_Material_Alloc sMa
                    WHERE  (qty_Issued + sMa.qty_Assigned) < qty_Required
                           AND Shop_Ord.Release_No = sMa.Release_No
                           AND Shop_Ord.Order_No = sMa.Order_No
                           AND (sMa.Part_No IN (SELECT Inventory_Part_Location.Part_No
                                                FROM   Inventory_Part_Location
                                                WHERE  Inventory_Part_Location.qty_OnHand - Inventory_Part_Location.qty_Reserved > 0)))
            AND (objState != 'Closed')
            AND (Revised_Due_Date < SYSDATE + 30))
Re: Performance Due to Buffer Sort [message #351672 is a reply to message #351669] Thu, 02 October 2008 04:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
FIRST: Run the following and tell us how long it takes, and how many rows it returns:

SELECT Contract
FROM   Site_Public
WHERE  Contract = User_Allowed_Site_aPi.Authorized(Contract));


SECOND: Do qty_Issued and qty_Required come from Shop_Ord? Or from Shop_Material_Alloc?

Ross Leishman
Re: Performance Due to Buffer Sort [message #351963 is a reply to message #351672] Sat, 04 October 2008 00:16 Go to previous message
senaka
Messages: 5
Registered: September 2008
Junior Member
Hi Thanks for replies.

That user defined method does not take much time. Even u can igonore that time. Performance issue os there even for this query
SELECT *
FROM Shop_Ord
WHERE Order_Code_db IN ('M',
'F',
'P')
AND (EXISTS (SELECT 1
FROM Shop_Material_Alloc sMa
WHERE (qty_Issued + sMa.qty_Assigned) < qty_Required
AND Shop_Ord.Release_No = sMa.Release_No
AND Shop_Ord.Order_No = sMa.Order_No
AND (sMa.Part_No IN (SELECT Inventory_Part_Location.Part_No
FROM Inventory_Part_Location
WHERE Inventory_Part_Location.qty_OnHand - Inventory_Part_Location.qty_Reserved > 0)))
AND (objState != 'Closed')
AND (Revised_Due_Date < SYSDATE + 30))

that two fields are qty_Issued and qty_Required come from Shop_Material_Alloc.

Further investigations I found that Shop_ord table does not get filtered. When modify the query as below with key field in the shop_ord then this get populated with in few second.

SELECT *
FROM Shop_Ord
WHERE Order_Code_db IN ('M',
'F',
'P')
AND (EXISTS (SELECT 1
FROM Shop_Material_Alloc sMa
WHERE (qty_Issued + sMa.qty_Assigned) < qty_Required
AND Shop_Ord.Release_No = sMa.Release_No
AND Shop_Ord.Order_No = sMa.Order_No
AND (sMa.Part_No IN (SELECT Inventory_Part_Location.Part_No
FROM Inventory_Part_Location
WHERE Inventory_Part_Location.qty_OnHand - Inventory_Part_Location.qty_Reserved > 0)))
AND (objState != 'Closed')
AND (Revised_Due_Date < SYSDATE + 30)
OR PART_NO = ' '..

The appended OR condition works well for me as that OR condition is never get TRUE.
Previous Topic: Dictionary Cache Hit ratio
Next Topic: need assistance to optimize the query
Goto Forum:
  


Current Time: Fri Dec 02 12:21:16 CST 2016

Total time taken to generate the page: 0.09401 seconds