Performance Problem

From: Ralf Korell <ralf_at_hydrogen.emi.de>
Date: 1995/09/12
Message-ID: <433c54$b0v_at_unlisys.unlisys.net>#1/1


Hi, oraclists !

We got a big problem with the server in our customers site, which is (still) a HP 9000/887 with HP-UX 9.05 Sometimes our application waits *15 to 20 MINUTES* for a simple question. Therefore HP took lot of data during a period of four weeks an do a 'performance analysis'.
So far so good, much pretty pictures, much numbers ..... ONE thing seems to be interesting and I want to know, if anybody got some experiences with it :

cite :
"Global Queue Depth :
[..] the queue for interprocess communication (IPC) is significantly high [..] a view to processlist shows that oracleiks (*) processes seems idle to about 98% because there are waiting for IPC. The number of processes waiting for IPC reached 125 at a maximum. Although this value is high at every DB-Application, in a SAP-Environment with oracle this value is between 60 and 70. "

                                                      (*) iks is our SID !


This is the one and only point, which seems not normal.

Where can I look for the error ? - The customer is *ANGRY* ! All standard performance-tuning-tasks are performed, but it doesn't help. (standard from my point of view is optimizing indexes, avoid disc-contention, use of multiple tablespace.... Additional some (oh yes) fine-tuning of several statements, inclusive re-engeneering of some code to PL/SQL - Packages to reduce network traffic . And so on and so on.......)

Additional Information :


> How much memory ?

 192 MB
> Which version of Oracle ?

 7.1.4.2
> How large is SGA?

     135494008 Bytes
> How many tables are working with a concurrent basis ?

     about 120 Tables
> How many client processes are running ?

        about 160 clients.
        

> MTS?
MTS is not configured.

> init.ora?

	db_files = 50
	open_cursors = 800
	db_file_multiblock_read_count = 32 
	db_block_buffers = 18000 
	shared_pool_size = 92000000 
	log_checkpoint_interval = 10000
	processes = 200  
	dml_locks = 500 
	log_buffer = 163840     
	sequence_cache_entries = 100     
	sequence_cache_hash_buckets = 89   
	max_dump_file_size = 10         
	optimizer_mode=RULE
	spin_count=0


Any help would be greatly appreciated !!!!

Ralf   

-- 
               | Ralf Korell, Database Consultant
   ('<         | EMi GmbH,Karl-Marx-Str. 204,    12055 Berlin, Germany
  ,',)         | CIS:100601.3532_at_compuserve.com    FAX: +49 30 6814031
 ''<<          | EMAIL: korell_at_emi.de              VOC: +49 30 6814032
----""----------------------------------------------------------------
Received on Tue Sep 12 1995 - 00:00:00 CEST

Original text of this message