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

[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 --//正好对上.