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