Re: Mysterious high consistent gets;
Date: Mon, 18 Jun 2018 13:16:22 -0600
Message-ID: <5a528969-d3df-56ac-8b8d-5af057e9fa7d_at_gmail.com>
There is likely a big difference in overall conditions between when you
run the query yourself versus how it is running out "in the wild" by
LoadRunner.
For example, in LoadRunner, are you also having other sessions (or the
same sessions) also performing data modification on the table(s) that
this query is scanning?
If so, then a fair portion of your "consistent gets" are likely coming
from undo segments, not from table/index segments, as the query executed
under LoadRunner is having to rebuild the consistent image at the
point-in-time when the query began while updates/deletes/merges are
happening concurrently.
In contrast, when you are running the query by yourself for testing with
autotrace and tkprof, there are probably no modifications to the
table(s) going on, so "consistent gets" are very simple and relatively
inexpensive.
On 6/18/18 12:48, Ashish Lunawat wrote:
> Hi, I have a query which when run through autotrace and tkprof shows
> me about 50,000 gets. But the same query when shot as a part of
> loadrunner performance testing causes about 1.2 million consistent
> gets as seen in the AWR report. How is this possible?
>
> The database is running on a 2 node RAC with each node having 32
> cores. Tkprof shows this query takes about .8 seconds and causes 50K
> consistent gets while when shot through loadrunner it, causes both the
> RAC nodes to go as much as 100% CPU. When monitoring session waits I
> can see that there is a hot block contention happening on a table with
> about 19K rows and an index on this table. This query is responsible
> for about 94% of the gets and thus high cpu utilization.
>
> Any clues how to troubleshoot this issue?
>
> Thanks
> Regards,
> Ashish
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 18 2018 - 21:16:22 CEST