Re: SQL Tuning

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 3 May 2016 08:56:28 +1000
Message-ID: <CAFeFPA9_fO_AD2E2Y+YtigKjDywB76qkTh0RVnjqJiwPJ2UrTw_at_mail.gmail.com>



Hi All

Thanks for the replies/sanity checks

Index range scans were used

I checked the buffer cache and had the testers stop some processes that were using the buffer cache a lot. That "fixed" the issue (band aid) and pretty much proves my point

We have requested for more memory for this test environment... Like I said it is severely restricted compared to production and even other test/dev environments of same app.


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 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

Misses in library cache during parse: 1

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total
Waited
  • Waited ----------
    SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 9.00 17.62 Disk file operations I/O 44 0.02 0.07 db file sequential read 30927 0.53 47.96 read by other session 8530 0.47 11.65 latch: cache buffers chains 99 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current  rows
------- ------ -------- ---------- ---------- ---------- ----------



Parse 21 0.00 0.01 0 0 0

    0
Execute 61 0.01 0.09 0 0 0

    0
Fetch 65 0.00 0.41 32 172 0   196
------- ------ -------- ---------- ---------- ---------- ----------



total 147 0.03 0.52 32 172 0   196

Misses in library cache during parse: 13 Misses in library cache during execute: 13

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total
Waited
  • Waited ----------
    Disk file operations I/O 1 0.00 0.00 db file sequential read 32 0.04 0.40

    1 user SQL statements in session.
   19 internal SQL statements in session.    20 SQL statements in session.


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

On Tue, May 3, 2016 at 2:38 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Lothar,
>
>
> Some extra CPU for the chain management is inevitable; though the
> difference is rather dramatic so Stefan's comments are also relevant. It's
> also possible that "the same plan" may also have done something different
> with predicates that turned an access predicate into a filter predicate
> which could result in the same number of blocks accessed in an index range
> scan (say) but a much larger amount of work per index entry to filter out
> entries.
>
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Lothar Flatz [l.flatz_at_bluewin.ch]
> *Sent:* 02 May 2016 16:46
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: SQL Tuning
>
> Hi Jonathan,
>
> what also puzzles me is the CPU time. About the same number of buffer
> gets, but 10 times more CPU. Can't remember where I have seen that before.
>
> Regards
>
> Lothar
> On 02.05.2016 12:54, Jonathan Lewis wrote:
>
>
>
> Niall,
>
> Given there are only 68K consistent gets for 31K blocks read I think that
> some of the disk count must be from a tablescan or index fast full scan -
> which might make the average seem less wonderful. Can't argue with the
> "check the wait summary", though, for a quick check. Better still, the plan
> headed Rowsource Operations" should show the time, disk and CR buffer gets
> accumulated - so easy to see exactly where most of the time went.
>
> Regards
> Jonathan Lewis
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Niall Litchfield [niall.litchfield_at_gmail.com]
> *Sent:* 02 May 2016 09:51
> *To:* Jack van Zanen
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: SQL Tuning
>
> Jack
>
> The "slow" query does 30k disk accesses, the "fast" one 0. If the plans
> really are identical then you've likely got your explanation right there. I
> would personally want to doublecheck the summary wait information that
> TKPROF can produce as well looks like you've got about 60s of wait time
> which would equate to an average i/o time of 2ms which is actually pretty
> good (for disk).
>
> On Mon, May 2, 2016 at 5:14 AM, Jack van Zanen <jack_at_vanzanen.com> 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
>>
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
>
>
> --
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 03 2016 - 00:56:28 CEST

Original text of this message