OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Commit interval (Oracle 11g, Unix)

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

Commit interval [message #655058]

Sat, 20 August 2016 07:20

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi,

Recently our clients reported slowness in their batch job processing. The Database is Oracle 11g.

Client had sent us the AWR from production system for Analysing . In our analysis we found that the log file sync is considerably high.
So suggested to the Application team to consider reducing the number of commits in the batch jobs.

The Application team responded that earlier they had considered reducing number of commits.
It impacted the application causing deadlocks in the Application. This they says is due to multiple threads in the batch job.

Application is in Java.

My question is how can reducing the commits can cause deadlocks in this scenario, even if the job uses multiple threads.

Can this be a cause of a very poor Application design, which I doubt Application team don't want to reveal and don't want to bother to revisit their design approach. My doubt is there could be serious flaw in their design approach which is causing deadlocks, and reducing commits alone cannot cause any deadlocks.

Kindly advise.

Thanks,
Ninan.

Report message to a moderator

Re: Commit interval [message #655059 is a reply to message #655058]

Sat, 20 August 2016 07:40

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You are right.
The performances problem come from committing too often, there is only ONE frequency to commit: once per business transaction.
The deadlock comes from the application design and code.

The solution is to review and rewrite this code, above all if it is an application that comes from SQL Server or is designed to be "independent of the database" (something that is impossible).

[Updated on: Sat, 20 August 2016 07:45]

Report message to a moderator

Re: Commit interval [message #655060 is a reply to message #655059]

Sat, 20 August 2016 07:51

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

The Application is in Java and Database is Oracle 11g. Application team has mentioned that the Application uses multiple threads for the batch job. It could be a problem with the design of these Application threads , is my guess.

Report message to a moderator

Re: Commit interval [message #655061 is a reply to message #655059]

Sat, 20 August 2016 07:54

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

The solution is to review and rewrite this code, above all if it is an application that comes from SQL Server

Could be worse than that! It is Java. Probably yet another appallingly designed application written by Java developers who do not know what a database is. No understanding of constraints, normalization, or transactions. They treat Oracle as though it were a data bucket, not a database.

Report message to a moderator

Re: Commit interval [message #655062 is a reply to message #655058]

Sat, 20 August 2016 07:57

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

In our analysis we found that the log file sync is considerably high.

You may be able to eliminate the log file sync event totally by adjusting the COMMIT_% parameters. Example here,
http://www.orafaq.com/node/2895

Report message to a moderator

Re: Commit interval [message #655063 is a reply to message #655062]

Sat, 20 August 2016 08:06

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

COMMIT_WRITE is indeed a good way to avoid the performances problem without changing the code but it will not solve the dead-lock problem which will come as they have (supposedly) already tested it.

Report message to a moderator

Re: Commit interval [message #655072 is a reply to message #655062]

Sun, 21 August 2016 01:31

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

COMMIT_WRITE has been deprecated since 11g right?

Report message to a moderator

Re: Commit interval [message #655074 is a reply to message #655072]

Sun, 21 August 2016 01:38

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Then use its equivalent in your version.

Report message to a moderator

Re: Commit interval [message #655530 is a reply to message #655074]

Thu, 01 September 2016 04:19

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Here is how I observe performance design in coding over the last several years.

1. developers write a row at a time solution (solution is functionally correct but deemed too slow)

2. so developers modify row at a time to include bulk fetch and bulk update (2-10 times faster but deemed still too slow)

3. so developers break data up into streams and run multiple threads (another 2-10 times faster but deemed still too slow and now deadlock issues)

I believe there is a school somewhere in the world that teaches this approach as I see big contracting shops doing this everywhere. It may make sense in some circles as it allows large development shops to create systems using lots of "framework components" that large masses of developers can learn to use with reduced effort, but it causes no end to performance issues. I am sad about the lack of respect for native parallelism features; on the other hand, it give me a job for life.

Report message to a moderator

Re: Commit interval [message #655533 is a reply to message #655530]

Thu, 01 September 2016 04:48

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

You forgot 1a) Blame the database for being "too slow".

@OP: Be very very sure before you even look at that feature, I would strenuously recommend not using it. ever.

Report message to a moderator

Re: Commit interval [message #655535 is a reply to message #655533]

Thu, 01 September 2016 04:51

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

haha yes.

But my list is not an observation of complaints. It is an observation of how code is actually written these days by large shops that seem to be very JAVA oriented and not database wise.

Report message to a moderator

Re: Commit interval [message #655536 is a reply to message #655535]

Thu, 01 September 2016 04:58

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

I suspect that Java is key to this. Java developers sometimes try to apply inappropriate techniques, if they lack an understanding of relational databases. For example, Java is multithreaded; SQL isn't. Java can do transactions across sessions; SQL can't. Java is a procedural language; SQL is set oriented. Hence all their "tuning" is aimed at using procedural techniques and parallelism that are disastrous in a relational database.

They tend to treat Oracle as a data bucket, not a database.

Report message to a moderator

Previous Topic:	How to Optimise this query
Next Topic:	Improve query execution times

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 17:52:19 CST 2025