Oracle

Oracle – tkprof – Extract all waits from output file using Powershell

Posted by

0

$astr=@("Event", "\*\*\*\*", "--------")
#Gets them all
#set up the regexp
$regex = '(?ms) Event waited on(.+?)\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'
#get-content gets an array of strings, join them all into one string separated by newline
$string=(Get-Content .\CCI1_ora_15824_2.txt) -join "`n"
# search for the regexp in the string and output the matches
Select-String -input $string -Pattern $regex -AllMatches | `
% { $_.Matches } | `
% { 
    $arr = $_.Value -split  "`n"
    foreach ($line in $arr) {
     # output only lines that do not contain the strings in the array, trim it and replace "more than one space" with a semi-colon
     Select-String -input $line -pattern $astr -NotMatch | ForEach-Object {$_.Line.Trim() -replace '  +',";"}
    }
}

Oracle – Idle Connections

Posted by

0

Determine connections that have been idle for more than a certain amount of time
(be aware that last_call_et also shows number of seconds active also)


Examples

-- Connections idle for > 60 minutes 
select
s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60) >60
order by username, last_call_et desc;


-- Connections idle for > 1 day 
select
s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60 / 60 /24) > 1
order by username, last_call_et desc;

-- Connections idle for > 5 days 

select s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60 / 60 /24) > 5
order by username, last_call_et desc;

Oracle – Find Linux Process ID from SID

Posted by

0

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid "Linux process id", s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;


-- Get Linux processes that have no related session(s)
select inst_id, spid, program from gv$process where addr not in ( select paddr from gv$session ) and spid is not null


-- Generate Linux kill commands for processes that have no sessions (sessions that may have been killed at Oracle level
select p.inst_id,s.sid, s.serial#, s.program, s.username, p.spid, p.username,
       'kill -9 '||p.spid
from 
gv$session s
RIGHT OUTER JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where p.spid in(
SELECT spid
FROM   gv$process
WHERE NOT EXISTS (SELECT 1
                  FROM gv$session
                  WHERE paddr = addr));

Oracle – Quick Check using Scripts

Posted by

0

Not exhaustive


select * from v$sga_target_advice

select * from v$pga_target_advice

select * from v$shared_pool_advice


-- Dictionary Cache Hit Ratio
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
  FROM   v$rowcache;

 -- -----------------------
  -- Library Cache Hit Ratio
  -- -----------------------
  SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
  FROM   v$librarycache;

 -- -------------------------------
  -- DB Block Buffer Cache Hit Ratio
  -- -------------------------------
  SELECT (1 - (phys.value / (db.value + cons.value))) * 100
  FROM   v$sysstat phys,
         v$sysstat db,
         v$sysstat cons
  WHERE  phys.name  = 'physical reads'
  AND    db.name    = 'db block gets'
  AND    cons.name  = 'consistent gets';

-- ---------------
  -- Latch Hit Ratio
  -- ---------------
  SELECT (1 - (Sum(misses) / Sum(gets))) * 100
  FROM   v$latch;

-- -----------------------
  -- Disk Sort Ratio
  -- -----------------------
  SELECT (disk.value/mem.value) * 100
  FROM   v$sysstat disk,
         v$sysstat mem
  WHERE  disk.name = 'sorts (disk)'
  AND    mem.name  = 'sorts (memory)';

-- Locks
SELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text
FROM v$session a,v$lock b, v$sqlarea c   WHERE b.type in ('TM','TX','UL') and   a.sid=b.sid and 
 lmode > 0 and  ((a.PREV_HASH_VALUE = c.hash_value and  a.prev_sql_addr = c.address and  a.sql_hash_value = 0) or  
(c.hash_value=a.sql_hash_value and c.address = a.sql_address))