Last week I received an email from one of my readers, and long time DBA colleague, Wayne, about an interesting thing he encountered, and how he troubleshoot it. The challenge and especially the way he approached it are worth sharing, so other DBAs can learn from it.
Here is the challenge that Wayne was fighting for some time! In the evenings, usually around 11 pm, Wayne, was alerted that the USERS tablespaces was getting over 97% full, however by the time he was checking in the morning, there was no space issue at all, there was plenty of free space in the USERS tablespace.
So what could cause the temporary filling of the USERS tablespace, and leave no clue?
- application loading data, and then getting rid of it?
- tables growing and then getting truncated?
- some mysterious process in the background?
- batch jobs filling up the tablespace?
After confirming with the users, Wayne concluded there was nothing running at that time from the application perspective, no script, no batch job, no application tables growing. What could it be?
To answer the question, another important question needs to be asked: what would you do next?
Here is what Wayne did, he followed a great troubleshooting process.
Since the database where the challenge occurred was 11gR2, SE (Standard Edition), there weren’t many troubleshooting tools available, there was no AWR to turn to, to check for SQL statements that ran at 11:00 pm the previous night.
Wayne had already Statspack setup, a valuable free tool for SE, or for those who do not have the luxury of AWR. Otherwise, he would have installed and setup Statspack. Next he checked the Statspack repository and identified the statement that was running around or before the time the tablespace was at 97% full.
This is what he found:
create table "xxxxxx".CMP3$76802 tablespace "USERS" nologging l
ob (BLOB_CONTENT) store as (tablespace "USERS" enable storage in
row nocache nologging) as select /*+ DYNAMIC_SAMPLING(0) FULL("
xxxxxx"."yyyyyy_FILES") */ * from "xxxxxx"."yyyyyy_FILES"
…
Doing more research, the following was uncovered. These CMP3$* and also CMP4$* tables are created by the Compression Advisor. During its analysis the advisor creates these tables and inserts sample data for analysis. In this case, the table that was analyzed contained a LOB column, and the size of the CMP3$ table got large enough to almost fill up the USERS tablespace. When the
Compression Advisor process completed, then the table was dropped, therefore the space in the USERS tablespace was okay in the morning, with plenty of free space.
If the Compression Advisor fails for whatever reason, then these CMP3$* and CMP4$* tables are not dropped, they are left behind. Based on Oracle’s documentation, it is safe to drop those tables.
The irony of the whole situation is that in Standard Edition you can’t use compression anyways, so why is Oracle running the Compression Advisor at all?
The Compression Advisor is part of the Segment Advisor, and in order to disable it you have to actually disable the Segment Advisor aka the “auto space advisor” task (in 11g and 12.1c). And that is exactly what Wayne did!
What I loved about this challenge, is the way Wayne approached it! By looking into the Statspack reports to troubleshoot it! Thank you Wayne for sharing your experience with us!
