Compressed table - drop unused column Options

[kregan77]

Does anyone know exactly what happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.

My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?

Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it.

This was on an 11.1.0.7 system.

Thanks for any info you can provide on how dropping unused columns on a compressed table actually works.

[burleson]

Hi Kregan,

>> when you drop an unused column off a compressed table, does it uncompress?

Good question!

Logic says, why bother?

Oracle is designed for high speed, it only "marks columns" in the dictionary.

************************************
>> Where does this uncompression occur?

In the data buffer. But bne aware, Oracle has several types of compression:

http://www.dba-oracle.com/oracle11g/sf_Ora...or_the_DBA.html

The docs say that all uncompress is done at page rretrieval time . . .

*************************************

>> exactly what happens if you mark a column unused in a compressed table and then alter table drop unused columns?

The column is "logically" removed from the data dictionary.

It still exists, until the table is reorged . . .

****************************************
>>

See MOSC Note:17613.1 "ORA-03113":

http://dba-oracle.com/m_ora_03113_end_of_f...ons_channel.htm


Your ORA-03113 likely will disappear as soon as the table is reorg'ed to physically remove the column (using dbms_redefinition).

[kregan77]

Thanks for your prompt reply Mr. Burleson.

Regarding your article on compression, I was a bit confused about what you said regarding decompression because early on in the article you state:

Please note that there is common misconception that 11g table compression decompresses data while reading and holds it in the uncompressed form in the cache. That is not quite correct since one of the salient features of the database compression is that we do not have to uncompress the data before reading it and the data stays in the compressed form even in the cache.

But then later you talk about decompression when blocks are read - so I'm a bit confused as to which is the case. I'm sure I'm missing something here.

Even if decompression does occur when blocks are read - how does this play into a "DROP UNUSED COLUMN" operation? What's still not clear to me is if decompression must occur when an unused column is dropped off of a table - I don't know if a physical read must occur when removing column data.

You say that decompression occurs within the "Data buffer" - I'm assuming this is the buffer cache in the SGA? This seems to imply that maybe what happened is when they dropped the unused column from a 400G compressed table, it exceeded the limits of their buffer cache and this scenario was not properly handled by the server process which keeled over resulting in the -3113.

Sure is frustrating they did a system restore without grabbing the alert logs/trace files first!!

Thanks again for your help!

Hi Kregan,

>> when you drop an unused column off a compressed table, does it uncompress?

Good question!

Logic says, why bother?

Oracle is designed for high speed, it only "marks columns" in the dictionary.

************************************
>> Where does this uncompression occur?

In the data buffer. But bne aware, Oracle has several types of compression:

http://www.dba-oracle.com/oracle11g/sf_Ora...or_the_DBA.html

The docs say that all uncompress is done at page rretrieval time . . .

*************************************

>> exactly what happens if you mark a column unused in a compressed table and then alter table drop unused columns?

The column is "logically" removed from the data dictionary.

It still exists, until the table is reorged . . .

****************************************
>>

See MOSC Note:17613.1 "ORA-03113":

http://dba-oracle.com/m_ora_03113_end_of_f...ons_channel.htm


Your ORA-03113 likely will disappear as soon as the table is reorg'ed to physically remove the column (using dbms_redefinition).

[burleson]

Hi

>> I don't know if a physical read must occur when removing column data.

Logic dictates that Oracle must visit the block to do the row . . .

I'll bet this wuld not have happened if the DBA had flushed the data buffer after making this change!



*******************************************
Oracle has implemented one form or another of compression for years!

Back since Oracle8, I think . . .

They are all different, as the article notes, and you don't seem sure which you have . . . :

Simple index compression in Oracle 8i

<LI>

Table-level compression in Oracle9ir2 (create table mytab COMPRESS)

<LI>

LOB compression (utl_compress) in Oracle 10g

<LI>

Row-level compression in Oracle 11g, even for materialized views (create table mytab COMPRESS FOR ALL OPERATIONS; )

<LI>

<LI>

<LI>

******************************************
>> Sure is frustrating they did a system restore

Why is "they" Maybe I should be taljking directly to the DBA!

If they open nan SR on MOSC I would reproduce it on test, take a BBED dump of the condition and see if you hit a bug:

http://dba-oracle.com/googlesearchsite_pro...TF-8&q=bbed

Good Luck!

Let me know if you can reproduce it.

My bet is that you hit a bug . . . .

[kregan77]

"They" is our customer - they are using our product which includes a "job" whose purpose is to drop unused columns off of tables. So, they did not directly issue the statement, they were just using our product to remove fields they have inactivated from a record type table. The application is a data warehouse type of application and the purpose of the unused approach is to allow them to schedule the actual storage removal to a time when they are not loading into the table (via direct path). So, unfortunately, the error occurred and they wiped out the evidence before contacting us.

I was attempting to reproduce the problem by creating a small tablespace and a compressed table and loading into the table until I was getting tablespace full errors, then I would mark a column unused and drop it. This was partly due to Oracle support mentioning something that sounded like the decompression occurred in the "default tablespace" which could have meant the table's defined tablespace or the instance default tablespace. I then also created a small temporary tablespace and retried the test - in both cases I didn't have a problem.

Now, I may try to see if I can shrink the buffer cache or the SGA and try again and see if I can reproduce it.

Thanks again for your help!

[burleson]

Hi Kregan,

>> they are using our product which includes a "job" whose purpose is to drop unused columns off of tables.

Aha! Change your job to flush the buffer pool afterwards.

http://www.dba-oracle.com/t_flush_buffer_cache.htm

And force them to try it in test and qa before doing it in prod!

****************************************************
>> So, unfortunately, the error occurred and they wiped out the evidence before contacting us.

Well, you can charge them to reproduce it!

Get a full backup from before, and replicate the whole shebang in your environment.

But like I said, this smells like a bug to me!

Have you checked the bugs database? http://support/oracle.com

>> Thanks again for your help!

Hopefully this is helping!

My company (Burleson Consulting) does the database-side for dozens of vendor products, ensuring best practices, &c . . .

If you want to rent an expert for a few hours, just call me at 800-766-1884 . . .

Let us know how this ends!

[kregan77]

I think the issue with reproducing it on their end is that it is a 400GB compressed table and therefore poses some challenges in replicating

The buffer pool should be flushed after what? Do you mean flush it prior to the drop unused column statement to ensure we are maximizing it for this task?

We will have a hard time convincing them to try anything that might result in the crash since it caused them quite a bit of trouble since the table was left in a corrupted state after the failure. This is why they had to do a full system restore. These tables are being constantly loaded into and any delay can cause a lot of problems for them.

This is why we are trying to figure out how to reproduce it on our end.

Hi Kregan,

>> they are using our product which includes a "job" whose purpose is to drop unused columns off of tables.

Aha! Change your job to flush the buffer pool afterwards.

http://www.dba-oracle.com/t_flush_buffer_cache.htm

And force them to try it in test and qa before doing it in prod!

****************************************************
>> So, unfortunately, the error occurred and they wiped out the evidence before contacting us.

Well, you can charge them to reproduce it!

Get a full backup from before, and replicate the whole shebang in your environment.

But like I said, this smells like a bug to me!

Have you checked the bugs database? http://support/oracle.com

>> Thanks again for your help!

Hopefully this is helping!

My company (Burleson Consulting) does the database-side for dozens of vendor products, ensuring best practices, &c . . .

If you want to rent an expert for a few hours, just call me at 800-766-1884 . . .

Let us know how this ends!

[burleson]

Hi Kregan,

First, I would not have allowed a drop column during work time, even though Oracle allows it! I ALWAYS reorg and then bounce my systens after dropping a column . . . .

I'm just guesssing about flushung the buffer, but I could be wrong . . .

If I were you I would find a related bug on Oracle bugs database, and blame it on that . . .

http://www.bing.com/search?q=ora-03113+%22...mp;sp=-1&sk=