Re: Mysterious high consistent gets;
Date: Tue, 19 Jun 2018 03:28:10 +0800
Message-ID: <CAEzAyeD6Xmp2AHU5RfmJX6=t81hgxwUX=D-f6wcS-QkHjZONSA_at_mail.gmail.com>
Thanks
On Tue, Jun 19, 2018 at 3:16 AM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:
> 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:28:10 CEST