Re: Oracle Dictionary has extremely large number of blocks for a small number of rows.
Date: Fri, 18 Mar 2022 10:03:03 +0000
Message-ID: <AM8P194MB1628F73B71D62608452E7A5685139_at_AM8P194MB1628.EURP194.PROD.OUTLOOK.COM>
Good point about cluster tables Andy.
Also, the NUM_ROWS comes from the gather of dictionary stats. You may wish to compare that to the actual number of rows (select count(*) from <table>) and consider if it is appropriate to gather dictionary stats (probably - your stats are somewhat out of date)
regards
Neil.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Andy Sayer <andysayer_at_gmail.com> Sent: 18 March 2022 08:22
To: ashish.lunawat_at_gmail.com <ashish.lunawat_at_gmail.com> Cc: Oracle-L Freelists <oracle-l_at_freelists.org> Subject: 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<mailto: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
Received on Fri Mar 18 2022 - 11:03:03 CET