Ask The Real Tom

April 28, 2008

Tune a oracle database for performance

Filed under: oracle — Tags: , , , — admin @ 8:34 pm

Let me explain the situation first.

  • Only one Sun Solaris 8 Server
  • 12 CPU’s
  • 48 G RAM
  • 3 Databases (1 Tera, 150 G, 10 G)
  • 3 Applications (2 Java Jboss, 1 Forte)
  • CPU load at batch processing at night constantly about 60
  • SAN I/O throughput about 400 MB/s possible
  • 9i system statistics (not suitable for the current workload)

In other words an old overloaded system with slow cpu’s. Since buying new hardware and separate the databases from the applications is a bureacratic mission which takes a lot of time (about 1 year) and the time for the nightly batch processing get more tight, I am in the challenging situation to get the maximum out of this box.

So what I am going to try is to tune the system so it’s not so cpu bound any more. There is a lot of unused resources on the san. Some long running query are very subtle to changes and could make the application to take longer -> that mean the batch to fail!
One important thing to know is that many statement are hinted.

Possibility One

Change the system so it uses more I/O than CPU. But wait, more Logical I/O mean more CPU cycles are used. To continue this strategy will be necessary to check how the cpu time gets spent.
I will follow up on this idea on a later blog …. maybe

Possibility Two

Reduce general resource usage. This might be not that easy since many specialist and consultants already tuned the system quite well.
As I said many statements are hinted already and they perform ok I think with better statistics it would be possible to get more out of the unhinted statements.

So let put together single thoughts:

  • Many statements perform better with the NO_CPU_COSTING hint.
  • The newly gathered system statistics (not activated) on production show a much slower CPU (duing the increased workload)
  • “_optimizer_cost_model”=choose could be changed to io (because of the fact with the no_cpu_costing hint)
  • mreadtim < sreadtim, means that the current workload statistics are not used (mreadtim may me useless gathered on systems with san since it’s too small)
  • db_file_multiblock_read_count is set to 16 (but the is a MBRC value in the system statistics what make it not used I think)
  • nestet loop joins are less cpu intensive than hash joins -> but more logical I/O may make this assumption invalid
  • cost = (#srds * sreadtm + #mrds * mreadtm + #cpucycles / cpuspeed) / sreadtm (not sure if this is true… but anyway it just may if workload statistics are used)

So I decided to update the system statistics with values from production db and tune them a bit. Maybe tomorrow I will give an update the new values.

Powered by WordPress