Re: Instrumenting Poor performance

From: Upendra nerilla <nupendra_at_hotmail.com>
Date: Tue, 18 Sep 2018 00:02:55 +0000
Message-ID: <SN1PR10MB036821EB2BA707C155A897ECD81E0_at_SN1PR10MB0368.namprd10.prod.outlook.com>

Validated the object is indeed OE_ORDER_LINES_ALL by picking a block from 10046 trace and confirmed. Validated the indexes, none of them were recently created.

Would it help caching the entire table? alter table.. cache;

Unfortunately we hit the issue again today.. 🙁 I am gathering a full 10046 trace, sqlt with all other reports..

---

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Sent: Monday, September 17, 2018 7:05 PM To: Dominic Brooks; nupendra_at_hotmail.com Cc: Martin Berger; Oracle-L oracle-l
Subject: Re: Instrumenting Poor performance

Looking at the numbers:

the CR (query) gets per execution are consistent across the two reports, so it's unlikely that you have a large volume of undo being applied for read-consistency; application of undo records would have to show up as extra CR gets.

Further - if your 92.7% is counting the waits for "db file sequential read" in the trace file and they're coming from reads of the OE_ORDER_LINES_ALL table then they're not reads of the undo tablespace, which adds the argument that it's not about big updates and large scale read-consistency work. You can cross check the 10046 reads very easily by checking the "file#=" entry on the waits (as the obj# may be deceptive - it certainly can be in v$active_session_history). Are the reads from a file number that matches the undo tablespace or from a file number that matches the data object.

It seems more likely that some recent (or current) activity has simply pushed lots of the blocks of OE_ORDER_LINES_ALL table out of the buffer cache and you're having to read (and re-read, possibly) lots of blocks to get the result.

I suppose it's also possible that the plan has actually changed and you're using a different, much less precise, index to access the table (hypothetically someone could have dropped and rebuilt a couple of indexes with the names the wrong way round - I think that would make it possible to get the same plan hash value while using indexes with different definitions)

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Upendra nerilla <nupendra_at_hotmail.com> Sent: 17 September 2018 22:25
To: Dominic Brooks
Cc: Martin Berger; Oracle-L oracle-l
Subject: Re: Instrumenting Poor performance

Bad run:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        0      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3    160.44    1289.07    3673632   13274897          0         368


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        6    160.44    1289.07    3673632   13274897          0         368


Good run:
call     count       cpu    elapsed       disk      query    current        rows


------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0
Execute      9      0.00       0.00          0          0          0           0
Fetch        9    119.20     119.95        135   40086840          0        2111


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       18    119.20     119.96        135   40086840          0        2111

Upon looking at the details, "OE_ORDER_LINES_ALL" seems to be the object which was read from disk..

db file sequential read 92.7% 1,258.1501s 3,692,162 0.0003s 0.0001s 0.5071s

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 18 2018 - 02:02:55 CEST