Low performance and time responses

[Manuel G]

OK Charles, I?ll give you information tomorrow (in Spain is 21:55 and I don?t have system access now)

Thanks.

[Charles McCary]

Manuel,

Ok - but I probably won't look at it until July 5th sometime. Tomorrow is our Independence Holiday and I will be celebrating.

tx,

Charlie

[A. Clay Stephenson]

Hi Manuel,

In spite of everything you have said, I continue to think the problem lies in the code itself. In your case there is a fairly easy way of increasing memory without spending any money. I would simply halt the cluster, transfer the memory from machine B to machine A
and then manually start the application w/o MC/SG. If performance improves, you have your answer. In your case, I would set bufpages to a non-zero value and thus turn off dynamic buffer cache. I would start with a value of about 24 MB (bufpages=6144) and measure performance. Then increase it and see it the situation improves. Avoiding dynamic buffer cache allows you to alter just 1 variable at a time.
I have seen very few cases where even a badly tuned machine behaves as you describe (unless it is woefully inadequate for the task) but I have seen many cases where poorly constructed code does this and no amount of hardware/tuning will fix the problem.

Good luck and regards, Clay

[Bill Thorsteinson]

Your symptoms suggest that you have a table that has
grown to large to keep in memory that is being
tablescanned by queries.

Look at the v$sql view of the statements in the SGA
for expensive statements. (See attached script
for a start.)

For query optimization I would suggest enabling
the autotrace command. Your developers can
then analyze the queries by entering
set autotrace on
into an sqlplus session before running their query.
If they are developing the queries in Pro*C then
tkprof works well, but is more difficult.
Also look at running explain plans on the query.
These won't work well unless you have a reasonable
amount of date in the larger tables.

You should run analyze on the production tables
to help the optimizer to its job. Rapidly growing tables
should be analyzed every week or two. You can
build a script to analyze all the application tables and
analyze them. Run it it batch every Sunday.

More memory will help in the short term. Give Oracle as
many block buffers as you can without causing
HP-UX to swap.

[Magdi KAMAL]

Hi Manuel,

I would do the following :

1. dmc_mx_pct from 2 to 10.
2. Oracle SGA of 17 MB is really not enough for a 256 MB system ! I would give 100 MB to Oracle System Global Area.
3. Swap should be increased to 1024 MB ( four times physical memory ).

"vmstat -S "command gives the following columns :

Under procs :
- r : Process (r)running.(*)
- b : Process (b)blocked for a resource.(*)
- w : Process (w)wait for I/O operation.(*)

Under Memory :
- avm : Active virtuel pages.
- free: Size of the free list.

Under Pages :
- si : Process swaped in.(*)
- so : Process swaped out.(*)
- pi : Pages paged in.(*)
- po : Pages paged out.(*)
- fr : Pages freed per second.
- de : Anticipated short term memory shortfall.
- sr : Pages scanned by clock algorithm.

Under faults :
- in : Device interupptes per second.
- sy : System calls per second.
- cs : Context switching per second.

Under CPU :
- us : User time. (*)
- sy : System time.(*)
- id : CPU idle. (*)


May you have a look to the output of this command and give special attention under columns marked with a star (*).

4. Try to investigate wheather new network components had just been intergrated into the network topology. This is a major issue for network traffic and performances falldown ( new net card, new hub, new switch, new gateway or new router) or configuration of one of them !!!.

Hope this helps.

Magdi

[Manuel G]

Hello all:

- I?ve got other point, oracle archive log is started, could this cause a load problem?

- We have HP Mirror Disk Software too for mirroring disks in the external box (2+2 disks), could it be a load problem mirroring?

[Manuel G]

Hi:

- I?ve got some statistics from sar.
- I think we?ve got high I/O traffic from disks and processes remaina waiting for an answer making CPU overloading.
- I try to format results and make some graphs this weekend.
- Next week we are going to:

* Increase Oracle SGA.
* Continue reviewing code with tkprof trying to minimize accesses.
* Stop archive log (this thread writes a lot of information in disks).

- I attach statistics and results for lvdisplay.

- Charles, disks are not stripped (I think ...). Files ended in ".lv" are the results. base is where we have tablespaces, programa is where we have application binaries and redo log files, and internal is where we have /oracle partition. First and second one are in external box disks and third in the internal one.

Thank you very much for your help and have a great weekend.

[Manuel G]

Hi:

- I?ve got some statistics from sar (see attach).
- First review of them show us a high I/O traffic, it seems processes keep waiting from disks answers.

- This week we are going to:

* Increase SGA size.
* Continue reviewing code.
* Stop archive logging (it writes a lot of information on disks).

- I hope tell you more during week.

- Results of lvdisplay are on *.lv files. /base (base.lv) has tablespaces files, /prg (prog.lv) has binary application files and archive log files, /oracle (oracle.lv) is the oracle partition. First two are in the external box disks (mirrored 2+2 disks) and the third one is in the internal disk.

- We are formatting results in excel, I attach some graphs when we finish them.

- Thank you very much.

[Steve Slade]

Manual,

I assume that when you say 'turn off archive logging' that you mean turn off archive log mode, as you cannot stop the database from logging the changes it makes to its' tables, etc. This will change your ability to recover from any disk/software failiures in the future, as you will no longer be able to roll forward - and you will have to have more outages for cold backups. (You can add multiple log writers to increase through put).

I agree with the guys above that the problems definitely seems code related and not HPUX related. could you maybe show us the contents of your init.ora file - as maybe you have some Oracle options switched on which are taking resources that you do not need. (Or maybe a utlbstat/estat report with timed stats - of 10 -20 mins of bad period).

I noted, though is that you have re-indexed your system using tkprof as a guide - but this performance inhancement has deteriated. Are you using the cost or rule based optimiser. If you are using the cost based optimiser it sounds as if your statistics might be a bit old.

[Volker Borowski]

Trying a diffrent approach:

with 6 to 7 users causing trouble, there should be no need to go for MTS.

Now something to be cleared first.
- Is your problem related to the amount of data ?
- Is your problem related to the number of concurrent users.

You said, that responsetimes got better as you decreased the amount of data. That let's one think, your either have old statistics, or your application is doing too much tablescans.
Try to find Index-Solutions for bad statements. Do COMPUTE STATISTICS to your application tables.

Check if ONE user can work with the application, even when there is a lot of data inside the DB. If YES, you definately have a lock-problem in your application, when several users are working. You might need to restructure the application then.

Do not know if this helps.....
Volker