HCC and CPU consumption

We tested the CPU consumption for HCC and traditional segments.
We run select sum(Col1) from table  under the 10046 trace for traditional table and its compresses copies. 10046 trace shows the time in microseconds. All segments were in the SGA in order to exclude physical IO. Original table size was 16g, the compressed size is

------------------- ------------------ -------

Table type           Compression ratio   CPU

------------------- ------------------ -------

Original table            1               1

Compress for OLTP         1.8             0.8

QL                        4.9             3.3

QH                       20               3.5

AL                       20               3.5

AH                       59              11  
----------------------------------------------
            
As you can see QL, QH, AL takes 3.3-3.5 more CPU cycles (CPU time).
But we see the significant compression ratio for HCC segments.
Therefore, if any select in traditional DB spend 90% time in IO wait and 10% time in CPU
then with 10-times compression it will spend 30% on the CPU and 90/10=9% in IO = 39% of original time.

The packing time depends on the compression ratio (the more compression ratio the more time to create HCC segment).

The unpacking time not depends on the compression ratio.

HCC algorithms are:

Query Low  - LZO
Query High - ZLIB
Archive Low - ZLIB
Archive High - bzip2

CPU performace comparison

For comparison purposes we tested some modern CPU (previous post) .
Main aim is to evaluate one core.

So, we wrote two bash scripts :


 time for ((i=0;i<10000000;i++)); do :; done

((i=0)); time while (( i<10000000 )); do ((i+=1)); done


Because both test give similar results I show only one column (timings in seconds):

---------------- -----------------------------------
                 for((i=0;i<10000000;i++));do :;done
---------------- -----------------------------------
SuperMicro                         52.38-53.85     
Intel Xeon X5675 3.07GHz          sys 3.17-3.30s   
                                                   
ODA                                58.22-60.07     
Intel Xeon X5675 3.07GHz          sys 3.32-3.51s   
                                                   
Exadata                            57.80-59.20     
Intel Xeon X5675 3.07GHz          sys 3.32-3.85s   
                                                   
T4 max-ipc                          138-149.11     
2824MHz                           sys 0.005-0.012s 
                                                   
T4 max-throughput                   144.78-145.30  
2824MHz                           sys 0.004s       
                                                   
Sun SPARC Enterprise M4000          161.78         
System clock frequency: 1012 MHz  sys 0.008s       
Run MHz 2150                                       
                                                   
SunFire 240                         290.25         
SUNW,UltraSPARC-IIIi              sys 0.70s        
1503 MHz                                           
                                                   
IBM JS22 POWER6 4 ГГц               227.30         
IBM,                              sys 6.672s       
frequency 4005000000 Processor Speed               
-----------------------------------------------------

Full table here.

                                                   
Conclusions:
- we cannot see the difference between T4 max-ipc and max-throughput modes.
- T4 slowly than Intel Xeon 5675 approximately 2.5-3 times

ORA-01665: control file is not a standby control file

Today was the task : open physical standby database in read-write mode, do the write test on it and revert it back to physical standby.

I enabled flashback database on standby, open it and flashed back.
But after flashback the standby don't want to back to its standby state:

RMAN> flashback database to restore point yu1;

Starting flashback at 13.07.2012 07:19:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished flashback at 13.07.2012 07:19:29
 

...

RMAN> sql 'alter database mount standby database';

using target database control file instead of recovery catalog
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 07/13/2012 07:21:45
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01665: control file is not a standby control file

The solution for this problem is:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY DATABASE;

FAL[server]: DGID from FAL client not in Data Guard configuration

Creating another DataGuard I obtained new message in alert.log, which I never see before:

FAL[server]: DGID from FAL client not in Data Guard configuration

Obviously it is my mistake: the typo error in the log_archive_config parameter.

In one DB:
log_archive_config       = "DG_CONFIG=(p198,p199)"

In other DB
log_archive_config       = "DG_CONFIG=(f198,f199)"

After correction log_archive_config the error message disappeared.