Oracle Database Performance Tuning
often you will see application users complaining that their concurrent programs are running slow. So being a DBA, how will you validate and work with the situtation.
High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the session is doing join your query v$session with v$session_wait.
1. First get the concurrent program request id.
2. Find the sid of it.
SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM apps.fnd_concurrent_requests
WHERE request_id = TO_NUMBER(<your request id>)
)
);
3. Also run the sql to find more details about it.
SELECT NVL(s.username,'(oracle)') as username,s.sid,s.serial#,sw.event,sw.wait_time, sw.seconds_in_wait, sw.state FROM v$session_wait sw,v$session s
WHERE s.sid=sw.sid and s.username= '&username'ORDER BY sw.seconds_in_wait DESC;
4.Check the events that are waiting for something.
5.Try to find out the objects locks for that particular session.
select
blocking_session B_SID,
blocking_instance B_Inst
from v$session
where sid = ?
Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as "db file sequential read" (for index scan) or "db file scattered read" (for full table scan).When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.
6. Get more details about the objects.
6.Run SQL Tuning Advisor (@$ORACLE_HOME/rdbms/admin/sqltrpt.sql) by providing
SQL_ID as the input for generating the findings and recommendations.
SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics.
SQL Tuning Advisor suggests indexes that might be very useful.
SQL Tuning Advisor suggests query rewrites.
SQL Tuning Advisor suggests SQL profile.
7. Know more about the session what exactly it is doing.
select * from v$session where sid='?';
---Taken from Arup Nanda's blog.
select row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#
from v$session
where sid = ?;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————————————— —————————————— ———————————————— ——————————————
241876 1024 2307623 0
select owner, object_type, object_name, data_object_id
from dba_objects
where object_id = 241876;
OWNER OBJECT_TYPE OBJECT_NAME DATA_OBJECT_ID
————— ———————————— ———————————— ——————————————
APPS TABLE T1 241877
Finding the row information
REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM 1. owner
REM 2. table name
REM 3. data_object_id
REM 4. relative file ID
REM 5. block ID
REM 6. row Number
REM
select *
from &1..&2
where rowid =
dbms_rowid.rowid_create (
rowid_type => 1,
object_number => &3,
relative_fno => &4,
block_number => &5,
row_number => &6
)
/
SQL> @rowinfo APPS T1 241877 1024 2307623 0
COL1 C
————— —
1 x
----------------------------------
Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/
Called Waiting
SID USERNAME PROGRAM STATE secs ago for secs EVENT
————— ——————— ——————————— ——————— ————————— ———————— ——————————————————
2832 APPS sqlplus.exe Waiting 152 151 SQL*Net message
from client
3089 APPS sqlplus.exe Waiting 146 146 enq: TX - row lock
contention
3346 APPS sqlplus.exe Working 18 49 SQL*Net message
from client
=====================
Checking the data access and from which table it is coming
select SID, state, event, p1, p2
from v$session
where username = 'ARUP';
SID STATE EVENT P1 P2
———— ——————— ——————————————————————— —— ————
2201 WAITING db file sequential read 5 3011
The P1 column shows the file ID, and the P2 column shows the block ID. From that information in the result in Listing 7, you can get the segment name from the extent information in DBA_EXTENTS, as shown below:
select owner, segment_name
from dba_extents
where file_id = 5
and 3011 between block_id
and block_id + blocks;
OWNER SEGMENT_NAME
—————— —————————————
APSS T1
select s.value
from v$sesstat s, v$statname n
where s.sid = 3806
and n.statistic# = s.statistic#
and n.name = 'CPU used by this session';
SELECT a.average_wait "SEQ READ", b.average_wait "SCAT READ"
FROM sys.v_$system_event a, sys.v_$system_event b
WHERE a.event = 'db file sequential read'
AND b.event = 'db file scattered read';
select
s1.h_date,
trunc(s1.v_avg,2) pyh_reads,
trunc(s2.v_avg,2) dir_reads,
trunc(s1.mb_sec,2) pyh_mb_s,
trunc(s2.mb_sec,2) dir_mb_s,
trunc((s2.v_avg/s1.v_avg)*100,2) R_PCT
from
--S1-B-----------
(
select
trunc(b_snap_date,'HH') h_date,
sum(snap_value) svalue,
sum(snap_value/snap_secs) v_avg,
sum(snap_value/snap_secs)*v_db_block_size/1024/1024 mb_sec
from
(select
s.INSTANCE_NUMBER,
cast (s.END_INTERVAL_TIME as date) e_snap_date,
cast (s.BEGIN_INTERVAL_TIME as date) b_snap_date,
(cast(s.END_INTERVAL_TIME as date) - cast(s.BEGIN_INTERVAL_TIME as date))*24*60*60 snap_secs,
t.VALUE,
(t.VALUE-LAG (t.VALUE) OVER (ORDER BY s.INSTANCE_NUMBER, s.BEGIN_INTERVAL_TIME)) snap_value
from
DBA_HIST_SNAPSHOT s,
DBA_HIST_SYSSTAT t
where 1=1
and s.SNAP_ID = t.SNAP_ID
and s.DBID = t.DBID
and s.INSTANCE_NUMBER = t.INSTANCE_NUMBER
and s.DBID = (select DBID from V$DATABASE)
and t.STAT_NAME = 'physical reads'
) pr,
(select VALUE v_db_block_size from v$parameter where name = 'db_block_size')
where snap_value > 0
group by trunc(b_snap_date,'HH'),v_db_block_size
) S1,
--S2-B-----------
(
select
trunc(b_snap_date,'HH') h_date,
sum(snap_value) svalue,
sum(snap_value/snap_secs) v_avg,
sum(snap_value/snap_secs)*v_db_block_size/1024/1024 mb_sec
from
(select
s.INSTANCE_NUMBER,
cast (s.END_INTERVAL_TIME as date) e_snap_date,
cast (s.BEGIN_INTERVAL_TIME as date) b_snap_date,
(cast(s.END_INTERVAL_TIME as date) - cast(s.BEGIN_INTERVAL_TIME as date))*24*60*60 snap_secs,
t.VALUE,
(t.VALUE-LAG (t.VALUE) OVER (ORDER BY s.INSTANCE_NUMBER, s.BEGIN_INTERVAL_TIME)) snap_value
from
DBA_HIST_SNAPSHOT s,
DBA_HIST_SYSSTAT t
where 1=1
and s.SNAP_ID = t.SNAP_ID
and s.DBID = t.DBID
and s.INSTANCE_NUMBER = t.INSTANCE_NUMBER
and s.DBID = (select DBID from V$DATABASE)
and t.STAT_NAME = 'physical reads direct'
) pr,
(select VALUE v_db_block_size from v$parameter where name = 'db_block_size')
where snap_value > 0
==============================
8.Get the sql text.
select sql_fulltext
from v$sql l, v$session s
where s.sid = 3806
and l.sql_id = s.sql_id;
9.
More:
1.Run TOP command in Linux to check CPU usage.
2.Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.
3.Enable the trace file before running your queries,then check the trace file using tkprof create output file.
According to explain plan check the elapsed time for each query,then tune them respectively.
What is the use of iostat/vmstat/netstat command in Linux?
Iostat – reports on terminal, disk and tape I/O activity.
Vmstat – reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat – reports on the contents of network data structures.
What to Look for in AWR Report and STATSPACK Report?
Many DBAs already know how to use STATSPACK but are not always sure what to check regularly.
Remember to separate OLTP and Batch activity when you run STATSPACK, since they usually
generate different types of waits. The SQL script “spauto.sql” can be used to run STATSPACK
every hour on the hour. See the script in $ORACLE_HOME/rdbms/admin/spauto.sql for more
information (note that JOB_QUEUE_PROCESSES must be set > 0). Since every system is different,
this is only a general list of things you should regularly check in your
STATSPACK output:
¦ Top 5 wait events (timed events)
¦ Load profile
¦ Instance efficiency hit ratios
¦ Wait events
¦ Latch waits
¦ Top SQL
¦ Instance activity
¦ File I/O and segment statistics
¦ Memory allocation
¦ Buffer waits
10. Enable trace and find the top sqls to tune them.
Trace can be enabled either from front end or back end. From front end, before strating the program, it can be enabled from program definition screen. And from backend, it can be enabled by:
step 1
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,v$session vs
,v$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
--order by 9,2,1
order by 9
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;
Enter value for request_id: 75696767
old 9: AND a.request_id = &Request_ID
new 9: AND a.request_id = 75696767
REQUEST_ID SID SERIAL# OSUSER PROCESS SPID
---------- ---------- ---------- ------------------------------ ------------------------ ------------------------
75696767 25 50125 applmgr 21945 186
SQL> !ps -ef|grep 186
oracle 1218 28905 37 03:43:25 pts/2 0:00 grep 186
oracle 186 1 0 03:39:17 ? 0:00 oracleOICP (LOCAL=NO)
SQL> oradebug setospid 186
Oracle pid: 72, Unix process pid: 186, image: oracle@eprdsc1
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/*/db/tech_st/11.2.0.3/admin/*_*/diag/rdbms/uat/UAT/trace/UAT_ora_21152.trc
SQL>
SQL>
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL>
tkprof *_ora_12762.trc *_ora_12762.txt explain=apps/k33p1tup sort='(prsela,exeela,fchela)' print=10
tkprof raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10
Note:Ensure max_dump_file_size is set to unlimited before enabling the trace.
