Performance Investigation(Database / SQL)

Share via:

Dear Readers,

In this article, we will see Performance Investigation(Database / SQL) .

Performance Investigation Database

UNDERSTANDING:
Database running slow or application running slow. These are the basic and practical issue come in day today activity.
Before Investigation have below answers from application or client or users

👉 When this issue happens.
👉 When last it ran successfully.
👉 What exactly issue is.
👉 Any patching / upgrade happen in application side.
👉 Any data Volume change or Any change in job

INVESTIGATION:
When Following things:

👉Load average of OS
👉Get SWAP memory status (Total & Free) from TOP command
👉Get OSPID which is consuming high
👉Check Filesystem, mountpoints, ASM storage.
👉Get output of vmstat,iostat.
👉Check for blocking, ORA error in DB alert log.
👉Check if any backup running during issue period.
👉Check it any lag in Golden gate or Data Guard process.

load averages: 0.86, 1.02, 1.09
520 processes: 518 sleeping, 2 on cpu
CPU states: 97.4% idle, 1.3% user, 1.3% kernel, 0.0% iowait, 0.0% swap
Kernel: 24410 ctxsw, 8195 trap, 17687 intr, 20406 syscall, 44 fork, 6215 flt
Memory: 60G phys mem, 5663M free mem, 60G total swap, 55G free swap

PID USERNAME NLWP PRI NICE SIZE RES STATE TIME CPU COMMAND
13857 oracle 7 60 0 28G 28G sleep 0:00 0.12% oracle

SOLUTION:
1.Get sql_id,username,sid from ospid .

2.Get SQLTEXT from above sid

3.Get P1,P2,P3 from above sid. Where P1 is File#, P2 Block,P3 Rowid

4.Get the table name from Pt 2

5.Check no table should be STALE, No Index should be UNUSEABLE state.

6. Check there should be ample space in every tablespace .
7. Check for space in Temporary tablespace.
8. Check for fragmentation of table percentage ( more than 20% fragmented as per oracle)
9. Check if any change in execution plan of sql_id

10. Run sqltrpt.sql from $ORACLE_HOME/rdbms/admin on that sql_id
11. IF there is change in plan .check the best plan in DEV/TEST and pinned from them to existing database by using coe.sql (Centre of Excellence (CoE) )

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website               www.ktexperts.com
Facebook Page KTexperts
Linkedin Page   : KT EXPERTS

Follow Me On
Linkedin :Rakesh Monga

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.00 out of 5)
Loading...

Add Comment