How to know what is going on inside the Database
Sometimes we are very anxious to know,if any user or customer reports an issue related to Database.We are quite confused that time what to check and how to take the actions on database.Based on some experts and few oracle documentations ,i have summarized few points which will be quite helpful for me as well as readers to know the inside things of Oracle.
Oracle mainly plays with 3 things.
I.CPU
II.Memory
III.I/O
If any of them comes to bottleneck it starts causing performance issues.Here is few below steps which i usually follow to monitor the CPU bottlenecks.
3 Steps
1.Issue a top (Unix/Linux/any flavour) command in bash prompt
2.Get SID from the top CPU Consuming process ids.
3.Get the details about the SID like SQLs,Messages everything whatever you want.
bash-3.2$ top
load averages: 13.08, 14.59, 16.48 03:35:22
1497 processes:1486 sleeping, 11 on cpu
CPU states: 80.1% idle, 14.1% user, 5.8% kernel, 0.0% iowait, 0.0% swap
Memory: 256G real, 83G free, 158G swap in use, 40G swap free
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
27490 orcl 1 0 0 0K 0K cpu/46 52:38 1.56% oracle
27520 orcl 1 0 0 0K 0K cpu/37 52:10 1.56% oracle
27523 orcl 1 0 0 0K 0K cpu/20 52:06 1.56% oracle
27554 orcl 1 0 0 0K 0K cpu/61 51:25 1.56% oracle
27610 orcl 1 0 0 0K 0K cpu/29 51:16 1.56% oracle
26153 orcl 2 40 0 0K 0K cpu/40 31:59 1.28% oracle
9784 orcl 2 0 0 0K 0K sleep 17:39 1.22% oracle
27492 orcl 2 20 0 0K 0K sleep 46:26 1.03% oracle
19133 orcl 2 30 0 0K 0K cpu/4 2:50 1.02% oracle
27509 orcl 2 30 0 0K 0K cpu/33 46:04 0.98% oracle
-Few SQL Queries-
FINDING SID From PID
SQL> select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=&processid;
Enter value for processid: 939
old 1: select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=&processid
new 1: select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=27490
SID SPID
------------------------------------------
3648 27490
SQL>select SID,SERIAL#,SOFAR,TOTALWORK,TIME_REMAINING from v$session_longops where SID='3648';
It will report the session information and timing details of the currently long running jobs.
Finding what each Oracle process is doing
SQL>
The above sql will give the sql details.
If this is a concurrent request related problem ,see here to HOW TO GET SQL_ID & SQL_TEXT FOR CONCURRENT REQUEST TROUBLESHOOTING.
Your knowledge sharing and valuable suggestions will be highly appreciated....
Happy Learning.........
Sometimes we are very anxious to know,if any user or customer reports an issue related to Database.We are quite confused that time what to check and how to take the actions on database.Based on some experts and few oracle documentations ,i have summarized few points which will be quite helpful for me as well as readers to know the inside things of Oracle.
Oracle mainly plays with 3 things.
I.CPU
II.Memory
III.I/O
If any of them comes to bottleneck it starts causing performance issues.Here is few below steps which i usually follow to monitor the CPU bottlenecks.
3 Steps
1.Issue a top (Unix/Linux/any flavour) command in bash prompt
2.Get SID from the top CPU Consuming process ids.
3.Get the details about the SID like SQLs,Messages everything whatever you want.
bash-3.2$ top
load averages: 13.08, 14.59, 16.48 03:35:22
1497 processes:1486 sleeping, 11 on cpu
CPU states: 80.1% idle, 14.1% user, 5.8% kernel, 0.0% iowait, 0.0% swap
Memory: 256G real, 83G free, 158G swap in use, 40G swap free
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
27490 orcl 1 0 0 0K 0K cpu/46 52:38 1.56% oracle
27520 orcl 1 0 0 0K 0K cpu/37 52:10 1.56% oracle
27523 orcl 1 0 0 0K 0K cpu/20 52:06 1.56% oracle
27554 orcl 1 0 0 0K 0K cpu/61 51:25 1.56% oracle
27610 orcl 1 0 0 0K 0K cpu/29 51:16 1.56% oracle
26153 orcl 2 40 0 0K 0K cpu/40 31:59 1.28% oracle
9784 orcl 2 0 0 0K 0K sleep 17:39 1.22% oracle
27492 orcl 2 20 0 0K 0K sleep 46:26 1.03% oracle
19133 orcl 2 30 0 0K 0K cpu/4 2:50 1.02% oracle
27509 orcl 2 30 0 0K 0K cpu/33 46:04 0.98% oracle
FINDING SID From PID
SQL> select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=&processid;
Enter value for processid: 939
old 1: select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=&processid
new 1: select s.sid,p.spid from v$process p, v$session s where s.paddr = p.addr and p.spid=27490
SID SPID
------------------------------------------
3648 27490
SQL>select SID,SERIAL#,SOFAR,TOTALWORK,TIME_REMAINING from v$session_longops where SID='3648';
It will report the session information and timing details of the currently long running jobs.
Finding what each Oracle process is doing
SQL>
set lines 200 pages 1000
column pu format a8 heading 'O/S|Login|ID'
column su format a8 heading 'Oracle|User ID'
column stat format a8 heading 'Session|Status'
column ssid format 999999 heading 'Oracle|SID'
column sser format 999999 heading 'Oracle|Serial|#'
column spid format 999999 heading 'UNIX|Process|ID'
column txt format a28 heading 'Current Statment'
SELECT p.username pu, s.username su, s.status stat, s.sid ssid, s.serial# sser, lpad(p.spid,7) spid,
substr(sa.sql_text,1,540) txt
FROM v$process p, v$session s, v$sqlarea sa
WHERE p.addr=s.paddr AND s.username is not null
AND s.sql_address=sa.address(+)
AND s.sql_hash_value=sa.hash_value(+)
AND s.status='ACTIVE' ORDER BY 1,2,7;
If this is a concurrent request related problem ,see here to HOW TO GET SQL_ID & SQL_TEXT FOR CONCURRENT REQUEST TROUBLESHOOTING.
Your knowledge sharing and valuable suggestions will be highly appreciated....
Happy Learning.........
Comments
Post a Comment