Oracle Interface for Google Visualization API (external data source interface)

I have been looking for a while at the Google Visualization API and finally found a way to produce the required JSON to produce any graphs directly and indirectly from Oracle database. I will make a white paper available on how to do this. Checkout this link: http://lab4.oraperf.com/demogoogle.html. The procedure handles the SQL that the Google API uses (not for all functionality yet (pivot, offset, format are not yet supported).

More on this later.

Running a simple test

Here is a test that I did sometime ago. Should repeat it with SSD :)

How does one show the impact of long Seek times on the total I/O response time? I took a maxtor 200GB firewire harddrive (for my linux RAC test installation) and reconfigured the hard drive to have partitions of 2 GB on the outer tracks and inner tracks (accomplished with fdisk). Disk /dev/sda: 203.9 GB, 203927060480 bytes 255 heads, 63 sectors/track, 24792 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes DeviceBoot Start End Blocks Id System /dev/sda1 1 250 2008093+ 83 Linux /dev/sda2 251 24542 195125490 83 Linux /dev/sda3 24543 24792 2008125 83 Linux Then downloaded the Reader.c program from the website of Jonathan Lewis (didn't want to write my own program). The test was done with running two processes running against the partitions. The partition size is important to overcome caching issues on the disk (and the system buffer cache). I also choose raw partitions to overcome potential locking issues in the file system(s). Here are the results: Test 1 (run 2 processes reading from partition 1 doing a 1000 Random Reads each) + ./read /dev/sda1 R 8192 262144 1000 13 File name: /dev/sda1 Random or Serial: R Read Size: 8192 Boundary: 262144 Read Count: 1000 Rand Seed: 13 Descriptor: 3 + ./read /dev/sda1 R 8192 262144 1000 19 [root@linrac1 tmp]# File name: /dev/sda1 Random or Serial: R Read Size: 8192 Boundary: 262144 Read Count: 1000 Rand Seed: 19 Descriptor: 3 real 0m11.323s user 0m0.010s sys 0m0.030s real 0m11.536s user 0m0.000s sys 0m0.040s Now we run the same test against partition 3 (inside of the disk) + ./read /dev/sda3 R 8192 262144 1000 23 + ./read /dev/sda3 R 8192 262144 1000 29 File name: /dev/sda3 Random or Serial: R Read Size: 8192 Boundary: 262144 Read Count: 1000 Rand Seed: 29 Descriptor: 3 [root@linrac1 tmp]# File name: /dev/sda3 Random or Serial: R Read Size: 8192 Boundary: 262144 Read Count: 1000 Rand Seed: 23 Descriptor: 3 real 0m17.398s user 0m0.000s sys 0m0.010s real 0m17.482s user 0m0.000s sys 0m0.090s The last test (run 1 process against partition 1 and 1 process against partition 3) + ./read /dev/sda1 R 8192 262144 1000 33 File name: /dev/sda1 Random or Serial: R Read Size: 8192 Boundary: 262144 Read Count: 1000 Rand Seed: 33 Descriptor: 3 + ./read /dev/sda3 R 8192 262144 1000 39 [root@linrac1 tmp]# File name: /dev/sda3 Random or Serial: R Read Size: 8192 Boundary: 262144 Read Count: 1000 Rand Seed: 39 Descriptor: 3 real 0m32.673s user 0m0.010s sys 0m0.090s real 0m32.926s user 0m0.000s sys 0m0.070s So to summarize: Outside Partition 11.4 seconds for 2000 8K reads Inside Partition 17.4 seconds for 2000 8K reads (50 percent slower Inside/Outside Partition 32.7 seconds for 2000 8K reads (200 percent slower) Average read Outside 11.4 seconds / 2000 = 0.0057 seconds (5.7 ms) Average read Inside 17.4 seconds / 2000 = 0.0087 seconds (8.7 ms) Average read Inside/Out 32.7 seconds / 2000 = 0.0164 seconds (16.4 ms) So the total number of I/Os that we can do really depends on where the data is located on the disk. According to maxtor the average seek time for this disk is around 9.3 ms. It means that the full seek time will be around 16-18 ms. That seems to fit. Subtract the average outside time from the average inside/out (16.4 - 0.5) and we get 15.9 millisecond as may be 90 percent of the full seek time. So even for the simple ATA drives, it is important where the data is stored.

This is a picture taken by the Hubble telescope before it broke down. NASA is desperately trying to repair it. But this last picture shows the surface of URANUS. It has great detail. More detail will follow in a later blog.

OUG Scotland (making money while flying)

On Sunday september 10th I flew to Scotland to present at the Oracle Usergroup in Glasgow. I was aware of the new rules with carry on lugguage (well I thought I knew) and handed my carry on back over at the checkin counter and just double checked to see if the rules were still very strict. Turns out I was allowed to bring the bag on board, however it was too heavy and that is why it had to be checked in. Fine, I was not in a hurry so no problem. Oh, I flew Transavia and paid 2 euro (excluding tax) from this roundtrip ticket.

Got on board with 50 other passengers (plane was a 737-700, so many empty seats). Got to Glasgow Prestwick International Airport walked to the terminal building to get my lugguage, and of course it didn't show up. Filed a missing luguage report (that was a big challenge due to noise in the building and the accent of the lady asking questions), turns out there was another passenger missing his bag, that made me feel good :)

Turns out he was going to Glasgow also and helped me to find the train (no ticket needed) and when we arrived at the Central Train station in Glasgow he helped me to find the hotel. It was a beautiful day and many people were out shopping.

Checked in to the hotel and again had trouble understanding the girl at checkin, got to the room and ofcourse got on to the internet.

Monday arrives and I write my presentation in the morning as I have my presentation at 14:30. Decide to buy a clean shirt and make my way to the venue (Radisson hotel next to central station). But I can't find it. Get phone calls from the organisers who are worried (I am not) that I may miss the presentation slot. So I ask them to tell me the name of the road that the hotel is on and they can't. For the next 10 minutes I wander around Glasgow and then Doug Burns finally gives me permission to land at the hotel. Put on the new the shirt, did the presentation and then there was beer.

Oh and the lugguage didn't show until Tuesday afternoon (after my second presentation). Turns that Transavia pays 50 euro per day for the missing lugguage. That is at least 100 euro. Not bad if the ticket was costing 2 euro. Minus the price of the shirt, that I needed any way :-)

Miracle Director Training Program

It has been a hectic week. My wife got a job and is now working 32 hours a week, that took less then 2 weeks, and on her first day I was in Denmark with my good friend Mogens Norgaard from Miracle Denmark. He said to me that I should come and be part of the Miracle Director Training Program. Last week it was Thomas Presslie from Miracle Scotland who got the same treatment.

The training program is pretty though and Mogens took a picture of me this morning after I had slept a couple of hours on the couch in the office. Ofcourse one has to wake up if the first people get into the office: "Good Morning Anjo!" So I roll of the couch and go the yellow house and fire up the laptop at the big Oaktable (yes that famous table) and I start doing email.

So we are making plans for Miracle Benelux and we are planning some Oracle and SQL Server events in November and December. We will present some of the big names of the Oracle and SQL Server world. So keep your calender and budget available for these events :)

More info on these events later .......

(I got some problems posting the picture, will try to figure it out later)

Back again

It took a while for a new post to show up on this blog, but I am back. The reason for that is that I quit my comfy job at Symantec and will startup Miracle Benelux together with Mogens Norgaard. So now I can post what ever I want without having some corporate lawyer looking over my shoulder.

So why quit? I tried to make a difference for close to 5 years and after the 3rd reorg in the last 3 years I realized that I wouldn't be able to make the product and the difference that I wanted. I am used to change and change is good if progress is made to achieve your goal. But I felt that all the changes where actually causing us to loose focus on what needed to be done. And working for a large company on a product that has less than 2 percent revenue impact ............

So is the new thing going to be easy? Of course not, but I will have fun trying!

Will keep you posted on the progress!

Running a simple test

How does one show the impact of long Seek times on the total I/O response time? I took a maxtor 200GB firewire harddrive (for my linux RAC test installation) and reconfigured the hard drive to have partitions of 2 GB on the outer tracks and inner tracks (accomplished with fdisk).

Disk /dev/sda: 203.9 GB, 203927060480 bytes
255 heads, 63 sectors/track, 24792 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

DeviceBoot Start End Blocks Id System
/dev/sda1 1 250 2008093+ 83 Linux
/dev/sda2 251 24542 195125490 83 Linux
/dev/sda3 24543 24792 2008125 83 Linux


Then downloaded the Reader.c program from the website of Jonathan Lewis (didn't want to write my own program). The test was done with running two processes running against the partitions. The partition size is important to overcome caching issues on the disk (and the system buffer cache). I also choose raw partitions to overcome potential locking issues in the file system(s).

Here are the results:

Test 1 (run 2 processes reading from partition 1 doing a 1000 Random Reads each)

+ ./read /dev/sda1 R 8192 262144 1000 13
File name: /dev/sda1
Random or Serial: R
Read Size: 8192
Boundary: 262144
Read Count: 1000
Rand Seed: 13
Descriptor: 3
+ ./read /dev/sda1 R 8192 262144 1000 19
[root@linrac1 tmp]# File name: /dev/sda1
Random or Serial: R
Read Size: 8192
Boundary: 262144
Read Count: 1000
Rand Seed: 19
Descriptor: 3

real 0m11.323s
user 0m0.010s
sys 0m0.030s

real 0m11.536s
user 0m0.000s
sys 0m0.040s

Now we run the same test against partition 3 (inside of the disk)
+ ./read /dev/sda3 R 8192 262144 1000 23
+ ./read /dev/sda3 R 8192 262144 1000 29
File name: /dev/sda3
Random or Serial: R
Read Size: 8192
Boundary: 262144
Read Count: 1000
Rand Seed: 29
Descriptor: 3
[root@linrac1 tmp]# File name: /dev/sda3
Random or Serial: R
Read Size: 8192
Boundary: 262144
Read Count: 1000
Rand Seed: 23
Descriptor: 3

real 0m17.398s
user 0m0.000s
sys 0m0.010s

real 0m17.482s
user 0m0.000s
sys 0m0.090s

The last test (run 1 process against partition 1 and 1 process against partition 3)
+ ./read /dev/sda1 R 8192 262144 1000 33
File name: /dev/sda1
Random or Serial: R
Read Size: 8192
Boundary: 262144
Read Count: 1000
Rand Seed: 33
Descriptor: 3
+ ./read /dev/sda3 R 8192 262144 1000 39
[root@linrac1 tmp]# File name: /dev/sda3
Random or Serial: R
Read Size: 8192
Boundary: 262144
Read Count: 1000
Rand Seed: 39
Descriptor: 3

real 0m32.673s
user 0m0.010s
sys 0m0.090s

real 0m32.926s
user 0m0.000s
sys 0m0.070s

So to summarize:

Outside Partition 11.4 seconds for 2000 8K reads
Inside Partition 17.4 seconds for 2000 8K reads (50 percent slower
Inside/Outside Partition 32.7 seconds for 2000 8K reads (200 percent slower)

Average read Outside 11.4 seconds / 2000 = 0.0057 seconds (5.7 ms)
Average read Inside 17.4 seconds / 2000 = 0.0087 seconds (8.7 ms)
Average read Inside/Out 32.7 seconds / 2000 = 0.0164 seconds (16.4 ms)

So the total number of I/Os that we can do really depends on where the data is located on the disk. According to maxtor the average seek time for this disk is around 9.3 ms. It means that the full seek time will be around 16-18 ms. That seems to fit. Subtract the average outside time from the average inside/out (16.4 - 0.5) and we get 15.9 millisecond as may be 90 percent of the full seek time.

So even for the simple ATA drives, it is important where the data is stored.