​[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上有索引。要充分利用這些資訊，最佳化一些內部檢視的查詢效能。