Lock on a table

[Former Member]

Hi Everydoby.

I have SAP R3 / ECC6 on AIX 5.3 System. Database Oracle 10g.

I would like you to explain me if there is a way to solve this problem about locks.

Let me explain:

We have several jobs launched all nights (specifics programs) and some of them access to same tables (MBEW for example).

When I launch a single job in development system, it takes 2 hours. When I Launch this same job in Production, it takes more than

10 hours! That's why I think this job is blocked by locks from others jobs.

How can I verify that locks are the problems?

Could I configure locks not to lock other users when accessing the table?

Many thanks for your help.

Regards

[Former Member]

Many thanks for your answers guys.

I have generated the AWR reports. Many Many informations on this report. What do I have to check first on this report to understand the problem with my jobs which take too many times (when they are launched at the same time)?

Thanks again experts!

[stefan_koehler]

Hello David,

there is a section called "Top 5 Timed Foreground Events" which describes where the most time is spent on the database. If you have an issue with some row level locks you should see a high wait time on "enq: TX - row lock contention".

There is also a section called "Segments by Row Lock Waits" which points you to the segments with the most corresponding waits.

You could also upload this report somewhere and post the link here so we can take a look on it too.

Regards

Stefan

[Former Member]

Many thanks again!

Here is the report.

http://dl.free.fr/qLyTq3qEo

Let me know if you see something wrong

Many thanks again

Regards

[Former Member]

SQL*Net message from client looks to be very high....

[stefan_koehler]

Hello David,

"SQL*Net message from client" is an idle wait event - which means if you don't run a SQL on the database in a session you will see this wait event.

What i can see from this AWR:

1) You don't have a lock issue on database level

2) You have some really bad SQLs running on JITHD / JITIT / JITCO / AFRU

I know this issues with JITHD / JITIT and JITCO / AFRU - most of the databases that i have tuned were used in automotive business.

I am sure that your high job run time is not caused by any locking issues on the database - you need to go a step back and look from there. If you would need some assistance by this issue - check out [my consulting services|http://www.soocs.de] ... i have been specialized on such performance issues. Just write me an e-mail and we can talk about the details.

Regards

Stefan

[former_member188883]

Hi,

you can verify the database lock entries using tcode db01. Ensure that entries are getting cleared regularly and there are not many entries at a given point of time.

If you see that many lock entries exists in db01 and its not getting cleared, it is an indication that there is an issue with database locking mechanism.

Regards,

Deepak Kori

[stefan_koehler]

Hello David,

How can I verify that locks are the problems?
Could I configure locks not to lock other users when accessing the table?

Well without any indication about a particular SQL or something like that - i would start with ST03n to verify where the time is spent on your production system (maybe on DB or on ABAP, etc.). If you really can identify the root issue at DB time i would create an AWR report to get a basic idea about the database status at this time.

shell> sqlplus / as sysdba
SQL> @?/rdbms/admin/awrrpt

No - the Oracle locking mechanism is used to guarantee read consistency at every time. By the way you also don't lock the whole table - just various rows of it by using a DML. I disregard DDL and "SELECT FOR UPDATE" in this case.

Regards

Stefan