[20210528]V$INDEXED_FIXED_COLUMN视图.txt

--//看了一些文档,提到V$INDEXED_FIXED_COLUMN视图,该视图保存了X$ 表的索引信息.
--//首先说明一点 X$实际上一些内存结构,比如数组,指针,链接等等,实际上一些视图就是基于这些X$表构件出来.
--//而其对应的索引是什么结构呢,在内存结构上的b*tree 索引,oracle从不公开这些信息,也许是也许不是.

--//如果知道X$ 表对应的索引,使用其索引,也许能写出更好的sql语句.

http://ermanarslan.blogspot.com/2021/04/rdbms-vindexedfixedcolumn-useful-view.html

In this context, if a performance problem appears in a query on some v$ views, it would be useful to look at the
execution plan. If we see FIXED TABLE FULL when accessing x$ tables,  we can get the information from
V$INDEXED_FIXED_COLUMN which columns of the related x$ table are indexed and then we may change our query to make the
optimizer use that index and thus solve the problem.

--//另外X$ 可能缺乏统计信息,导致选择不好的执行计划,建议在改变系统配置或者新系统上线时执行:
execute sys.dbms_stats.GATHER_FIXED_OBJECTS_STATS();
execute sys.dbms_stats.GATHER_DICTIONARY_STATS();

--//收集X$表的统计信息,也许能获得更好的性能.

1.环境:
SYS@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select * from v$session where sid=2;
no rows selected

SYS@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3v0kwpwuncunw, child number 0
-------------------------------------
select * from v$session where sid=2

Plan hash value: 1627146547

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |        |       |     1 (100)|       |       |          |
|   1 |  MERGE JOIN CARTESIAN     |                 |      1 |  1378 |     0   (0)|       |       |          |
|   2 |   NESTED LOOPS            |                 |      1 |   370 |     0   (0)|       |       |          |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |      1 |   161 |     0   (0)|       |       |          |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |      1 |   209 |     0   (0)|       |       |          |
|   5 |   BUFFER SORT             |                 |      1 |  1008 |     0   (0)| 73728 | 73728 |          |
|*  6 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |  1008 |     0   (0)|       |       |          |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5C160134
   3 - SEL$5C160134 / W@SEL$3
   4 - SEL$5C160134 / E@SEL$3
   6 - SEL$5C160134 / S@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("W"."KSLWTSID"=2)
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter(("S"."INDX"=2 AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0))


SYS@book> select * from V$INDEXED_FIXED_COLUMN where table_name='X$KSLWT';
TABLE_NAME INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION
---------- ------------ -------------------- ---------------
X$KSLWT               1 KSLWTSID                           0

--//仅仅X$KSLWT.KSLWTSID上有索引。要充分利用这些信息，优化一些内部视图的查询性能。