Re: Oracle Database Performance Problem

prashant.kodagali_at_patni.com wrote:
> 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

Step 1: Patch to 9.2.0.6 or above.

Step 2: Find the offending statements and Explain Plan

         or autotrace

Step 3: Address specific issues by tuning parameters

HTH

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)