{"id":1109,"date":"2011-10-20T18:35:55","date_gmt":"2011-10-20T16:35:55","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=1109"},"modified":"2011-10-20T18:36:59","modified_gmt":"2011-10-20T16:36:59","slug":"rdbms-11-2-0-2-unrecoverable-operations-of-compression-advisor-running-daily","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2011\/10\/20\/rdbms-11-2-0-2-unrecoverable-operations-of-compression-advisor-running-daily\/","title":{"rendered":"RDBMS 11.2.0.2: unrecoverable operations of Compression Advisor running daily"},"content":{"rendered":"<p>At one of my customers, we wondered about some entries in v$datafile.unrecoverable_change# for the most critical tablespace of the application. Certainly no application components were allowed to perform nologging operations for segments inside this tablespace and yet there was evidence that &#8220;something&#8221; was done with nologging.<\/p>\n<ul>\n<li>The unrecoverable_time was shortly after 10 pm<\/li>\n<li>The ASH data covering this time period showed activity by DBMS_SCHEDULER and Autotask Jobs.<\/li>\n<li>An internal DDL log table showed the following operations:<\/li>\n<\/ul>\n<pre lang=\"SQL\">create table \"MYOWNER\".DBMS_TABCOMP_TEMP_UNCMP\r\ntablespace \"DAT_MYOWNER\" nologging\r\nas\r\nselect \/*+ FULL(\"MYOWNER\".\"MYTAB\") *\/ *  from \"MYOWNER\".\"MYTAB\"  sample block( 3.55) mytab ;   \r\n\r\nCREATE TABLE \"MYOWNER\".DBMS_TABCOMP_TEMP_CMP ORGANIZATION HEAP\r\nTABLESPACE \"DAT_MYOWNER\"\r\nCOMPRESS FOR ALL OPERATIONS\r\nNOLOGGING\r\nAS\r\nSELECT  * FROM \"MYOWNER\".DBMS_TABCOMP_TEMP_UNCMP MYTAB;\r\n\r\nDROP TABLE \"MYOWNER\".DBMS_TABCOMP_TEMP_UNCMP PURGE;\r\ndrop table \"MYOWNER\".DBMS_TABCOMP_TEMP_CMP purge;\r\n<\/pre>\n<p><strong>Summary:<\/strong><\/p>\n<p>I find it suboptimal that Compression Advisor is executed on a daily basis during the maintenance window and that the Compression Advisor can not be seperately disabled without also disabling the Segment Advisor. (Although there is supposed to be an enhancement request open for this.)<\/p>\n<p>In addition to that, it is more than problematic that the is using the user tablespace, which could lead to tablespace pressure and that the operations are performed as unrecoverable and therefore logged in v$datafile.unrecoverable_time, overwriting potential important information of real &#8220;user&#8221; operations performing with NOLOGGING.<\/p>\n<p>More information about Compression Advisor can be found in MOS Note: How Does Compression Advisor Work? [ID 1284972.1]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>At one of my customers, we wondered about some entries in v$datafile.unrecoverable_change# for the most critical tablespace of the application. Certainly no application components were allowed to perform nologging operations for segments inside this tablespace and yet there was evidence that &#8220;something&#8221; was done with nologging. The unrecoverable_time was shortly after 10 pm The ASH [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[57,5],"tags":[],"class_list":["post-1109","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1109"}],"version-history":[{"count":13,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1109\/revisions"}],"predecessor-version":[{"id":1123,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1109\/revisions\/1123"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}