HYBRID COLUMNAR COMPRESSION - OVERVIEW

I couldn't find much from Oracle Documentation.However there are very good bits and pieces on the net. Here are my takeaways from these readings.

1). HYBRID COLUMNAR COMPRESSION [HCC] is only for EXADATA.There seems to be some technical difficulties in implementing it for the traditional storage (Don't know what it is!).

2). Concept:

Compression is very effficient for a data which is quite repetetive. With repetitive values, we can replace the Values with the smaller values (eg Symbols/Special Characters) and create the metadata to map these values; this metadata can be used to decompress the data.In a nutshell ,Compression saves space at the cost of processing power (CPU Usage).

Below diagram shows a typical Oracle Data block Stores the Data.C1-5 are Columns.

Values with Lower Cardinality (more duplicated elements) are the potential candidates for the efficient compression. With this, Columns are set to have more duplicated values than the rows.

So with HCC, Columns are grouped together in a logical area called Compression Unit.Which is a logical grouping of multiple datablocks.

So the compression unit would look like.

3). There are 2 types of HCC

- Warehouse Compression - On Average, 10 Times Space Savings.

- Archive Compression - On Average, 15 Times Space Savings(70% at some customer sites)

4). Warehouse Compression

-   Syntax: 

                 CREATE TABLE X

                                        COMPRESS FOR QUERY [LOW  | HIGH]

                                        AS

                                        SELECT * FROM Y;

                   Where   HIGH | LOW - Specifies the Extent of Compression.

                - Warehouse Compression is optimized for Query Performance.

5). Archive Compression

-   Syntax: 

                 CREATE TABLE X

                                        COMPRESS FOR ARCHIVE [LOW  | HIGH]

                                        AS

                                        SELECT * FROM Y;

                   Where   HIGH | LOW - Specifies the Extent of Compression.

                 - Archive Compression is optimised for Storage savings.

6). When a session places lock in the particular row HCC locks the whole Compression Unit Resulting in multiple row lock. Hence it may not be suitable for OLTP application where frequent update/insert occurs.

7). A Partition Table can have different type of HCC based on the Partition .For example. The latest partition can have COMPRESS BY QUERY whereas the older partition which hardly being queried can create using COMPRESS BY ARCHIVE.

8). The existing table can be converted to get benefit from HCC.However only newly added rows will be compressed .To compress the existing rows you need to do ALTER TABLE MOVE ;

9). You can check the characteristics of a row using DBMS_COMPRESSION package. To know more about the package click here

10)CPU Consumption for compression and decompression of the data is offset'd by less logical reads. Archive Compression consumes relatively more CPU compared to Warehouse Compression.

Generate DML/DDL/QUERY from SQL:

Lets consider an example to generate an SQL from select query to online datafile of a database.

The actual query is

select name from v$datafile;

From the above one we need to get the datafile online sql script.

ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.306.705691421' online ;

The final script would be,

set heading off

set lines 2000

select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;' from v$datafile;

Above command would give an output like

ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.306.705691421' online ;

ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.302.705691421' online ;

Two basic things to consider is

1) Whatever you need display as it is,should be given in ' ' quotes.

2) To get the value of a variable (in our case ‘NAME’ which gives Datafile file path and name) give it in Double Pipe. i.e, || ||

Taking our example, Lets break the SQL for better understanding.

SQL>select 'ALTER DATABASE DATAFILE ''' || name || ''' online ;' from v$datafile;

Here “ALTER DATABASE DATAFILE” is the text we want to display at first. Hence the query “select 'ALTER DATABASE DATAFILE ' “. Remember there is a space between datafile and the ending single quote. Next we want datafile name with full location, which the actual query gets.

To get the value of the Name Column. We need to place column name in || ||

select 'ALTER DATABASE DATAFILE ' || name ||

This would command would give an something like

ALTER DATABASE DATAFILE /oradb01/TEST/data/apps ……

However we want ' to be included for the datafile name value.

Hence it should be

select 'ALTER DATABASE DATAFILE ' '' || name || ''

To add a single quote we need to add additional single quote to print single quote Hence we need to give 2 single quotes. Now we just need to add online; word to the end of it.

Which is ' online ; ' . Note, you need to give space between the online and the starting single quote. 'online ; ' .Now adding it all would give

select 'ALTER DATABASE DATAFILE ' ''|| name || '' ' online ;' from v$datafile;

However the above need to be formatted as(remove the space between the quotes)

select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;' from v$datafile;

Got the knack?

UNIX Tips

'VI' Search and Replace: -

Go to the Command Mode(By Pressing ‘Esc’)

First occurrence on current line: :s/OLD/NEW

On current line: :s/OLD/NEW/g

Between two lines #,#: :#,#s/OLD/NEW/g (example 1,10s/OLD/NEW/g)

Every occurrence in file: :%s/OLD/NEW/g

 To append the text at the end of  the line   

                     %s/$/ \>\> \/tmp\/test\/test.log/g  

Where

' \' - is a DELIMITER

Above command will add '>> /tmp/test/test.log' to end of the each line.

Eg, File having the line 

                    cat session.sql

                    ALTER SYSTEM KILL SESSION '358, 22' IMMEDIATE ; 

                    Would become

                    ALTER SYSTEM KILL SESSION '358, 22' IMMEDIATE ; >> /tmp/test/test.log  

To add the text infront of the line         

                    %s/^/SQL\> /

                    Would become

                    SQL> ALTER SYSTEM KILL SESSION '358, 22' IMMEDIATE ; >>  /tmp/test/test.log    

Remove ^M(Carriage Return) from the file:

You could face this issue when doing FTP/SFTP from Windows to Unix.

To remove ^M for say 100 files in a directory.

Just go the the directory  and run

                    perl -p -i -e 's/\r\n$/\n/g'   *

$  -  for the end 

 ^  -  for the beginning 

\  -  as a delimiter to special character (like > < $ etc) if putting special symbol..put "\" and then place the actual character you want to display. 

% - place after the command prompt to makes changes across the whole file.

PART 2 :EXADATA V2 - Overview

1).Infiband

• Only Storage Network Supported by Exadata
• Provides Highest Possible performance available - 40Gb/Sec each direction.
• Uses ZDP Protocol (Zero Data Loss UDP Protocol) - Open Source Developed by Oracle Corp
• Can also be used as RAC interconnect.
• Each Infiband Link is able to carry the full data bandwidth of the entire Cell

2).Exadata comes to 2 DISK Type - SAS and SATA.

• SAS - For Maximum Performance
• SATA -For Maximum Capacity

3).Sun Database Machine doesn't include any software licence.

4).Sun Database Storage Server Details :

• RAM - 72 GBP(18 x4 )
• Disk Drives - 584 GB ( 4 x 2.5 146 GB)
• Oracle Enterprise Linux
• Oracle Database 11.2

5).A Full Rack Exadata can have 8 Database Servers,14 exadata cells,3 Infiband Switches

6).SQL Offloading is done by Database Server using iDB(Oracle Data Transfer Protocol)

7).CELLSRV component of Exadata storage server is used for Smart scan processing.CELLSERV Scans the blocks from storage and extract the relevant columns and send it back to the database server.

8).Database Server can break the SQL query and send the small portion of it to each exadata cells and upon retrieving the
results ,Database Server constructs the results and required rows are returned to clients.

9).We can use the below query to find the list of functions which can be offloaded to the Exadata.

Select * from v$sqlfn_metadata where offloadable='YES'

10).Exadata Storage server can perform Smart scans on encrypted tablespace and Columns.

11).With Performance of incremental Backup performance is significantly improved as the changes are tracked from individual block level rather than the large group of blocks.

Database Cloning Issue in R12

Currently doing a R12 Single Instance Clone from PROD to TEST

I removed the Target ORACLE_HOME Direcories and untar'd the Source Oracle Home.I haven't touched the Oracle Inventory as it already had the Target Environment Details as its more of a refresh.

Though we know the ORACLE_HOME is already there.I tried adding it using the below command.

export ORACLE_HOME=/oracle/home/directory/11.1.0

cd $ORACLE_HOME/clone/bin

perl clone.pl ORACLE_HOME="/oracle/home/directory/11.1.0" ORACLE_HOME_NAME="11g_RDBMS_EE_" ORACLE_BASE="/opt/oracle"

It errored with the following message.

SEVERE:OUI-10197:Unable to create a new Oracle Home at /opt/oracle/product/11.1.0_IOFD. Oracle Home already exists at this location. Select another location.

Hence i went ahead and start doing the RMAN Restoration.While logging into 'sqlplus'.it throwed the bizzare error.

Enter user-name:

exec(): 0509-036 Cannot load program oracleIOFD because of the following errors:

0509-150 Dependent module libskgxp11.so could not be loaded.

0509-022 Cannot load module libskgxp11.so.

0509-026 System error: A file or directory in the path name does not exist.

ERROR:

ORA-12547: TNS:lost contact

Action Taken:

Did a relink.using relink all. After that i was able to login to sqlplus without any issue.However there were many errors with the relink .

Hence dettached the ORACLE_HOME from the Inventory and re-added as follow.

To De-attach Oracle Home from Inventory :

cd /oracle/home/directory/11.1.0/oui/bin

./runInstaller -silent -detachHome ORACLE_HOME="/oracle/home/directory/11.1.0" ORACLE_HOME_NAME="11g_RDBMS_EE_"

To Attach:

export ORACLE_HOME=/oracle/home/directory/11.1.0

cd $ORACLE_HOME/clone/bin

perl clone.pl ORACLE_HOME="/oracle/home/directory/11.1.0" ORACLE_HOME_NAME="11g_RDBMS_EE_" ORACLE_BASE="/opt/oracle"

This is has fixed the issue.

PART 1 :EXADATA V2 - Overview

1).Based on SunFire X4275 Server.Processor is Intel Xeon E5440 Processor(64 Bit).

2).Exadata V2 requires 11G R2 (alteast 11.2.01)

3).Works with Single Instance /RAC

4).Exadata can offload some database processing to the Exadata Storage Server.This ability is known as Smart Scan.

For Example below operations can be offloaded to Exadata Storage Server.

• Table Scan Predicate Filters.
• Join Processing
• CPU Intensive Decompression and Decryption.
• File Creation Operations.

5).Exadata V2 Introduces HYBRID COLUMNAR COMPRESSION.Which mainly Reduces no of IO's

6).Uses Infiband network which has much throughput than conventional storage networks.

7).Exadata cell

• A Computer with CPU,Memory,Bus,disks,Host Channel Adapter.
• OS is Linux.
• Building Block for Storage.
• Houses Disk Storage and Exadata Storage Software Provided by Oracle.
• Add more cells for More Storage and More I/O Capacity.

8).The Database Server and Exadata Cells Communicate using Infiband Network.

9).Cell is mainly for storing Database Files.You can use it for storing Business Files using Database File System.

10).All the software's are preinstalled.Oracle Enterprise Linux is used as OS for Database server.

How to find what process has loaded a particular DLL. ?

You may hit this when cleaning up Oracle Installation on Windows box.Sometimes it may happen that few files (eg.OCI.DLL) may not get deleted.When you try to delete it says "Access Denied."

As we don't know the process which has loaded this DLL.First we need to find the process and kill it.

Following command can be used to find the process which loads a particular dll.

tasklist /m

Eg..,

C:\Documents and Settings>tasklist /m en-US.dll

Image Name PID Modules
========================= ======
chrome.exe 5976 en-US.dll
chrome.exe 4272 en-US.dll
chrome.exe 2932 en-US.dll
chrome.exe 2488 en-US.dll
chrome.exe 4456 en-US.dll
chrome.exe 3228 en-US.dll
chrome.exe 440 en-US.dll


This way we can find out the specific process and kill them without rebooting the server.

Alternatively,you can use tool like 'unlocker' .But may not be appropriate in production environment.