In Linux, check what the Oracle process is doing and whether SQL causes the lock to kill the process.

Source: Internet
Author: User
Tags what sql

PS-l view system processes
Pid id of each process.
Ppid: the parent process ID of each process.
UID: The UID of each process owner.
User: the username of each process owner.
The priority of each process in PRI.
The priority of the process.
Size: the code size of the process, the data size, and the total size of the stack space. The Unit is kb.
Tsize: the code size of the process. This is a strange value for Kernel processes.
Dsize data and stack size.
TRS text resident size.
D. The page project marked as "not clean.
The size of the library page used by Lib. It does not work for the elf process.
RSS the total number of physical memory occupied by the process, in KB.
Share the amount of memory used by the process.
Stat the status of the process. S indicates the sleep state, D indicates the non-interrupted sleep state, r indicates the running state, and Z indicates the frozen state;

T indicates the stop or trace status.
Time the total CPU time occupied by the process since it was started. If you enter the accumulative mode, the time also includes the sub-process of this process.

The time used. The title is changed to ctime.

According to the OS process number, you should be able to find out what SQL statement it is executing.
-- Query the SID number of the Oracle OS process:
Select SES. Sid from V $ session SES, V $ process pro where pro. spid = & spid and SES. paddr = pro. ADDR;
-- Query the SQL statement that the SID connects to the user:
Select SQL _text from V $ sqltext_with_newlines where (hash_value, address)
In (select SQL _hash_value, SQL _address from V $ session where Sid = & SID) order by address, piece;
Check whether Oracle locks are caused by SQL statements.

View the session_id that caused the lock
Process started by sync4j
Select * from V $ locked_object where OS _user_name = 'sync4j'
Find seesion_id = 139
Locate the cause SQL statement
Select a. username,
A. machine,
A. program,
A. Sid,
A. Serial #,
A. status,
C. piece,
C. SQL _text
From v $ session,
V $ sqltext C
Where a. Sid = 139
And a. SQL _address = C. Address (+)
Order by C. Piece
V $ sqltext is the SQL statement that causes the lock.

Alter system kill session 'sid, serial # 'Kill Process
Sid, serial # can be found in V $ session

========================================

1. First, use v $ session_wait to determine the library cache pin session waiting for the event

2. Confirm the object to pin:
Select kglnaobj
From SYS. x $ kglob
Where inst_id = userenv ('instance ')
And kglhdadr = V $ session_wait.p1raw;

3. Determine the blocked session:
Select a. Sid, A. Serial #, A. username, A. paddr, A. logon_time,
A. SQL _hash_value, B. kglpnmod
From v $ session A, SYS. x $ kglpn B
Where a. saddr = B. kglpnuse
And B. inst_id = userenv ('instance ')
And B. kglpnreq = 0
And B. kglpnmod not in (0, 1)
And B. kglpnhdl = V $ session_wait.p1raw;

Determine which sessions are using a package/process/function/view. If so, do not perform operations such as drop or complie.
Select C. Sid
From SYS. x $ lglob A, SYS. x $ kglpn B, V $ Session C
Where a. kglnaobj = upper-case package/process/function/View
And a. kglhdadr = B. kglpnhdl
And B. kglpnuse = C. saddr;

Starting ------------------------ 1. First, use v $ session_wait to determine the library cache pin session waiting for the event

2. Confirm the object to pin:
Select kglnaobj
From SYS. x $ kglob
Where inst_id = userenv ('instance ')
And kglhdadr = V $ session_wait.p1raw;

3. Determine the blocked session:
Select a. Sid, A. Serial #, A. username, A. paddr, A. logon_time,
A. SQL _hash_value, B. kglpnmod
From v $ session A, SYS. x $ kglpn B
Where a. saddr = B. kglpnuse
And B. inst_id = userenv ('instance ')
And B. kglpnreq = 0
And B. kglpnmod not in (0, 1)
And B. kglpnhdl = V $ session_wait.p1raw;

Determine which sessions are using a package/process/function/view. If so, do not perform operations such as drop or complie.
Select C. Sid
From SYS. x $ lglob A, SYS. x $ kglpn B, V $ Session C
Where a. kglnaobj = upper-case package/process/function/View
And a. kglhdadr = B. kglpnhdl
And B. kglpnuse = C. saddr;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.