Tuesday, December 26

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!

Thursday, December 21

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)

Thursday, December 14

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.

Monday, December 11

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.

Sunday, December 10

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.

Tuesday, December 5

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.

Sunday, December 3

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 6400Dell Inspiron 8600
CPUIntel Core2 Duo 2.0GhzIntel Pentium M 1.4GHz
Memory2048Mb768Mb
Disk120Gb 5400rpm30Gb 5400rpm
GraphicsATI X1400 128MBNvidia Geforce 5250Go
Network54Mbit wifi + 100Mbit wired11Mbit Wifi + 100Mbit wired
OpticalDVD+RWDVD/CD+RW
Pointing deviceTouchpadTouchpad / poiting stick
Screen1680x1050 high gloss1680x1050
Weight2.8 kg3.1 kg
SizeA bit smallerQuite big
VMWare speedFastSlow



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.