Merry Christmas and VMWare 6

.. and all that. :)

Been chilling in Sweden for the last few days, had a terrible flight back with a 2 hour delay due to fog over south east england and then a very annoying crying baby on the plane.
We even had a distubance in the queue to the gate, some guy from the middle east was making a scene and started threatening a few other people for bumping in to him. I have to admit, I knew it wasn't a good idea to mess with airport security, but these guys where hard. The scene was quite funny, when they asked him to come with them and he started questioning them they said something in the lines of "Sir, you have 2 seconds to comply or we will remove you with force." We didn't seem him again after that.
Anyway, got socks and a few good CD's for Christmas. Always good with new music.
I installed VMWare Workstation 6 beta yesterday and had a play around with that. A word of warning, although it has some cool features and is meant to be a bit faster the beta version is a debug build and in classic vmware-style the debug builds are slow... really slow. Booting CentOS 4 takes at least 50% longer. The "freeze bug" when starting a VM in Vista is fixed though.

All jolly good fun!

Using Solaris projects with Oracle

In older versions of Solaris (9 and earlier) we used to set the system resource control in /etc/system, in Solaris 10 and later we do it in a neater way. No more reboots or mocking about, all online, even while Oracle is running.
It's all about projects now.
I've just finished installing 10gR2 under Solaris Express Community Release Build 54 (long name ey?) under VMware on my new laptop.
Example project:

root@solvm[~]$ projadd -U oracle -K \
"project.max-shm-memory=(priv,4g,deny);\
project.max-sem-nsems=(priv,256,deny);\
project.max-sem-ids=(priv,100,deny);\
project.max-shm-ids=(priv,100,deny)" oraproj

-- Lets review our project

root@solvm[~]$ su - oracle
Sun Microsystems Inc.   SunOS 5.11      snv_54  October 2007
oracle@solvm[~]$ projects
default oraproj
oracle@solvm[~]$ projects -l oraproj
oraproj
        projid : 101
        comment: ""
        users  : oracle
        groups : (none)
        attribs: project.max-sem-ids=(priv,100,deny)
                 project.max-sem-nsems=(priv,256,deny)
                 project.max-shm-ids=(priv,100,deny)
                 project.max-shm-memory=(priv,4294967296,deny)
oracle@solvm[~]$

It's described slightly different in the Oracle installation manual but I'd say my way is slightly better. prctl is legacy. You can use usermod -K project=oraproj oracle to set the new project as the users default project (not really necessary)

Manual operations in an automatic SGA database

Oracle 10g introduced the nifty feature of automatic SGA, simply set the parameter sga_target and sga_max_size to the maximum size you can afford and Oracle will do the rest. But we still have a few manual settings, say you want a tablespace with a non-default block size (also a new feature) than the database default and you create, say a 4k block size, tablespace in a 8k database and you allocate a db_4k_cache_size for that. How do Oracle allocate that memory?

Test:

SQL> select name,value from v$parameter
       where name like 'sga_target'
     /

NAME            VALUE
--------------- --------------------
sga_target      599785472

SQL> select name,round(bytes/1024/1024) M from v$sgainfo;

NAME                                              M
---------------------------------------- ----------
Fixed SGA Size                                    2
Redo Buffers                                      6
Buffer Cache Size                               416
Shared Pool Size                                140
Large Pool Size                                   4
Java Pool Size                                    4
Streams Pool Size                                 0
Granule Size                                      4
Maximum SGA Size                                572
Startup overhead in Shared Pool                  68
Free SGA Memory Available                         0

11 rows selected.

SQL> alter system set db_4k_cache_size=4M;

System altered.

SQL>  alter system set db_4k_cache_size=0;

System altered.

-- What actually happend here, from what 
-- did Oracle steal those 4Mb
-- Let's query the v$sga_resize_ops view to see that was done.

SQL> select parameter,oper_type,initial_size,target_size 
       from v$sga_resize_ops 
       where start_time>sysdate-1
     /

PARAMETER        OPER_TYPE     INITIAL_SIZE TARGET_SIZE
---------------- ------------- ------------ -----------
db_cache_size    SHRINK           436207616   427819008
db_4k_cache_size GROW                     0     8388608
db_4k_cache_size SHRINK             8388608           0
db_cache_size    GROW             427819008   436207616

SQL>

Ok, we can see that Oracle shrank the db_cache_size with the requested size and allowed the db_4k_cache_size to grow and when reset back to zero it freed the memory and grew the db_cache_size. Oracle could just as well have allocated the memory to the large_pool if the automatic SGA engine thought that would make more sense.
I've got the last 7 days worth for resize ops in my weekly database report that is e-mailed to me every Monday morning, it's definitely worth keeping an eye on whats going on. Sometimes it's just easier to configure the SGA manually.

Testing iSCSI

I've been reading a lot about iSCSI lately, Sun is pushing the ZFS and iSCSI integration in the latest release of Open Solaris and it's the new hot thing in the Oracle community. I remember testing the Linux target ("server side") a few years ago and back then it was quite flaky, far from production ready.
This time around it seems pretty solid.

I just used the very simple instrucions here to compile the iscsi kernel module in Ubuntu, I created two "zero files" using dd (one for my VMware Linux test and one for my Vista laptop) and added them as LUN's to the /etc/ietd.conf config file.

# cat /etc/ietd.conf
Target starbuck
        Lun 0 Path=/u03/starbuck_lun0.dat,Type=fileio
Target oravm
        Lun 0 Path=/u03/oravm_lun0.dat,Type=fileio

The installation in CentOS 4 was simple, do a yum -y install iscsi-initiator-tools and set the discovery host and initiator name in /etc/iscsi.conf. Starting /etc/init.d/iscsi gives:

iscsi-sfnet: Loading iscsi_sfnet version 4:0.1.11-3
iscsi-sfnet: Control device major number 254
iscsi-sfnet:host3: Session established
scsi3 : SFNet iSCSI driver
  Vendor: IET       Model: VIRTUAL-DISK      Rev: 0
  Type:   Direct-Access                      ANSI SCSI revision: 04
SCSI device sdd: 524288 512-byte hdwr sectors (268 MB)
SCSI device sdd: drive cache: write through
SCSI device sdd: 524288 512-byte hdwr sectors (268 MB)
SCSI device sdd: drive cache: write through
 sdd: unknown partition table
Attached scsi disk sdd at scsi3, channel 0, id 0, lun 0

Create a partition and file system on the whole disk with

printf "n\np\n1\n\n\nw\n" | fdisk /dev/sdd && mkfs.ext3 /dev/sdd1

Done!

Conneting the iSCSI disk in Vista was pretty straight forward as well, add the iSCSI discovery IP and do a refresh of your targets.

Read about Oracle RAC and iSCSI in this article.

Monitoring RMAN I/O performance

When trying to cut rman backup times there are a lot of parameters to try to tune, database settings, storage subsystem settings, file system settings etc etc.
It can sometimes be hard to remember all the results you get, luckily Oracle keeps them for you. Just query the v$rman_backup_job_details view for the results.

Example:

column END_TIME format a16
column START_TIME format a16
column COMMAND_ID format a20

select 
 command_id, 
 to_char(start_time,'YYMMDD HH24:MI') start_time,
 to_char(end_time,'YYMMDD HH24:MI') end_time,
 round(input_bytes/1024/1024) backup_size,
 round(INPUT_BYTES_PER_SEC/1024/1024) in_mb_sec,
 round(OUTPUT_BYTES_PER_SEC/1024/1024) out_mb_sec 
from 
 v$rman_backup_job_details 
where
 start_time > sysdate - 7
order 
 by start_time
/

COMMAND_ID           START_TIME       END_TIME         BACKUP_SIZE  IN_MB_SEC OUT_MB_SEC
-------------------- ---------------- ---------------- ----------- ---------- ----------
2006-12-10T00:34:25  061210 00:35     061210 00:45           13722         22         10
2006-12-10T23:17:48  061210 23:17     061210 23:26           13784         26         12

Oracle ASM instance startup script

Tought I'd publish my Oracle ASM instance sysv (Redhat/CentOS/Unbreakable etc) init script. The default oracleasm script provided by asmlib only prepares the disks available to ASM, you also need to start the basic instance for ASM. I've set the ASM instance to start just after oracleasm and to be killed just before it. Make sure your normal Oracle instance startup scripts are started in the correct order, i.e. after this script for startup and before this script for shutdown.
It reads the default /etc/sysconfig/oracle config file, if nothing it sets it goes to defaults and should be able to start the instance. If you don't have the ASM instance name set in /etc/sysconfig/oracle (as ASM_SID=FOO) and your instance name is not +ASM you may have to edit the script to change the default ASM SID.

Download the script here. Copy the script to /etc/init.d/ and use chkconfig --add oraasm to add it to startup. Do a chkconfig --list oraasm to verify that the script was added the runlevels you use.

Please report any bugs, problems or suggestions for the script to me.

Date range generation in Oracle

Another interesting question someone asked in the IRC #oracle chat.
-How do I create a table with all dates from 2000 until today in it?
It's rather simple in plain SQL, no PL/SQL or scripting required.

SQL> create table date1 (d date);

Table created.

SQL> insert 
  into date1(d) 
select 
  to_date('00-01-01','YY-DD-MM') + level 
from 
  dual 
where 
  (to_date('00-01-01','YY-DD-MM')+level) < sysdate 
connect 
  by level<=1000000
/

2530 rows created.

SQL> select min(d) s, max(d) e from date1;

S         E
--------- ---------
02-JAN-00 05-DEC-06

SQL>

Oracle trace event 10132

The new "in" Oracle trace event seems to be 10132, it's pretty cool. Most Oracle DBA's and developers have probably at least heard about trace levels 10046 and 10053.
10132 is a different from the classic two in the sense that it is is really easy for anyone to intepret, the 10132 trace event gives you a full execution plan and optimizer settings in the udump (user_dump_dest) directory.
It's an easy thing to throw in a script or even as a logon trigger when troubleshooting query problems. Think hibernate or other (weird) ORM tool.

Lets take a look:

SQL> alter session set events
  2  '10132 trace name context forever, level 12';

Session altered.

SQL> select e.ename,d.dname from dept d, emp e where
  2  d.deptno=e.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
...
MILLER     ACCOUNTING

14 rows selected.

SQL>

And the trace we get looks like this.

Laptops - 3 years ago and today

So I figured it was time to get a new laptop, my old trustworthy Dell Inspiron 8600 has done a great job but it's starting to show it's age, it's actually 3 years old on the day next week (Yes, remembering the exact date you bought your last laptop is weird, I know). My 8600 had a few scratches and marks but it still looks pretty ok. Performance wise it's not to bad, I've been running Open Solaris for the last 18 months and it's been working great, felt fast and snappy. But when it comes to heavy multi tasking, Solaris container and VMWare it's not really keeping up with times.
After browsing the laptop market it was quite clear that getting a Core2 machine was the best option if I wanted performance. I was very tempted to get a cool Acer Ferrari 4006, the price is pretty great now when the 5000-series is just around the corner, but the AMD ML-40 benchmarks put me off, the Core2 totally trash it. Since my old Dell has been working great I went and got a new Dell to replace it. Although I was looking for a smaller machine this time I actually went for pretty much the exact replacement for the 8600, the new Dell Inspiron 6400. Funny thing, the price of the new machine turned out to be pretty much exactly the same as I payed for my old machine.

	Dell Inspiron 6400	Dell Inspiron 8600
CPU	Intel Core2 Duo 2.0Ghz	Intel Pentium M 1.4GHz
Memory	2048Mb	768Mb
Disk	120Gb 5400rpm	30Gb 5400rpm
Graphics	ATI X1400 128MB	Nvidia Geforce 5250Go
Network	54Mbit wifi + 100Mbit wired	11Mbit Wifi + 100Mbit wired
Optical	DVD+RW	DVD/CD+RW
Pointing device	Touchpad	Touchpad / poiting stick
Screen	1680x1050 high gloss	1680x1050
Weight	2.8 kg	3.1 kg
Size	A bit smaller	Quite big
VMWare speed	Fast	Slow

It's quite nice to be able to boot up a CentOS 4.4 running 10gR2 and still have great performance. Both in the VM and in the host OS.
The one thing I don't like is that the 6400 don't have the pointing stick thingy, just the touch pad. But I'm getting used to it.