Performance Problem : PL/SQL

All we need is an easy explanation of the problem, so here it is.

SELECT
  NULL AS TRX_NUMBER ,
  NULL AS CUSTOMER_TRX_ID ,
  VW_SAP_MPP_INV1.DOCENTRY AS DOCENTRY ,
  VW_SAP_MPP_INV1.LINENUM AS LINENUM ,
  VW_SAP_MPP_INV1.ITEMCODE AS ITEMCODE ,
  VW_SAP_MPP_INV1.U_ALTDESC AS U_ALTDESC ,
  VW_SAP_MPP_INV1.DESCRIPTION AS DESCRIPTION ,
  VW_SAP_MPP_INV1.PRICE AS PRICE ,
  VW_SAP_MPP_INV1.QUANTITY AS QUANTITY ,
  VW_SAP_MPP_INV1.LINETOTAL AS LINETOTAL ,
  VW_SAP_MPP_INV1.TAXSTATUS AS TAXSTATUS ,
  VW_SAP_MPP_INV1.TAX AS TAX ,
  VW_SAP_MPP_INV1.WHSCODE AS WHSCODE ,
  VW_SAP_MPP_INV1.U_WP AS U_WP ,
  VW_SAP_MPP_INV1.U_CASENUM AS U_CASENUM ,
  VW_SAP_MPP_INV1.U_FACILITY AS U_FACILITY ,
  VW_SAP_MPP_INV1.U_OPENED AS U_OPENED ,
  VW_SAP_MPP_INV1.U_CLOSED AS U_CLOSED ,
  VW_SAP_MPP_INV1.U_CONTACTPERSON AS U_CONTACTPERSON ,
  VW_SAP_MPP_INV1.LINETYPE AS LINETYPE ,
  VW_SAP_MPP_INV1.VISORDER AS VISORDER ,
  VW_SAP_MPP_INV1."DATECREATEPORTAL" AS DATECREATEPORTAL ,
  NULL AS DATEMODIFYPORTAL ,
  NULL AS HDR_UPDATEDATE ,
  NULL AS LINE_UPDATEDATE ,
  NULL AS INV_CURR_CODE ,
  NULL AS HDR_ETL_UPDATE_DT ,
  VW_SAP_MPP_INV1.LINE_ETL_UPDATE_DT AS LINE_ETL_UPDATE_DT ,
  NULL AS WO_ETL_UPDATE_DT ,
  VW_SAP_MPP_INV1.ITEM_DESC AS ITEM_DESC ,
  VW_SAP_MPP_INV1.SOURCEID AS SOURCEID  
FROM
  ODS.VW_SAP_MPP_INV1 VW_SAP_MPP_INV1
WHERE
  (VW_SAP_MPP_INV1.LINE_ETL_UPDATE_DT >= TO_DATE(SUBSTR('2022-07-26 15:35:19.0',0,19),'YYYY-MM-DD HH24:MI:SS'))
 The `View` is a simple select from a `table`

It fetch 50 rows within 0.50 sec(without the "WHERE" condition)
With "WHERE" condition it takes 80 seconds.

Please suggest on how to improve this performance.Performance Problem : PL/SQL

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It is not a simple select from a table. You have 2 tables with a join.

Fetching the first 50 rows randomly without a condition is not the same as fetching the first 50 rows specified by conditions.

  1. The datatypes of columns used for join do not match, indicated by SYS_OP_C2C internal conversion function in plan. This should be fixed because it will never be optimal like this.

  2. Even though you use Exadata (indicated by STORAGE FULL), the storage indexes are not some kind of magic that can replace standard indexes in all scenarios. An index may help here, but we do not know your tables, the amount and distribution of data. Try creating an index on SAP_INV1(ETL_UPDATE_DT), or if such index exists, check the statistics to see why it is not used.

  3. Finally, parallel query may be an unnecessary overhead depending on the amount of data, which again, we do not know.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply