Problem: We are experiencing MASSIVE data bloat, associated with a simple application. The product which generally takes ~225MB of base storage for the table contents has grown to 178 GB in a few months.
Background: We are seeing the creation of a whole set of other tables that are sucking disk space prodigiously. They do not appear immediately (or even overnight) when we install our 8 core tables. Somewhere along the line, they appeared. They are taking up about 45 GB and are not understood. For example, why the LOB files? We have no image or other LOB data loaded.
We are running Oracle 11g. The application has 8 core tables shown below that consume ~225 MB. The SRCH_CACHE table is a "look up" table that has four full-text indexes, as displayed by the next set of tables below. On the left, you see the table sizes after the installation and on the right after some weeks of operation. These first two sets of tables look "reasonable" to me. That is, the 8 core tables and the fully populated text index tables are not entirely unrealistic. The ~40 GB index tables seem a bit excessive, but assume they are reasonable given the 900,000 records that are being full-text indexed.
As part of our troubleshooting, we dropped the application entirely. When we dropped the 8 core tables (as a test) these files all disappeared. So, they appear directly related to the SRCH application. They appear to be "system" tables, but they are growing daily (~1GB per day) even though the underlying data in the SRCH application remains steady at ~225 MB. Between the index files and these "other" files, we have 90 GB associated with a 225 MB application.
Any thoughts would be greatly appreciated.
Rob
UPDATE: Identified some of the tables with a new product. That still leaves the SYS_IOT and SYS_LOB files that I am trying to understand.
| Core Tables |
| SRCH_CACHE | 224.0 | MB | >> | SRCH_CACHE | 224.00 | MB |
| SRCH_CACHE_ACTION | 0.1 | MB | SRCH_CACHE_ACTION | 0.06 | MB |
| SRCH_CACHE_STATUS | 0.1 | MB | SRCH_CACHE_ACTION_PK | 0.06 | MB |
| SRCH_LOG | 0.1 | MB | SRCH_CACHE_LD | 19.00 | MB |
| SRCH_SEARCH | 0.1 | MB | SRCH_CACHE_PK | 18.00 | MB |
| SRCH_SEARCH_FIELD | 0.1 | MB | SRCH_CACHE_SD | 120.00 | MB |
| SRCH_SEARCH_ITEM | 0.1 | MB | SRCH_CACHE_STATUS | 0.06 | MB |
| SRCH_SEARCH_TYPE | 0.1 | MB | SRCH_CACHE_STATUS_PK | 0.06 | MB |
| | SRCH_CACHE_TL | 22.00 | MB |
| SRCH_LOG | 0.38 | MB |
| SRCH_SEARCH | 0.06 | MB |
| SRCH_SEARCH_FIELD | 0.06 | MB |
| SRCH_SEARCH_ITEM | 0.06 | MB |
| SRCH_SEARCH_TYPE | 0.06 | MB |
| SRCH_VALID_OPERATORS | 0.06 | MB |
| SRCH_VALID_OPERATORS_PK | 0.06 | MB |
| |
| Index Tables on the SRCH_CACHE Table |
| DR$SRCH_CACHE_TXT1$I | 0.1 | MB | >> | DR$SRCH_CACHE_TXT1$I | 9612.00 | MB |
| DR$SRCH_CACHE_TXT1$R | 0.1 | MB | DR$SRCH_CACHE_TXT1$R | 0.06 | MB |
| DR$SRCH_CACHE_TXT2$I | 0.1 | MB | DR$SRCH_CACHE_TXT1$X | 2560.00 | MB |
| DR$SRCH_CACHE_TXT2$R | 0.1 | MB | DR$SRCH_CACHE_TXT2$I | 1601.00 | MB |
| DR$SRCH_CACHE_TXT3$I | 0.1 | MB | DR$SRCH_CACHE_TXT2$R | 0.06 | MB |
| DR$SRCH_CACHE_TXT3$R | 0.1 | MB | DR$SRCH_CACHE_TXT2$X | 400.00 | MB |
| DR$SRCH_CACHE_TXT4$I | 0.1 | MB | DR$SRCH_CACHE_TXT3$I | 11191.00 | MB |
| DR$SRCH_CACHE_TXT4$R | 0.1 | MB | DR$SRCH_CACHE_TXT3$R | 0.06 | MB |
| | DR$SRCH_CACHE_TXT3$X | 3064.00 | MB |
| DR$SRCH_CACHE_TXT4$I | 8185.00 | MB |
| DR$SRCH_CACHE_TXT4$R | 0.06 | MB |
| DR$SRCH_CACHE_TXT4$X | 3968.00 | MB |
| |
Other tables related to the "SRCH" tables. These "disappear" when the SRCH
tables are dropped. |
These tables "appeared" and they
"dropped" when I dropped the main SRCH_CACHE table. Thus, they appear to be related. | >> | SYS_IOT_TOP_12174443 |
31.0 | MB |
| SYS_IOT_TOP_12174449 |
1,884.0 | MB |
| SYS_IOT_TOP_12174456 |
31.0 | MB |
| SYS_IOT_TOP_12174462 |
1,920.0 | MB |
| SYS_IOT_TOP_12174469 |
31.0 | MB |
| SYS_IOT_TOP_12174475 |
1,920.0 | MB |
| SYS_IOT_TOP_12174482 |
30.0 | MB |
| SYS_IOT_TOP_12174488 |
1,861.0 | MB |
| SYS_LOB0012174440C00006$$ |
0.1 | MB |
| SYS_LOB0012174445C00002$$ |
1,671.2 | MB |
| SYS_LOB0012174453C00006$$ |
0.1 | MB |
| SYS_LOB0012174458C00002$$ |
1,536.2 | MB |
| SYS_LOB0012174466C00006$$ |
0.1 | MB |
| SYS_LOB0012174471C00002$$ |
8,844.2 | MB |
| SYS_LOB0012174479C00006$$ |
0.1 | MB |
| SYS_LOB0012174484C00002$$ |
26,007.2 | MB |
| SYS_LOB0012769351C00029$$ |
0.1 | MB |
| SYS_LOB0012769360C00026$$ |
0.1 | MB |
| SYS_LOB0012769360C00027$$ |
0.1 | MB |
| SYS_LOB0012769360C00028$$ |
1.2 | MB |
| SYS_LOB0012769360C00069$$ |
0.1 | MB |
| SYS_LOB0012769381C00043$$ |
0.1 | MB |
| SYS_LOB0012769499C00006$$ |
0.1 | MB |
| SYS_LOB0012769508C00022$$ | 0.1 | MB |