Dynamic Oracle Learning Performance Table

According to the description in OracleDocument, v $ sysstat stores the resource usage of the entire instance (instance-wide) from the moment the database instance runs. Similar to v $ s

According to the description in OracleDocument, v $ sysstat stores the resource usage of the entire instance (instance-wide) from the moment the database instance runs. Similar to v $ s

Oracle learning Dynamic Performance Table v $ locked_object

This view lists all the locks obtained by each transaction on the system.

Description of columns in V $ LOCKED_OBJECT:

XIDUSN: rollback segment number
XIDSLOT: Slot Number
XIDSQN: serial number
OBJECT_ID: ID of the locked object
SESSION_ID: sessionID of the lock
ORACLE_USERNAME: The Oracle user name holding the lock
OS _USER_NAME: operating system username holding the lock
PROCESS: Operating System PROCESS number
LOCKED_MODE: Lock mode. The value is the same as that in table 1.

Example: 1. Use the DBA role to view the locks in the current database. You can use the following SQL statement:
Select object_id, session_id, locked_mode from v $ locked_object;

Select t2.username, t2.sid, t2.serial #, t2.logon _ time
From v $ locked_object t1, v $ session t2
Where t1.session _ id = t2.sid order by t2.logon _ time;

If a long-standing column appears, the lock may not be released. We can use the following SQL statement to kill abnormal locks that have not been released for a long time:

Alter system kill session 'sid, serial #';

Oracle learning Dynamic Performance Table v $ locked

V $ LOCK this view lists the locks currently owned by the Oracle server and uncompleted locks or locks. If you think the session is waiting for the event queue, you should check this view. If you find that the session is waiting for a lock, the order is as follows:

Use V $ LOCK to find the LOCK held by the session.
Use V $ SESSION to find the SQL statement executed by the session holding the lock or waiting for the lock.
Use V $ SESSION_WAIT to find out what causes the session lock to be blocked.
Use V $ SESSION to obtain more information about lock-holding programs and users.

Common columns in V $ LOCK

SID: indicates the session information holding the lock.
TYPE: indicates the lock TYPE. Values include TM and TX.
LMODE: information about the lock mode in which the session is waiting. It is represented by numbers 0-6 and corresponds to table 1.
REQUEST: information about the lock mode of the session REQUEST.
ID1 and ID2: The identifier of the lock object.

Public lock type

In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes the DML statement, the system automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, the system automatically applies for the TX lock and places the lock flag of the actually locked data row. In this way, when the consistency of the TX lock is checked before the transaction locks, the lock mark does not need to be checked row by row. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, SX, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks, as shown in table 1 below.

TX: Row-Level Lock and transaction lock

When changing data, it must be in mode 6 ). Each active transaction has a lock. It will be released at the end of the transaction (commit/rollback. If the columns included in a block are changed without the ITL (interested transaction list) slot (entries), the session places the lock in the Shared mode (mode 4 ). The session is released when it obtains the ITL slot of the block. When a transaction initiates a DML statement for the first time, it obtains a TX lock, which is kept until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. After the first session is submitted, the TX lock is released before other sessions can be locked. Specifies rollback segments and transaction table items.

Press the column to avoid competition:
Avoid competition for TX-6 types, depending on your application.
To avoid TX-4 type contention, consider adding the INITRANS parameter value of the object.

TM: Table-Level Lock

When the database executes any DDL statement, it must be in its own layout mode; for example, alter table and drop table. DML statements such as insert, update, and delete are in the sharing mode. It prevents other sessions from executing ddl statements on the same object at the same time. Any object has the data being changed, and the tmlock must exist. The lock points to the object. To avoid competition in the TM queue, you can consider shielding the object table-Level Lock and blocking the table-Level Lock to prevent the object from executing any ddl statements.

ST: Space transaction lock

Each database (non-instance) has an ST lock. In addition to local tablespace management, the space management operation (create or delete extents) must be in its scheduling mode. The creation, dropping, extension, and truncation objects are all in this lock. Most common reasons are competition for disk sorting (not using real temporary tablespace) or rollback segment expansion or contraction.

To avoid competition, follow these steps:
Use a real temporary tablespace (true temporary tablespaces) and a temporary file. The temporary segment is not created or deleted after the disk is sorted.
Use local tablespace management.
Specify rollback segments to avoid dynamic expansion and contraction, or use automatic undo management.
Avoid the application from creating or deleting database objects.

UL: User-Defined lock

You can customize locks. The content is large and has little to do with this section.

Connection column in V $ LOCK

Column View Joined Column (s)
Sid v $ SESSION SID
ID1, ID2, type v $ LOCK ID1, ID2, TYPE
ID1 DBA_OBJECTS OBJECT_ID
TRUNCID1/0/41) V $ ROLLNAME USN

If the session is waiting for the lock, this can be used to find the lock held by the session. It can be used to find the locked object (type = 'TT') of the DML lock TYPE and can be used to find the rollback segments used in the row-level transaction lock (type = 'tx, however, you need to query through the V $ TRANSACTION connection.

Table 1 Oracle tmlock types

Lock mode

Lock description

Explanation

SQL operations

0

None

1

NULL

Null

Select

2

SS (Row-S)

Row-level shared locks. Other objects can only query these data rows.

Select for update, Lock for update, Lock row share

3

SX (Row-X)

Row-level exclusive locks. DML operations are not allowed before submission.

Insert, Update, Delete, Lock row share

4

S (Share)

Shared lock

Create index, Lock share

5

SSX (S/Row-X)

Shared row-level exclusive locks

Lock share row exclusive