FINDING OUT WHAT AN ORACLE PROCESS IS DOING
High CPU Usage, Oracle, Session
0
QUESTIONS
How to match OS processes to Oracle processes?
What a particular OS Oracle process (PID) is doing in an Oracle database?
What is an easy way to match the OS PID to the Oracle session?
How to find out SQL being executed by an OS process ID (PID)?
What a particular OS Oracle process (PID) is doing in an Oracle database?
What is an easy way to match the OS PID to the Oracle session?
How to find out SQL being executed by an OS process ID (PID)?
REQUEST
I want to find out details about a session in a database for the specific operating system process.
SOLUTION
To find out details about an oracle session of the Unix process use v$session with v$process views.
TESTCASE
SQL> set sqlprompt "_user'@'SES_1 > "
SYS@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
373
---------------------------------------------------
-- * Victor Khalturin
-- * Script for producing CPU load on a database
-- * The only 1 core is utilized by the script
---------------------------------------------------
DECLARE
l_result NUMBER;
BEGIN
FOR id IN 1..999999999
LOOP
l_result := (id/DBMS_RANDOM.VALUE + id*DBMS_RANDOM.VALUE)/DBMS_RANDOM.VALUE;
END LOOP;
END;
/
While script is running issue top command and find a process with heavy cpu consumption
top - 14:23:38 up 33 days, 1:37, 3 users, load average: 0.82, 0.32, 0.23
Tasks: 272 total, 2 running, 270 sleeping, 0 stopped, 0 zombie
%Cpu0 : 0.0 us, 0.3 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu4 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu5 :100.0 us, 0.0 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu6 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu7 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 98963392 total, 48400364 free, 845448 used, 49717576 buff/cache
KiB Swap: 25165820 total, 25165820 free, 0 used. 68142432 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
50845 oracle 20 0 28.746g 93632 89128 R 100.0 0.1 1:52.81 oracle_50845_db
493 root 20 0 0 0 0 S 0.3 0.0 12:58.63 xfsaild/dm-0
51179 oracle 20 0 157844 4236 3424 R 0.3 0.0 0:00.16 top
55584 oracle -2 0 28.729g 57976 55088 S 0.3 0.1 35:06.55 ora_vktm_dbpilo
1 root 20 0 193640 8268 5500 S 0.0 0.0 1:14.86 systemd
Here the process with PID 50845 consumes 100% of CPU (Cpu5).
To find out details about what the process is doing in the database
-- Session 2
set sqlprompt "_user'@'SES_2 > "
SET LINES 300
SET PAGES 999
COL OSUSER FOR A6
COL DBUSER FOR A5
COL SID FOR 9999
COL MACHINE FOR A20
COL MODULE FOR A10
SELECT OSUSER,USERNAME DBUSER,SID,SERIAL#,SQL_ID,STATUS,MACHINE,
PORT,PROGRAM,LAST_CALL_ET ACTIVE_TIME_SEC
FROM V$SESSION WHERE PADDR=(SELECT ADDR FROM V$PROCESS WHERE SPID=&PID);
Enter value for pid: 50845
old 3: FROM V$SESSION WHERE paddr=(SELECT addr FROM V$PROCESS WHERE SPID=&PID)
new 3: FROM V$SESSION WHERE paddr=(SELECT addr FROM V$PROCESS WHERE SPID=50845)
OSUSER DBUSE SID SERIAL# SQL_ID STATUS MACHINE PORT PROGRAM ACTIVE_TIME_SEC
------ ----- ----- ------- ------------- -------- ------------ ------ ------------------------------- ---------------
oracle SYS 373 5112 f1pbykq4q25ms ACTIVE dbpilot.net 0 sqlplus@dbpilot.net (TNS V1-V3) 181
and what SQL is being exected right now
SET LONG 200000000
SET LINES 300
SET PAGES 999
COL PARSING_SCHEMA_NAME FOR A20
COL SQL_FULLTEXT FOR A60
SELECT PARSING_SCHEMA_NAME,SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQL_ID';
Enter value for sql_id: f1pbykq4q25ms
old 1: SELECT PARSING_SCHEMA_NAME,SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQL_ID'
new 1: SELECT PARSING_SCHEMA_NAME,SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='f1pbykq4q25ms'
PARSING_SCHEMA_NAME SQL_FULLTEXT
-------------------- ------------------------------------------------------------
SYS DECLARE
l_result NUMBER;
BEGIN
FOR id IN 1..999999999
LOOP
l_result := (id/DBMS_RANDOM.VALUE + id*DBMS_RA
NDOM.VALUE)/DBMS_RANDOM.VALUE;
END LOOP;
END;
A complete SQL query to retrieve the same data
COL PID FOR A5
SELECT S.OSUSER,P.SPID PID,S.USERNAME DBUSER,S.SID,S.SERIAL#,
S.STATUS,S.LAST_CALL_ET ACTIVE_TIME_SEC, S.SQL_ID,
SQL.SQL_FULLTEXT FROM V$SESSION S, V$PROCESS P, V$SQL SQL
WHERE S.PADDR=P.ADDR AND S.SQL_ID=SQL.SQL_ID AND
S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER AND P.SPID='&PID';
Enter value for pid: 50845
old 3: WHERE S.PADDR=P.ADDR AND S.SQL_ID=SQL.SQL_ID AND S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER AND P.SPID='&PID'
new 3: WHERE S.PADDR=P.ADDR AND S.SQL_ID=SQL.SQL_ID AND S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER AND P.SPID='50845'
OSUSER PID DBUSE SID SERIAL# STATUS ACTIVE_TIME_SEC SQL_ID SQL_FULLTEXT
------ ----- ----- ----- ---------- -------- --------------- ------------- ------------------------------------------------------------
oracle 50845 SYS 373 5112 ACTIVE 416 f1pbykq4q25ms DECLARE
l_result NUMBER;
BEGIN
FOR id IN 1..999999999
LOOP
l_result := (id/DBMS_RANDOM.VALUE + id*DBMS_RA
NDOM.VALUE)/DBMS_RANDOM.VALUE;
END LOOP;
END;
So, we’ve found that the top CPU consumed process 50845 is executing sql_id f1pbykq4q25ms.
Version : 14:46 23.01.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Tags In
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (62)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (23)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
- Без рубрики (1)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum