Oracle SQL

    More tricks with OPT_PARAM

    Posted on May 4, 2017 by Roger MacNicol Posted in cell_offload, hints, oracle, SmartScan

    Did you know you can set most parameters for the execution of a single statement without using an Alter Session by using an OPT_PARAM hint? For example, regular parameters (here forcing the storage clause in the query plan):

    SQL> select /*+ OPT_PARAM('cell_offload_plan_display' 'always') */ col1 From table1;
    

    and underscore parameters:

    SQL>  select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation;
    

    However if you try conflicting settings that set a different value in an inner query block, the value you set in the outermost query block will prevail. In this trvial example _arch_comp_dbg_scan=2048 will prevail:

    SQL> select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation
    where n_nationkey = (
        select /*+ OPT_PARAM('_arch_comp_dbg_scan',0) */ min(r_regionkey)
        from region where n_name < r_name); 
    

    Another point to remember is that not all parameters get their value from the cursor environment. For example the buffer cache gets the raw value of _serial_direct_read so it cannot be overruled in this way: 

    SQL> select /*+ OPT_PARAM('_serial_direct_read', always) */ n_name from nation; 
    

     will not force a DR scan of the table but an alter session will.

    Just one more tool to keep in your toolkit for when you need it.

    —
    Roger

    Print PDF eBook
    hints oracle Roger MacNicol SmartScan
    « Improvements to HCC with wide tables in 12.2
    Offloading row level security to Smart Scan »
    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