​[20210528]V$INDEXED_FIXED_COLUMN视图.txt

[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上有索引。要充分利用这些信息，优化一些内部视图的查询性能。