Home » RDBMS Server » Performance Tuning » Slow Query Sql (Oracle 10g)
Slow Query Sql [message #656454] Thu, 06 October 2016 04:32 Go to next message
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 #656461 is a reply to message #656454] Thu, 06 October 2016 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like rv_batch is a view, in which case we need to see the view query.
Re: Slow Query Sql [message #656517 is a reply to message #656461] Sun, 09 October 2016 20:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7262
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 Go to previous messageGo to next message
cookiemonster
Messages: 13014
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 Go to previous messageGo to next message
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
Explain Plan Before
---------------------------------------------------------------------------------------------------
| 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 Go to previous messageGo to next message
BlackSwan
Messages: 25856
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 #656632 is a reply to message #656628] Wed, 12 October 2016 23:59 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
Quote:
Quote:
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
All columns already indexed, I 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
Re: Slow Query Sql [message #656638 is a reply to message #656627] Thu, 13 October 2016 00:54 Go to previous messageGo to next message
John Watson
Messages: 7262
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 #656640 is a reply to message #656638] Thu, 13 October 2016 01:26 Go to previous messageGo to next message
rayrevan
Messages: 21
Registered: August 2016
Junior Member
Quote:
John Watson
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?
Sorry, i dont understand what u mean, can u give example of predicate section exec plan?

And Big Thanks to John Watson, Cookiemonster, and all response its really help a lot Smile
Re: Slow Query Sql [message #656641 is a reply to message #656640] Thu, 13 October 2016 01:29 Go to previous messageGo to next message
John Watson
Messages: 7262
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>
You have not said what performance you require.
Re: Slow Query Sql [message #656642 is a reply to message #656641] Thu, 13 October 2016 01:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7262
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 Go to previous messageGo to next message
John Watson
Messages: 7262
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 Go to previous message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
rayrevan wrote on Thu, 13 October 2016 05:59
I 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
Previous Topic: Partitioning huge table
Next Topic: Exceeding maxi,um number of cursors
Goto Forum:
  


Current Time: Wed Feb 21 09:24:44 CST 2018

Total time taken to generate the page: 0.01784 seconds