Some tuning advise
in [Oracle]
|
From: Eric von Horst on 22 Oct 2007 09:02 Hi, I need some general advise on tuning. I am responsible for an third-party application that uses an Oracle 9.2.0 database on 2 CPU HP-UX11.11 with 4Gb mem Apparently this database has not been tuned/adapted in any way by the vendor. (a long time ago I was an Oracle DBA but I left the Oracle plane at Oracle 7.3) Anyway, the application is an event system that receives about 5000 to 10.000 events per day (not an enormous amount for an Oracle db). The events in the db can be viewed by a dedicated client that runs on a Pc. This client starts a connection to the DB and keeps the connection open. I did some testing an no additional Oracle processes were created by the clients once they were running. The DB_BLOCK_BUFFERS = 2000 and SHARED_POOL_SIZE = 24000000, LOG_BUFFER = 65536 What I would like to do is to increase the number of db writers to 1 per CPU (thus 2). Also, the SORT_AREA_SIZE is still set to the default value (64k). I would set it to 5Mb (a rough estimate is that one event is about 1024 bytes so 5Mb should allow to sort on about 1 day of events) I have only 10 clients and the system can handle the increase in memory The SORT_AREA_RETAINED_SIZE = SORT_AREA_SIZE since the client keeps the connection open and thus there is no reason to free the sort memory after the sorting operation. The HASH_AREA_SIZE = 1.5*SORT_AREA_SIZE (read this in a DBA forum somewhere) Any other tips or advise? I don't expect miracles in performance gain but I convinced the performance can be better since the config of the default is db is really basic) With kind regards, Eric
From: fitzjarrell on 22 Oct 2007 11:10 On Oct 22, 8:02 am, Eric von Horst <z80vsvi...(a)hotmail.com> wrote: > Hi, > > I need some general advise on tuning. > > I am responsible for an third-party application that uses an Oracle > 9.2.0 database on 2 CPU HP-UX11.11 with 4Gb mem > Apparently this database has not been tuned/adapted in any way by the > vendor. > (a long time ago I was an Oracle DBA but I left the Oracle plane at > Oracle 7.3) > > Anyway, the application is an event system that receives about 5000 to > 10.000 events per day (not an enormous amount for an Oracle db). > The events in the db can be viewed by a dedicated client that runs on > a Pc. This client starts a connection to the DB and keeps the > connection open. I did some testing an no additional Oracle processes > were created by the clients once they were running. > > The DB_BLOCK_BUFFERS = 2000 and SHARED_POOL_SIZE = 24000000, > LOG_BUFFER = 65536 > > What I would like to do is to increase the number of db writers to 1 > per CPU (thus 2). > Also, the SORT_AREA_SIZE is still set to the default value (64k). I > would set it to 5Mb (a rough estimate is that one event is about 1024 > bytes so 5Mb should allow to sort on about 1 day of events) I have > only 10 clients and the system can handle the increase in memory > The SORT_AREA_RETAINED_SIZE = SORT_AREA_SIZE since the client keeps > the connection open and thus there is no reason to free the sort > memory after the sorting operation. > The HASH_AREA_SIZE = 1.5*SORT_AREA_SIZE (read this in a DBA forum > somewhere) > > Any other tips or advise? I don't expect miracles in performance gain > but I convinced the performance can be better since the config of the > default is db is really basic) > > With kind regards, > > Eric Posting the full Oracle version is quite helpful, as 9.2.0 says nothing, really. Have you run Statspack reports on this database to at least get a feel for how it's responding? Have you traced sessions to see if, in fact, there are areas needing attention? Until you have a basic picture of how this system responds to the load you can't expect anyone to know what to tell you. Run Statspack and post a report, or enable tracing and post the tkprof formatted output so we can see what this system is doing and, hopefully, provide some useful insight. David Fitzjarrell
From: R. Schierbeek on 22 Oct 2007 12:46 "Eric von Horst" <z80vsvic20(a)hotmail.com> wrote > Hi, > I need some general advise on tuning. > I am responsible for an third-party application that uses an Oracle > 9.2.0 database on 2 CPU HP-UX11.11 with 4Gb mem > Apparently this database has not been tuned/adapted in any way by the vendor. > (a long time ago I was an Oracle DBA but I left the Oracle plane at > Oracle 7.3) > > Anyway, the application is an event system that receives about 5000 to > 10.000 events per day (not an enormous amount for an Oracle db). > The events in the db can be viewed by a dedicated client that runs on > a Pc. This client starts a connection to the DB and keeps the > connection open. I did some testing an no additional Oracle processes > were created by the clients once they were running. > > The DB_BLOCK_BUFFERS = 2000 and SHARED_POOL_SIZE = 24000000, > LOG_BUFFER = 65536 > > What I would like to do is to increase the number of db writers to 1 per CPU (thus 2). > Also, the SORT_AREA_SIZE is still set to the default value (64k). I > would set it to 5Mb (a rough estimate is that one event is about 1024 > bytes so 5Mb should allow to sort on about 1 day of events) I have > only 10 clients and the system can handle the increase in memory > The SORT_AREA_RETAINED_SIZE = SORT_AREA_SIZE since the client keeps > the connection open and thus there is no reason to free the sort > memory after the sorting operation. > The HASH_AREA_SIZE = 1.5*SORT_AREA_SIZE (read this in a DBA forum) > > Any other tips or advise? I don't expect miracles in performance gain > but I convinced the performance can be better since the config of the > default is db is really basic) > > With kind regards, > Eric Hello Eric, Since Oracle 9 the SORT_AREA_SIZE parameters are obsolete; if (and only if) PGA_AGGREGATE_TARGET is set. This is a smarter global parameter and worth setting if it's not already. Same goes for HASH_area_size. The pga-target is for all sessions summed( Aggregated) so 1 Gbyte is a normal value for a Linux system of maybe 10 Gbyte RAM. Ymmv though. Cheers, Roelof Schierbeek
From: Ian M on 22 Oct 2007 13:07 R. Schierbeek wrote: > "Eric von Horst" <z80vsvic20(a)hotmail.com> wrote >> Hi, >> I need some general advise on tuning. >> I am responsible for an third-party application that uses an Oracle >> 9.2.0 database on 2 CPU HP-UX11.11 with 4Gb mem >> Apparently this database has not been tuned/adapted in any way by the vendor. >> (a long time ago I was an Oracle DBA but I left the Oracle plane at >> Oracle 7.3) >> >> Anyway, the application is an event system that receives about 5000 to >> 10.000 events per day (not an enormous amount for an Oracle db). >> The events in the db can be viewed by a dedicated client that runs on >> a Pc. This client starts a connection to the DB and keeps the >> connection open. I did some testing an no additional Oracle processes >> were created by the clients once they were running. >> >> The DB_BLOCK_BUFFERS = 2000 and SHARED_POOL_SIZE = 24000000, >> LOG_BUFFER = 65536 >> >> What I would like to do is to increase the number of db writers to 1 per CPU (thus 2). >> Also, the SORT_AREA_SIZE is still set to the default value (64k). I >> would set it to 5Mb (a rough estimate is that one event is about 1024 >> bytes so 5Mb should allow to sort on about 1 day of events) I have >> only 10 clients and the system can handle the increase in memory >> The SORT_AREA_RETAINED_SIZE = SORT_AREA_SIZE since the client keeps >> the connection open and thus there is no reason to free the sort >> memory after the sorting operation. >> The HASH_AREA_SIZE = 1.5*SORT_AREA_SIZE (read this in a DBA forum) >> >> Any other tips or advise? I don't expect miracles in performance gain >> but I convinced the performance can be better since the config of the >> default is db is really basic) >> >> With kind regards, >> Eric > > Hello Eric, > Since Oracle 9 the SORT_AREA_SIZE parameters are obsolete; > if (and only if) PGA_AGGREGATE_TARGET is set. This > is a smarter global parameter and worth setting if it's not already. > Same goes for HASH_area_size. > The pga-target is for all sessions summed( Aggregated) so 1 Gbyte is a normal value > for a Linux system of maybe 10 Gbyte RAM. Ymmv though. > > Cheers, > Roelof Schierbeek > > > Hi Eric, Your setting of db_block_buffers cries foul but without knowing more about the database and the environment it is hard to identify the best value. I would add another 0 out of hand i.e. db_block_buffers = 20000 (just keep an eye on your glance output) db_block_buffers has also been updated oracle is pointing everyone towards db_cache_size but the basic rules are not that different. I would recommend taking statspack snapshots every 30 minutes on the periods you consider "slow" this is normally the best approach to start tuning. Outside of that welcome back to Oracle ;) Regards, Ian
From: Frank van Bortel on 22 Oct 2007 13:22
Eric von Horst wrote: > Hi, > > I need some general advise on tuning. Why? There's absolutely nothing in your post that leads to anything to be said (apart from the obvious: state your version including 5th decimal, start looking where anything might be wrong). You're not suffering from ODCTD, are you? -- Regards, Frank van Bortel Top-posting is one way to shut me up... |