OraFAQ Forum: Performance Tuning » performance of database , please help

Home » RDBMS Server » Performance Tuning » performance of database , please help (oracle 9i, 9.2.0.1, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

performance of database , please help [message #329140]

Tue, 24 June 2008 04:27

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.

Attachment: repo_24junenew.txt
(Size: 100.91KB, Downloaded 1926 times)

[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

seema.taunk
Messages: 96
Registered: October 2006

Member

Any help or suggestion?

Report message to a moderator

Re: performance of database , please help [message #329228 is a reply to message #329140]

Tue, 24 June 2008 10:49

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: performance of database , please help [message #329229 is a reply to message #329228]

Tue, 24 June 2008 10:54

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?

Report message to a moderator

Re: performance of database , please help [message #329233 is a reply to message #329140]

Tue, 24 June 2008 11:12

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: performance of database , please help [message #329244 is a reply to message #329229]

Tue, 24 June 2008 12:10

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: performance of database , please help [message #329598 is a reply to message #329244]

Thu, 26 June 2008 00:05

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?

Report message to a moderator

Re: performance of database , please help [message #330158 is a reply to message #329598]

Fri, 27 June 2008 12:53

michael_bialik
Messages: 621
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 21:01:53 CDT 2024