Dynamic Oracle Learning Performance Table

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 real temporary tablespace (true temporary tablespaces) and use temporary files. 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
6	X (Exclusive)	Exclusive lock	Alter table, Drop table, Drop index, Truncate table, Lock exclusive

The larger the number, the higher the lock level, the more operations affected. A general query statement such as select... from...; is a lock smaller than 2, and sometimes appears in v $ locked_object. Select... from... for update; is the lock of 2.

When you use the for update substring to open a cursor in a dialog box, all data rows in the returned dataset are locked exclusively at the Row level (Row-X). Other objects can only query these data rows, update, delete, or select... for update operation. Insert/update/delete...; is the lock of 3.

Inserting the same record before the commit operation does not respond, because the last 3 lock will always wait for the last 3 lock, and we must release the previous one to continue working.

When an index is created, 3 or 4 locks are generated. Locked_mode is 2, 3, 4 does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc.) operations will prompt a ora-00054 error. When there is a primary foreign key constraint, update/delete...; may produce 4 or 5 locks. The DDL statement is a 6 lock.

If a lock problem occurs, a DML operation may wait for a long time and no response is returned. When you connect to the database directly, do not use the OS system command $ kill process_num or $ kill-9 process_num to terminate the user connection, because a user process may generate more than one lock, killing the OS process cannot completely clear the lock. Remember to use alter system kill session 'sid, serial # 'At the database level to kill abnormal locks.

Example:

I demonstrated the TX and tmlock as follows:

1. create table TMP1 (col1 VARCHAR2 (50); -- create a temporary table
2. select * from v $ lock; -- disable the current lock Information
3. select * from tmp1 for update; -- lock
4. select * from v $ lock; -- check whether two more records exist in the current lock list. The types are tx and tm, respectively, as shown in table 1.
5. Open a new connection and then
Select * from tmp1 for update; -- Haha, wait for the status
Select * from v $ lock; -- two new records are added, and the other one is type = tx, lmode = 0
7. view the SQL statement being executed by the locked session
SQL> select/* + NO_MERGE (a) NO_MERGE (B) NO_MERGE (c) */. username,. machine,. sid,. serial #,. last_call_et "Seconds", B. id1, c. SQL _text "SQL"
From v $ session a, v $ lock B, v $ sqltext c
Where a. username is not null and a. lockwait = B. kaddr
And c. hash_value = a. SQL _hash_value
8. Use the previous for update statement commit or rollback, and the newly opened session will have a lock. If you are interested, you can close the window that is executed first when trying two for update statements to see what kind of response oracle will give.

This section is one of the most time and energy I have spent in organizing the v $ series views. I read the document repeatedly and searched the actual use cases of various materials on the Internet, that is, don't get started. So far, I still have no confidence in understanding this section, so in addition to examples in the above text, I posted the collected content without any self-understanding, I am worried that, in case of incorrect understanding, it will cause problems for others who browse this article.

1 2 3 Next Page