Oracle who is taking more CPU in last 15 Min !!!!!!!!!!!!!

most CPU in last 15 minutes
Sql Taking more CPU
last wait in 15 min


event last 15 Min

SELECT a.event, sum(a.wait_time + a.time_waited) total_waits
FROM v$active_session_history a
WHERE a.sample_time between sysdate-1/24/4
AND sysdate
GROUP BY a.event
ORDER BY 2 desc;


EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
control file parallel write 5718745
4459398
log file parallel write 3522504
os thread startup 2449356
db file sequential read 624895
control file sequential read 108942
db file async I/O submit 96203
db file scattered read 0




SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= 'ON CPU'
AND sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
ORDER BY count(*) desc
)
where rownum <= 5;




for 15 row

SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= 'ON CPU'
AND sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
ORDER BY count(*) desc
)
where rownum <= 15;





SELECT SQL_TEXT FROM V$SQL WHERE sql_id = '&sql_id';

Some Unix useful command

vmstat – System Activity, Hardware And System Information

The command vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity.

# vmstat 3


Display Memory Utilization Slabinfo

# vmstat -m


Get Information About Active / Inactive Memory Pages

# vmstat -a

procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
r b swpd free inact active si so bi bo in cs us sy id wa st
1 0 1032808 106792 560216 1221460 3 2 34 75 2 1 35 19 44 1 0









Proc:
-------
r: How many processes are waiting for CPU time.
b: Wait Queue - Process which are waiting for I/O (disk, network, user input,etc..)


Memory:
-----------
swpd: shows how many blocks are swapped out to disk (paged). Total Virtual memory usage.

Note: you can see the swap area configured in server using "cat proc/swaps"


free: Idle Memory
buff: Memory used as buffers, like before/after I/O operations
cache: Memory used as cache by the Operating System


Swap:
---------
si: How many blocks per second the operating system is swapping in. i.e
Memory swapped in from the disk (Read from swap area to Memory)
so: How many blocks per second the operating system is swaped Out. i.e
Memory swapped to the disk (Written to swap area and cleared from
Memory)


In Ideal condition, We like to see si and so at 0 most of the time, and we definitely don’t like to see more than 10 blocks per second.


IO:
------
bi: Blocks received from block device - Read (like a hard disk)
bo: Blocks sent to a block device - Write


System:
-------------
in: The number of interrupts per second, including the clock.
cs: The number of context switches per second.


CPU:
--------
us: percentage of cpu used for running non-kernel code. (user time, including
nice time)
sy: percentage of cpu used for running kernel code. (system time - network, IO
interrupts, etc)
id: cpu idle time in percentage.
wa: percentage of time spent by cpu for waiting to IO.











/home/anujs # ps -C oracle -o pid=
1506
8212
8214
10271
14009
14013
14015
14019
14021
14023
14025
14027
14029
14031
14033
14035
14037
14039
14041
14043
14045
14047
14125
14131
14133
14135
14150
14165
14174
14216
14467
18275
25441
27346
27614
27773
28357
28924
29032
30518
30520
30522



pgrep oracle
1506
8212
8214
10271
14009
14013
14015
14019
14021
14023
14025
14027
14029
14031
14033
14035
14037
14039
14041
14043
14045
14047
14125
14131
14133
14135
14150
14165
14174
14216
14467
18275
25441
27346
27614
27773
28357
28924
29032
30518
30520
30522


or

ps -ef|grep oracle |awk {'print $2'}

ps -ef|grep oracle|grep -v grep |awk {'print $2'}





Find Out The Top 10 Memory Consuming Process

# ps -auxf | sort -nr -k 4 | head -10


Find Out top 10 CPU Consuming Process

# ps -auxf | sort -nr -k 3 | head -10



Multiprocessor Usage

The mpstat command displays activities for each available processor, processor 0 being the first one. mpstat -P ALL to display average CPU utilization per processor:

# mpstat -P ALL




pmap – Process Memory Usage

The command pmap report memory map of a process. Use this command to find out causes of memory bottlenecks.
# pmap -d PID

To display process memory information for pid # 47394, enter:

# pmap -d 47394




ps -FAL

anujs 31253 31204 31253 0 1 64964 2256 0 Aug04 ? 00:00:09 /usr/bin/akonadi_vcard_resource --identifier akonadi_vcard_resource_0
anujs 31730 15911 31730 3 2 225930 71560 0 09:57 ? 00:09:24 /opt/google/chrome/chrome --channel=15906.2c88550.547559033 --type=renderer --lang=en-GB --force-fieldtes
anujs 31730 15911 31732 0 2 225930 71560 0 09:57 ? 00:00:01 /opt/google/chrome/chrome --channel=15906.2c88550.547559033 --type=renderer --lang=en-GB --force-fieldtes
anujs 31766 15911 31766 1 2 233705 105644 0 09:58 ? 00:03:03 /opt/google/chrome/chrome --channel=15906.2df0e00.1200530882 --type=renderer --lang=en-GB --force-fieldte
anujs 31766 15911 31768 0 2 233705 105644 0 09:58 ? 00:00:01 /opt/google/chrome/chrome --channel=15906.2df0e00.1200530882 --type=renderer --lang=en-GB --forc




# ps -Al
To turn on extra full mode (it will show command line arguments passed to process):
# ps -AlF

To See Threads ( LWP and NLWP)
# ps -AlFH


To See Threads After Processes
# ps -AlLm


Print All Process On The Server
# ps ax

# ps axu


Print A Process Tree


# ps -ejH

28359 28359 28359 pts/1 00:00:00 bash
925 925 925 pts/2 00:00:00 bash
944 944 925 pts/2 00:00:00 su
954 954 925 pts/2 00:00:00 bash
10270 10270 925 pts/2 00:00:00 sqlplus
10271 10271 10271 ? 00:00:07 oracle
2797 2797 2797 pts/3 00:00:00 bash
18761 18761 2797 pts/3 00:00:00 su
18765 18765 2797 pts/3 00:00:00 bash


# ps axjf

ps axjf
PPID PID PGID SID TTY TPGID STAT UID TIME COMMAND
0 2 0 0 ? -1 S 0 0:00 [kthreadd]
2 3 0 0 ? -1 S 0 0:00 \_ [migration/0]


10270 10271 10271 10271 ? -1 Ss 1001 0:07 | \_ oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
28351 2797 2797 2797 pts/3 18765 Ss 1000 0:00 \_ /bin/bash
2797 18761 18761 2797 pts/3 18765 S 0 0:00 | \_ su
18761 18765 18765 2797 pts/3 18765 S+ 0 0:00 | \_ bash
28351 3260 3260 3260 pts/5 3260 Ss+ 1000 0:00 \_ /bin/bash
28351 16674 16674 16674 pts/6 16674 Ss+ 1000 0:00 \_ /bin/bash
28351 25436 25436 25436 pts/7 25436 Ss+ 1000 0:00 \_ /bin/bash
28351 16223 16223 16223 pts/10 17014 Ss 1000 0:00 \_ /bin/bash




Set Output In a User-Defined Format

# ps -eo pid,tid,class,rtprio,ni,pri,psr,pcpu,stat,wchan:14,comm
# ps axo stat,euid,ruid,tty,tpgid,sess,pgrp,ppid,pid,pcpu,comm



# ps -eopid,tt,user,fname,tmout,f,wchan

# ps -eopid,tt,user,fname,tmout,f,wchan
PID TT USER COMMAND TMOUT F WCHAN
1 ? root init - 4 5560
2 ? root kthreadd - 1 kthreadd
3 ? root migratio - 1 migration_thread
4 ? root ksoftirq - 1 run_ksoftirqd
5 ? root watchdog - 5 watchdog
6 ? root events/0 - 1 worker_thread
7 ? root netns - 1 worker_thread
8 ? root async/mg - 1 async_manager_thread
9 ? root pm - 1 worker_thread
10 ? root sync_sup - 1 bdi_sync_supers
11 ? root bdi-defa - 1 -
12 ? root kintegri - 1 worker_thread
13 ? root kblockd/ - 1 worker_thread
14 ? root kacpid - 1 worker_thread






top


Hot Key


t – Displays summary information on and off.

m – Displays memory information on and off.

A – Sorts the display by top consumers of various system resources. Useful for quick identification of performance-hungry tasks on the system.

f – Enters an interactive configuration screen for top. Helpful for setting up top for a specific task.

o – Enables you to interactively select the ordering within top.

r – Issues renice command.

k – Issues kill command.

z – Turn color/mono on or off.






# pstree

# pstree
init─┬─acpid
├─akonadi_control─┬─2*[akonadi_ical_re]
│ ├─akonadi_maildir
│ ├─akonadi_maildis
│ ├─akonadi_nepomuk───{akonadi_nepomuk}
│ ├─akonadi_vcard_r
│ ├─akonadiserver─┬─mysqld───13*[{mysqld}]
│ │ └─10*[{akonadiserver}]
│ └─3*[{akonadi_control}]
├─auditd───{auditd}
├─avahi-daemon
├─avahi-dnsconfd
├─bacula-fd───{bacula-fd}
├─chrome───3*[chrome───{chrome}]
├─console-kit-dae───63*[{console-kit-dae}]
├─cron



pmap – Process Memory Usage

The command pmap report memory map of a process. Use this command to find out causes of memory bottlenecks.

# pmap -d PID

To display process memory information for pid # 47394, enter:

# pmap -d 47394
Sample Outputs:



iostat interval count

only device
iostat -d


iostat 10 5


iostat 10 5
Linux 2.6.34.7-0.5-desktop (apt-amd-02) 03/10/11 _x86_64_

avg-cpu: %user %nice %system %iowait %steal %idle
34.96 0.03 19.54 1.20 0.00 44.35

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 8.15 68.16 149.04 372632289 814740822
sda1 0.00 0.00 0.00 1186 34
sda2 0.36 6.97 4.61 38082698 25181888
sda3 0.60 17.08 3.85 93353181 21048118
sda4 5.69 44.12 140.58 241195000 768510782
dm-0 12.18 10.57 94.26 57778914 515299512
dm-1 4.62 33.55 46.32 183415726 253211270

Oracle find full table scan and Cartesian join

Oracle full table scan

Find the sql and tune , if possible

"full scans are not evil, indexes are not good"




Cartesian Joins
Cartesian join is formed when data is retrieved from multiple tables without writing necessary join conditions

SELECT COUNTRY_NAME, REGION_NAME FROM COUNTRIES, REGIONS;
20 rows in the COUNTRIES table and 4 rows in the REGIONS table
This will process 20*4 = 80 rows





SELECT SQL_TEXT ,
USERNAME ,
DISK_READS_PER_EXEC,
BUFFER_GETS ,
DISK_READS,
PARSE_CALLS ,
SORTS ,
EXECUTIONS ,
ROWS_PROCESSED ,
HIT_RATIO,
FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
CPU_TIME,
ELAPSED_TIME,
ADDRESS,
HASH_VALUE,
sql_id
FROM
(SELECT SQL_TEXT ,
B.USERNAME ,
ROUND((A.DISK_READS/DECODE(A.EXECUTIONS,0,1,A.EXECUTIONS)),2)
DISK_READS_PER_EXEC,
A.DISK_READS ,
A.BUFFER_GETS ,
A.PARSE_CALLS ,
A.SORTS ,
A.EXECUTIONS ,
A.ROWS_PROCESSED ,
100 - ROUND(100 *
A.DISK_READS/GREATEST(A.BUFFER_GETS,1),2) HIT_RATIO,
A.FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
CPU_TIME,
ELAPSED_TIME,
ADDRESS,
HASH_VALUE,
sql_id
FROM SYS.V_$SQLAREA A, SYS.ALL_USERS B
WHERE A.PARSING_USER_ID=B.USER_ID AND
-- B.USERNAME NOT IN ('SYS','SYSTEM')
B.USERNAME NOT IN
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
ORDER BY 3 DESC)
WHERE ROWNUM < 21





set linesize 200
col sql_text format a100
select sql_id,substr(sql_text,1,100) sql_text from sys.v_$sql
where hash_value in (select hash_value
from sys.v_$sql_plan
where options = 'CARTESIAN'
AND operation LIKE '%JOIN%'
-- and OBJECT_OWNER='PROD1'
)
and PARSING_SCHEMA_NAME not in
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
order by hash_value




For All the user

SELECT
sql_id,
sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
FROM
(SELECT c.sql_id sql_id,
sql_text,
count(*) total_large_scans,
executions
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND c.hash_value = a.hash_value
AND b.bytes/1024 > 1024
group by c.sql_id,sql_text, executions)
order by 4 desc




For not a system user

SELECT
sql_id,
sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
FROM
(SELECT c.sql_id sql_id,
sql_text,
count(*) total_large_scans,
executions
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND c.hash_value = a.hash_value
AND b.bytes/1024 > 1024
AND c.PARSING_SCHEMA_NAME not in
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
group by c.sql_id,sql_text, executions)
order by 4 desc




col operation for a45
SELECT
LPAD(' ',depth)||P.OPERATION||'_'||P.OPTIONS||' '||P.OBJECT_NAME operation
, last_starts * cardinality e_rows_x_starts
, last_output_rows a_rows
, LAST_CR_BUFFER_GETS bgets
, LAST_DISK_READS pread
, LAST_DISK_WRITES pwrites
, LAST_ELAPSED_TIME elapsed
, LAST_MEMORY_USED
, LAST_TEMPSEG_SIZE
, LAST_EXECUTION
FROM V$SQL_PLAN_statistics_all P
WHERE sql_id='&sql_id'
order by child_number,id
/





col cn format 99
col ratio format 99
col ratio1 format A6
--set pagesize 1000
set linesize 140
break on sql_id on cn
col lio_rw format 999
col "operation" format a60
col a_rows for 999,999,999
col e_rows for 999,999,999
col elapsed for 999,999,999
col TCF_GRAPH format a20
Def v_sql_id=&SQL_ID

select
-- sql_id,
--hv,
childn cn,
--ptime, stime,
case when stime - nvl(ptime ,0) > 0 then
stime - nvl(ptime ,0)
else 0 end as elapsed,
nvl(trunc((lio-nvl(plio,0))/nullif(a_rows,0)),0) lio_ratio,
--id,
--parent_id,
--starts,
--nvl(ratio,0) TCF_ratio,
' '||case when ratio > 0 then
rpad('-',ratio,'-')
else
rpad('+',ratio*-1 ,'+')
end as TCF_GRAPH,
starts*cardinality e_rows,
a_rows,
--nvl(lio,0) lio, nvl(plio,0) parent_lio,
"operation"
from (
SELECT
stats.LAST_ELAPSED_TIME stime,
p.elapsed ptime,
stats.sql_id sql_id
, stats.HASH_VALUE hv
, stats.CHILD_NUMBER childn
, to_char(stats.id,'990')
||decode(stats.access_predicates,null,null,'A')
||decode(stats.filter_predicates,null,null,'F') id
, stats.parent_id
, stats.CARDINALITY cardinality
, LPAD(' ',depth)||stats.OPERATION||' '||
stats.OPTIONS||' '||
stats.OBJECT_NAME||
DECODE(stats.PARTITION_START,NULL,' ',':')||
TRANSLATE(stats.PARTITION_START,'(NRUMBE','(NR')||
DECODE(stats.PARTITION_STOP,NULL,' ','-')||
TRANSLATE(stats.PARTITION_STOP,'(NRUMBE','(NR') "operation",
stats.last_starts starts,
stats.last_output_rows a_rows,
(stats.last_cu_buffer_gets+stats.last_cr_buffer_gets) lio,
p.lio plio,
trunc(log(10,nullif
(stats.last_starts*stats.cardinality/
nullif(stats.last_output_rows,0),0))) ratio
FROM
v$sql_plan_statistics_all stats
, (select sum(last_cu_buffer_gets + last_cr_buffer_gets) lio,
sum(LAST_ELAPSED_TIME) elapsed,
child_number,
parent_id,
sql_id
from v$sql_plan_statistics_all
group by child_number,sql_id, parent_id) p
WHERE
stats.sql_id='&v_sql_id' and
p.sql_id(+) = stats.sql_id and
p.child_number(+) = stats.child_number and
p.parent_id(+)=stats.id
)
order by sql_id, childn , id
/