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.
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
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)
- OPTIMIZER_INDEX_CACHING is set to 100
- OPTIMIZER_INDEX_COST_ADJ is set to 10
- 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.