Search This Blog

Total Pageviews

Thursday 30 June 2011

Unix Kill all the process for the user

latest command


Don't try this command on prod ...

$ kill -9 %1

Oracle Sort Info

Oracle Sort Info In PGA as well




select a.event,
a.sid,
c.serial# ,
c.sql_hash_value hash_value,
decode(d.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+)
and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance')
and a.sid = c.sid
and a.p1 = b.file_id
and a.event = 'direct path read'
union all
select a.event,
a.sid,
d.serial# ,
d.sql_hash_value hash_value,
decode(e.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX', null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_temp_files b, v$parameter c, v$session d, x$ktsso e
where d.saddr = e.ktssoses(+)
and d.serial# = e.ktssosno(+)
and e.inst_id(+) = userenv('instance')
and a.sid = d.sid
and b.file_id = a.p1 - c.value
and c.name = 'db_files'
and a.event = 'direct path read'
order by 1,2;

Oracle Weblogic Server patch info

Patch WDJ7 is mapped to patch 9438213 which is a combo patch includes the following patches:

- Patch 4D53 is mapped to patch 9100465
- Patch XLXA is mapped to patch 9221722
- Patch NIXN is mapped to patch 8990616


(Doc ID 1116655.1) - How to manually patch the 11g Grid Control Weblogic Server prior to installing Grid Control.

(Doc ID 1072763.1) - How to Download and Apply recommended WLS patch (WDJ7) on WLS home for 11g Grid Control Install/Upgrade

Oracle waiting ---


Oracle Waiting currently ......

Oracle Waits



select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , username, blocking_session,seconds_in_wait, wait_time ,event
from gv$session s 
where state = 'WAITING'
and wait_class != 'Idle';



set linesize 300 pagesize 300 
col SQL_TEXT for a70 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , s.sql_id ,s.prev_sql_id,sql_text from gv$session s, gv$sql q
where sid = &sid
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id )
and q.inst_id=s.inst_id;


select wait_class_id, wait_class,total_waits, time_waited from gv$session_wait_class where sid = &sid;



WAIT_CLASS_ID WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
------------- ---------------------------------------------------------------- ----------- -----------
1893977003 Other                                                                     31        7022
3875070507 Concurrency                                                               19          14
3386400367 Commit                                                                     2           0
2723168908 Idle                                                                      36        3300
2000153315 Network                                                                   36           0
1740759767 User I/O                                                               16344        1074

6 rows selected.


select event, total_waits, time_waited from v$system_event e, v$event_name n
where n.event_id = e.event_id
and e.wait_class_id = &wait_class_id ;


EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
os thread startup                                                      12601      148864
latch: cache buffers chains                                            65878       55103
buffer busy waits                                                       2393        1509
enq: TX - index contention                                                 8         458
latch: In memory undo latch                                                3           2
latch: row cache objects                                                 515         340
row cache lock                                                             4          45
cursor: mutex X                                                            1           0
cursor: pin S                                                            110         230
cursor: pin S wait on X                                                   13          22
latch: shared pool                                                      1219        7622
library cache lock                                                         5         227
library cache load lock                                                   17         260
library cache: mutex X                                                    82         301
resmgr:internal state change                                              54         734

15 rows selected.

Resetting v$filestat timings dbms_system.kcfrms

apt-rdbms-01.co.uk:APTDB\sys> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;

FILE# MAXIORTM
------------------ ------------------
1 71
2 131
3 8
4 1
5 1

apt-rdbms-01.co.uk:APTDB\sys> exec dbms_system.kcfrms();

PL/SQL procedure successfully completed.

apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;

FILE# MAXIORTM
------------------ ------------------
1 0
2 0
3 0
4 0
5 0

Oracle DBA

anuj blog Archive