Re: Instrumenting Poor performance
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-lReceived on Tue Sep 18 2018 - 02:02:55 CEST