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

You really won't be able to answer that without first looking into the total memory usage on your server. You don't mention what additional software is running on this server, so it is difficult to say what could be the cause of your problem. It is possible you're paging to disk on each login due to excessive memory consumption. Increasing tablespace sizes won't help you with that situation. And, you can't prove you have tablespace issues to begin with. You haven't reported any 'cannot extend' errors listing a specific tablespace, so I'd look elsewhere. You also do not state how your datafiles/controlfiles/logfiles are stored, or whether you have more than one disk to contain them. Placing all of your database files on one disk is a sure recipe for severe performance problems.

I am not familiar with Windows 2003 server and its memory management so I cannot comment on whether or not the 3.75 gig of RAM is being fully utilized. On earlier versions of Windows any memory over 3 gig was 'iffy' and depended upon the kernel switches used to boot the server. This is where your analysis of total memory usage is necessary. If you haven't yet done so you should probably download the Windows 2003 Resource Kit:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en

Run Memmonitor.exe and see what it reports. You may find you're suffering from a lack of available resources resulting in paging to disk, which, in turn, is causing your disk write issues and your lagging login response.

David Fitzjarrell Received on Fri Aug 26 2005 - 11:06:52 CDT