Performance Investigation(Database / SQL)

Share via:

Dear Readers,

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

Performance Investigation Database

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

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

13857 oracle 7 60 0 28G 28G sleep 0:00 0.12% oracle

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 
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)

Add Comment