SessionMonitoring &Issue finding

 

 

 

·       Generally,thereare3types ofissuesreportedbyclient–

Ø Atpresentthereisslownessonapplication

Ø Slownessreportedatanypastinterval

Ø SlownessreportedbyclientforanyparticularSQLID

 

·       Atpresentthereisslowness onapplication

Ø To checkblockingandwaitingsession(v$session)

Ø Checkanylongrunningsession

Ø Checkfornumberactivesession.Ifthere arepileupofmany sessions, if yes then further analysis needed.

Ø Checkalertlog.Checktablespaces,mount point.

Ø E.G – Deployment was stuck , after checking alert log found out thatparticulartablespacewasfull.Onceweaddeddatafilestuck session got release.

Ø CheckatOSlevelwhetherCPUutilizationandmemory consumption is normal.

Ø If not then check which processes are consuming the session checkwhether Itisoraclesessionoranyother OSlevelsession.

Ø Checkfor anyhangsession (v$sess_io)

Ø Askapplicationteamtocheckfrom there endor torestartthe application.

Ø Furtherproactiveanalysiswhichcanbedonefindouttheissue. (But it will take good amount of time).

Ø CheckWaiteventsinactivesessions-Dbsequentialread,DB scatter read, Enq SQ contention.

Ø Checkforexecutionplanandhistoricaldetailsofcurrentlyrunning SQL. If there is sudden change in plan, elapsed time or CPU time for query in past few days

·       Slownessreportedatanypastinterval

Ø CheckforAWRreport&ASHreport

Ø Checkalertlogforthat particulartime.

Ø CheckAWRreportforatimebeforeissue reportedandforatime after issue reported and for same time at early day.

v What tocheckinAWR–

Ø DBTime–

Ø DBCPU–

Ø Usercall–

Ø Transactions-

Ø InstanceEfficiencyPercentages-

Ø Sharedpoolstatistics

Ø Top10ForegroundEventsbyTotal Wait Time

Ø SQLorderedbyCPUtime-

Ø TopBlockingsessions-

·      SlownessreportedbyclientforanyparticularSQL ID –

Ø Checkblockingorlocking.

Ø ChecklongrunningSQL andCheckforactivesession.i.ethere could be some other queries that are going sub optimally and consuming huge resources which impacting application query

Ø CheckOS level(CPU,memory).

Ø CheckAlertLogs

Ø CheckwhetherqueryrunningnewlyondatabaseandifnotCheck for historical data of SQL.

Ø Checkforchangedinexecutionplan,elapsedtimeetc.Ifyes check what could be reasons for it?

Ø Checkfor statistics&fragmentationfor tablesusedinqueries

Ø Check for earlier plan and new plan difference. If earlier Index scanisgoingandnowitgoingfulltablescan,findreasonsforit.

Ø IfIndexarethereandnotpickedupbyquerycheckthepotential reasons.

Ø The potential reasons could be Indexes are in unusable state, function is used on indexed columns, Index column datatype and bind variable datatype is different, Index column is Varchar and literalvalueispassedisnumberwithoutsingleinverted,stats are stale etc.

Ø RunSqlAnalyserandcrosscheckitssuggestions

Ø Checkindexescanbesuggested,checksqlhintcanbesuggested to force the query to pick index or used parallel hint can be used.Check for partition of table . Check for tuning the query

Ø CheckSQLprofileorSQlBaselinecanbeset

 

 

 

Ø Checkforexecutionplan.

Ø  Historicaldata(dba_hist_sqlstat)

Ø  forcurrentexecutions:use v$sql

 

 

·        OtherReasonsforlossofperformanceofquery–

Ø Structuralchanges.

Ø Changesonthedata values.

Ø Aged/oldstatistics.

Ø Databaseupgrades.

Ø Databaseparameter.

Ø OS &hardwarechanges.

Ø Applicationchanges.