Home » RDBMS Server » Performance Tuning » Slow Query Sql (Oracle 10g)
| Slow Query Sql [message #656454] |
Thu, 06 October 2016 04:32  |
 |
rayrevan
Messages: 21
Registered: August 2016
|
Junior Member |
|
|
Good day sir,
I got some problem with this query, whenever i try to execute this query it will take 4-12 sec(not stable), i dont know it can be happpen, maybe server issue but this query just around 1500+ row, why this query so slow?
Any Suggestion?
Thank you
SELECT RV.AD_CLIENT_ID,RV.AD_ORG_ID,RV.M_PRODUCT_ID,MP.VALUE,MP.NAME,MP.M_PRODUCTSUBCAT_ID,RV.M_LOCATOR_ID,
RV.M_WAREHOUSE_ID, RV.EXPIRYDATE, RV.M_ATTRIBUTESETINSTANCE_ID, MP.M_PRODUCT_CATEGORY_ID,
(SELECT MAX(MOVEMENTDATE) FROM M_TRANSACTION
WHERE M_PRODUCT_ID = RV.M_PRODUCT_ID
AND M_LOCATOR_ID = RV.M_LOCATOR_ID
AND AD_ORG_ID = RV.AD_ORG_ID
AND AD_CLIENT_ID = RV.AD_CLIENT_ID) AS LAST_MOVE,
RV.QTY, RV.LOT,PP.PRICELIST, RV.GUARANTEEDATE, N.NAME AS CLASSPRODUCT,M.NAME AS MOVINGCLASS,MP.C_UOM_ID,RL.NAME as REGISTRATIONSTATUS,MP.REGISTRATIONDUE,
GETQTYORDERED(RV.M_PRODUCT_ID,RV.M_WAREHOUSE_ID) AS QTYOREDERED ,GETQTYRESERVED(RV.M_PRODUCT_ID,RV.M_WAREHOUSE_ID) AS RESERVED,RV.QTY-(GETQTYRESERVED(RV.M_PRODUCT_ID,RV.M_WAREHOUSE_ID)) AS AVAILABLE
FROM RV_BATCH RV
INNER JOIN M_PRODUCT MP ON MP.M_PRODUCT_ID = RV.M_PRODUCT_ID
LEFT JOIN M_PRICELIST_VERSION PV ON (RV.AD_CLIENT_ID = PV.AD_CLIENT_ID and PV.M_pricelist_Version_id getpricelist_version_id3(1000000, SYSDATE))
LEFT JOIN M_PRODUCTPRICE PP ON (MP.M_PRODUCT_ID = PP.M_PRODUCT_ID and PP.M_pricelist_Version_id = PV.M_pricelist_Version_id)
LEFT JOIN AD_Ref_lIST M ON (MP.MovingClass2=M.VALUE and M.AD_Reference_ID=1000096)
LEFT JOIN AD_Ref_lIST N ON (MP.Classification=N.VALUE and N.AD_Reference_ID=1000007)
LEFT JOIN AD_REF_LIST RL ON RL.value = MP.REGISTRATIONSTATUS AND RL.AD_Reference_ID=1000013
WHERE
RV.AD_CLIENT_ID = 1000000 AND RV.QTY<>0
AND RV.AD_ORG_ID = 1000001
GETQTYORDERED FUNCTION
CREATE OR REPLACE FUNCTION COMPIERE.GETQTYORDERED
(
p_M_Product_ID IN NUMBER,
p_M_Warehouse_ID IN NUMBER
)
RETURN NUMBER
AS
QTYORDERED NUMBER := '0';
--add by novran
BEGIN
SELECT COALESCE(SUM(ABS(MT.MOVEMENTQTY)),0) INTO QTYORDERED FROM M_TRANSACTION MT
WHERE MT.MOVEMENTTYPE = 'C-' AND MT.M_PRODUCT_ID = p_M_Product_ID
AND MT.M_LOCATOR_ID IN (SELECT M_LOCATOR_ID FROM M_LOCATOR WHERE M_WAREHOUSE_ID = p_M_Warehouse_ID);
RETURN QTYORDERED;
END;
/
GETQTYRESERVED FUNCTION
CREATE OR REPLACE FUNCTION COMPIERE.GETQTYRESERVED
(
p_M_Product_ID IN NUMBER,
p_M_Warehouse_ID IN NUMBER
)
RETURN NUMBER
AS
QTYRESERVED NUMBER := '0';
--add by novran
BEGIN
SELECT COALESCE(SUM(COL.QTYRESERVED),0) INTO QTYRESERVED
FROM
C_ORDERLINE COL
INNER JOIN C_ORDER CO ON COL.C_ORDER_ID = CO.C_ORDER_ID
WHERE CO.C_DOCTYPE_ID = '1000029'
AND M_PRODUCT_ID = p_M_Product_ID
AND CO.M_WAREHOUSE_ID = p_M_Warehouse_ID
AND COL.QTYRESERVED<>'0';
RETURN QTYRESERVED;
END;
/
Execution Plan
----------------------------------------------------------
Plan hash value: 3398698752
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44447 | 29M| | 26098 (6)| 00:05:14 |
| 1 | SORT AGGREGATE | | 1 | 29 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | M_TRANSACTION | 1 | 29 | | 80 (0)|
|* 3 | INDEX RANGE SCAN | M_TRANSACTION_IDX01 | 100 | | | 3 (0)| 00:0
|* 4 | HASH JOIN RIGHT OUTER | | 44447 | 29M| | 26098 (6)| 00:05:14 |
| 5 | TABLE ACCESS BY INDEX ROWID | AD_REF_LIST | 5 | 150 | | 4 (0)|
|* 6 | INDEX RANGE SCAN | AD_REF_LIST_VALUE | 5 | | | 2 (0)| 00
|* 7 | HASH JOIN RIGHT OUTER | | 44447 | 27M| | 26093 (6)| 00:05:14 |
| 8 | TABLE ACCESS BY INDEX ROWID | AD_REF_LIST | 5 | 150 | | 4 (0)|
|* 9 | INDEX RANGE SCAN | AD_REF_LIST_VALUE | 5 | | | 2 (0)| 0
|* 10 | HASH JOIN RIGHT OUTER | | 44447 | 26M| | 26087 (6)| 00:05:14 |
| 11 | TABLE ACCESS BY INDEX ROWID | AD_REF_LIST | 5 | 150 | | 4 (0)|
|* 12 | INDEX RANGE SCAN | AD_REF_LIST_VALUE | 5 | | | 2 (0)|
|* 13 | HASH JOIN OUTER | | 44447 | 25M| 24M| 26082 (6)| 00:05:13 |
| 14 | VIEW | | 43937 | 24M| | 11312 (10)| 00:02:16 |
|* 15 | HASH JOIN RIGHT OUTER | | 43937 | 11M| | 11312 (10)| 00:02:16 |
| 16 | TABLE ACCESS BY INDEX ROWID| M_PRICELIST_VERSION | 1 | 9 | | 2 (0)|
|* 17 | INDEX UNIQUE SCAN | M_PRICELIST_VERSION_KEY | 1 | | | 1 (0)| 00:0
|* 18 | HASH JOIN | | 22126 | 5682K| 3528K| 11309 (10)| 00:02:16 |
| 19 | VIEW | RV_BATCH | 22126 | 3262K| | 10221 (10)| 00:02:03 |
|* 20 | FILTER | | | | | | |
| 21 | SORT GROUP BY | | 22126 | 2398K| 53M| 10221 (10)| 00:02:03 |
| 22 | NESTED LOOPS | | 442K| 46M| | 5847 (16)| 00:01:11 |
|* 23 | HASH JOIN | | 442K| 44M| | 5094 (4)| 00:01:02 |
| 24 | TABLE ACCESS FULL | M_LOCATOR | 152 | 6688 | | 2 (0)| 00:00:0
|* 25 | HASH JOIN | | 442K| 25M| 4480K| 5081 (3)| 00:01:01 |
| 26 | TABLE ACCESS FULL | M_ATTRIBUTESETINSTANCE | 109K| 3194K| | 397 (2)| 00:00:0
|* 27 | TABLE ACCESS FULL | M_TRANSACTION | 442K| 13M| | 3540 (4)| 00:00:43 |
|* 28 | INDEX UNIQUE SCAN | M_WAREHOUSE_KEY | 1 | 6 | | 0 (0)| 00
| 29 | TABLE ACCESS FULL | M_PRODUCT | 52945 | 5790K| | 601 (1)| 00:00:08 |
| 30 | TABLE ACCESS FULL | M_PRODUCTPRICE | 4128K| 66M| | 7674 (3)| 00:
---------------------------------------------------------------------------------------------------- |
|
|
|
|
|
| Re: Slow Query Sql [message #656517 is a reply to message #656461] |
Sun, 09 October 2016 20:28  |
|
rayrevan
Messages: 21
Registered: August 2016
|
Junior Member |
|
|
RV_Batch View
SELECT MT.AD_CLIENT_ID,MT.AD_ORG_ID,MA.M_ATTRIBUTESETINSTANCE_ID,MA.LOT,CASE WHEN MA.EXPIRYDATE IS NULL THEN TO_DATE('01/01/3000','DD/MM/YYYY') ELSE MA.EXPIRYDATE END AS EXPIRYDATE,
SUM(MT.MOVEMENTQTY) AS QTY,MO.M_LOCATOR_ID,MO.VALUE,MT.M_PRODUCT_ID,M_WAREHOUSE_ID,MA.GUARANTEEDATE
FROM M_TRANSACTION MT
LEFT JOIN M_LOCATOR MO ON MO.M_LOCATOR_ID = MT.M_LOCATOR_ID
LEFT JOIN M_ATTRIBUTESETINSTANCE MA ON MA.M_ATTRIBUTESETINSTANCE_ID = MT.M_ATTRIBUTESETINSTANCE_ID
LEFT JOIN M_WAREHOUSE MW ON MW.M_WAREHOUSE_ID = MO.M_WAREHOUSE_ID
GROUP BY MA.M_ATTRIBUTESETINSTANCE_ID,MA.LOT,MA.EXPIRYDATE,MO.M_LOCATOR_ID,MO.VALUE,MT.AD_CLIENT_ID,MT.AD_ORG_ID,MT.M_PRODUCT_ID,M_WAREHOUSE_ID,MA.GUARANTEEDATE
ORDER BY MA.M_ATTRIBUTESETINSTANCE_ID ASC |
|
|
|
| Re: Slow Query Sql [message #656524 is a reply to message #656517] |
Mon, 10 October 2016 02:50 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your rv_batch view includes an aggregation and an order by, which means that it cannot be merged. It also includes outer joins, which force a join order. These two issues reduce the optimizer's options hugely. Do you really need the aggregation and the outer joins (don't just say "yes" - think about it)?
You certainly do not need the ORDER BY, get rid if it.
If you adjust the view so that the predicates applied at operations 2 and 3 of your plan could be pushed into it, you might see an improvement.
You also need to post the plan again and this time include the predicate section. Without that, I don not see how one can really advise.
|
|
|
|
| Re: Slow Query Sql [message #656525 is a reply to message #656524] |
Mon, 10 October 2016 03:47 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Never put an order by in a view. It allows you to be lazy and just do 'select * from view' but has down sides for anything more complicated, as is the case here.
|
|
|
|
| Re: Slow Query Sql [message #656627 is a reply to message #656524] |
Wed, 12 October 2016 20:59 |
|
rayrevan
Messages: 21
Registered: August 2016
|
Junior Member |
|
|
i try to improve query with where clause and delete order by, normally exec time 3-2 sec and now its just take 1 sec, but i cant get rid off this sum aggregation or outer join. I do not know what should i
RV_Batch View Before
SELECT MT.AD_CLIENT_ID,MT.AD_ORG_ID,MA.M_ATTRIBUTESETINSTANCE_ID,MA.LOT,CASE WHEN MA.EXPIRYDATE IS NULL THEN TO_DATE('01/01/3000','DD/MM/YYYY') ELSE MA.EXPIRYDATE END AS EXPIRYDATE,
SUM(MT.MOVEMENTQTY) AS QTY,MO.M_LOCATOR_ID,MO.VALUE,MT.M_PRODUCT_ID,M_WAREHOUSE_ID,MA.GUARANTEEDATE
FROM M_TRANSACTION MT
INNER JOIN M_LOCATOR MO WITH (INDEX(M_LOCATOR_KEY)) ON MO.M_LOCATOR_ID = MT.M_LOCATOR_ID
INNER JOIN M_ATTRIBUTESETINSTANCE MA ON MA.M_ATTRIBUTESETINSTANCE_ID = MT.M_ATTRIBUTESETINSTANCE_ID
INNER JOIN M_WAREHOUSE MW ON MW.M_WAREHOUSE_ID = MO.M_WAREHOUSE_ID
WHERE AD_CLIENT_ID = 1000000
GROUP BY MA.M_ATTRIBUTESETINSTANCE_ID,MA.LOT,MA.EXPIRYDATE,MO.M_LOCATOR_ID,MO.VALUE,MT.AD_CLIENT_ID,MT.AD_ORG_ID,MT.M_PRODUCT_ID,M_WAREHOUSE_ID,MA.GUARANTEEDATE
ORDER BY MA.M_ATTRIBUTESETINSTANCE_ID ASC
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 842K| 155M| | 28798 (7)| 00:05:46 |
| 1 | VIEW | RV_BATCH | 842K| 155M| | 28798 (7)| 00:05:46 |
| 2 | SORT GROUP BY | | 842K| 89M| 193M| 28798 (7)| 00:05:46 |
| 3 | NESTED LOOPS | | 842K| 89M| | 7395 (23)| 00:01:29 |
|* 4 | HASH JOIN | | 842K| 84M| | 5961 (4)| 00:01:12 |
| 5 | TABLE ACCESS FULL | M_LOCATOR | 152 | 6688 | | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 842K| 49M| 4480K| 5939 (4)| 00:01:12 |
| 7 | TABLE ACCESS FULL| M_ATTRIBUTESETINSTANCE | 109K| 3194K| | 397 (2)| 00:00:05 |
|* 8 | TABLE ACCESS FULL| M_TRANSACTION | 842K| 24M| | 3565 (4)| 00:00:43 |
|* 9 | INDEX UNIQUE SCAN | M_WAREHOUSE_KEY | 1 | 6 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
RV_Batch After
SELECT MT.AD_CLIENT_ID,MT.AD_ORG_ID,MA.M_ATTRIBUTESETINSTANCE_ID,MA.LOT,CASE WHEN MA.EXPIRYDATE IS NULL THEN TO_DATE('01/01/3000','DD/MM/YYYY') ELSE MA.EXPIRYDATE END AS EXPIRYDATE,
MT.MOVEMENTQTY AS QTY,MO.M_LOCATOR_ID,MO.VALUE,MT.M_PRODUCT_ID,M_WAREHOUSE_ID,MA.GUARANTEEDATE
FROM M_TRANSACTION MT
INNER JOIN M_LOCATOR MO ON MO.M_LOCATOR_ID = MT.M_LOCATOR_ID
INNER JOIN M_ATTRIBUTESETINSTANCE MA ON MA.M_ATTRIBUTESETINSTANCE_ID = MT.M_ATTRIBUTESETINSTANCE_ID
INNER JOIN M_WAREHOUSE MW ON MW.M_WAREHOUSE_ID = MO.M_WAREHOUSE_ID
WHERE AD_CLIENT_ID = 1000000
GROUP BY MA.M_ATTRIBUTESETINSTANCE_ID,MA.LOT,MA.EXPIRYDATE, MT.MOVEMENTQTY,MO.M_LOCATOR_ID,MO.VALUE,MT.AD_CLIENT_ID,MT.AD_ORG_ID,MT.M_PRODUCT_ID,M_WAREHOUSE_ID,MA.GUARANTEEDATE
Explain Plan After
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 358K| 38M| | 14333 (2)| 00:02:52 |
| 1 | SORT GROUP BY | | 358K| 38M| 86M| 14333 (2)| 00:02:52 |
|* 2 | HASH JOIN | | 358K| 38M| | 5089 (3)| 00:01:02 |
|* 3 | TABLE ACCESS FULL | M_WAREHOUSE | 21 | 189 | | 2 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 717K| 71M| | 5070 (3)| 00:01:01 |
| 5 | TABLE ACCESS FULL | M_LOCATOR | 149 | 6407 | | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 717K| 41M| 4480K| 5051 (2)| 00:01:01 |
| 7 | TABLE ACCESS FULL| M_ATTRIBUTESETINSTANCE | 109K| 3194K| | 397 (2)| 00:00:0
| 8 | TABLE ACCESS FULL| M_TRANSACTION | 717K| 21M| | 2937 (2)| 00:00:36
----------------------------------------------------------------------------------------------------
And last thing, when i add hint index to table M_transaction, it didnt work.
|
|
|
|
| Re: Slow Query Sql [message #656628 is a reply to message #656627] |
Wed, 12 October 2016 21:10 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT MT.ad_client_id,
MT.ad_org_id,
MA.m_attributesetinstance_id,
MA.lot,
CASE
WHEN MA.expirydate IS NULL THEN To_date('01/01/3000', 'DD/MM/YYYY')
ELSE MA.expirydate
END AS EXPIRYDATE,
Sum(MT.movementqty) AS QTY,
MO.m_locator_id,
MO.value,
MT.m_product_id,
m_warehouse_id,
MA.guaranteedate
FROM m_transaction MT
INNER JOIN m_locator MO WITH (INDEX(m_locator_key))
ON MO.m_locator_id = MT.m_locator_id
INNER JOIN m_attributesetinstance MA
ON MA.m_attributesetinstance_id = MT.m_attributesetinstance_id
INNER JOIN m_warehouse MW
ON MW.m_warehouse_id = MO.m_warehouse_id
WHERE ad_client_id = 1000000
GROUP BY MA.m_attributesetinstance_id,
MA.lot,
MA.expirydate,
MO.m_locator_id,
MO.value,
MT.ad_client_id,
MT.ad_org_id,
MT.m_product_id,
m_warehouse_id,
MA.guaranteedate
ORDER BY MA.m_attributesetinstance_id ASC
There should be index on the following columns
MO.m_locator_id
MT.m_locator_id
MA.m_attributesetinstance_id
MT.m_attributesetinstance_id
MW.m_warehouse_id
MO.m_warehouse_id
ad_client_id
|
|
|
|
|
|
| Re: Slow Query Sql [message #656638 is a reply to message #656627] |
Thu, 13 October 2016 00:54 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I have already asked you to show the exec plans with the predicate section. Without that, I do not see how one can assist further.
However, CM and I have already given you a massive performance improvement. What more do you need?
|
|
|
|
|
|
| Re: Slow Query Sql [message #656641 is a reply to message #656640] |
Thu, 13 October 2016 01:29 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
orclz> |
|
|
|
| Re: Slow Query Sql [message #656642 is a reply to message #656641] |
Thu, 13 October 2016 01:40 |
|
rayrevan
Messages: 21
Registered: August 2016
|
Junior Member |
|
|
Quote:You have not said what performance you require.
I need stable Performance and low cost time consumption, since this query can perform 4-12 sec or more (unstable). So i hope it can be faster and stable than before.
Query
SELECT RV.AD_CLIENT_ID,RV.AD_ORG_ID,RV.M_PRODUCT_ID,MP.VALUE,MP.NAME,MP.M_PRODUCTSUBCAT_ID,RV.M_LOCATOR_ID,
RV.M_WAREHOUSE_ID, RV.EXPIRYDATE, RV.M_ATTRIBUTESETINSTANCE_ID, MP.M_PRODUCT_CATEGORY_ID,(SELECT MAX(MOVEMENTDATE) FROM M_TRANSACTION
WHERE M_PRODUCT_ID = RV.M_PRODUCT_ID
AND M_LOCATOR_ID = RV.M_LOCATOR_ID
AND AD_ORG_ID = RV.AD_ORG_ID
AND AD_CLIENT_ID = RV.AD_CLIENT_ID) AS LAST_MOVE,
RV.QTY, RV.LOT,PP.PRICELIST, RV.GUARANTEEDATE, N.NAME AS CLASSPRODUCT,M.NAME AS MOVINGCLASS,MP.C_UOM_ID,RL.NAME as REGISTRATIONSTATUS,MP.REGISTRATIONDUE,
GETQTYORDERED(RV.M_PRODUCT_ID,RV.M_WAREHOUSE_ID) AS QTYOREDERED ,GETQTYRESERVED(RV.M_PRODUCT_ID,RV.M_WAREHOUSE_ID) AS RESERVED,RV.QTY-(GETQTYRESERVED(RV.M_PRODUCT_ID,RV.M_WAREHOUSE_ID)) AS AVAILABLE
FROM RV_BATCH RV
INNER JOIN M_PRODUCT MP ON MP.M_PRODUCT_ID = RV.M_PRODUCT_ID
INNER JOIN M_PRICELIST_VERSION PV ON (RV.AD_CLIENT_ID = PV.AD_CLIENT_ID and PV.M_pricelist_Version_id = getpricelist_version_id3(1000000, SYSDATE))
LEFT JOIN M_PRODUCTPRICE PP ON (MP.M_PRODUCT_ID = PP.M_PRODUCT_ID and PP.M_pricelist_Version_id = PV.M_pricelist_Version_id)
LEFT JOIN AD_Ref_lIST M ON (MP.MovingClass2=M.VALUE and M.AD_Reference_ID=1000096)
LEFT JOIN AD_Ref_lIST N ON (MP.Classification=N.VALUE and N.AD_Reference_ID=1000007)
INNER JOIN AD_REF_LIST RL ON RL.value = MP.REGISTRATIONSTATUS AND RL.AD_Reference_ID=1000013
WHERE
RV.AD_CLIENT_ID = 1000000 AND RV.QTY<>0
AND RV.AD_ORG_ID = 1000001
EXP Plain
Execution Plan
----------------------------------------------------------
Plan hash value: 3845512276
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 591 | 395K| | 9840 (10)| 00:01:59 |
| 1 | SORT AGGREGATE | | 1 | 29 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | M_TRANSACTION | 1 | 29 | | 80 (
|* 3 | INDEX RANGE SCAN | M_TRANSACTION_IDX01 | 100 | | | 3 (0)| 00
| 4 | NESTED LOOPS OUTER | | 591 | 395K| | 9840 (10)| 00:01:59 |
|* 5 | HASH JOIN RIGHT OUTER | | 584 | 380K| | 8914 (11)| 00:01:47 |
| 6 | TABLE ACCESS BY INDEX ROWID | AD_REF_LIST | 5 | 150 | | 4 (
|* 7 | INDEX RANGE SCAN | AD_REF_LIST_VALUE | 5 | | | 2 (0)|
|* 8 | HASH JOIN RIGHT OUTER | | 584 | 363K| | 8910 (11)| 00:01:47 |
| 9 | TABLE ACCESS BY INDEX ROWID | AD_REF_LIST | 5 | 150 | | 4 (
|* 10 | INDEX RANGE SCAN | AD_REF_LIST_VALUE | 5 | | | 2 (0)
|* 11 | HASH JOIN | | 584 | 346K| | 8905 (11)| 00:01:47 |
| 12 | TABLE ACCESS BY INDEX ROWID | AD_REF_LIST | 5 | 150 | | 4 (
|* 13 | INDEX RANGE SCAN | AD_REF_LIST_VALUE | 5 | | | 2 (0)| 00:00
| 14 | VIEW | | 43937 | 24M| | 8900 (11)| 00:01:47 |
|* 15 | HASH JOIN | | 43937 | 11M| 3528K| 8900 (11)| 00:01:47 |
| 16 | VIEW | RV_BATCH | 22126 | 3262K| | 7788 (12)| 00:01:34 |
|* 17 | FILTER | | | | | | |
| 18 | SORT GROUP BY | | 22126 | 2398K| 53M| 7788 (12)| 00:01:34 |
| 19 | NESTED LOOPS | | 442K| 46M| | 3413 (24)| 00:00:41 |
|* 20 | HASH JOIN | | 442K| 44M| | 2660 (2)| 00:00:32 |
| 21 | TABLE ACCESS FULL | M_LOCATOR | 152 | 6688 | | 2 (0)| 00:00
|* 22 | HASH JOIN | | 442K| 25M| 4480K| 2647 (2)| 00:00:32 |
| 23 | TABLE ACCESS FULL | M_ATTRIBUTESETINSTANCE | 109K| 3194K| | 397 (2)
|* 24 | INDEX FAST FULL SCAN | M_TRANSACTION_COBA | 442K| 13M| | 1106 (1)
|* 25 | INDEX UNIQUE SCAN | M_WAREHOUSE_KEY | 1 | 6 | | 0 (0)|
| 26 | NESTED LOOPS | | 52997 | 6262K| | 603 (1)| 00:00:08 |
| 27 | TABLE ACCESS BY INDEX ROWID| M_PRICELIST_VERSION | 1 | 9 | | 2 (0)
|* 28 | INDEX UNIQUE SCAN | M_PRICELIST_VERSION_KEY | 1 | | | 1 (0)| 0
| 29 | TABLE ACCESS FULL | M_PRODUCT | 52997 | 5796K| | 601 (1)| 00:00:08
| 30 | TABLE ACCESS BY INDEX ROWID | M_PRODUCTPRICE | 1 | 17 | | 2
|* 31 | INDEX UNIQUE SCAN | M_PRODUCTPICE_KEY | 1 | | | 1 (0)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("M_LOCATOR_ID"=:B1)
3 - access("AD_CLIENT_ID"=:B1 AND "AD_ORG_ID"=:B2 AND "M_PRODUCT_ID"=:B3)
5 - access("N"."VALUE"(+)=SYS_OP_C2C("from$_subquery$_006"."CLASSIFICATION"))
7 - access("N"."AD_REFERENCE_ID"(+)=1000007)
8 - access("M"."VALUE"(+)=SYS_OP_C2C("from$_subquery$_006"."MOVINGCLASS2"))
10 - access("M"."AD_REFERENCE_ID"(+)=1000096)
11 - access("RL"."VALUE"=SYS_OP_C2C("from$_subquery$_006"."REGISTRATIONSTATUS"))
13 - access("RL"."AD_REFERENCE_ID"=1000013)
15 - access("RV"."AD_CLIENT_ID"="PV"."AD_CLIENT_ID" AND "MP"."M_PRODUCT_ID"="RV"."M_PRODUCT_ID")
17 - filter(SUM("MT"."MOVEMENTQTY")<>0)
20 - access("MO"."M_LOCATOR_ID"="MT"."M_LOCATOR_ID")
22 - access("MA"."M_ATTRIBUTESETINSTANCE_ID"="MT"."M_ATTRIBUTESETINSTANCE_ID")
24 - filter("MT"."AD_ORG_ID"=1000001 AND "MT"."AD_CLIENT_ID"=1000000)
25 - access("MW"."M_WAREHOUSE_ID"="MO"."M_WAREHOUSE_ID")
28 - access("PV"."M_PRICELIST_VERSION_ID"="GETPRICELIST_VERSION_ID3"(1000000,SYSDATE@!))
31 - access("PP"."M_PRICELIST_VERSION_ID"(+)="PV"."M_PRICELIST_VERSION_ID" AND
"MP"."M_PRODUCT_ID"="PP"."M_PRODUCT_ID"(+))
Statistics
----------------------------------------------------------
4623 recursive calls
17 db block gets
3127505 consistent gets
33573 physical reads
0 redo size
186722 bytes sent via SQL*Net to client
1558 bytes received via SQL*Net from client
99 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1468 rows processed
|
|
|
|
| Re: Slow Query Sql [message #656643 is a reply to message #656642] |
Thu, 13 October 2016 02:04 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your filter at operation 17 is
(SUM("MT"."MOVEMENTQTY")<>0).
this is required because in your calling query you have
AND RV.QTY<>0
If you remove the predicate from the calling query and add it to the predicate in the view definition as
WHERE MT.MOVEMENTQTY<>0
you may get a different plan. No promises
Also you need to look at the definition of your MT_TRANSACTION_COBA index. I think it must be on MOVEMENTQTY and AD_ORG_ID and AD_CLIENT_ID. Is that correct? In what order?
What about M_TRANSACTION_IDX01? Can you add M_LOCATOR_ID to it?
--update: a couple of typos.
[Updated on: Thu, 13 October 2016 02:08] Report message to a moderator |
|
|
|
| Re: Slow Query Sql [message #656646 is a reply to message #656642] |
Thu, 13 October 2016 02:15 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
One more point: what do you get from this -
select count(*) from M_PRICELIST_VERSION where "M_PRICELIST_VERSION_ID"="GETPRICELIST_VERSION_ID3"(1000000,SYSDATE@!);
and what is the code of that function?
[Updated on: Thu, 13 October 2016 02:20] Report message to a moderator |
|
|
|
| Re: Slow Query Sql [message #656649 is a reply to message #656632] |
Thu, 13 October 2016 03:47 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
rayrevan wrote on Thu, 13 October 2016 05:59I trying to improve SUM aggregation like this code
Before
Sum(MT.movementqty) AS QTY
After
(select SUM(MT.MOVEMENTQTY)from M_transaction MT)
But output from qty is false/wrong
How could it possibly be right?
The original sums movementqty for every record in the current group.
The new version sums movementqty for every row in M_transaction and assigns that same value to every row in the result set.
Besides that, the most efficient way to do sum is to just use sum.
As for variable response time - using functions can do that to you.
Suppose the function runs in 0.01 seconds.
If you're returning 100 rows that'll take 1 second in total.
If you're returning 1000 rows that'll take 10 seconds in total.
Try incorporating the function queries directly into the select
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 18:05:14 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
