Troubleshooting
Problem
Yantra App ( DCS / DCM ) : Oracle database performance troubleshooting / check points / Bad queries / fragmentation / data size / Bad costly SQL V$SQLAREA V$SESSION
Symptom
Following are some of the queries / investigation points to look into for ANY
DB Performance issues :
* Oracle Statspack report
* Monitor DB using
Quest Spotlight / Instance Monitor
* Review Trace files
* Review high
disk, buffer gets, IO, Top SQLs
* Look for missing indexes from Base
product
* Identify additional custom indexes already created ( in rare
scenario's this can slow-down perf as well )
* Need for any additional
indexes based on Top IO / slow SQLs
Generic System / OS related
check-points :
* Machine horsepower info :
* CPUs, RAM
* CPU
load / utilization
* Memory load / utilization
* IO load : Check
vmstat, iostat
*
Specific to DCS :
* For tracing any specific
function / running session / print, follow "How to trace" solutions
* For
Print, find a existing / completed job requestand re-submit using
WMSYantrabg <WHSE_CODE> "whbu503 -R REQ_NO"
* Identify if all SQL objects
are valid
* Packages ( Base Yantra, Integration )
* Procedures (
Base Yantra, Integration )
* Sequences
* Triggers, if any ( ( Base
Yantra, Integration )
* Review prints for all SQL queries are utilizing
appropriate indexes
*
Specific to Platform / DCM / Yantra5x
*
Check if DB Caching is enabled
* JVM options are set as per tuning
guide
*
Cause
Resolving The Problem
<http://soldc.sun.com/articles/performance_tools.html>
Identify Bad SQL
queries :
select disk_reads
, buffer_gets
,
executions
, loads
, buffer_gets /
decode(executions,0,1,executions) "Average"
, 'Estimated Response =
'
, buffer_gets/decode(executions,0,1,executions)/500
"Response"
, sql_text
from v$sqlarea q
where executions >
50
and disk_reads > 1000
order by
disk_reads desc
, executions desc ;
=================
Following query identifies
* Tables with high extent fragmentation
* Due to Small extents
*
High data volume
* Look for if extent sizes are varying ( exponentially
increasing , a No-no, we recommend flat sizes )
SELECT
substr(tablespace_name, 1, 15) Tname,
segment_name,
segment_type,
substr(owner, 1, 10) "OWNER",
count(*) "Extents",
sum(bytes/(1024*1024)) SUM_MB,
blocks, min(bytes) MIN_EXT_SZ, max(bytes)
MAX_EXT_SZ, avg(bytes) AVG_EXT_SIZE
FROM
sys.dba_extents
WHERE
owner != 'SYS'
group by tablespace_name,
segment_name,
segment_type,
owner,
blocks
having count(*) >
&Threshold_extent_count
order by count(*)
desc
========================
Following query identifies a set of
tables with total DATA size above a user-input threshold
Things to look
out for :
* Are purges actively running
* Tables with high-data
volume are OK to hold that data volume as per purge criteria
*
SELECT
substr(tablespace_name, 1, 15) Tname,
segment_name,
segment_type,
substr(owner, 1, 10) "OWNER",
count(*) "Extents", sum(bytes/(1024*1024)) SUM_MB,
blocks, min(bytes)
MIN_EXT_SZ, max(bytes) MAX_EXT_SZ, avg(bytes) AVG_EXT_SIZE
FROM
sys.dba_extents
WHERE
owner != 'SYS'
and segment_name like
'YFS%'
group by tablespace_name,
segment_name,
segment_type,
owner,
blocks
having
sum(bytes/(1024*1024)) > &size_limit_MB
order by sum(bytes/(1024*1024)) desc
Historical Number
PRI48846
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21534230