Jump to content
tipsu

Strange behavior

Recommended Posts

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

 

 

Share this post


Link to post
Share on other sites

Hi Tipsu,

Please the-run the query with autotrace so we can see the execution I/O stats:

http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=Autotrace+&sa=Search&siteurl=www.dba-oracle.com%2Fart_dbazine_oracle_10g_data_warehouse.htm&ref=www.google.com%2F&ss=16003j20608105j27

Depending on your release, slow second execution speed is a bug in cardinality feedback.  Please check MOSC to see if this matches your release.

http://support.oracle.com 

If so, it is fixed by changing _optimizer_use_feedback to false.

Rrmember, if this is PROD or you are not using opt_param, you must notify MOSC that you are changing a hidden parm.

Use the last statement on this page:

http://www.dba-oracle.com/t_cardinality_feedback.htm

==============================

Also, do you have a specific question, or is this a technical support request?

All technical support requests are filled by filing a service request on MOSC:

http://support.oracle.com

Remdmber, this forum is for asking specific questions, not as a free replacement for Oracle support!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi,

What?  You ignored ALL of my suggestions?

What did MOSC say?

Where us the autotrace output that I requested?

Why are you running a long-ago de-supported release of Oracle?

Who said anything about rebuilding indexes?  That’s ridiculous.

You really thought that the index trees changed?

You obviously think you know more about Oracle than me since you ignored my advice.

Please don’t post service requests here anymore, and don’t ask for my advice if you already know better.  

I worked hard on your question, and you crapped on ALL of it.

 

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×