Oracle SQL

    Serial Scans failing to offload

    Posted on October 13, 2020 by Roger MacNicol Posted in adaptive serial direct path reads, cell_offload, oracle, SmartScan

    Very Large Buffer Cache

    We’ve observed databases with very large buffer caches where Serial Scans don’t make use of Smart Scan when that would have executed faster: improvements to the decision making for Serial Scans have been made under bug  31626438. This fix is back-portable.

    A key difference between PQ and Serial is that as part of granule generation PQ sums the sizes of all the partitions that have not been pruned and passes that total size to the buffer cache decision making logic. Because the entire size to be scanned is considered, we make an accurate determination of smart scan benefits and the risk of cache thrashing.

    Serial Scans on partitioned tables do not involved the coordinator and have no opportunity to get the larger picture, instead they start work immediately so each partition is considered one at a time and only that one partition’s size is considered by the decision for using Buffer Cache or Direct Read (and hence offload). In the presence of very large buffer caches any given partition can fail the “Is Medium” test (or even the “Is Small” test) and so not get offloaded.

    In order to avoid this situation an upper bound of 100MB for using a buffer cache scan has been implemented for any serially scanned segment that:

    • isn’t using Automatic Big Table Caching (ABTC).
    • hasn’t had the Small Table parameter changed to a non-default value.

    Any partitions larger than 100 MB will now automatically use Direct Read and hence offload on Exadata.

    See also: Part 1

    See also: Part 2

    NSMTIO: kxfxghwm:[HWM_NOT_FOUND]

    Another case to watch out for is when NSMTIO tracing shows HWM_NOT_FOUND and then choosing a Buffer Cache scan when a Direct Read offloaded scan would have been faster. This can happen when a PQ query gets executed serially (NB: this is NOT the downgrade to serial case, this is still PQ but on a single thread). In this case the coordinator again does not have the opportunity to process all the partitions and as part of that gather the High Water Mark (HWM) for each segment and checkpoint them so we fall back on buffer cache scans. A fix for this is currently being investigated.

    Mixed Block Sizes

    I have consistently advised against mixing block sizes in a database without a compelling reason backed up by empirical evidence, but for those who must the “Is Medium Table” logic for whether to use buffer cache or direct read has been improved when the database has more than one block size in use. This is tracked by bug 24655250 and fixed in 20.1.

    See also  Random thoughts on block sizes

    Print PDF eBook
    direct path reads oracle Roger MacNicol SmartScan
    « Simple function returning Parallel slave info
    Funny friday Oracle SQL quiz: query running N seconds »
    photo Sayan Malakshinov

    Oracle Database Developer Choice Award winner
    Oracle ACE Oracle ACE
    Oracle performance tuning expert
    UK / Aylesbury / Transmedia Dynamics
    sayan@orasql.org

    Simple Android Oracle client

    Get it on Google Play

    Recent Posts

    • Where does the commit or rollback happen in PL/SQL code?
    • :1 and SP2-0553: Illegal variable name “1”.
    • ORA exceptions that can’t be caught by exception handler
    • Another interesting troubleshooting case
    • SQL*Plus tips #9: Reading traces and incident files

    Recent Comments

    • Oracle SQL | Shining some light on Database In-Memory vs the Exadata Columnar Cache in 12.1.0.2 on Tracing Hybrid Columnar Compression in an offload server
    • Oracle SQL | More on tracing the offload server on Tracing Hybrid Columnar Compression in an offload server
    • Oracle SQL | DMLs and the Columnar Cache on ADW on How to tell if the Exadata column cache is fully loaded
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 3
    • Oracle SQL | Serial Scans failing to offload on When bloggers get it wrong – part 2
    • Oracle SQL | Serial Scans failing to offload on When bloggers get it wrong – part 1
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 2
    • Oracle SQL | DMLs and the Columnar Cache on ADW on What’s new in 12.2 CELLMEMORY Part 1
    • Oracle SQL | The beginners guide to Oracle Table Scans on When bloggers get it wrong – part 1
    • Case Study | Oracle Scratchpad on Lateral view decorrelation(VW_DCL) causes wrong results with rownum

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL