Qi Yang's Workbook

Exadata's EHCC and NoSQL database

2011-12-29T15:24:00.002-05:00

Among all the good feature Exadata have, EHCC ( Exadata Hybrid Columnar Compression) comes as the top of my list. Exadata cell introduce a new storage concept called CU (Compression Unit). The CU is sitting on top of regular oracle data blocks. Inside the CU, the data is organized by column (not by row). To put in BigData term, it is cloumn oriented store. This way, Exadata archived high compression ratio which is not available in traditional row oriented database. In my test case, the EHCC compression ratio can easily hit 10 and sometime even 20. The high compression ratio plus other feature Exadata have give exadata an edge to Big Data problem. Think about your I/O requirement is just reduced by 1 order after the compression for same amount of data.

Another trend in today's Database world is what is called NoSQL database. It's targeting to solve BigData problem. It's very hard for RDBMS to hadle data at perabyte level. Some of open source product are coming into play like Cassandra, MangoDB and HBase. To me this NoSQL databases are just another approach to tackle the Big Data problem although they are fundamentally different with RDBMS.

So there are two approaches to solve the same problem. It's interesting to see how the products changes in both school for wide adoption.

11g Memory Management

2011-11-09T16:56:00.003-05:00

AMM

Automatic Memory Management is introduced in 11g. MMAN process will manage the size of PGA + SGA as needed once database parameter MEMORY_TARGET is set.

On Linux Platform don't enable AMM if you use Huge pages. Another error you often will get is

'ORA-00845: MEMORY_TARGET not supported on this system'

Most of time this is because you don't have enough size of /dev/shm (Yes, Oracle is using memory map if you enable AMM.) You can change the /dev/shm size by editing /etc/fstab file or remount it.

 tmpfs                   /dev/shm                tmpfs   size=xxxxm       0 0

 mount -t tmpfs -o size=5G  tmpfs /dev/shm

ASMM

Automatic Shared Memory Management is introduced in 10g. MMON and MMAN processes manage the size of SGA once database parameter SGA_TARGET is set.

If you are running RAC, test thoroughly before you enable ASMM. It may give you some trouble during memory adjusting process.

11g R2 RAC SCAN and VIP

2011-09-09T23:54:00.001-04:00

VIP is for quick response if one node crashed. VIP is on node level.
SCAN will allow RAC to change (Adding Node, Remove Node) without worrying about client TNS update. It is cluster level VIP.
When client connect to SCAN Listener, it will redirect the connection to a local listener on VIP. In this sense, SCAN listener acts like old remote listener. It didn't start server process itself. but hand the client connection to local listener which will start a server process.

11g ASM, DB use Hugepages Memory on Linux

2011-09-09T23:22:00.000-04:00

Steps

Allocate memory for hugepage memory

/etc/sysctl.conf - vm.nr_hugepages
cat /proc/meminfo to verify

Allow ASM/DB user to lock enough memory

/etc/security/limits.conf - oracle soft memlock 12582912
For ASM - add ulimit -l 12582912 into /etc/init.d/ohasd
cat /proc/(ASM,DB)process-id/limits to verify

Disable DB/ASM AMM

alter system set memory_target=0
ipcs -m to verify the allocated share memory equals SGA size

Reference:

http://yong321.freeshell.org/oranotes/HugePages.txt

SQL avg/max on Oracle Interval Data Type

2011-08-09T10:24:00.001-04:00

Recently someone was asking me how to do avg/max on Oracle data type interval. After do a little bit of googling and reading. Finally I came up with the solution. Although it's not elegant, it is good enough to do the job.

Here is the SQL query



desc table1

Name                Null     Type          

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

CREATED_ON                   TIMESTAMP(6)  

MODIFIED_ON                  TIMESTAMP(6)  

...



select extract(day from numtodsinterval(avg(sysdate+(modified_on-created_on)-sysdate)*86400, 'second')) as days,

extract(minute from numtodsinterval(avg(sysdate+(modified_on-created_on)-sysdate), 'day'))  as minutes

from table1

where modified_on != created_on;

In Oracle Database, timestamp - timestamp date type will return interval data type. As avg/max can't function on interval directly, I did a a little data conversion as you can see in the query.