[20240913]关于x$表分析问题.txt

--//昨天使用一个查询涉及X表执行有点慢,我分析X表.执行dbms_stats.GATHER_FIXED_OBJECTS_STATS()时我发现并没有分析涉及到X$,看
--//看一些细节.

1.环境:
SYS@book> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> select * from dba_tab_statistics where LAST_ANALYZED>=trunc(sysdate);
no rows selected
--//当天没有分析过表.

2.测试:
SYS@book> execute sys.dbms_stats.GATHER_FIXED_OBJECTS_STATS();
PL/SQL procedure successfully completed.

SYS@book> select owner,table_name,object_type,num_rows,blocks from dba_tab_statistics where LAST_ANALYZED>=trunc(sysdate) and table_name like 'X$KGL%' ;
OWNER TABLE_NAME  OBJECT_TYPE  NUM_ROWS     BLOCKS
----- ----------- ------------ -------- ----------
SYS   X$KGLOB     FIXED TABLE     11893
SYS   X$KGLLK     FIXED TABLE      1362
SYS   X$KGLPN     FIXED TABLE        31
SYS   X$KGLST     FIXED TABLE       316
SYS   X$KGLTR     FIXED TABLE      1591
SYS   X$KGLXS     FIXED TABLE      4564
SYS   X$KGLDP     FIXED TABLE      5969
SYS   X$KGLNA     FIXED TABLE     11846
SYS   X$KGLAU     FIXED TABLE       783
SYS   X$KGLNA1    FIXED TABLE     11866
SYS   X$KGLRD     FIXED TABLE      6034
SYS   X$KGLSN     FIXED TABLE        24
SYS   X$KGLSIM    FIXED TABLE        31
SYS   X$KGLMEM    FIXED TABLE       150
SYS   X$KGLJSIM   FIXED TABLE         0
SYS   X$KGLJMEM   FIXED TABLE         0
16 rows selected.
--//X$表 blocks都没有计数.
--//可以发现并没有X$KGLcursor;X$KGLcursor_child的统计分析信息.

SYS@book> select count(*) from  sys.x$kglcursor_child;
  COUNT(*)
----------
      1150

SYS@book> select count(*) from  sys.x$kglcursor;
  COUNT(*)
----------
      5184

--//于是手工分析看看.
BEGIN dbms_stats.gather_table_stats('SYS', 'X$KGLCURSOR', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$KGLCURSOR", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 40823
ORA-06512: at "SYS.DBMS_STATS", line 40097
ORA-06512: at "SYS.DBMS_STATS", line 40256
ORA-06512: at "SYS.DBMS_STATS", line 40804
ORA-06512: at line 1
--//'X$KGLCURSOR_CHILD类似.

SYS@book> select count(*) from v$fixed_table ;
  COUNT(*)
----------
      3031

SYS@book> select count(*) from dba_tab_statistics where LAST_ANALYZED>=trunc(sysdate)  ;
  COUNT(*)
----------
      1221

SYS@book> select count(*) from dba_tab_statistics where LAST_ANALYZED>=trunc(sysdate) and table_name like 'X$%' ;
  COUNT(*)
----------
      1221

--//可以发现并不是全部表都有分析,而是仅仅1部分,为什么?

SYS@book> SELECT count(*)
  FROM v$fixed_table
 WHERE EXISTS (SELECT 1 FROM dba_tab_statistics WHERE LAST_ANALYZED >= trunc(sysdate)
   AND table_name LIKE 'X$%'
   AND owner       =  'SYS'
   AND table_name  =  name);
  COUNT(*)
----------
      1221

SYS@book> @ v2 gv$fixed_table
Show SQL text of views matching "gv$fixed_table"...

no rows selected


VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$FIXED_TABLE                 select inst_id,kqftanam, kqftaobj, 'TABLE', indx, con_id from x$kqfta union all select
                               inst_id,kqfvinam, kqfviobj, 'VIEW', 65537, con_id from x$kqfvi union all select inst_id,kqfdtnam,
                               kqfdtobj, 'TABLE', 65537, con_id from x$kqfdt

--//V$FIXED_TABLE包括视图。即使是表也包括两部分x$kqfta, x$kqfdt.

SELECT count(*)
  FROM v$fixed_table
 WHERE not EXISTS (SELECT 1 FROM dba_tab_statistics WHERE LAST_ANALYZED >= trunc(sysdate)
   AND table_name LIKE 'X$%'
   AND owner       =  'SYS'
   AND table_name  =  name)
   and type='TABLE'

  COUNT(*)
----------
       203

SELECT *
  FROM v$fixed_table
 WHERE not EXISTS (SELECT 1 FROM dba_tab_statistics WHERE LAST_ANALYZED >= trunc(sysdate)
   AND table_name LIKE 'X$%'
   AND owner       =  'SYS'
   AND table_name  =  name)
   and type='TABLE';

--//输出太长略.

WITH a
        AS (SELECT *
              FROM dba_tab_statistics
             WHERE     LAST_ANALYZED >= TRUNC (SYSDATE)
                   AND table_name LIKE 'X$%'
                   AND owner = 'SYS')
SELECT *
  FROM x$kqfdt
 WHERE     NOT EXISTS
              (SELECT 1
                 FROM a
                WHERE table_name = KQFDTNAM)
       AND KQFDTNAM LIKE 'X$KGL%';


ADDR                   INDX    INST_ID     CON_ID   KQFDTOBJ KQFDTNAM                       KQFDTEQU
---------------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------
0000000016DE99F0         10          1          0 4294951056 X$KGLTABLE                     X$KGLOB
0000000016DE9AB8         15          1          0 4294951061 X$KGLCURSOR                    X$KGLOB
0000000016DE9A90         14          1          0 4294951060 X$KGLCLUSTER                   X$KGLOB
0000000016DE9AE0         16          1          0 4294952684 X$KGLCURSOR_CHILD_SQLID        X$KGLOB
0000000016DE9B08         17          1          0 4294952680 X$KGLCURSOR_CHILD_SQLIDPH      X$KGLOB
0000000016DE9A68         13          1          0 4294951059 X$KGLINDEX                     X$KGLOB
0000000016DE9B58         19          1          0 4294953372 X$KGLCURSOR_PARENT             X$KGLOB
0000000016DE9A40         12          1          0 4294951058 X$KGLTRIGGER                   X$KGLOB
0000000016DE9B80         20          1          0 4294953759 X$KGLSQLTXL                    X$KGLOB
0000000016DE9A18         11          1          0 4294951057 X$KGLBODY                      X$KGLOB
0000000016DE9B30         18          1          0 4294952683 X$KGLCURSOR_CHILD              X$KGLOB
11 rows selected.

--//这些X$视乎都是从X$KGLOB派生出来的,所以仅仅能分析X$KGLOB,这类表在x$kqfdt有47个.
--//203-47 = 156

--//这样就好理解为什么查询不到X$KGLCURSOR的索引.
SYS@book> @ xind X$KGLCURSOR
no rows selected

SYS@book> @ xind X$KGLOB
TABLE_NAME                     INDEX_NUMBER COLUMN_NAME                    COLUMN_POSITION     CON_ID
------------------------------ ------------ ------------------------------ --------------- ----------
X$KGLOB                                   1 KGLNAHSH                                     0          0
X$KGLOB                                   2 KGLOBT03                                     0          0

WITH a
        AS (SELECT *
              FROM dba_tab_statistics
             WHERE     LAST_ANALYZED >= TRUNC (SYSDATE)
                   AND table_name LIKE 'X$%'
                   AND owner = 'SYS')
SELECT *
  FROM x$kqfta
 WHERE     NOT EXISTS
              (SELECT 1
                 FROM a
                WHERE table_name = KQFTANAM)

ADDR                   INDX    INST_ID     CON_ID   KQFTAOBJ   KQFTAVER KQFTANAM                         KQFTATYP   KQFTAFLG   KQFTARSZ   KQFTACOC
---------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
00000000733A88C0       1298          1          0 4294953929          2 X$DIAG_DDE_USR_ACT_PARAM_DEF           10         34       1384         12
00000000733A8820       1296          1          0 4294953927          2 X$DIAG_HM_MESSAGE                      10         34       3516         16
0000000016C76250       1187          1          0 4294952946          3 X$DBGRIFX                               4          0        840          7
00000000733A8F50       1319          1          0 4294953965          5 X$DIAG_LOG_EXT                         10         34      10669         42
00000000733A8B40       1306          1          0 4294953937          3 X$DIAG_IPS_FILE_METADATA               10         34       1376         11
00000000733A9180       1326          1          0 4294953962          2 X$DIAG_DFW_CONFIG_CAPTURE              10         34        936          9
0000000016C6DBF0        757          1          0 4294951596          3 X$JOXFT                                 5          2        200         29
0000000016C74310       1087          1          0 4294952424         10 X$KEWRTSEGSTAT                          4          0       1344         68
00000000733AA0D0       1375          1          0 4294954950          3 X$DIAG_VTEST_EXISTS                    10         34       1536         13
0000000016C761B0       1185          1          0 4294952773          3 X$DBGRIPX                               4          0        784         17
00000000733A8CD0       1311          1          0 4294953942          3 X$DIAG_IPS_CONFIGURATION               10         34       1200         15
0000000016C6AFE0        616          1          0 4294951517          3 X$KTFBUE                                5          2         56         14
00000000733A83C0       1282          1          0 4294953956          5 X$DIAG_ADR_INVALIDATION                10         34        912          8
00000000733A9F90       1371          1          0 4294954949          2 X$DIAG_V_SWPERRCOUNT                   10         34        912          8
00000000733A8870       1297          1          0 4294953928          2 X$DIAG_DDE_USER_ACTION_DEF             10         34       1816         15
00000000733A9C70       1361          1          0 4294954930          3 X$DIAG_VPROBLEM                        10         34       2648         31
0000000016C76DE0       1224          1          0 4294955958          4 X$DBGTFLIST                             4          0        564          8
00000000733A9CC0       1362          1          0 4294954939          3 X$DIAG_VPROBLEM_BUCKET1                10         34        960         11
0000000016C74090       1079          1          0 4294951981          8 X$KEWRSQLIDTAB                          4          0       3112          9
00000000733A8640       1290          1          0 4294953960          3 X$DIAG_VIEWCOL                         10         34        980          9
0000000016C76FC0       1230          1          0 4294955973          5 X$DBGTFSSQLT                            4         34       5080         24
00000000733A8EB0       1317          1          0 4294953954          2 X$DIAG_INC_METER_PK_IMPTS              10         34       1496         12
00000000733A9F40       1370          1          0 4294954948          2 X$DIAG_V_ACTPROB                       10         34        912          8
00000000733A8910       1299          1          0 4294953930          2 X$DIAG_DDE_USER_ACTION                 10         34       1008         13
00000000733A94A0       1336          1          0 4294954913          3 X$DIAG_VSHOWINCB_I                     10         34       1488         10
0000000016C741D0       1083          1          0 4294952426          6 X$KEWRATTRNEW                           4          0        112         10
00000000733A8E60       1316          1          0 4294953953          2 X$DIAG_INC_METER_IMPT_DEF              10         34       1584         12
00000000733A9EA0       1368          1          0 4294954945          2 X$DIAG_VIPS_PKG_MAIN_PROBLEM           10         34       1466          9
00000000733A97C0       1346          1          0 4294954924          2 X$DIAG_VIPS_FILE_COPY_LOG              10         34       2672         19
00000000733A8460       1284          1          0 4294953913          4 X$DIAG_PROBLEM                         10         34       1704         19
00000000733A96D0       1343          1          0 4294954922          2 X$DIAG_VIPS_PKG_FILE                   10         34       2040         18
00000000733A9590       1339          1          0 4294954916          2 X$DIAG_VSHOWCATVIEW                    10         34        992         10
00000000733A94F0       1337          1          0 4294954915          3 X$DIAG_V_INCFCOUNT                     10         34        920          9
0000000016C743B0       1089          1          0 4294952433          2 X$KEWPDBINSNAP                          5          2         32          7
00000000733A8190       1275          1          0 4294952216          0 X$KSXP_STATS                            9         18          0          0
0000000016C76480       1194          1          0 4294952811          3 X$DBKRUN                                4          6       1320         21
0000000016C64190        263          1          0 4294956167          2 X$KSUGBLNETSTATAWR                      4          0        696         29
0000000016C76570       1197          1          0 4294952831          3 X$DBKH_CHECK_PARAM                      4          6         40         11
0000000016C763E0       1192          1          0 4294952813          3 X$DBKRECO                               4          6       1080         21
00000000733A8E10       1315          1          0 4294953945          3 X$DIAG_INC_METER_CONFIG                10         34       1000         14
0000000016C76340       1190          1          0 4294952494          4 X$DBGLOGEXT                             4          0      15188         38
00000000733A99F0       1353          1          0 4294954934          2 X$DIAG_V_IPSPRBCNT                     10         34        912          8
0000000016C77830       1257          1          0 4294953508          2 X$CDBVW$                                4          0          2          4
00000000733A8FF0       1321          1          0 4294953955          2 X$DIAG_RELMD_EXT                       10         34       1024         14
00000000733A9D60       1364          1          0 4294954940          3 X$DIAG_VPROBLEM_BUCKET_COUNT           10         34       1520         12
00000000733A9090       1323          1          0 4294953950          3 X$DIAG_EM_DIAG_JOB                     10         34       2828         15
0000000016C74400       1090          1          0 4294952434          2 X$KEWRSQLSUM                            5          2         32         10
00000000733AA120       1376          1          0 4294954964          3 X$DIAG_VADR_CONTROL                    10         34       1072         21
00000000733A81E0       1276          1          0 4294952217          0 X$SKGXP_PORT                            9         18          0          0
00000000733A9B80       1358          1          0 4294954935          3 X$DIAG_VPROBLEM1                       10         34       2632         29
0000000016C76ED0       1227          1          0 4294955967          5 X$DBGTFSQLT                             4         34       5080         24
00000000733A9FE0       1372          1          0 4294954953          3 X$DIAG_VIPS_PKG_INC_CAND               10         34       2336         37
00000000733A8A00       1302          1          0 4294953933          2 X$DIAG_DDE_USR_INC_ACT_MAP             10         34        967          8
00000000733A8780       1294          1          0 4294953925          3 X$DIAG_HM_FDG_SET                      10         34        940         10
00000000733A8AA0       1304          1          0 4294953935          2 X$DIAG_IPS_PACKAGE_INCIDENT            10         34        936         11
00000000733A8280       1278          1          0 4294952219          0 X$SKGXP_MISC                            9         18          0          0
00000000733A8DC0       1314          1          0 4294953944          4 X$DIAG_INC_METER_INFO                  10         34        992         16
00000000733A84B0       1285          1          0 4294953914          5 X$DIAG_INCCKEY                         10         34       1520         11
00000000733A90E0       1324          1          0 4294953951          2 X$DIAG_EM_TARGET_INFO                  10         34       2748         14
00000000733A8A50       1303          1          0 4294953934          5 X$DIAG_IPS_PACKAGE                     10         34       1472         21
0000000016C73F50       1075          1          0 4294951957          3 X$KEWXOCF                               4          0        128         11
00000000733A9C20       1360          1          0 4294954937          3 X$DIAG_V_INC_METER_INFO_PROB           10         34        920          9
00000000733A9630       1341          1          0 4294954921          3 X$DIAG_VINC_METER_INFO                 10         34       1520         20
00000000733A9A90       1355          1          0 4294954932          3 X$DIAG_VPROBLEM_INT                    10         34       2424         28
0000000016C6CC50        707          1          0 4294952685          2 X$KZEKMFVW                              4          2        652         10
00000000733A9A40       1354          1          0 4294954931          3 X$DIAG_VPROBLEM_LASTINC                10         34       2416         27
00000000733A9AE0       1356          1          0 4294954941          2 X$DIAG_VEM_USER_ACTLOG                 10         34       1108          8
0000000016C77060       1232          1          0 4294956260          5 X$DBGTFSUSRT                            4         34       5080         24
00000000733A80F0       1273          1          0 4294953645          0 X$KSIPC_INFO                            9         18          0          0
00000000733A9E00       1366          1          0 4294954944          4 X$DIAG_DIAGV_INCIDENT                  10         34       2344         37
00000000733A8B90       1307          1          0 4294953938          3 X$DIAG_IPS_FILE_COPY_LOG               10         34       1984         12
00000000733A8FA0       1320          1          0 4294953947         11 X$DIAG_ALERT_EXT                       10         34      12723         58
00000000733A99A0       1352          1          0 4294954933          2 X$DIAG_V_IPSPRBCNT1                    10         34        912          8
0000000016C764D0       1195          1          0 4294952815          3 X$DBKINFO                               4          6        664          9
0000000016C60B30         89          1          0 4294951100          6 X$KSMSP                                 4          0         88         12
00000000733A8C30       1309          1          0 4294953940          3 X$DIAG_IPS_PKG_UNPACK_HIST             10         34        960         12
00000000733A91D0       1327          1          0 4294953963          2 X$DIAG_DFW_CONFIG_ITEM                 10         34       1564         11
0000000016C740E0       1080          1          0 4294952429          4 X$KEWRTSQLPLAN                          4          0       3112         10
00000000733A9400       1334          1          0 4294953972          4 X$DIAG_PDB_SPACE_MGMT                  10         34       1048         15
00000000733A9450       1335          1          0 4294954912          3 X$DIAG_VSHOWINCB                       10         34       1488         10
0000000016C77880       1258          1          0 4294953649          2 X$COMVW$                                4          0          2          4
0000000016C6A450        579          1          0 4294952094          4 X$KTUQQRY                               5          2      38352         16
00000000733A87D0       1295          1          0 4294953926          3 X$DIAG_HM_INFO                         10         34       1568         11
00000000733A9950       1351          1          0 4294954929          2 X$DIAG_V_INCCOUNT                      10         34        912          8
00000000733AA080       1374          1          0 4294954957          3 X$DIAG_VPDB_PROBLEM                    10         34       1704         19
0000000016C762F0       1189          1          0 4294952492          9 X$DBGALERTEXT                           4          0      15188         53
00000000733AA030       1373          1          0 4294954951          3 X$DIAG_VNOT_EXIST_INCIDENT             10         34       2328         36
00000000733A9310       1331          1          0 4294953969          3 X$DIAG_DFW_PURGE_ITEM                  10         34       1248         15
0000000016C70A80        906          1          0 4294951434          3 X$KWQSI                                 4          0         80         11
0000000016C68510        479          1          0 4294951535         20 X$LOGMNR_CONTENTS                       4          2      37800        128
00000000733A9680       1342          1          0 4294954927          2 X$DIAG_VIPS_FILE_METADATA              10         34       1376         11
00000000733A93B0       1333          1          0 4294953971          2 X$DIAG_PDB_PROBLEM                     10         34        976         12
0000000016C76520       1196          1          0 4294952830          3 X$DBKH_CHECK                            4          6         48         11
0000000016C76200       1186          1          0 4294952774          3 X$DBGRIKX                               4          0        616          9
00000000733A9860       1348          1          0 4294954956          2 X$DIAG_VIPS_PKG_INC_DTL1               10         34       1808         27
00000000733A8960       1300          1          0 4294953931          2 X$DIAG_DDE_USR_ACT_PARAM               10         34       1252         12
00000000733A9770       1345          1          0 4294954923          2 X$DIAG_VIPS_PACKAGE_HISTORY            10         34       2024         16
00000000733A89B0       1301          1          0 4294953932          2 X$DIAG_DDE_USR_INC_TYPE                10         34       1065          8
00000000733A8230       1277          1          0 4294952218          0 X$SKGXP_CONNECTION                      9         18          0          0
0000000016C77010       1231          1          0 4294955975          5 X$DBGTFSOPTT                            4         34       5080         24
0000000016C74130       1081          1          0 4294952430          4 X$KEWRTOPTENV                           4          0       3112          8
0000000016C76E80       1226          1          0 4294955964          5 X$DBGTFVIEW                             4         34       5080         24
00000000733A9130       1325          1          0 4294953952          2 X$DIAG_AMS_XACTION                     10         34        904          7
00000000733A8C80       1310          1          0 4294953941          3 X$DIAG_IPS_REMOTE_PACKAGE              10         34       1511         11
00000000733A9900       1350          1          0 4294954926          3 X$DIAG_VINCIDENT_FILE                  10         34       1488         13
00000000733A9360       1332          1          0 4294953970          4 X$DIAG_ADR_CONTROL_AUX                 10         34        928         10
00000000733A9810       1347          1          0 4294954925          2 X$DIAG_VIPS_PACKAGE_SIZE               10         34        912          8
00000000733A9720       1344          1          0 4294954955          2 X$DIAG_VIPS_PACKAGE_FILE               10         34       1576         15
00000000733A9DB0       1365          1          0 4294954943          3 X$DIAG_VHM_RUN                         10         34       1768         23
0000000016C778D0       1259          1          0 4294953706          2 X$OBLNK$                                4          0          2          4
00000000733A8370       1281          1          0 4294953948          5 X$DIAG_ADR_CONTROL                     10         34       1036         17
0000000016C74B80       1114          1          0 4294952425          4 X$KEWMSVCMV                             5          0        416         21
00000000733A9220       1328          1          0 4294953966          2 X$DIAG_DFW_PATCH_CAPTURE               10         34        936          9
00000000733A8F00       1318          1          0 4294953946          7 X$DIAG_DIR_EXT                         10         34       2449         15
0000000016C64140        262          1          0 4294956166          3 X$KSUGBLNETSTAT                         4          4        696         12
0000000016C609F0         85          1          0 4294951147          6 X$KSMMEM                                1          0          8          5
00000000733A8500       1286          1          0 4294953915          6 X$DIAG_INCIDENT_FILE                   10         34       1376         11
00000000733A8BE0       1308          1          0 4294953939          4 X$DIAG_IPS_PACKAGE_HISTORY             10         34       2016         15
00000000733A8AF0       1305          1          0 4294953936          3 X$DIAG_IPS_PACKAGE_FILE                10         34        928         10
00000000733A86E0       1292          1          0 4294953923          7 X$DIAG_HM_FINDING                      10         34       1445         32
00000000733A8550       1287          1          0 4294953957          3 X$DIAG_SWEEPERR                        10         34       1012         11
0000000016C6E410        783          1          0 4294953461          2 X$KXFBBOX                               4          0       2368         18
00000000733A95E0       1340          1          0 4294954920          4 X$DIAG_VINCIDENT                       10         34       2336         37
00000000733A8690       1291          1          0 4294953922          5 X$DIAG_HM_RUN                          10         34       1845         25
00000000733A8410       1283          1          0 4294953912          6 X$DIAG_INCIDENT                        10         34       2176         34
00000000733A9270       1329          1          0 4294953967          2 X$DIAG_DFW_PATCH_ITEM                  10         34        944         10
00000000733A9040       1322          1          0 4294953949          3 X$DIAG_EM_USER_ACTIVITY                10         34       1368         14
0000000016C76390       1191          1          0 4294952812          4 X$DBKFDG                                4          6        504         29
00000000733A92C0       1330          1          0 4294953968          4 X$DIAG_DFW_PURGE                       10         34       1288         19
0000000016C6DC40        758          1          0 4294951780          3 X$JOXFM                                 5          2        360         60
0000000016C76430       1193          1          0 4294952814          2 X$DBKFSET                               4          6         48          8
00000000733A80A0       1272          1          0 4294953644          0 X$KSIPC_PROC_STATS                      9         18          0          0
00000000733A9D10       1363          1          0 4294954938          3 X$DIAG_VPROBLEM_BUCKET                 10         34        960         11
00000000733A8D70       1313          1          0 4294953943          3 X$DIAG_INC_METER_SUMMARY               10         34       1032         17
0000000016C6A950        595          1          0 4294953867          2 X$KTMTXNCRCLONE                         4          0         48         11
00000000733A9BD0       1359          1          0 4294954936          3 X$DIAG_VPROBLEM2                       10         34       2640         30
00000000733A85F0       1289          1          0 4294953959          6 X$DIAG_VIEW                            10         34       4816         12
00000000733A8140       1274          1          0 4294952215          1 X$KSXPTESTTBL                          10          2        146         15
0000000016C74270       1085          1          0 4294952428          3 X$KEWRTSQLTEXT                          4          0       1096         10
00000000733A98B0       1349          1          0 4294954928          2 X$DIAG_VIPS_PKG_INC_DTL                10         34       1816         28
0000000016C69F00        562          1          0 4294951022          8 X$KTURD                                 4          0        640         24
00000000733A8D20       1312          1          0 4294953964          3 X$DIAG_IPS_PROGRESS_LOG                10         34        992         15
0000000016C69C80        554          1          0 4294953845          2 X$SHADOW_DATAFILE                       5          2         32         12
00000000733A9EF0       1369          1          0 4294954947          2 X$DIAG_V_ACTINC                        10         34        920          9
0000000016C6BF80        666          1          0 4294953144          3 X$KGLOBXML                              5          0       2120          8
00000000733A9B30       1357          1          0 4294954942          2 X$DIAG_VEM_USER_ACTLOG1                10         34       1108          8
0000000016C762A0       1188          1          0 4294952491          2 X$DBGDIREXT                             4          0       2792         12
0000000016C76F70       1229          1          0 4294956259          5 X$DBGTFUSRT                             4         34       5080         24
0000000016C76F20       1228          1          0 4294955970          5 X$DBGTFOPTT                             4         34       5080         24
0000000016C76160       1184          1          0 4294952772          3 X$DBGRICX                               4          0       1328         32
0000000016C76E30       1225          1          0 4294955961          4 X$DBGATFLIST                            4          4        564         10
00000000733A9E50       1367          1          0 4294954946          2 X$DIAG_VIPS_PACKAGE_MAIN_INT           10         34        912          8
00000000733A9540       1338          1          0 4294954914          3 X$DIAG_V_NFCINC                        10         34        952         11
00000000733A85A0       1288          1          0 4294953958          3 X$DIAG_PICKLEERR                       10         34       1584         13
00000000733A8730       1293          1          0 4294953924          3 X$DIAG_HM_RECOMMENDATION               10         34       1623         23
0000000016C66EE0        408          1          0 4294951629          5 X$KCBSH                                 4          0         64          9
156 rows selected.

--//我尝试分析其中几个表,可以分析的.
SYS@book> BEGIN dbms_stats.gather_table_stats('SYS', 'X$KCBSH', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
  2  /

PL/SQL procedure successfully completed.

SYS@book> select count(*) from dba_tab_statistics where LAST_ANALYZED>=trunc(sysdate)  ;
COUNT(*)
----------
      1224

--//不知道为什么execute sys.dbms_stats.GATHER_FIXED_OBJECTS_STATS();跳过这些表.
--//有一些是读取外部文件，不分析有一定道理.我全部分析看看,剩下这些不能分析。

WITH a
        AS (SELECT *
              FROM dba_tab_statistics
             WHERE     LAST_ANALYZED >= TRUNC (SYSDATE)
                   AND table_name LIKE 'X$%'
                   AND owner = 'SYS')
SELECT *
  FROM x$kqfta
 WHERE     NOT EXISTS
              (SELECT 1
                 FROM a
                WHERE table_name = KQFTANAM);

ADDR                   INDX    INST_ID     CON_ID   KQFTAOBJ   KQFTAVER KQFTANAM                         KQFTATYP   KQFTAFLG   KQFTARSZ   KQFTACOC
---------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
0000000016C6DBF0        757          1          0 4294951596          3 X$JOXFT                                 5          2        200         29
00000000733A8190       1275          1          0 4294952216          0 X$KSXP_STATS                            9         18          0          0
0000000016C77830       1257          1          0 4294953508          2 X$CDBVW$                                4          0          2          4
00000000733A81E0       1276          1          0 4294952217          0 X$SKGXP_PORT                            9         18          0          0
00000000733A8280       1278          1          0 4294952219          0 X$SKGXP_MISC                            9         18          0          0
0000000016C6CC50        707          1          0 4294952685          2 X$KZEKMFVW                              4          2        652         10
00000000733A80F0       1273          1          0 4294953645          0 X$KSIPC_INFO                            9         18          0          0
0000000016C77880       1258          1          0 4294953649          2 X$COMVW$                                4          0          2          4
0000000016C68510        479          1          0 4294951535         20 X$LOGMNR_CONTENTS                       4          2      37800        128
00000000733A8230       1277          1          0 4294952218          0 X$SKGXP_CONNECTION                      9         18          0          0
0000000016C778D0       1259          1          0 4294953706          2 X$OBLNK$                                4          0          2          4
0000000016C609F0         85          1          0 4294951147          6 X$KSMMEM                                1          0          8          5
0000000016C6DC40        758          1          0 4294951780          3 X$JOXFM                                 5          2        360         60
00000000733A80A0       1272          1          0 4294953644          0 X$KSIPC_PROC_STATS                      9         18          0          0
14 rows selected.
--//共 14+47 = 61 x$表没有分析.这些表无法分析.

SELECT count(*)
  FROM v$fixed_table
 WHERE not EXISTS (SELECT 1 FROM dba_tab_statistics WHERE LAST_ANALYZED >= trunc(sysdate)
   AND table_name LIKE 'X$%'
   AND owner       =  'SYS'
   AND table_name  =  name)
   and type='TABLE'

  COUNT(*)
----------
       61

--//正好对上.