Re: Oracle Dictionary has extremely large number of blocks for a small number of rows.

I haven’t read all the data as it’s not too easy on mobile. These tables are stored in clusters so multiple tables use the same blocks, you need to sum up all the table rows within the cluster if you want to make a meaningful observation about rows per block. This means that joins between these tables (which Oracle relies on for basic operations) can be super quick.

If you have slow data dictionary queries, I recommend you share them along with the execution plans with row source execution statistics.

Thanks,
Andy

On Fri, 18 Mar 2022 at 08:10, Ashish Lunawat <ashish.lunawat_at_gmail.com> wrote:

> 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_ANALYZED
> -------------------- ------------------------------ ---- ------------
> ------------- --------- ------ ------ -------------------
> 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
> SYS 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
>
> Thanks
> -Ashish
>
>
>

--
http://www.freelists.org/webpage/oracle-l