SQL & PL/SQL :: Index Compression Increasing The Size?
Jun 21, 2012
I had created a Primary key and wanted to compress as per my senior instructions.Below are my results the size increased after compression.
select compression from dba_indexes where index_name = 'TEST_IDX';
Compression
----------
DISABLED
select sum(blocks) no_of_blocks, (sum(blocks)*8192)/(1024*1024)size_MB
[code]....
We ran a compression on the primary key index TEST_IDX
ALTER INDEX SCOTT.TEST_IDX REBUILD INITRANS 15 TABLESPACE DATA_01 COMPRESS;
ANALYZE INDEX SCOTT.TEST_IDX VALIDATE STRUCTURE;
Now when i ran the below select statement:
select compression from dba_indexes where index_name = 'TEST_IDX';
Compression
----------
ENABLED
select sum(blocks) no_of_blocks, (sum(blocks)*8192)/(1024*1024)size_MB
[code]....
As you can see after compression the blocks and size has been increased, but i ran for many tables and other indexes, we observed the blocks and size was reduced by 50-70%, i am not sure why this happened to the index compression.
1.2.0.2 on RHL.. 3 Log Groups with 1 member each. db_recovery_file_dest string /oracle/oraarch For the purpose of increasing log file size, if i use ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 300M; but it creates Log Group with 2 member. one is at /oracle/oraarch location and other at /oracle/oradata (db_create_file_dest).
We are using ORACLE MANAGED FILE SYSEM . I want only 1 member at /oracle/oraarch (to keep the previous setting intact ...just increasing the size from 100 to 300M). If I manually give the path where to create the logfile member, I get this error: ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M; ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M * ERROR at line 1: ORA-00301: error in adding log file '/oracle/oraarch/DB/onlinelog/' - file cannot be created ORA-27038: created file already exists Additional information: 1
I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.
The size of redolog member is 12m . Can I increase the size of that member dynamically, without adding a new member to that group and dropping the old one.
How can in increase the allocated space for a schema in Apex Admin section?
I know you can set this when creating a schema alongsite a workspace and looks like the only way to do it is via raising a service request for more space and loggin in as ADMIN and approving it (in increments of 500MB).
We have a requirement from the customer to start using data and index compression in our 11g database.. Is this something available in Oracle 10g,11g without any additional costs? We are not sure if this will work with our application so we will have to test it in-house, is it possible to compress the existing table data/index to test it out?
Our database size is 100GB and i removed few records from a table and rebuild the index.The size of Index reduced considerably after Index rebuild but now i see our database size increased to 115GB.I know online rebuild creates second index which is also removed after the build is finished then why the increase in database size?Is there a way so it shows up more space ?
select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1'; 8GB
ALTER INDEX abc_index_1 REBUILD ONLINE ;
select sum(bytes) from dba_segments where owner='abc' and segment_name='abc_index_1'; 2GB
Due to an ORA-08177: can't serialize access for this transaction in our Java application using serializable transactions and after running some tests, we decided to increase INITRANS from 3 to 5 for our tables.
what would be the disadvantages from this measure? Space? I have done some tests with large tables with INITRANS 3 and 5, populated with large amounts of data and the space occupied is the same in bytes and blocks. Performance? Something else?
We are seeing volume issue when taking Rman level 0 backup for a database , the database version is 11.2.0.2 and its on RHEL 2.1. As 11g supports compression for RMAN, we have implemented so as to reduce the backup space used.
" CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE "
However during full backup the volume size increases, meaning we have to increase /data volume (currently 500G) to more then a 1T for just rman to go through, else the backup hangs. Once backup is done we again bring down the volume size to less then 1T. The other compression parameters are HIGH and MEDIUM, hoever I am not very sure if changing to high or low will work as I couldn't find any right doc in meta link or may be I didn't searched correctly, I will continue to look for that.
Hybrid Columnar Compression is dependent on the underlying storage system. See Oracle Database Licensing Information for more information.
The below is from the Oracle® Database PL/SQL Packages and Types Reference Compression Constant Compression Level Description COMP_FOR_OLTP 2 OLTP compression COMP_FOR_QUERY_HIGH 4 High compression level for query operations COMP_FOR_QUERY_LOW 8 Low compression level for query operations COMP_FOR_ARCHIVE_HIGH 16 High compression level for archive operations COMP_FOR_ARCHIVE_LOW 32 Low compression level for archive operations
To use Compression Level 4 or higher do we have to have ZFS or Pillar storage ?
We want to use archive compression for our standby(standalone non-exadata). We don't have license for Advanced Compression. Is the archive compression possible without Advanced Compression?
What are the default features available along with 10g R2 Enterprise Edition , Especially for RMAN Backup ? What are the features required for licensing in 10g R2 EE version .
Using Oracle 11g's compression feature in production? I haven't read anything negative yet, that doesn't meant that there isn't anything that could have an adverse affect. I wanted to check to see if there are any affects on the performance or any disadvantages of using this compression feature. I have tested this on one my major tablespace and I did see a big difference in the reduce size on the tablespace but I am still hesitated to put this into production.
After the reboots each system was using around 30 GB of memory... now that it's been up for a week memory is up to 98GB used on each system. None of the systems are swapping.
Considering the below factors, I am planning to increase the buffer cache value from 256Mb to 512Mb.
1. Buffer cache hit ratio value is around 35% even in the normal period. 2. free buffer requested value is below during peak & normal hours below.
Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ free buffer requested 54,694,995 15,226.9 2,523.7 free buffer requested 23,412,674 6,501.7 2,585.9
3. most of the top 5 physical reads & logical reads queries are well tuned and some of queries are doing FTS on small tables (table count min 1500 max 35000). SO indexing option is not required for these queires. But these queries getting executed frequently.
SQL> show sga
Total System Global Area 2148534928 bytes Fixed Size 731792 bytes Variable Size 1879048192 bytes Database Buffers 268435456 bytes Redo Buffers 319488 bytes
5.top 5 waitevents during db slow performance & high cpu utilization (>80%) issue.
Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- latch free 1,848,898 153,793 52.00 buffer busy waits 395,280 87,201 29.49 db file scattered read 3,488,648 34,199 11.56 enqueue 4,052 10,897 3.68 CPU time 5,567 1.88
6. Top 5 waitvents during normal activities and CPU utilization is around 40%.
Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 1,860 45.32 db file scattered read 1,133,669 985 23.99 imm op 776 605 14.73 sbtinfo2 208 139 3.40 sbtbackup 2 123 3.00
I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.
For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.
I am trying to enable OLTP compression on tables and at tablespace level for the tables
Steps I am following are:
1. Move indexes to its own tablespace 2. enable OLTP compression at table level: alter table table_name move compress for OLTP 3. Rebuild indexes 4. Issue I have is what to do with tables with LOB columns ALTER TABLE lob_table MOVE LOB (LOB_COL) STORE AS (TABLESPACE index_tbsp); -- Is this correct? 5. alter tablespace data_tablespace default compress for OLTP;
I have a question, is the sequence of steps correct. For tables with LOB columns do we needto move lobindex to index tablespace. Beacuse lobsegment and lobindex are created in data tablespace?
3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.
4) Below statement is correct or not ? IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.
if above statement is correct, then how to find out whether table data is using block level compression ?
5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.
confirm what is the conclusion on this ?
SQL> declare rid rowid; n number; begin select max(rowid) into rid from NOVAR.PAYMENT_SWP; n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid); dbms_output.put_line(n); end; / 2 3 4 5 6 7 8 9 1
PL/SQL procedure successfully completed.
SQL> SQL> SET SERVEROUTPUT ON SQL> / 1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;
MAX(ROWID) ------------------ AAsz4fAHSAAAD3IABs
(ii) 2nd table
SQL> set serveroutput on SQL> declare rid rowid; n number; begin select max(rowid) into rid from NOVAR.PREPAYMENT_SWP; n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid); dbms_output.put_line(n); end; 2 3 4 5 6 7 8 9 10 / 1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;
I am Using Oracle 11.2.0.3. I had a script that droped partitions for a table which should have dropped nearly 30 gb of data. We have data and index tablespace.
Query of dba_free_space showed no change on data tablespace. However, index tablespace showed increase in free space.
select sum(bytes / (1024 * 1024 * 1024)) "Size (GB)" from dba_free_space where tablespace_name ='&tbs_name'
I am using following command to drop partition
ALTER TABLE table_name DROP PARTITION "partition_name" UPDATE GLOBAL INDEXES;
What should be done to increase free space shown for tablespace after partition drop?
I noticed that listener file in diag/trace is rapidly increasing. I am using 11g Rel 1 on window 2008 server Its 1 GB in couple of days. Is there any way if I can turn it off ?
