Aha – the DBA, Unix guy and myself sussed the IO problems we were having…the temporary tablespace datafiles were on a filesystem which was marked as a archive redo log area on the RAID array and were set up as a concatenated volume rather than a stripe….with obvious performance impact! It will take a while to organise the array reorganisation to fix the problem but it should boost performance since the current stats are showing that it’s about 50 times slower service times on that part of the array than the place the files are supposed to be at.
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 thought…that 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 mystery…more time on it next week.
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 I’ve needed to boost the RAM on my box to 2Gb to cope with my requirements.
I’ve currently got the main machine itself configured without any Oracle on it – just use that for MS Office stuff.
I’ve 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.
I’m currently working on a data warehouse – it’s 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 we’re expecting.
The team I’m working with on the warehouse is bright but not particularly experienced in DW so we’re 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.
I’m going to grab a few of the queries we’ve audited from the users and see how they plan out and what I find.
Had to happen in the end I guess!
I’m just getting used to all this blogging lark which everyone seems to raving about so I’m a bit of a novice – in fact I’m still trying to work out how it all hangs together – please bear with me!