SQL script to know what and all changes patch has done in Instance
col patch_name format a8 heading “Patch|Name”
col instance_name format a10 heading “Instance|Name”
col name format a10 heading “Node Name”
col APPL_TOP format a10
col subdir format a20
col filename format a18
col latest format a20
col driver_file_name format a12 heading “Driver|File|Name”
col action format a10
set lines 150
set pages 1000
Select
J.PATCh_NAME,
H.APPLICATIONS_SYSTEM_NAME Instance_Name,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME appl_top,
D.SUBDIR,
D.FILENAME,
max(F.VERSION) latest,
E.ACTION_CODE action
from
AD_BUGS A,
AD_PATCH_RUN_BUGS B,
AD_PATCH_RUN_BUG_ACTIONS C,
AD_FILES D,
AD_PATCH_COMMON_ACTIONS E,
AD_FILE_VERSIONS F,
AD_PATCH_RUNS G,
AD_APPL_TOPS H,
AD_PATCH_DRIVERS I,
AD_APPLIED_PATCHES J
where
A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and
C.FILE_ID = D.FILE_ID and
E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and
D.FILE_ID = F.FILE_ID and
G.APPL_TOP_ID = H.APPL_TOP_ID and
G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and
B.PATCH_RUN_ID = G.PATCH_RUN_ID and
C.EXECUTED_FLAG = ‘Y’ and
G.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = ‘&Patch_Number’))
GROUP BY
J.PATCH_NAME,
H.APPLICATIONS_SYSTEM_NAME,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME,
D.SUBDIR,
D.FILENAME,
E.ACTION_CODE;
Shell Program to get Total Memory being consumed by FORMS Process
#!/bin/sh
ALLPROCS=`ps -ef|grep “f60webmx webfile=” | grep -v grep | tr -s ” ” | cut -f3 -d” “`
LOOPCTR=1
for EACHPROC in $ALLPROCS
do
if [ $LOOPCTR -eq 1 ]
then PROCLIST=”${EACHPROC}”
LOOPCTR=2
else PROCLIST=”${PROCLIST},${EACHPROC}”
fi
done
# echo $PROCLIST
# ps -lf -p $PROCLIST -o rss,vsz,osz > f60pro…cinfo.out
TOTMEM=0
PROCCTR=0
PROCMEM=`ps -lf -p $PROCLIST -o rss,vsz,osz | tail +2 | cut -f1 -d” “`
for EACHMEM in $PROCMEM
do
TOTMEM=`expr $TOTMEM + $EACHMEM`
PROCCTR=`expr $PROCCTR + 1`
done
echo “The total memory for the ${PROCCTR} f60 forms processes is ${TOTMEM} KB”
Script to Check for active and inactive JDBC Sessions
set lines 120
set pages 500
column module heading “Module Name” format a48;
column machine heading “Machine Name” format a25;
column process heading “Process ID” format a10;
column inst_id heading “Instance ID” format 99;
column inst_id heading “Program” format a15;
column username for a10
column sid for 9999
column sql_text for a5…0
prompt =======================================================
prompt JDBC Connections
select to_char(sysdate, ‘dd-mon-yyyy hh24:mi’) Time from dual
/
prompt =======================================================
prompt
prompt JDBC Connection Usage Per JVM Process
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), process from v$session
where program like ‘%JDBC%’
group by process
order by 1 asc
/
prompt
prompt Connection Usage Per Module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), module
from v$session
where program like ‘%JDBC%’
group by module
order by 1 asc
/
prompt
prompt Connection Usage Per process and module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), process, module
from v$session
where program like ‘%JDBC%’
group by process, module
order by 1 asc
/
prompt
prompt Idle connections for more than 3 hours
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*),machine, program
from v$session
where program like ‘%JDBC%’
and last_call_et > 3600 *3
group by machine, program
/
prompt
prompt Active connections which are taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from v$session
where program like ‘%JDBC%’
and last_call_et > 600
and status = ‘ACTIVE’
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from v$session s, v$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like ‘%JDBC%’
and s.last_call_et > 600
and s.status = ‘ACTIVE’
/
prompt
prompt Active connections which are taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from v$session
where program like ‘%JDBC%’
and last_call_et > 1200
and status = ‘ACTIVE’
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from v$session s, v$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like ‘%JDBC%’
and s.last_call_et > 1200
and s.status = ‘ACTIVE’
/
prompt
prompt Active connections which are taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from v$session
where program like ‘%JDBC%’
and last_call_et > 1800
and status = ‘ACTIVE’
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from v$session s, v$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like ‘%JDBC%’
and s.last_call_et > 1800
and s.status = ‘ACTIVE’
/
prompt
prompt Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = ‘APPLSYSPUB’
and s.sql_hash_value= t.hash_value
and t.sql_text like ‘%fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3%’;
prompt =======================================================
SCRIPT TO KNOW “ACTIVE USERS” FOR OACOREGROUP
REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select ‘Number of user sessions : ‘ || count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != ‘Y’ and PSEUDO_FLAG = ‘N’ and (last_connect + decode(FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’)/60)/24) > sysdate and counter < limit_connects;
REM
REM END OF SQL
REM
HOW TO DETERMINE “ACTIVE FORMS USERS” FOR FORMSGROUP
Check the number of f60webmx processes on the Middle Tier server.
For example:
ps -ef | grep f60webx | wc -l
Oracle Tuning And Diagnostics Script — Active Session Info
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece
Trace SQL Query Average Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s
WHERE s.sql_id=’4n01r8z5hgfru’
Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))
Session Elapsed Processing Time
SELECT s.sid, s.username, s.module,
round(t.VALUE/1000000,2) “Elapsed Processing Time (Sec)”
FROM v$sess_time_model t, v$session s
WHERE t.sid = s.sid
AND t.stat_name = ‘DB time’
AND s.username IS NOT NULL
AND t.VALUE/1000000 >= ‘1’ –running more than 1 second
ORDER BY round(t.VALUE/1000000,2) DESC
Session Elapsed Processing Time Statistic By SID
SELECT a.sid, b.username, a.stat_name, ROUND((a.VALUE/1000000),2) “Time (Sec)”
FROM v$sess_time_model a, v$session b
WHERE a.sid = b.sid
AND b.sid = ‘194’
ORDER BY ROUND((a.VALUE/1000000),2) DESC
Use Longops To Check The Estimation Query Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,
elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,
round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> ‘0’
Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’
Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece
RAC Active Table Locking
SELECT b.sid, b.serial#, a.spi0A Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’
Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece
RAC Active Table Locking
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text,
b.state, c.sql_text,b.logon_time,
b.STATUS, e.owner, e.object_name “Table Lock”
FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece
Monitor Highest SQL Wait Time Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Monitor Highest Object Wait Time Using Active Session History (ASH)
SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, dba_objects o, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
GROUP BY o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Monitor Highest Event Wait Time Using Active Session History (ASH)
SELECT h.event “Wait Event”, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.event_id = e.event_id
AND e.wait_class <> ‘Idle’
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC
Database Time Model Statistic
SELECT wait_class, NAME, ROUND (time_secs, 2) “Time (Sec)”,
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
(SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event
AND n.wait_class <> ‘Idle’
AND time_waited > 0
UNION
SELECT
‘CPU’,
‘Server CPU’,
SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN (‘background cpu time’, ‘DB CPU’))
ORDER BY time_secs DESC;
Monitor I/O On Data Files
SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim
FROM v$filestat vfs, dba_data_files dbf
WHERE vfs.file# = dbf.file_id
I/O Stats For Data Files & Temp Files
SELECT file_no,
filetype_name,
small_sync_read_reqs “Synch Single Block Read Reqs”,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
round(small_sync_read_latency/1000,2) “Single Block Read Latency (s)”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
async_io “Asynch I/O Availability”
FROM v$iostat_file
WHERE filetype_id IN (2,6) –data file and temp file
I/O Stats By Functionality
SELECT function_name,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
number_of_wait “I/O Waits”,
round(wait_time/1000,2) “Total Wait Time (ms)”
FROM v$iostat_function
ORDER BY function_name
Temporary Tablespace Usage By SID
SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype,
tu.extents, tu.blocks, SQL.sql_text
FROM v$tempseg_usage tu, v$session s, v$sql SQL
WHERE tu.session_addr = s.addr
AND tu.session_num = s.serial#
AND s.sql_id = SQL.sql_id
AND s.sql_address = SQL.address
Monitor Overall Oracle Tablespace
SELECT d.STATUS “Status”,
d.tablespace_name “Name”,
d.contents “Type”,
d.extent_management “Extent Management”,
d.initial_extent “Initial Extent”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”,
TO_CHAR(NVL(a.bytes – NVL(f.bytes, 0), 0)/1024/1024,’99,999,999.999′) “Used (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) “Used %”,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),’99,999,990.900′) “MaxSize (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.maxbytes * 100, 0), ‘990.00’) “Used % of Max”
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,’NO’,bytes,’YES’,maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;
Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
‘physical reads’, VALUE,0))/
(SUM(DECODE(name, ‘db block gets’, VALUE,0))+
(SUM(DECODE(name, ‘consistent gets’, VALUE, 0))))))*100),2)
|| ‘%’ “Buffer Cache Hit Ratio”
FROM v$sysstat –Use gv$sysstat if running on RAC environment
Library Cache Hit Ratio
SELECT SUM(pins) “Total Pins”, SUM(reloads) “Total Reloads”,
SUM(reloads)/SUM(pins) *100 libcache
FROM v$librarycache –Use v$librarycache if running on RAC environment
DB Session Memory Usage
SELECT se.sid,n.name, MAX(se.VALUE) maxmem
FROM v$sesstat se, v$statname n
WHERE n.statistic# = se.statistic#
AND n.name IN (‘session pga memory’,’session pga memory max’,
‘session uga memory’,’session uga memory max’)
GROUP BY n.name, se.sid
ORDER BY MAX(se.VALUE)
Nagulu Polagani
Latest posts by Nagulu Polagani (see all)
- ORA-12012 ORA-20001 ORA-06512: at “SYS.DBMS_STATS” - April 3, 2018
- PKIX path building failed, unable to find valid certification path - January 30, 2018
- ORA-16057: server not in Data Guard configuration - December 19, 2017