Oracle 9i Multiprocessor Tuning

From: Ben Rockwood (benr@cuddletech.com)
Date: Thu Nov 03 2005 - 20:29:43 EST


Hello Managers,

  Today my DBA calls me and is upset that a SQL INSERT was taking longer
than usual. Its a Oracle 9i database (dedicated server) on an E420R (4
procs, 4 gigs). I took a look at the system and it was pretty
peaceful. I asked her to do another insert and I watched. The system
went exactly 75% idle, I look at mpstat and sure enough she's tapping
out a single cpu but nothing more. IO (Fibre Channel to a 3510) was
almost dead quiet. Looks like an optimization problem to me.

  Never the less, why was a SQL INSERT acting single threaded? Does
anyone know enough about 9i tuning to know what the problem might be?
db_writer_processes is set to 1 which I think needs to change, but it
was the main oracle(SID) process that was looking for cpu, not the dbw
processes so I don't think thats it. I read up on the parallell_*
init.ora parameters but I'm not entirely sure that these apply outside
of an OPS/RAC setup so I don't wanna mislead my DBA.

  Part of the problem here is that I've only got a single DBA, she's a
Jr. DBA at best but doesn't admit it and won't let me look at the SQL
she's running to see if I can help. If the INSERT is chewing up CPU
but not IO its gotta be some nasty statement... reguardless, why is it
acting single threaded?

  Any pointers would be appreciated. Thanks.

  benr.
_______________________________________________
sunmanagers mailing list
sunmanagers@sunmanagers.org
http://www.sunmanagers.org/mailman/listinfo/sunmanagers



This archive was generated by hypermail 2.1.7 : Wed Apr 09 2008 - 23:34:39 EDT