Oracle Database Performance Problem

From: <prashant.kodagali_at_patni.com>
Date: 26 Aug 2005 07:59:05 -0700
Message-ID: <1125068345.214351.27550@g14g2000cwa.googlegroups.com>

Hi,
My Oracle database version is 9.2.0.1.0 on windows 2003 server. I am facing performance issue when user logs in to the application or any write operation in oracle database. The avg disc queue write length on windows server goes very high and it takes few minutes to login into aplication. And other user also experience performance problem.

I checked the tablsespace consumption, it is as follows(tablespaces with ITG are used by application):
Tablespace Name KBytes Used Free Used%
---------------- --------- --------- ------------ ------

DRSYS               20,480     9,920       10,560   48.4
INDX                25,600        64       25,536     .3
ITG_CLOB           307,200    69,760      237,440   22.7
ITG_DATA           204,800   153,664       51,136   75.0
ITG_IDX            512,000   284,480      227,520   55.6
ITG_TEMP           204,800        64      204,736     .0
SYSTEM             409,600   400,384        9,216   97.8
TOOLS               10,240        64       10,176     .6
UNDOTBS1           271,360    19,776      251,584    7.3
USERS               25,600        64       25,536     .3


Tablespace Name     KBytes      Used         Free   Used%


---------------- --------- --------- ------------ ------

XDB                 39,040    38,848          192   99.5
                 --------- --------- ------------
sum              2,030,720   977,088    1,053,632

I need to increase any tablespace size?

The memory settings are (database server has 3.75 GB RAM) Shared Pool 768 MB
Buffer Cache 480 MB
Large Pool 200 MB
Java Pool 32 MB
SGA max size 1497.527 MB
Aggregate PGA 196 MB

are these settings ok?

Or there can be some other problem.

Thanks
Prashant Received on Fri Aug 26 2005 - 09:59:05 CDT