Every once and awhile I come across a valuable utility, I have come across a new
Oracle utility that can be utilized starting with 9i release 2 that can help estimate the space savings from compression.
The process creates tables in the default tablespace of the user executing the procedure. It is recommended that you make sure the default tablespace is other then SYSTEM or SYSAUX.
The process is created via a package procedure available via a download from Oracle Technet at
http://www.oracle.com/technology/products/database/compression/compression-advisor.html
Example of the package installation
[oracle@oel54node1 compression_advisor]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 23 22:33:57 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbmscomp.sql
Package created.
Synonym created.
Grant succeeded.
No errors.
SQL> @prvtcomp.plb
Package body created.
Synonym created.
Grant succeeded.
No errors.
Here is an example of its execution
SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('SYS','AUD$','OLTP',20) ;
Sampling table: SYS.AUD$
Sampling percentage: 20%
Compression Type: OLTP
Compression ratio: 1.53
PL/SQL procedure successfully completed.
Subscribe to:
Post Comments (Atom)
4 comments:
Mike,
I ran into an issue with the compression advisor running but causing a problem when it is replicated (via Streams) to an 11.1 database. How can I disable the compression Advisor?
Thanks,
Eric Fall
UMich
Eric,
Nice to hear from you, could you give a little more detail on the issue. I have always run the compression advisor manually through the packaged procedure. What exactly is the issue from a streams perspective? Are you using streams on a table that is compressed? If so compressed tables and streams will not work together unless on 11.2.
Mike,
We have 11.2 replicating via streams to an 11.1. The advisor runs at 10pm during the Maintenance window (ie, when stats are collected) and the advisor creates 2 tables in the application scheam (not sys) and these tables are compressed. When Streams replicates the create table statement, the apply fails with ORA-14464: Compression Type not specified. We are trying to put in rules to skip these tables, but that is causing additional problems for streams. I was hoping to find a way to disable the Compression advisor.
thanks,
Eric
Mike,
We found out the compression advisor is bundled in with the 'auto space advisor', so we had to disable that using:
exec dbms_auto_task_admin.disable(client_name=>'auto space advisor', operation=>null,window_name=>null);
Eric
Post a Comment