Performance Investigation(Database / SQL)

Share via:

Post Views: 0

Dear Readers,

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

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 .

select p.spid,s.sid,s.serial#,s.sql_id,s.prev_sql_id, s.serial#,s.username, s.osuser from gv$session s, gv$process p where s.paddr= p.addr and p.spid in ('&sid') order by p.spid;

1	select p.spid,s.sid,s.serial#,s.sql_id,s.prev_sql_id, s.serial#,s.username, s.osuser from gv$session s, gv$process p where s.paddr= p.addr and p.spid in ('&sid') order by p.spid;

2.Get SQLTEXT from above sid

select sql_text,sql_id from dba_hist_sqltext where sql_text like '%&sql_text%';

1	select sql_text,sql_id from dba_hist_sqltext where sql_text like '%&sql_text%';

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

select TERMINAL,event, sql_id,osuser,machine,module,p1,p2,p3,process,status ,username from v$session where sid=&sid;

1	select TERMINAL,event, sql_id,osuser,machine,module,p1,p2,p3,process,status ,username from v$session where sid=&sid;

4.Get the table name from Pt 2

Select table_name ,tablespace_name from dba_tables where table_name=’%tbls’;

1	Select table_name ,tablespace_name from dba_tables where table_name=’%tbls’;

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

select owner , table_name, stale_stats,STATTYPE_LOCKED ,last_analyzed,PARTITION_NAME from dba_tab_statistics where owner= '&own' and table_name ='&tbl';

1	select owner , table_name, stale_stats,STATTYPE_LOCKED ,last_analyzed,PARTITION_NAME from dba_tab_statistics where owner= '&own' and table_name ='&tbl';

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

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sqlid' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number
and executions_delta > 0 order by 1, 2, 3
/

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = '&sqlid' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number

and executions_delta > 0 order by 1, 2, 3

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