OraFAQ Forum: Server Administration » Database Blocking Lock

Home » RDBMS Server » Server Administration » Database Blocking Lock (Oracle Database 10g Release 10.2.0.1.0 - 64bit Production)

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

Database Blocking Lock [message #530409]

Tue, 08 November 2011 01:38

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Hai,

Can anyone advice , how to avoid blocking locks.

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530410 is a reply to message #530409]

Tue, 08 November 2011 01:43

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do not lock or forbid more than one session or use MS/Access.

If you had never blocking locks then lock would not exist.
Locks and blocking locks are necessary to ensure transactional properties.

Regards
Michel

[Updated on: Tue, 08 November 2011 01:44]

Report message to a moderator

Re: Database Blocking Lock [message #530412 is a reply to message #530410]

Tue, 08 November 2011 01:51

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

We upgraded our database server from Oracle Database 10g Release 10.1.0.3.0 - Production RH4 to Oracle Database 10g Release 10.2.0.1.0 - 64bit Production.

Before Blocking Locks issue used to arise once in a week. But now it is daily twice. Any paramters i need to change? Please advice.

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530413 is a reply to message #530412]

Tue, 08 November 2011 01:57

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no blocking locks issue in the database. Blocking locks are part of the database (instance) life.

Explain (in details) your actual problem.

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530415 is a reply to message #530413]

Tue, 08 November 2011 02:01

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

When more number of users are connected to our Database instance, any one machine found blocking the database. And others are in waiting.

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530417 is a reply to message #530415]

Tue, 08 November 2011 02:10

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Is it required to change the value of any parameters in our database instance?

Regards,
Paules

Report message to a moderator

Re: Database Blocking Lock [message #530418 is a reply to message #530415]

Tue, 08 November 2011 02:13

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

Senior Member

Hi - what tool are you using to diagnose the problem? I would usually run the $ORACLE_HOME/rdbms/admin/utllockt.sql script as a starting point. Can you do that, while the problem is occuring? As Michel says, locks are probably to do with your application, but the output of that script will confirm this.
John.

Report message to a moderator

Re: Database Blocking Lock [message #530419 is a reply to message #530417]

Tue, 08 November 2011 02:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And so, what do you expect? Oracle holds locks until your application says it to no more hold them, that is commit or rollback.
Oracle just follows what you tell it.

So investigate why this session is holding the locks so long and if it is appropriate.

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530420 is a reply to message #530418]

Tue, 08 November 2011 02:22

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Iam using Toad 7.6.0.11 . This is showing the Blocking session. When he/she exits our application,problem is resolving.

But my doubt is such problems used to arise in our old database once in a week. But now in this new database twice in a day.What may be the reason?

Is there any relation between this Issue and the Parameters defined in v$parameter?? Please advice?

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530421 is a reply to message #530420]

Tue, 08 November 2011 02:28

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

When he/she exits our application,problem is resolving.

This is the expected behaviour.

Quote:

But my doubt is such problems used to arise in our old database once in a week. But now in this new database twice in a day.What may be the reason?

Better performances so more concurrent sessions.
Once again where is the problem?
Blocking/blocked sessions are NOT a problem, it is database life.
Only inappropriate locks are a problem and this is an APPLICATION issue NOT and Oracle one.

Quote:

Is there any relation between this Issue and the Parameters defined in v$parameter??

NO!

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530422 is a reply to message #530421]

Tue, 08 November 2011 02:33

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

ok. Thanks. I got it.

I,m facing another Issue . When we changed our database server , the Sorting Order in most of our Reports(generated by Report builder) changed. Is this related to database ?or application?
Please advice.

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530423 is a reply to message #530422]

Tue, 08 November 2011 02:37

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Is this related to database ?or application?

Application: they (most likely) do not specify an order on the queries.
Or, if it is on string, you have not the same NLS values.

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530424 is a reply to message #530423]

Tue, 08 November 2011 02:44

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

Sorting Order in most of our Reports (generated by Report builder) changed

Did you (actually, developer(s)) use appropriate ORDER BY clauses? Did you rely on the (wrong) assumption that GROUP BY will also do ORDER BY? Did reports change in any way (for example, modified break order within groups)?

Report message to a moderator

Re: Database Blocking Lock [message #530431 is a reply to message #530424]

Tue, 08 November 2011 03:08

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

I checked the NLS Values. All those are same.

For the Reports with ORDER BY Clause there are no Issues.

For the reports in which ORDER BY not there, it was showing some order using GROUP BY. This is making problem now. Is it possible to get the same order in the Old Database without using ORDER BY?

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530433 is a reply to message #530431]

Tue, 08 November 2011 03:10

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, the code was wrong, the code needs to be fixed.

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530435 is a reply to message #530433]

Tue, 08 November 2011 03:31

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

ok.Thank u.

I have another doubt. The following parameters are zero in the New Server.

1.shared_pool_size
2.large_pool_size
3.java_pool_size
4.db_cache_size.

Where as these are having values in the old server. Is it require to assign values to these paramters? If I assign values to these parameters, can I improve performance?

Regards,
Paules

Report message to a moderator

Re: Database Blocking Lock [message #530436 is a reply to message #530435]

Tue, 08 November 2011 03:42

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

Senior Member

Quote:

We upgraded our database server from Oracle Database 10g Release 10.1.0.3.0 - Production RH4 to Oracle Database 10g Release 10.2.0.1.0 - 64bit Production.

Paules, who did this upgrade? If it was external consultants, they should have explained how and why they re-configured memory usage. Also, they should have applied the 10.2.0.5 patchset.
You probably need to investigate the whole upgrade process, and see if it really was done appropriately.

Report message to a moderator

Re: Database Blocking Lock [message #530438 is a reply to message #530436]

Tue, 08 November 2011 03:50

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Yes, Installation done by external consultants. But they did not made any changes to the memory usage. By Default these paramters came zero. Creating the data files and data migration done by me.

They did not applied the patch set which u mentioned. So now how can I solve the Issues?

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530441 is a reply to message #530438]

Tue, 08 November 2011 04:00

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

Senior Member

I think you have a problem: your upgrade was not planned and tested properly, and your consultants did not do a good job. For example:
The issue of blocking sessions should have been identified before you went live.
The 10.2.0.5 patchet should have been applied before the data migration.
The changes in memory config should have been controled.
The bahaviour of your reports should have been identified and corrected before going live.

Immediate advice: apply the 10.2.0.5 patchset. See how the application performs then, and if there are issues create a new topic and post the AWR reports (or statspack reports if you don't have the appropriate licences for AWR) that show the problems.

You might want to ask your consultants for comments on the above. Good luck.

Report message to a moderator

Re: Database Blocking Lock [message #530442 is a reply to message #530441]

Tue, 08 November 2011 04:15

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Dear Mr.Michel,

It is not possible to alter the values of the parameters Instead of applying the patch set?

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530443 is a reply to message #530442]

Tue, 08 November 2011 04:22

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is John that was talking to you. Wink

Yes it is possible to change the parameters but have a look at sga_max_size and sga_target parameters they have been set instead of your previous ones. Check their values they should be at least the sum of the previous ones.

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530444 is a reply to message #530441]

Tue, 08 November 2011 04:22

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Dear Mr.John,

It is not possible to alter the values of the parameters Instead of applying the patch set?

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530446 is a reply to message #530444]

Tue, 08 November 2011 04:26

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And my answer does not agree you?

Regards
Michel

Report message to a moderator

Re: Database Blocking Lock [message #530447 is a reply to message #530443]

Tue, 08 November 2011 04:27

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Dear Mr.Michel,

Can I do it, when the database is on? Or I have to shutdown before doing the changes to the parameters?

Also What these parameters about? If I increase what is the advantage I will get?

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530448 is a reply to message #530446]

Tue, 08 November 2011 04:28

Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates

Senior Member

Dear Mr.Michel,

I'm agreed of your answer .I replied asked Mr.John before getting your reply.

Regards,
Paules.

Report message to a moderator

Re: Database Blocking Lock [message #530449 is a reply to message #530444]

Tue, 08 November 2011 04:29

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

Senior Member

Quote:

It is not possible to alter the values of the parameters Instead of applying the patch set?

Of course you can. But to what values? There is no reason to assume that values appropriate to 10.1.0.3 are appropriate to the current release and workload.
But before you do anything, you must apply the latest patchset. This includes hundreds (thousands?) of bug fixes as well as major improvements in behaviour. There is no point in trying to tune the outdated release you have at the moment. Go back to your consultants, and tell them that they should have done this.
I can't contribute any more to this discussion. Good luck.

Report message to a moderator