Re: SQL Tuning
Date: Mon, 2 May 2016 19:54:41 -0400
Message-ID: <5727E8C1.4060006_at_gmail.com>
On 05/02/2016 12:14 AM, Jack van Zanen wrote:
> Hi All,
>
>
> I have two identical databases as far as versions, datasize OS etc is
> concerned and have a query that produces an identical execution plan.
> However this part of the 10046 trace is significantly different.
>
> The slow query is on test (first listing) and is severely constricted
> in its memory so my explanation would be that the tables involved are
> actually in the buffer cache in prod (second listing) where the sga
> is much larger and therefore no disk I/O is required.
>
> I will be having a look at the buffer cache next to check what is in there
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 1 0.01 0.22 0 0 0
> 0
> Fetch 1 3.74 65.97 30927 68453 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 3.76 66.21 30927 68453 0
> 0
>
>
>
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.02 0 8 0
> 0
> Execute 1 0.01 0.01 0 83 0
> 0
> Fetch 1 0.32 0.53 0 68507 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 0.34 0.57 0 68598 0
> 0
>
>
> Jack van Zanen
>
> -------------------------
> This e-mail and any attachments may contain confidential material for
> the sole use of the intended recipient. If you are not the intended
> recipient, please be aware that any disclosure, copying, distribution
> or use of this e-mail or any attachment is prohibited. If you have
> received this e-mail in error, please contact the sender and delete
> all copies.
> Thank you for your cooperation
Hi Jack,
I believe that the key phrase for solving the mystery is "severely
restricted memory". Essentially, on the first database, with a lot of
memory, the underlying table is not considered a "big table" (> 2% SGA)
and is read using the normal buffered reads, thereby utilizing the SGA
as a cache. On the instance with a much smaller SGA, the same table
would be considered a "big table", because of the much smaller SGA, and
would be read using direct reads. Now, direct reads do all kinds of
stuff, expanding address space among others. That would require
additional CPU resources to handle. You can check whether your CPU
consumption is in the user mode or in the kernel mode. If it is the
kernel mode, I am prepared to bet that the difference is in the type of
read (buffered vs. direct). Note that this type of decision is not made
by the optimizer, so plans would remain the same, but by the SQL
executioner (well, I know it's "executor", but couldn't resist) layer.
This is a new behaviour on version 11G and I've seen such problems
before. Basically, if the table is larger than 2% SGA, then a full table
scan is performed by utilizing direct reads, rather than buffered reads.
Regards
-- Mladen Gogala Oracle DBA Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 03 2016 - 01:54:41 CEST