Category Archives: hints

Smart Scan and Recursive queries

Posted on by Posted in adaptive serial direct path reads, CBO, hints, oracle, parallel, SmartScan, trace, troubleshooting Leave a comment

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:::)
Continue reading→

flashback_transaction_query: fixed table full table scan

Posted on by Posted in hints, oracle, query optimizing Leave a comment

Recently I got a question: why CBO chooses “FIXED TABLE FULL TABLE” scan instead of “FIXED INDEX” in case of a join to flashback_transaction_query view with the predicate “q.xid = a.versions_xid”:

select
  versions_xid,
  versions_startscn,
  versions_starttime,
  versions_endscn,
  versions_endtime,
  --scn_to_timestamp,
  to_char(a.classification), a.* 
from vms.advisory_arch_20190624 versions between
         timestamp timestamp'2019-06-24 13:28:00' and maxvalue a
    join flashback_transaction_query q
      on q.xid = a.versions_xid
 where a.advisoryid = 100511;

As you can see flashback_transaction_query is just a wrapper view for SYS.X$KTUQQRY, ie fixed table, which obviously has good “fixed index” for that:

And simple query with the predicate xid=hextoraw(…) uses it:

In such cases we need to check another part of the equality predicate, because usually it means that we use some function instead of literal, bind variable or SQL operator. Simple example with v$session_event:

explain plan for
SELECT SUM(E.TIME_WAITED)/100 AS TIME_WAITED 
FROM V$SESSION_EVENT E 
WHERE E.SID = SYS_CONTEXT('userenv', 'sid')
AND (E.EVENT = 'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink');

But it works fine with bind variables or if we use “precompute_subquery” hint to replace function call with the computed literal:

SQL> explain plan for
  2  SELECT  SUM(E.TIME_WAITED)/100 AS TIME_WAITED
  3  FROM  V$SESSION_EVENT E
  4  WHERE E.SID = :sid
  5    AND (E.EVENT = 'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink');

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 404853953

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    47 |     1 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    47 |            |          |
|   2 |   NESTED LOOPS            |                 |     3 |   141 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLES (ind:1) |   403 |  6851 |     1 (100)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    30 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("S"."KSLESSID"=TO_NUMBER(:SID) AND "S"."KSLESWTS"<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter(("D"."KSLEDNAM"='TCP Socket (KGAS)' OR "D"."KSLEDNAM" LIKE
              'SQL*Net%dblink') AND "S"."KSLESENM"="D"."INDX")
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
  1  explain plan for
  2  SELECT SUM(E.TIME_WAITED)/100 AS TIME_WAITED
  3  FROM
  4   V$SESSION_EVENT E
  5  WHERE E.SID in (select/*+ precompute_subquery */ SYS_CONTEXT('userenv', 'sid') from dual) AND (E.EVENT =
  6*   'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink')
SQL> /

Explained.

SQL> @xplan ""



PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2330447422

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    50 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    50 |            |          |
|   2 |   NESTED LOOPS            |                 |     3 |   150 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLES (ind:1) |   514 | 10280 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    30 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("S"."KSLESSID"=382 AND "S"."KSLESWTS"<>0 AND ("S"."CON_ID"=0 OR
              "S"."CON_ID"=3) AND "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter("S"."KSLESENM"="D"."INDX" AND ("D"."KSLEDNAM"='TCP Socket (KGAS)' OR
              "D"."KSLEDNAM" LIKE 'SQL*Net%dblink'))

19 rows selected.

So the same problem we got with flashback_transaction_query, because VERSIONS_XID is a pseudocolumn in fact (ie function). So we can easily hide it in inline view: 

SQL> ;
  1  explain plan for
  2  with a as (
  3  select/*+ no_merge */
  4         versions_xid, versions_startscn, versions_starttime, versions_endscn, versions_endtime, --scn_to_timestamp,
  5         to_char(x.classification),
  6         x.*
  7    from advisory_arch_20190624 versions between timestamp to_timestamp('2019-06-25 22:14:00', 'YYYY-MM-DD HH24:MI:SS') and maxvalue x
  8    where x.advisoryid = 100511
  9  )
 10  select/*+ leading(a q) use_nl(q) index(q (xid)) */ *
 11  from a
 12*      join flashback_transaction_query q on q.xid = a.versions_xid
SQL> /

Explained.

SQL> @xplan +outline

P_FORMAT
------------------------
typical +outline

1 row selected.


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2200855354

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |  2596 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                        |     1 |  2596 |     6   (0)| 00:00:01 |
|   2 |   VIEW                   |                        |     1 |    98 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | ADVISORY_ARCH_20190624 |     1 |     9 |     6   (0)| 00:00:01 |
|*  4 |   FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1)      |     1 |  2498 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$4" "X"@"SEL$4")
      USE_NL(@"SEL$62A6D27E" "X$KTUQQRY"@"SEL$3")
      LEADING(@"SEL$62A6D27E" "A"@"SEL$1" "X$KTUQQRY"@"SEL$3")
      FULL(@"SEL$62A6D27E" "X$KTUQQRY"@"SEL$3")
      NO_ACCESS(@"SEL$62A6D27E" "A"@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3" >"SEL$2")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A" >"SEL$1")
      OUTLINE(@"SEL$5C160134")
      OUTLINE(@"SEL$5")
      MERGE(@"SEL$5C160134" >"SEL$5")
      OUTLINE_LEAF(@"SEL$62A6D27E")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("X"."ADVISORYID"=100511)
   4 - filter("XID"="A"."VERSIONS_XID" AND ("CON_ID"=0 OR "CON_ID"=3))

And in addition a couple of my scripts for fixed tables:

https://github.com/xtender/xt_scripts/blob/master/fixed_tables.sql

https://github.com/xtender/xt_scripts/blob/master/fixed_indexes.sql

Correct syntax for the table_stats hint

Posted on by Posted in adaptive serial direct path reads, CBO, hints, oracle, SmartScan, trace, troubleshooting, undocumented 1 Comment

A friend contacted me to ask why they were having problems using the table_stats hint to influence optimizer decision making and also to influence the decision to use direct read or buffer cache scan so this is just a quick blog post to clarify the syntax as it is not well documented.

table_stats(<table_name> <method> {<keyword>=<value>} )

Method is one of: DEFAULT, SET, SCALE, SAMPLE

Keyword is one of: BLOCKS, ROWS, ROW_LENGTH
Continue reading→

More tricks with OPT_PARAM

Posted on by Posted in cell_offload, hints, oracle, SmartScan Leave a comment

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;
Continue reading→

PRECOMPUTE_SUBQUERY hint

Posted on by Posted in CBO, hints, undocumented 3 Comments

I’ve just found out that we can specify query block for PRECOMPUTE_SUBQUERY: /*+ precompute_subquery(@sel$2) */
So we can use it now with SQL profiles, SPM baselines and patches.

SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);

D
-
X

SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c437vsqj7c4jy, child number 0
-------------------------------------
select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
(select chr(level) from dual connect by level<=100)

Plan hash value: 272002086

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
              OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
              "DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
              OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='►' OR "DUMMY"='◄' OR
              "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
              "DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
              "DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
              "DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
              OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
              "DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
              OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
              "DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
              OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
              "DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
              OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
              "DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
              OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
              "DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
              OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
              "DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
              OR "DUMMY"='c' OR "DUMMY"='d'))

PS. I’m not sure, but as far as i remember, when I tested it on 10.2, it didn’t work with specifying a query block.
And I have never seen such usage.