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?
One of my databases which is running on exadata x2-2 , has been restored to non-exadata machine in order to test few things. I had a sub-partition table in the exadata , compressed for query high. test machine (NOT EXADATA) , after uncompressing this subpartiton tables i am getting the following err message :
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type I have executed the following commands : alter table crm.cm_ncd modify partition P01_CM_NCD nocompress; alter table crm.cm_ncd modify partition P02_CM_NCD nocompress; alter table crm.cm_ncd modify partition P03_CM_NCD nocompress;
[code]...
ERROR at line 1: ORA-12801: error signaled in parallel query server P005 ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type If all the partitions are uncompressed why i am getting this error message ?
I have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utilizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects.
When I am recreating the External table with NOLOGGING keyword at the bottom of the synatx, it's showing "operation not supported". Else are there any other points that I should look to increase the execution time of the above statement ?
I am having an Oracle 11g R2 RAC setup. My database size is 22TB in normal servers. I have checked my database in Exadata X2-2 and found out that the HCC was good and as per my assumption the 22TB can come down to 10TB .
My challenge is i need to transfer the 22TB data to the exadata quater rack which is having space constraint. Is there any way other than export & Import as data export and import will use entire 22TB. Is there any way to transfer the data to Exadata with compressed form from the source server.
I am having doubt on the no_index concept in oracle. I am using oracle exadata server.It is basically data warehouse project.I am in the situation to join some tables and get the result set for reporting purpose.
Among the tables, 2 tables having huge count.1st table has more than - 1,000,000,000 rows2nd table has more than - 200,000,000 rows when i join these 2 tables with some small set of tables, it is taking long time (around 20 MIN) to retrieve the result set. The final result set is around 100 rows only.
But, when i force NO_INDEX hint in the same query, is giving the same result in very fast manner(around 5 MIN). Because it is working based on cell smart scan. So, can i force the NO_INDEX hint to all tables? I forced the NO_INDEX hint only the table which is contain 200,000,000 rows not for others.
Query Plan : Normal Query using the the range scan based on the key. No_INDEX Query going full table scan.
1) Post Installation check list ie: How can I see every things are installed correctly. 2) upgrade the exadata OS version . 3) Upgrade the Database. 4) Test Migration from Oracle database to Exadata. 5) Right now there RAC servers are installed without domain, I need to add domain name to existing RAC.
I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
-------------------------------------------------------------------------------- -- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels -------------------------------------------------------------------------------- CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ), INVENTORY_CNT NUMBER ( 2 ) ); CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ), ITEMSEQ NUMBER ( 5 ) ); INSERT INTO [code].......
-------------------------------------------------------------------------------- -- Inserts for new items in the store -------------------------------------------------------------------------------- INSERT INTO TEMP_INV_1 VALUES ( 1, 11 ); INSERT INTO TEMP_INV_1 [code].......
I have to create/configure the environment for the database having high volume of the transactions nearly 10000 transactions per day and at peak time nearly 1500 transactions per second.
RAC can be one of the solution but that comes up with high cost. I googled for this type of environment configuration, what could be possible settings need to be configured for this environment configuration in both RAC and Non-RAC.
installing Oracle for a new Data-Center(still in planning).It will be an OLTP shop working 24/7, with 250 concurrent users.
1. Can it, in theory run with good performance on a quad processor(something like Intel Xeon Series 56XX) , with 32GB of memory? 2. What is the Oracle Database Edition required for this configuration? 3. What additional things should one take into account to plan the most cost-effective configuration?
In an OLTP environment what cursor_sharing setting is preferred?Though typically we retain the original setting for most of the parameters except memory settings etc. I have queries in the following context
No. I am not facing any issue as of now (I am not supporting any Live environment) But I want to know the desgn considerations
First of all in OLTP environment (say one I am referring) we use pl/sql variables which are obviously bind variables Only in case where plan is expected to change we use hard coded values like 'CREDIT' or 'DEBIT' etc. for acc_type column
Again there can be 2 scenario 1) we use the same query for both acc_type values 2) we use 2 different queries IF v_parameter = 'CR' select * from accounts where acc_type='CREDIT'... else select * from accounts where acc_type='DEBIT'... end if;
Again suppose the values are skewed and we gather stats with histograms hereIs't it the setting 'cursor_sharing=similar' which will be useful in above case?as with this setting optimizer will 'think' which plan to pick depending upon the values and bind variable peeking is taken care in option 2 above with IF ELSE clause?
BTW I have carried several tests but not getting conclusive results For example I created following table with skewed data, created index and gather stats with histogram
SQL> select object_id,count(*) from skewed_data_tab group by object_id;
SQL> create index i_skewed_tab_data on skewed_data_tab(object_id);
SQL> exec dbms_stats.gather_table_stats(user,'SKEWED_DATA_TAB',cascade=>true, method_opt=>'for all columns size 254');
Then traced with following options 1) alter session set events '10046 trace name context forever, level 12';
SQL> begin for i_outer in(select n from ids order by tstamp) loop for i_inner in (select /* for exact */ object_id,object_name,object_type from skewed_data_tab where object_id=i_outer.n)
[code]...
2) set termout off alter session set events '10046 trace name context forever, level 12'; @/u04/scripts/exact.sql 5 cat /u04/scripts/exact.sql select /* for exact */ object_id,object_name,object_type from skewed_data_tab where object_id=&1;
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?
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.
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.
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 forms 10g and basically we have a system that takes over 300 photos on a daily basis, this all works fine and with no issues except for say maybe 2-3 photos a month. Occasionally we will get a 'corrupt photo' (it not actually corrupt, it displays in everything as it should except forms) . When we encounter these photos forms just crashes out and the user is unable to query the record with the associated photo until it is deleted and a new one is taken (or alternatively if we take the photo from the database open it in paint.net and just hit save it will then work). There is no difference that we can see in the photo which doesnt work and those that do work. I have tried using WRITE_IMAGE_FILE to save the photo to disk and Read_Image_File to read from the disk to see if that makes a difference. If i save the file as jpeg and no compression it still crashes, if i save it with low compression it works fine but we lose quality which we dont want to lose. Bitmap wont work at all. Saving as JFIF and GIF works fine without any compression but we still lose quality.
The photo will display fine if we use a javabean to display it but in this instance a javabean is not an option.
One weird thing we noticed is that when we are on the form that crashes with these photos and query back a working photo first, and THEN query the 'corrupt photo' the corrupt photo displays fine, but if we go into the form and query a corrupt photo first forms crashes as explained.
My exadata quarter rack machine has two asm diskgroups, DATA1 with 5TB and RECO with 3TB. I'd like to resize RECO to 1TB and DATA1 to 7TB.
I know ALTER DISKGROUP RESIZE command, but my question is about resize RECO volume from 3 to 1 TB: is it supported by Oracle?? Let me know risks /issues with this resize?
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?
