Blog - comments

Hi Christopher, It's there. I't not an option that you check at install. Just use it by setting inme...
Hello, Thanks for the nice blog. I tried the latest 12c download available in Oracle's website and I...
Christopher Bernard
-- Here is a quick script to display which objects are locked in Share. Parameters: owner tablename....
Hey...I think you forgot that Hotspot have a JIT compiler too. The difference is in the time wherer ...
Anderson

Thanks for the content..

vani
Blog Saïd Mendi Compression advisor: excessive redo log file generation

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Posted by on in Development & Performance

Compression advisor: excessive redo log file generation

Last week at a customer site, we experienced an excessive redo log generation rapidly filling up the FRA (Flash/Fast Recovery Area) with archivelog files. This eventually caused the database to crash.

As usual, we started the problem analysis by scanning the alert.log file. The trouble began at 7 am when the DEFAULT_MAINTENANCE_PLAN started.

In less than 10 minutes more than 100 archivelog files of 50 Mb each were generated (cf. Below):


Setting Resource Manager plan SCHEDULER[0x3109]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Thu Jun 30 07:00:38 2011
Thread 1 advanced to log sequence 2648 (LGWR switch)
…....
…....
Thu Jun 30 07:09:44 2011
Thread 1 advanced to log sequence 2749 (LGWR switch) Errors in file X:\ORACLE\diag\rdbms\mydb_site1\mydb\trace\mydb_m000_2196.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 bytes is 100.00% used, and has 0 remaining bytes available.

 

As this output did not reveal that much, we started generating the AWR reports for the period when the database crashed (see AWR below).

Here are the top SQL consumers sorted by different criteria:



 

 

 

 

 

Based on these reports, we observed that the SQL Module consuming all the resources was always the DBMS_SCHEDULER which calls the stored procedure dbms_space.AUTO_SPACE_ADVISOR_JOB.

What does it mean?

During the default Oracle maintenance window, the DBMS.SCHEDULER package starts the AUTO_SPACE_ADVISOR_JOB job. This job's purpose is to trigger the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC stored procedure. This last one itself triggers the Automatic Segment Advisor which uses the Compression Advisor in order to identify all the segments which should benefit from table compression (Oracle Compression Feature).

Going deeper in our investigations about this Compression Advisor, we found out that this problem was due to bug 8896202 (Oracle Support) which should have been fixed in version 11.2.0.2 (cf. below). However, our customer was still hit by this issue.

 

METALINK

How Does Compression Advisor Work? [ID 1284972.1]
Compression advisor is a new advisor added in 11gR2.
It is shipped with Segment Advisor, and will be executed whenever Segment Advisor task run.
We can't disable it separately at this point of time, but an enhancement request has been submitted to separate it from Segment Advisor.
Segments that are at least 10MB in size and with at least 3 indexes will be evaluated for compressibility (but not all the tables which meet the requirements will be evaluated, an internal algorithm will decide which segment should be evaluated).
The dbms_compression package will be invoked to estimate how much space can be saved. It will create two internal tables (DBMS_TABCOMP_TEMP_UNCMP, DBMS_TABCOMP_TEMP_CMP) in the tablespace which hosts the segment.
DBMS_TABCOMP_TEMP_UNCMP is created with 99% sample blocks by default.
DBMS_TABCOMP_TEMP_CMP is created with compress option based on DBMS_TABCOMP_TEMP_UNCMP.
COMPRESSION_RATIO is generated by comparing DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP. These two tables will be dropped immediately after the estimation.
The recommendations of compressing table can be obtained from EM DB console.
Please note the following impacts:
Without applying unpublished bug 8896202, excessive redo log will be generated. (This is fixed in 11.2.0.2) however this bug still occurred on our site.
But be aware that bug 8896202 might not eliminate redo generation if object containing LOB data which is not having NOCACHE NOLOGGING option; check note  976722.1 for details.
Ora-20000 against tables DBMS_TABCOMP_TEMP_CMP and DBMS_TABCOMP_TEMP_UNCMP might be seen when performing dbms_stats
Extra disk space might be required to host DBMS_TABCOMP_TEMP_CMP and DBMS_TABCOMP_TEMP_UNCMP, it can result in space pressure in some cases.

 

So we again had a look at the AWR report and noticed that the top SQL statement was the TEMPORARY table creation against a table containing LOB segments:

sql-statement

SQL> select owner,table_name,column_name,cache,logging from dba_lobs where owner='BS_ZG' and table_name='G91_DOK_DMS';

OWNER            TABLE_NAME         COLUMN_NAME             CACHE      LOGGING                                                                               
BS_ZG            G91_DOK_DMS        G91_DOC_BLOB            NO         YES                                                                                   
BS_ZG            G91_DOK_DMS        G91_DOC_CLOB            NO         YES


Root cause and Workaround

"If a source table contains a LOB segment with "CACHE/LOGGING" enabled then despite the database NOLOGGING clause setting, Oracle will still generate excessive redo logs as the NOLOGGING for LOBS can only be invoked when the LOB segment is in 'NOCACHE and NOLOGGING'."

In our case we did not have cache enabled, however, our customer still was affected by the bug!

Therefore we implemented the following workaround:

  • Disable the Automatic Segment Advisor.

SQL>execute dbms_scheduler.disable('sys.auto_space_advisor_job');

or

BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/

 

Of course we are now awaiting (as several customers) the patch set 11.2.0.3 in order to verify if the bug is fixed even if the "auto space advisor" is enabled.

 

Hope this helped Laughing

Rate this blog entry:
3

Saïd Mendi is Consultant at dbi services. He has more than twenty years of experience in programming, database infrastructure management, engineering, and optimization. He is specialized in HP/UX, Sybase, and Oracle technologies such as standardisation, backup & recovery, tuning, high availability, monitoring, and service management & support. Saïd Mendi is ITIL Foundation V3 certified and Oracle Certified Professional 11g. Prior to joining dbi services, Saïd Mendi was Application Manager at Swisscom IT Services in Basel. He also worked as an Unix/Linux IT Administrator, Sybase and Oracle DBA at Crossair/Swiss in Basel. Saïd Mendi holds a BTS degree in Information Systems from France.

Comments

  • Guest
    Running_DBA Wednesday, 20 June 2012

    Cannot thank you enough for posting this. We have hundreds of GBs getting generated. Oracle support was clueless.

  • Guest
    Mendi Saïd Thursday, 21 June 2012

    Hi,
    You're welcome, that's a great satisfation for me it helped you...

    Best Regards
    Saïd Mendi

  • Guest
    Peter Winter Thursday, 12 July 2012

    Hi there

    We've just met exactly the same bug on 11.2.0.3. running on RHEL 64 Bit. Segment Space Advisor was inspecting a table containing LOB Segments of type "secure file". Creation of the temporary tables was aborted due to space pressure in the tablespace. Until then, around hundred Gig of archive logs were produced. I think the bugfix introduced in 11.2.0.2 is not working on all platforms, or maybe it's only working with conventional "old style LOBs, but not with secure files.

    Man thanks for your post an kind regards
    Peter

  • Guest
    Saïd Mendi Wednesday, 25 July 2012

    Hi Peter,

    Back from my holidays, I just read your post, it sounds to me that ORACLE did not fix it in the new Patchset.

    Thanks for your feedback...

    Saïd Mendi

  • Guest
    Gary F. Thursday, 09 May 2013

    Thank you for the very informative post. I've been researching why our DB generates so much redo at 11:00 PM, when everyone is asleep.

    Your post gives us all we need to determine the solution if this ever becomes a problem for us.

  • Guest
    Stefan Friday, 23 August 2013

    I think we have the same bug in 11.2.0.3 with normal BLOB... on RHEL 5 64-bit. So it is not fixed. Thank you for this useful article, I just disabled space advisor :)

Leave your comment

Guest Wednesday, 23 July 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter