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