Jump to content

tipsu

Members
  • Content count

    20
  • Joined

  • Last visited

Community Reputation

0 Neutral

About tipsu

  • Rank
    Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. tipsu

    Strange behavior

    Hi Dear Mr. Burleson! Thank You very much for answering! Forgot to say that its 10.2.0.3 db, no cardinaly feedbak. Just after 1631 executions the query slows down very significantly.. from 0.314 sec to max 34 sec per execution.. Rebuilding of indexes tried -not helping here Here is tkprof file with sort=exeela and sys=no. Thx! report1.prf
  2. tipsu

    Strange behavior

    Hi Dear Mr.Burleson! Simple query: SELECT SUM(NVL(AEL.ACCOUNTED_DR,0.0) - NVL(AEL.ACCOUNTED_CR,0.0) ) FROM PAYMENT_LINES AEL,PAYMENT_HEADERS AEH WHERE AEL.AE_HEADER_ID = AEH.AE_HEADER_ID AND (:b1 IS NULL OR AEH.ACCOUNTING_DATE >= FND_DATE.CANONICAL_TO_DATE(:b1) ) AND (:b3 IS NULL OR AEH.ACCOUNTING_DATE <= FND_DATE.CANONICAL_TO_DATE(:b3) ) AND AEL.AE_LINE_TYPE_CODE IN ( 'LIABILITY','PREPAY' ) AND AEL.THIRD_PARTY_ID = :b5; PAYMENT_LINES - 5,5 mln rows PAYMENT_HEADERS 1,5 mnl rows select count (*) PAYMENT_LINES AEL from AEL.AE_LINE_TYPE_CODE IN ( 'LIABILITY','PREPAY' ) ; 2,5 mnl rows PAYMENT_LINES_N4 index is on ( THIRD_PARTY_D,THIRD_PARTY_SUB_ID); PAYMENT_HEADERS_U1 index is unique on PAYMENT_HEADERS(AE_HEADER_ID) When executing this it , it goes very fast aprox 1616 execution times using the SAME execution plan: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 50 | 186 (0)| 00:00:03 | | 1 | SORT AGGREGATE | | 1 | 50 | | | | 2 | NESTED LOOPS | | 4 | 200 | 186 (0)| 00:00:03 | |* 3 | TABLE ACCESS BY INDEX ROWID| PAYMENT_LINES_ALL | 4 | 128 | 178 (0)| 00:00:03 | |* 4 | INDEX RANGE SCAN | PAYMENT_LINES_N4 | 876 | | 5 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| PAYMENT_HEADERS_ALL | 1 | 18 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | PAYMENT_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- But then it drastically slows down ...But the execution plan is the same from average time from 0.314 to max 34.86 sec.. SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO ---------- ------ ------------------------------ ------------- --------------- ------------ ------------ -------------- 134902 1 25-OCT-18 11.30.07.363 PM gdbbry6ffwmtt 1134951010 1,631 .314 11,989.6 134903 1 26-OCT-18 12.00.32.036 AM gdbbry6ffwmtt 63 28.267 483,428.3 134904 1 26-OCT-18 12.30.56.668 AM gdbbry6ffwmtt 77 22.435 486,315.5 134905 1 26-OCT-18 01.00.27.323 AM gdbbry6ffwmtt 51 34.864 483,535.9 134906 1 26-OCT-18 01.30.51.950 AM gdbbry6ffwmtt 77 22.437 484,825.3 134907 1 26-OCT-18 02.00.19.564 AM gdbbry6ffwmtt 57 31.226 492,802.6 134908 1 26-OCT-18 02.30.44.207 AM gdbbry6ffwmtt 79 21.756 487,045.1 134909 1 26-OCT-18 03.00.02.823 AM gdbbry6ffwmtt 75 23.828 481,076.8 134910 1 26-OCT-18 03.30.36.411 AM gdbbry6ffwmtt 82 20.983 491,630.9 134911 1 26-OCT-18 04.00.01.126 AM gdbbry6ffwmtt 78 22.814 484,869.8 134912 1 26-OCT-18 04.30.22.693 AM gdbbry6ffwmtt 84 21.233 486,296.2 134913 1 26-OCT-18 05.00.50.261 AM gdbbry6ffwmtt 106 10.808 238,767.7 Any advice here what should I do ? Thank you very much,Linda
×