Direct IO

The warehouse I’ve been working on has been struggling with IO performance for a while so I decided to poke my nose into what was going on with the setup of the database.

The warehouse uses a 32K block size and db_file_multiblock_read_count of 32 which should match the IO subsystem stripe size (?). I tried the trick of full table scanning a large table whilst running an extended trace (level 8) and it showed that indeed 32 blocks were being read in each IO request – thanks to Connor Mcdonald for the method for this – I’m sure I’ve seen it in other places too but that’s where I got it from.

A colleague of mine then passed me a URL for a page on Steve Adams site – http://www.ixora.com.au/tips/buffered_block_size.htm reading through this and the linked pages resulted in me checking various things.

OS is HP-UX which can support Direct IO – http://www.doag.org/pub/docs/sig/sap/2004-03/Dorer1.pdf

I Issued the mount command which showed that the filesystems were indeed mounted with convosync=direct and qio (Veritas Quick IO)

I checked the value of filesystemio_options in the Oracle database and it was set to asynch – Eureka I thoughtthat must be it.but after some simple back to back tests it had not improved the IO of a sample full table scan query at all.

Still a mysterymore time on it next week.

Virtual PC 2004

I use Microsoft Virtual PC 2004 to allow me to have multiple Oracle environments at home to test stuff out on seems to work quite well although Ive needed to boost the RAM on my box to 2Gb to cope with my requirements.

Ive currently got the main machine itself configured without any Oracle on it just use that for MS Office stuff.

Ive then set up 3 virtual machines:

Solaris 10 for x86 with Oracle RDBMS 10.1.0.3.0 I use this for Unix stuff, HTML Db and general db work

Windows XP Service Pack 2 with Oracle Application Server 10.1.2.0.0

Windows XP Service Pack 2 with Oracle RDBMS 10.1.0.4.0 database for the 10g AS installation

I can telnet into the Solaris box for most things which saves on needing to use the X Windows environment although that works inside the VM anyway.

I believe VMWare is a similar product but I use the MS product since it came with MS Action Pack which was a cheap(ish) way of getting licenced copies of all the MS software I use.

Cool stuff if you ask me.

Todays agenda!

Im currently working on a data warehouse its going reasonably well and offering lots of opportunities to learn new stuff. Todays activities will include a quick investigation into whether our users are getting star transformed queries as were expecting.

The team Im working with on the warehouse is bright but not particularly experienced in DW so were running up the learning curve real fast! There is a division of opinion at the moment as to the need to have queries running with star transformed execution plans my own opinion being that whilst it is a common occurrence in some DW, the most optimal plan for any given statement does not necessarily have to be a star transformed plan.

Im going to grab a few of the queries weve audited from the users and see how they plan out and what I find.