Oracle Scratchpad

January 15, 2023

Quiz Night

Filed under: Infrastructure,Oracle,Parallel Execution,Troubleshooting — Jonathan Lewis @ 6:25 pm GMT Jan 15,2023

Here’s a little extract from one of my “snap instance activity stats” packages picking out the figures where the upper case of the statistic name is like ‘%PARALLEL%’. I was very careful that nothing except one of my SQL*Plus sessions had done anything in the few seconds between the start and end snapshots so there’s no “(un)lucky timing” to explain the anomaly in these figures.

The quesion is: how can Oracle manage to claim hundreds of “checkpoint buffers written for parallel query” when there had been no parallel statements executing around the time the snapshots were taken?

Name                                                        Value
----                                                   ----------
DBWR parallel query checkpoint buffers written              1,430
queries parallelized                                            0
DML statements parallelized                                     0
DDL statements parallelized                                     0
DFO trees parallelized                                          0
Parallel operations not downgraded                              0
Parallel operations downgraded to serial                        0
Parallel operations downgraded 75 to 99 pct                     0
Parallel operations downgraded 50 to 75 pct                     0
Parallel operations downgraded 25 to 50 pct                     0
Parallel operations downgraded 1 to 25 pct                      0

Here’s a little background information if you don’t know why this is a puzzle.

When you start executiing a parallel tablescan (or index fast full scan) the first step that your session takes is to tell DBWR to write to disc any dirty blocks for the object you’re about to scan and wait for DBWR to confirm that the blocks have been written. The session does this because a parallel scan will bypass the buffer cache and read directly from disc to the PGA, so if there were any dirty blocks for the object in the buffer cache the query would not find them and get them to a correctly read-consistent state. The blocks written by DBWR in this case would (should) be reported under “DBWR parallel query checkpoint buffers written”.

Answer

It didn’t take long for the correct answer to appear in the comments. I updated a large number of rows in a table, then I set the parameter “_serial_direct_read” to “always” in my session and executed a query that did a tablescan of that table.

My session called the database writer to do an “object checkpoin”, then started waiting on event “enq: KO – fast object checkpoint” before reading the table using direct path reads of (just under) 1MB each.

I published this note because a question came up on the Oracle developer forum which (as a side note) had noted the apparent contradiction in a Statspack report, and I thought it was a useful lesson covering two important points.

First: the Oracle code keeps evolving, and the instrumentation and statistics don’t always keep up; in this case I think there’s a code path that says “fast object checkpoint”, but doesn’t have a flag that separates the newer serial scan option from the original parallel scan option – hence serial scans triggering “parallel query checkpoints”.

Secondly: when you see some performance figures that don’t seem to make sense or contradict your previous experience, it’s worth thinking around the oddity to see if you can come up with a feasible (and testable) explanation along the lines of “what if Oracle’s doing something new but calling some old instrumentation”.

Footnote

One of the comments includes some questions about how the whole parallel query / read-consistency / checkpointing works if more DML is happening as the query runs. I’ll come back to that in a couple of days.

7 Comments »

  1. […] Quiz night 39 (Jan 2023): how do you get “parallel query checkpointing” without parallel queries? […]

    Pingback by Quiz Catalogue | Oracle Scratchpad — January 15, 2023 @ 6:29 pm GMT Jan 15,2023 | Reply

  2. (adaptive) Serial direct reads?

    Comment by Sayan Malakshinov — January 15, 2023 @ 6:41 pm GMT Jan 15,2023 | Reply

  3. Hi Jonathan

    Thank you for your decades of service to the ORACLE community – I have personally owned many books and I am very curious about your posts (if I understand them!) everytime

    This is interesting for two reasons I feel –

    What happens to DBWR activity after FFS begins to support direct operation? Would it “pull the rug” underneath “if” it were to change blocks that are in the middle of the read by the FFS? Or does it effectively cause DBWR to wait till PX activity is completed? (I would think this would be the case – will pause DBWR flush) (And what happens if someone does ALTER DATABASE BEGIN BACKUP (old style OS backup) – wouldn’t it freeze datafile headers…

    And, if you have liberal use of such PX activity across many tables with direct mode, would this cause a lot of interleaving of DBWR force flush and wait for yet another PX session if it would have started on another set of tables? Add that to RAC and we are talking about a cashfusion (LMS) nightmare?

    It just seems as technology is evolving with disk I/O of NVME this will be a needless nightmare to continue to support/develop & support bugs?

    Btw, I came across one other interesting issue and wanted to communicate but I couldn’t copy the code due to Citrix restriction – In short – the only difference between function F1 and F2 was – DETERMINISTIC word! And I selected it as following

    SELECT F1(‘XYZ’) FROM BIGTABLE; SELECT F1(‘XYZ’) FROM BIGTABLE;

    Plan looks exactly the same, same LIO, everything – but time spent is a HUGE difference in CPU/Elapsed time!! It was interesting how DETERMINISTIC was basically not making function calls at all!! I am not sure why I am surprised – but I am pleasantly surprised to see it worked as expected

    Once again thank you very much for your decades of guidance and approach to “truth-seeking”

    thank you sudhir

    Comment by ora777 — January 16, 2023 @ 12:31 am GMT Jan 16,2023 | Reply

      • Hi Sayan

        Thank you for the links — they are pretty complex and interesting. The first example in first link is what I am using – But if you test your example –

        INSERT INTO X
        select
        f_deterministic(x) a
        ,f_deterministic(‘literal’) b
        from (select ‘not literal’ x
        from dual
        connect by level<=10
        );

        This should not depend upon array size of the client – and what I found was it was calling function with 'literal' and with x very much low number of time

        The actual test I did was little bit different

        with A as (select 'XYZ' c from dual)
        select DISTINCT F2(A.c) from BIGTABLE, A;

        with A as (select 'XYZ' c from dual)
        select DISTINCT F1(A.c) from BIGTABLE, A;

        Since F2 is deterministic – vs F1 not — the time difference was almost 8x due to size of BIGTABLE. The LIO, access plan exactly same between both SQL's.

        thank you!

        Comment by sudhir — January 16, 2023 @ 11:28 pm GMT Jan 16,2023 | Reply

        • Sudhir,

          In a simplified model, we can consider “insert-select” as a single fetch call (like ‘insert values returning’), since there are no fetch calls from a client: all results goes into “INSERT INTO X”. However, there are situations when is is more complex, such as in the case of statement-level restart.
          Therefore, in the case of ‘insert-select’, the same number of function executions should occur as in ‘select bulk collect into …’.

          In your actual test there will be only 1 fetch call, so your deterministic function will be called only once. And in case of a non-deterministic function, the number of function executions will be equal to the number of rows in `bigtable`

          Comment by Sayan Malakshinov — January 17, 2023 @ 12:28 am GMT Jan 17,2023

  4. A dumb explanation would be timing: DBWR captures its stats near real-time, while parallel queries update theirs at a later time. Another dumb idea would be: DBWR capturing not only parallel queries checkpoint stats but those required for serial direct path reads.

    Comment by Timur Akhmadeev — January 16, 2023 @ 8:49 am GMT Jan 16,2023 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: