Home » RDBMS Server » Performance Tuning » performance of database , please help (oracle 9i, 9.2.0.1, windows)
performance of database , please help [message #329140] Tue, 24 June 2008 04:27 Go to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi friends,

I am facing some problems like buffer busy waits, & average redo log write time in our database. Attached here is statspack report.
Can change in log_buffer size reduce average redo log write time?
If I increase initrans parameter for table which is facing buffer busy waits, will it OK?
I have changed DB_FILE_MULTIBLOCK_READ_COUNT parameter from 8 to 16 to spead up full table scans, but I didn't see any improvement.
Main table accessed by all is call_req nearly 4 lacs records.
Server memory : 4 GB
oracle memory : 1 GB
Kindly suggest.

[Updated on: Tue, 24 June 2008 06:19]

Report message to a moderator

Re: performance of database , please help [message #329223 is a reply to message #329140] Tue, 24 June 2008 10:38 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Any help or suggestion?
Re: performance of database , please help [message #329228 is a reply to message #329140] Tue, 24 June 2008 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
One does NOT tune any application at the database level.
You tune one SQL statement at a time.
Identify slow SQL statements & figure out how to make them faster.
Re: performance of database , please help [message #329229 is a reply to message #329228] Tue, 24 June 2008 10:54 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
thanks for your reply.
What abt parameters I have mentioned? Does increase in size of log_buffer decrease redo log write time?
Re: performance of database , please help [message #329233 is a reply to message #329140] Tue, 24 June 2008 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Does increase in size of log_buffer decrease redo log write time?
What do YOUR benchmark tests show you?

Stop & THINK!
If there was some magical correct value for any/every initora parameter, don't you think Oracle would simply hardcode that value & preclude user from changing.

The optimal value depends upon YOUR hardware & application.
Re: performance of database , please help [message #329244 is a reply to message #329229] Tue, 24 June 2008 12:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Does increase in size of log_buffer decrease redo log write time?
May be or may not be. Worse, it may cause you other unwanted issues.
Very rarely, the log_buffer needs to be set more than 1mb. It depends on nature of operations. Higher log_buffer size may be good
for bulk/DSS and may be bad for transaction processing.
Look into the offending statement.
Re: performance of database , please help [message #329598 is a reply to message #329244] Thu, 26 June 2008 00:05 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,

In the start of the day redo buffer allocation retries ,redo log space wait time are both zero in sysstat.
But as time goes, values of both increases. At the end of the day, redo buffer allocation retries becomes 4 & redo log space wait time becomes 42 approx.
I have read that to rdeuce redo log space wait time, increase log_buffer size.Is it OK?
Re: performance of database , please help [message #330158 is a reply to message #329598] Fri, 27 June 2008 12:53 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you think that wait time of 42 seconds per working day is killing your performance?

IMHO your problem are:
1. High CPU usage caused by using cursor_sharing = force (or similar) instead of using bind variables (and high number of soft parses as well).
2. Inefficient queries (hign number of block accesses per execution) probably caused by accessing data by non-selective indexes ( look at the queries in STATSPACK report and try tuning these queries).

Michael
Previous Topic: Unable to Understand Explain Plan
Next Topic: Long running SQL needs tuning
Goto Forum:
  


Current Time: Sat Dec 03 18:07:38 CST 2016

Total time taken to generate the page: 0.05159 seconds