Oracle Dictionary has extremely large number of blocks for a small number of rows.
From: Ashish Lunawat <ashish.lunawat_at_gmail.com>
Date: Fri, 18 Mar 2022 16:10:20 +0800
Message-ID: <CAEzAyeCXAubGUu45A0YX8hemMHjTm=85_x16oo4GAK47-7sv9Q_at_mail.gmail.com>
Hello everyone,
Date: Fri, 18 Mar 2022 16:10:20 +0800
Message-ID: <CAEzAyeCXAubGUu45A0YX8hemMHjTm=85_x16oo4GAK47-7sv9Q_at_mail.gmail.com>
Hello everyone,
We have two Oracle non RAC Databases running on version 12.2.0.1.0 and I observed that the queries are running very slow esp the ones querying data dictionary. I can see that certain tables in the data dictionary has an extremely large number of blocks for a comparatively very small number of rows. I am wondering what possibly could have caused this and how to fix this as we cannot run an alter table on a data dictionary.
Database 1
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED
-------------------- ------------------------------ ---- ------------
------------- --------- ------ ------ -------------------
SYS CDEF$ CLU 48730 1654432 0 0 51 2021-12-26 06:16:56 SYS CCOL$ CLU 59193 1654432 0 0 21 2021-12-26 06:13:56 SYS UET$ CLU 0 925 0 0 0 2019-01-25 02:19:36 SYS ICOL$ CLU 25547 1576224 0 0 32 2021-12-26 06:31:06 SYS NTAB$ CLU 451 3549 0 0 17 2019-01-25 02:19:00 SYS TYPE_MISC$ CLU 2751 3549 0 0 47 2019-01-25 02:19:36 SYS REFCON$ CLU 52 3549 0 0 14 2019-01-25 02:19:13 SYS ATTRCOL$ CLU 1806 1576224 0 0 46 2021-12-26 06:10:16 SYS LIBRARY$ CLU 190 3549 0 0 49 2019-01-25 02:18:56 SYS ICOLDEP$ CLU 658 3549 0 0 13 2019-01-25 02:18:50 SYS COL$ CLU 675267 1576224 0 0 71 2021-12-26 06:25:26 SYS SUBCOLTYPE$ CLU 202 3549 0 0 46 2019-01-25 02:19:27 SYS COLTYPE$ CLU 3165 1576224 0 0 48 2021-12-26 06:27:14 SYS IND$ CLU 13286 1576224 0 0 86 2021-12-26 06:42:58 SYS ASSEMBLY$ CLU 0 3549 0 0 0 2019-01-25 02:18:24 SYS VIEWTRCOL$ CLU 1 3549 0 0 22 2019-01-25 02:19:37 SYS TAB$ CLU 22823 1576224 0 0 133 2021-12-26 06:57:38 SYS CLU$ CLU 10 1576224 0 0 44 2021-12-26 06:20:14 SYS OPQTYPE$ CLU 348 1576224 0 0 23 2021-12-26 06:34:54 SYS LOB$ CLU 1207 1576224 0 0 51 2021-12-26 06:33:11 SYS TYPE$ CLU 3758 3894 0 0 89 2019-01-25 02:19:35 SYS PARAMETER$ CLU 6475 3894 0 0 62 2019-01-25 02:19:10 SYS METHOD$ CLU 2504 3894 0 0 48 2019-01-25 02:18:59 SYS COLLECTION$ CLU 1487 3894 0 0 84 2019-01-25 02:18:33 SYS ATTRIBUTE$ CLU 16880 3894 0 0 68 2019-01-25 02:18:25 SYS RESULT$ CLU 1583 3894 0 0 50 2019-01-25 02:19:14 SYS WRI$_OPTSTAT_OPR TAB 149616 29017 0 0 791 2021-12-26 06:58:52 SYS TRIGGER$ TAB 8292 4100 0 0 224 2021-12-26 06:35:36 SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P TAB 0 4537 0 0 0 2021-12-26 06:08:40 SYS VIEW$ TAB 8011 1471 0 0 62 2021-12-26 06:35:41 SYS WRH$_SQL_PLAN TAB 77973 8297 0 0 163 2021-12-26 06:36:08 SYS IDL_UB1$ TAB 91330 28991 0 0 20 2021-12-26 06:31:09 SYS IDL_UB2$ TAB 22047 2495 0 0 21 2021-12-26 06:31:09
Database 2
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZEDSYS LOB$ CLU 1507 1666936 0 0 51 2022-03-12 06:13:25 SYS VIEWTRCOL$ CLU 0 3144 0 0 0 2016-01-12 15:36:01 SYS IND$ CLU 44815 1668938 0 0 95 2022-03-18 00:30:17 SYS TAB$ CLU 73130 1668938 0 0 126 2022-03-17 22:19:25 SYS ATTRCOL$ CLU 2782 1634580 0 0 38 2021-12-02 22:34:48 SYS ASSEMBLY$ CLU 0 4322 0 0 0 2016-04-13 20:04:28 SYS ICOLDEP$ CLU 114 1386408 0 0 13 2020-01-09 22:20:31 SYS CLU$ CLU 10 1668938 0 0 47 2022-03-17 23:57:21 SYS TYPE_MISC$ CLU 1733 1386408 0 0 47 2020-01-09 22:34:48 SYS COL$ CLU 6975638 1668938 0 0 72 2022-03-18 00:35:21 SYS REFCON$ CLU 55 3144 0 0 14 2016-01-12 15:35:30 SYS OPQTYPE$ CLU 938 1667578 0 0 23 2022-03-13 10:29:39 SYS SUBCOLTYPE$ CLU 164 3144 0 0 46 2016-01-12 15:35:48 SYS HISTGRM$ CLU 389613 166065 0 0 34 2022-03-17 23:59:57 SYS ATTRIBUTE$ CLU 7827 2116 0 0 68 2020-01-09 22:27:46 SYS RESULT$ CLU 1077 2116 0 0 50 2016-01-12 15:35:31 SYS METHOD$ CLU 1672 2116 0 0 49 2016-01-12 15:35:20 SYS TYPE$ CLU 1672 2116 0 0 79 2020-01-09 22:27:46 SYS PARAMETER$ CLU 4929 2116 0 0 61 2016-01-12 15:35:28 SYS COLLECTION$ CLU 481 2116 0 0 69 2020-01-09 22:27:46 SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TAB 2750813 122516 0 0 60 2022-03-17 23:58:49 SYS WRH$_SQL_PLAN TAB 55698 4537 0 0 148 2022-03-17 23:11:31 SYS TRIGGER$ TAB 3941 5528 0 0 221 2022-03-12 00:32:25 SYS IDL_UB1$ TAB 57061 29680 0 0 20 2021-04-06 22:26:54 SYS AUD$ TAB 57462056 618272 0 0 140 2021-12-07 22:25:26
-------------------- ------------------------------ ---- ------------
------------- --------- ------ ------ -------------------
SYS HIST_HEAD$ TAB 8037468 62857 0 0 46 2022-03-12 00:33:05 SYS LIBRARY$ CLU 165 3144 0 0 48 2016-01-12 15:35:18 SYS NTAB$ CLU 272 3144 0 0 17 2016-01-12 15:35:21 SYS COLTYPE$ CLU 3715 1664427 0 0 47 2022-03-04 22:45:04 SYS ICOL$ CLU 62461 1662755 0 0 34 2022-02-26 13:12:37
Thanks
-Ashish
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 18 2022 - 09:10:20 CET