Home » RDBMS Server » Server Administration » Database Blocking Lock (Oracle Database 10g Release 10.2.0.1.0 - 64bit Production)
Database Blocking Lock [message #530409] Tue, 08 November 2011 01:38 Go to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member

Hai,

Can anyone advice , how to avoid blocking locks.

Regards,
Paules.
Re: Database Blocking Lock [message #530410 is a reply to message #530409] Tue, 08 November 2011 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
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 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530413 is a reply to message #530412] Tue, 08 November 2011 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
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
Re: Database Blocking Lock [message #530415 is a reply to message #530413] Tue, 08 November 2011 02:01 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530417 is a reply to message #530415] Tue, 08 November 2011 02:10 Go to previous messageGo to next message
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
Re: Database Blocking Lock [message #530418 is a reply to message #530415] Tue, 08 November 2011 02:13 Go to previous messageGo to next message
John Watson
Messages: 4367
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.

Re: Database Blocking Lock [message #530419 is a reply to message #530417] Tue, 08 November 2011 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
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
Re: Database Blocking Lock [message #530420 is a reply to message #530418] Tue, 08 November 2011 02:22 Go to previous messageGo to next message
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.

Re: Database Blocking Lock [message #530421 is a reply to message #530420] Tue, 08 November 2011 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
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
Re: Database Blocking Lock [message #530422 is a reply to message #530421] Tue, 08 November 2011 02:33 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530423 is a reply to message #530422] Tue, 08 November 2011 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
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
Re: Database Blocking Lock [message #530424 is a reply to message #530423] Tue, 08 November 2011 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 19289
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)?
Re: Database Blocking Lock [message #530431 is a reply to message #530424] Tue, 08 November 2011 03:08 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530433 is a reply to message #530431] Tue, 08 November 2011 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, the code was wrong, the code needs to be fixed.

Regards
Michel
Re: Database Blocking Lock [message #530435 is a reply to message #530433] Tue, 08 November 2011 03:31 Go to previous messageGo to next message
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
Re: Database Blocking Lock [message #530436 is a reply to message #530435] Tue, 08 November 2011 03:42 Go to previous messageGo to next message
John Watson
Messages: 4367
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.
Re: Database Blocking Lock [message #530438 is a reply to message #530436] Tue, 08 November 2011 03:50 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530441 is a reply to message #530438] Tue, 08 November 2011 04:00 Go to previous messageGo to next message
John Watson
Messages: 4367
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.
Re: Database Blocking Lock [message #530442 is a reply to message #530441] Tue, 08 November 2011 04:15 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530443 is a reply to message #530442] Tue, 08 November 2011 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
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
Re: Database Blocking Lock [message #530444 is a reply to message #530441] Tue, 08 November 2011 04:22 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530446 is a reply to message #530444] Tue, 08 November 2011 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And my answer does not agree you?

Regards
Michel
Re: Database Blocking Lock [message #530447 is a reply to message #530443] Tue, 08 November 2011 04:27 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530448 is a reply to message #530446] Tue, 08 November 2011 04:28 Go to previous messageGo to next message
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.
Re: Database Blocking Lock [message #530449 is a reply to message #530444] Tue, 08 November 2011 04:29 Go to previous messageGo to next message
John Watson
Messages: 4367
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.
Re: Database Blocking Lock [message #531089 is a reply to message #530449] Sun, 13 November 2011 05:00 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Thank u all. Resolved the Issues.

Regards,
Paules.
Re: Database Blocking Lock [message #531093 is a reply to message #531089] Sun, 13 November 2011 06:11 Go to previous message
Michel Cadot
Messages: 58477
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How?

Regards
Michel
Previous Topic: ORA-04030: out of process memory
Next Topic: Error While Starting Database
Goto Forum:
  


Current Time: Tue Jul 22 02:26:57 CDT 2014

Total time taken to generate the page: 0.17367 seconds