Oracle SQL
  • LICENSE

Smart Scan and Recursive queries

Posted on March 5, 2020 by Roger MacNicol Posted in adaptive serial direct path reads, CBO, hints, oracle, parallel, SmartScan, trace, troubleshooting

Since Christmas I have been asked to investigate two different “failures to use Smart Scan”. It turns out they both fell into the same little known restriction on the use of Direct Read. Smart Scan critically depends on Direct Read in order to read the synthetic output blocks into private buffers in PGA so with Direct Read disabled Smart Scan is also disabled. In these two cases the restriction is on using Direct Read on Serial Recursive queries.

Case 1: Materialized View Refresh

A customer asked me to investigate why his MView refresh was running slowly and was failing to use Smart Scan. He had used 'trace[NSMTIO] disk=highest' which showed the cause as:

Direct Read for serial qry: disabled(::recursive_call::kctfsage:::)

but that didn’t help him understand why that was happening and the terse but accurate reply to their SR “force parallel query” didn’t help him understand why either. Good DBAs want to understand, not just have rules to apply and I appreciate that.

Case 2: From Containers() clause

The From Containers() clause is a less well known feature of Containers databases that lets you query a table in one or more a Pluggable databases. So, for example, if I connect to my CDB directly:

sqlplus sys/***********@cdb1_pdb0 as sysdba

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1_PDB1                      READ WRITE NO
         4 CDB1_PDB2                      READ WRITE NO

SQL> select count(*) from obj$;

  COUNT(*)
----------
     75130

SQL> select count(*) from containers(obj$);

  COUNT(*)
----------
    226726

SQL> select count(*) from containers(obj$) where con_id=3;

  COUNT(*)
----------
     75671

Checking the NSMTIO tracing we see:

Direct Read for serial qry: disabled(::recursive_call::kctfsage::always DR:::)

The From Containers() clause scans a fixed table of PDBs, if there is a predicate on CON-ID only those PDBs that match the predicate and used otherwise all PDBs are used. A recursive query is issued against each PDB in the fixed table that meets the CON_ID clause and the query results are the Union All of the results from each matching PDB. Now if we force the query to run in parallel either by setting the session parameter to force parallel:

SQL> alter session force parallel QUERY parallel ;

or by using the Containers hint to pass the hint to each recursive query in turn:

SQL> select /*+ CONTAINERS(DEFAULT_PDB_HINT='PARALLEL 2') */ count(*) from containers(obj$) where con_id=3;

and check the tracing we see:

NSMTIO: NSMTIO:kkfdtsc:DirectRead:

Problem fixed.

How to get Smart Scan to engage

When Direct Read was introduced a short list of exceptions was added and one was on recursive serial queries, we have filed ER Bug 30905358 – FROM CONTAINERS() TABLE SCANS ARE NOT OFFLOADED  to track the request to relax the restriction on recursive queries in a future release. In the meantime the correct solution is to add a parallel hint to the query or, if you are unable to modify the query, use the Force Parallel Query parameter.

—
Roger

direct path reads hints oracle Roger MacNicol SmartScan troubleshooting
« PL/SQL functions and statement level consistency
Android Oracle Client 2.0 »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • 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

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

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

Meta

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