Home » RDBMS Server » Performance Tuning » Commit interval (Oracle 11g, Unix)
Commit interval [message #655058] Sat, 20 August 2016 07:20 Go to next message
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.

Re: Commit interval [message #655059 is a reply to message #655058] Sat, 20 August 2016 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: Commit interval [message #655061 is a reply to message #655059] Sat, 20 August 2016 07:54 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Commit interval [message #655062 is a reply to message #655058] Sat, 20 August 2016 07:57 Go to previous messageGo to next message
John Watson
Messages: 8922
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

Re: Commit interval [message #655063 is a reply to message #655062] Sat, 20 August 2016 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Commit interval [message #655072 is a reply to message #655062] Sun, 21 August 2016 01:31 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
COMMIT_WRITE has been deprecated since 11g right?
Re: Commit interval [message #655074 is a reply to message #655072] Sun, 21 August 2016 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Then use its equivalent in your version.

Re: Commit interval [message #655530 is a reply to message #655074] Thu, 01 September 2016 04:19 Go to previous messageGo to next message
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.
Re: Commit interval [message #655533 is a reply to message #655530] Thu, 01 September 2016 04:48 Go to previous messageGo to next message
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.
Re: Commit interval [message #655535 is a reply to message #655533] Thu, 01 September 2016 04:51 Go to previous messageGo to next message
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.
Re: Commit interval [message #655536 is a reply to message #655535] Thu, 01 September 2016 04:58 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: How to Optimise this query
Next Topic: Improve query execution times
Goto Forum:
  


Current Time: Fri Mar 29 02:24:44 CDT 2024