sap停oracle数据库,sap是怎么处理死锁的

abaper 说他们sap数据库 系统老是出现死锁,我说这个东西oracle会自动解锁的，有问题也是你们的程序写的不好自己去改程序

然后他们 然后到sap官方网站 上找了这个 要我帮他们去改ini_trans参数！  我觉得改这个有用吗？

[php]

Oracle deadlocks, ORA-00060

Summary

Symptom

ORA-00060: deadlock detected while waiting for resource

More Terms

ora-60 ora-000060

Cause and Prerequisites

In most cases, the deadlock is caused by a problem in the application. It

can be traced back to a programming error.

Under certain conditions, a deadlock may also be triggered by Oracle.

This deadlock has the following cause:

If a data record is locked in the database, this lock information is

written to the block. The space provided in the block header is used for

this. The space is defined when the table is created with the parameters

INITRANS and maxtrans. If a block becomes too full, the database cannot

allocate any more space to store this block and must wait for memory space

in the corresponding block. As a result, row level locking can become block

level locking.

If some parallel scripts now lock a large number of data records that are

in the same block, two or more of the scripts may sometimes cause a

deadlock, not because they lock the same data record, but because no

additional memory space can be allocated for the lock.

To find out whether this is a deadlock in Oracle, you need to examine the

trace file written by Oracle in a lot more detail. The file is usually

stored in the /oracle//saptrace/usertrace directory. In addition, the

exact file name/directory can usually be determined from the ORA-00060

error message in /oracle//saptace/background/alert.log.

Open the file - the 'deadlock graph' appears on the first two pages.

The deadlock graph is as follows:

---------Blocker(s)-------- ---------Waiter(s)-----

Resource Name process sess. holds waits process sess. hold waits

TX-00090004-00011000 43 39 X 35 46 S

TX-0006001a-0001397d 35 46 X 43 39 S

Here, the last column that specifies the Waits as type 'S' is important.

If an 'X' is displayed instead in the graph, it is NOT an Oracle deadlock.

Which object is it?

After the deadlock graph, the system immediately displays further

information on the object for which the deadlock was generated:

Rows waited on:

Session 39: obj - rowid = 000016F2 - 0003BC42.0000.0093

Session 46: obj - rowid = 000018C2 - 0001012D.004B.0016

Although the select statement of the session terminated by the deadlock is

usually also displayed, you can also refer to these two lines to see which

table it is：

000016F2 [hex value] corresponds to 5874 [decimal]

000018C2 [hex value] corresponds to 6338 [decimal]

sqlplus "sapr3/"

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=5874;

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=6338;

Is it an index?

If no rows are displayed here, for example,

Rows waited on:

Session 39: no row

Session 46: no row

the deadlock most probably occurred while index blocks were being locked.

Solution

Measures to eliminate/minimize the problem:

The problem, in most cases, is that the system tries to dynamically extend

the block space, but the block is too full. INITRANS defines the static

value reserved for each block for transaction information. However, this

value can increase to MAXTRANS if several transactions simulaneously try to

perform a lock. To prevent this error from occurring, you have to extend

the INITRANS value as much as possible so that a dynamic extension is not

required. The default value for INITRANS is 1. This is usually sufficient

for 'standard' tables/indexes. However, it is necessary to adjust this

value for special tables/indexes, for example, spool tables (TST01, TST03)

and BW tables/indexes into which data is loaded simultaneously.

You can use the following statement to determine the current value of

INITRANS and Maxtrans:

sqlplus "sapr3/"

SQL> SELECT table_name, owner, ini_trans, max_trans FROM dba_tables

WHERE table_name = '

SQL> SELECT index_name, owner, ini_trans, max_trans

FROM dba_indexes WHERE table_name = '

If the error occurs when jobs are being executed in parallel (usual data

loads), the INITRANS value should be set to the maximum number of parallel

running jobs. Otherwise, 20 is an appropriate value.

1. The default value for max_trans is usually 255 for both the table and

indexes. If a value other than 255 (0, for example) is returned for

max_trans, increase this value to at least the adjusted INITRANS

value. We recommend that you set max_trans to 255.

While the value could be extended using the commands

sqlplus "sapr3/"

SQL> ALTER TABLE

SQL> ALTER INDEX "" INITRANS 20;

This would only apply to newly created blocks.

If you have to adjust maxtrans:

sqlplus "sapr3/"

SQL> ALTER TABLE

SQL> ALTER TABLE "" maxtrans 255;

2. Check in accordance with the list below whether this is a 'Special

database object'. If this is the case, you may also have to perform

these actions for additional objects.

3. To extend the value for 'old' blocks as well, follow the steps below:

4. Extend the INITRANS/MAXTRANS values for ALL necessary objects. (These

include tables, indexes, in the case of 'special objects' and also for

these additional objects.)

5. Export the affected table (sapdba).

6. Import the table.

Object change by SAP

There are situations in which an object must be newly created or changed

from SAP. Possible changes include:

- Deletion and recreation of indexes in BW with data loads

- Transporting a 'new' object into another system in your

landscape

- Creating a missing object from transaction DB02

- Converting an object due to the deletion of fields (transaction

ICNV)

Unfortunately, the information on the previous INTRANS/MAXTRANS

value is NOT saved in the SAP ABAP Dictionary. At present, this

type of new object creation currently causes the INITRANS value to

be reset to default.

Special database objects

o Is it a partitioned table?

Change to the INITRANS value only for reallocated

blocks:

In this case, if these are partitioned tables and indexes, you must

query other DBA views to receive the values for the individual

partitions. Then select as follows:

sqlplus "sapr3/"

SQL> SELECT partition_name, ini_trans, max_trans FROM

dba_tab_partitions

WHERE table_name = '

AND table_owner = '';

SQL> SELECT partition_name, ini_trans, max_trans FROM

dba_ind_partitions

WHERE index_name = ''

AND index_owner = '';

For new table partitions, you must change the default INITRANS

value:

sqlplus "sapr3/"

SQL> ALTER TABLE

Now use the SQL statement above with regard to the

dba_tab_partitions view to check the value of the INI_TRANS column

again.

If you have not also adjusted this value after you adjust the

default INITRANS value at table level, then you must change the

INITRANS value for each existing table partition.

For the indexes, the value must only be changed for the index

itself; the values for the index partitions (and the new index

partitions that have to be created) are automatically adjusted.

sqlplus "sapr3/"

SQL> ALTER table

INITRANS 20;

SQL> ALTER INDEX "" INITRANS 20;

Change to the INITRANS values for old blocks and blocks

that are to be allocated:

This is only possible with reorganization of the affected table.

Create the reorganization scripts using sapdba and adjust every

occurrence of INITRANS in the tables and index SQL script.

Then run the scripts.

Background information

Background information

You can assign the INITRANS value when an object is created. If it

is not assigned at that stage, a hardcoded value of 1 for tables

and 2 for indexes is generated. Note that INITRANS defaults cannot

be defined for the tablespace, that is, there is no INITRANS column

in the dba_tablespaces.

During the creation of partitioned objects, this value is saved as

default to dba_part_indexes or in dba_part_tables.

When object partitions are created, an INITRANS that applies only

to the partition can also be assigned explicitly. If it is not

assigned, the default value is generated from dba_part_tables or

dba_part_indexes. During the initial creation of a partitioned

object with an initial partition, two INITRANS values can

subsequently be transferred (a default for the partitioned object

and an actual value for the partition).

In BW, a INITRANS value is never explicitly assigned when an object

is created. Provided that no changes are made with DB tools, the

hardcoded default value is generated.

o Are some of the indexes bitmap indexes?

The problem with bitmap indexes, as opposed to b-tree indexes, is

that there is no longer a 1:1 relationship between the index record

and the table data record. Data records in the Bitmap index are

stored in ranges. If this type of range is now extended, the entire

range must be locked. Deadlocks can occur if several parallel

processes try to access the same ranges simultaneously.

Extending the INITRANS value only helps under certain conditions in

this case. If this occurs very frequently, you should think about

alternatives.

- If the problem occurs during the loading process in InfoCubes,

you should change the loading process that deleted the bitmap

indexes previously and then recreate it. This has major

advantages from a performance point of view.

Problem: the automatic new creation from SAP causes your

previous values for INITRANS to be set back to the default (1).

- Unfortunately, it is not yet possible to automatically

incorporate this into the process for ODS objects. If the

problem occurs with ODS objects, a job that was running BEFORE

the loading process would be able to drop these indexes and

another job could create them again later. This means that you

can also specify a higher value for INITRANS.

- If neither of these two options are viable, you should consider

to what extent the indexes may be converted to b-tree indexes.

o Is this a table with a LOB field/is this a LOB object?

Under certain circumstances, a self-deadlock can also occur with

LOB objects.

In this case, the deadlock graph usually looks slightly different:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)------

process sess. holds waits process sess. holds waits

20017-0000010c 8 8 X 8 8 S

session 8: DID 0001-0008-00000001

session 8: DID 0001-0008-00000001

Rows waited on: Session 8: no row

There is only ONE row in the deadlock graph.

- You can use the following statement to determine which fields

have an LOB data type:

sqlplus "sapr3/"

SQL> SELECT table_name, column_name, data_type FROM

dba_tab_columns

where table_name='

- You can change the INITRANS/MAXTRANS value as follows:

SQL> ALTER table "

(index (INITRANS 20 maxtrans 255));

- If the problem persists, you must reorganize the table as

described above so that the change is applied to all blocks.

[/php]