I would do a describe on the table, but with 520 columns, it is not valuable for this purpose to see them all.
SQL> drop table TEST_T2179_CHAINING; Table dropped. SQL> create table test_T2179_chaining as select * from aradmin.T2179 where rownum < 11; Table created. SQL> exec dbms_stats.gather_table_stats('JWOLFF','test_T2179_chaining') PL/SQL procedure successfully completed. SQL> select table_name, chain_cnt from user_tables where table_name = 'TEST_T2179_CHAINING'; TABLE_NAME CHAIN_CNT -------------------------------- --------------- TEST_T2179_CHAINING 0 1 row selected. SQL> select index_name, index_type from dba_indexes where table_name = 'TEST_T2179_CHAINING'; INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- SYS_IL0000259055C00027$$ LOB SYS_IL0000259055C00151$$ LOB SYS_IL0000259055C00152$$ LOB SYS_IL0000259055C00387$$ LOB SYS_IL0000259055C00388$$ LOB SYS_IL0000259055C00389$$ LOB SYS_IL0000259055C00391$$ LOB SYS_IL0000259055C00392$$ LOB SYS_IL0000259055C00393$$ LOB SYS_IL0000259055C00394$$ LOB SYS_IL0000259055C00395$$ LOB SYS_IL0000259055C00396$$ LOB SYS_IL0000259055C00397$$ LOB SYS_IL0000259055C00398$$ LOB SYS_IL0000259055C00399$$ LOB SYS_IL0000259055C00400$$ LOB SYS_IL0000259055C00401$$ LOB SYS_IL0000259055C00406$$ LOB SYS_IL0000259055C00408$$ LOB SYS_IL0000259055C00435$$ LOB 20 rows selected.
So, I guess now I can conclude that just because my table has 520 columns, this is not a source for CHAIN_CNT in dba_tables on the table with so many columns. Rather, it is because of real row chaining or migration. Would you agree?
SQL> analyze table test_T2179_chaining compute statistics; Table analyzed. SQL> @?/rdbms/admin/utlchain Table created. SQL> analyze table test_T2179_chaining list chained rows into chained_rows; Table analyzed. SQL> SELECT owner_name, table_name, head_rowid, analyze_timestamp FROM chained_rows ORDER BY owner_name, table_name, head_rowid, analyze_timestamp; no rows selected
SQL> select count(*) from chained_rows; 3367 1 row selected. SQL> select LAST_ANALYZED, PARTITIONED, DEGREE, CHAIN_CNT, NUM_ROWS from user_tables where table_name = 'T2179'; LAST_ANAL PAR DEGREE CHAIN_CNT NUM_ROWS --------- --- ---------- --------------- --------------- 29-NOV-12 NO 1 0 104960
So, back to my original question, do I need to rebuild the LOB indexes, and if so, how do I do that?I do not think you need, unless your lob values have been shrunk a lot and will not be grow back in future, in other words LOB segments have lots of free space that will not be utilized in future.
Anyway... so, researching the T2179 table which is in every query, I found that it is about 15G and about 3 million records. That's not too big, nor should it be a source of contention for poor performance.It can. 15G / 3M rows gives 5M per row. Quite unusual. Definitely it is bad for full table scans.
Looking deeper, I found it had about 2 million chained rows (according to DBA_TABLES). But when I did as SB suggested and analyzed the table listing to CHAINED_ROWS table, there are really only about 1.some million chained rows.I would agree it is high. But can it really be improved? What is average row size?
That's still far too many in my opinion.
BTW: I've found the need for some bit-map indexes on very low cardinality columns which I think may be a silver bullet finding.be careful here. bitmap indexes are good for queries, but are evil for DMLs.
The application code is what it is, and there isn't much I can do to change it because it is not our codeOracle has some query and plan substitution features that may help.
And, most indexes have not been rebuilt since inception, but interestingly, they have a script that runs gather_index_stats on all the indexes (but it is not a dynamic SQL script and doesn't get the newer indexes that have been added).Oracle (starting from 10g) gathers stats automatically.
Yes, the table contains several clob fields (approx. 20) which I'm assuming is used for large text fields, and attaching documents to a ticket, etc.
Is 15G only rows data or rows and LOBs data? If 5M per row is not a case, you should find out if the table has too much free space in extents. It may happen after direct loads or insert /*+append*/ operations new extents above HWM are created each time. When loads done frequently (and with parallelism), for example as daily load, it may cause many sparsely populated extents. Use DBMS_SPACE for this research.