Performance tuning tips for Jr. DBAs

Performance Tuning for overall system performance goes down

==============================================================

1) first look at the top processes. using TOP command

2) ps -ef| grep says the database to which the session belongs.

or take strace -p < pid> , it will generate trace file for the PID, you can get the instance name for which this pid belongs to.

3) From the TOP command know the process id and match the paddr in v$session with addr in v$process where spid = process number that you see in the top command

In the select clause, you project,sid,

3)After finding the sid we need to getthe sql text

select sql_text from v$sqltext where sid=153; wait for few mminutes and see if it the correct one.

4)select event,total_waits from v$session where sid=153 order by tota_waits;

5) see the two tables how much time the session is waiting for the event to occur v$session_event, is the what event the session is waiting.

6) Then go to attain a trace on the sesion by going to dbms_system

by executing a procedure “dbms_system.set_sql_trace_in_session”

to execute this we need to get sid,serial# for sid from v$session

exec dbms_system.set_sql_trace_in_session(153,67,true) run it for 2 minutes and later execute

exec dbms_system.set_sql_trace_in_session(153,67,false)

7) go to user dump and identify the trace file by using ls -ltr and comparing with the current date.

8) to convert trace file to text file we use tkprof

syntax:

tkprof trace file output file explain = user/password sort=(sort options)- After analyzing the sql statements

9) select last_analyzed,num_rows from dba_tables where table_name='EMP';

10) then you go to dbms_stats, by executing

exec dbms_stats.Gather_schema_statistics('scott');

11) then see how many rows are present in the particular table;

select count(*) from emp;

12)then check to see if there are any indexes present.

select index_name from dba_ind_columns where column_name = 'ENAME';

13)Some times you should also look at the avg_row length for a particular table.

if it is greater than the block size then we have to move the table ( Re-org)

alter table move emp tablespace users;

14)After rebuild If there are any indexes present on the table, you must also rebuild them.

alter index pk_dept rebuild tablespace users;

====================================================================

LOCK SCENARIO COMPLAINED BY SINGLE USER

====================================================================

1) once you know the user name who complained then

select sid,serial#, from v$session where username='SCOTT';

2) then you will get a series of the session id's the user is running

we run select event from v$session_wait where sid = 147;

we run for all the available sessions. changing the sid.

3) then we go to dba_blockers;

select holding_session from dba_blockers;

(NOTE: if the views, v$session_wait,dba_blockers,dba_waiters doesn't exist then we have to run the script

@oracle_home/rdbms/admin/catblock.sql)

Or use the below query to know which session / sql is blocking which other session.

SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1,
v$session s1 ,
v$lock l2 ,
v$session s2 ,
v$sql sqlt1 ,
v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
/

You will get the blocking session details as below:

BLOCKING_STATUS
--------------------------------------------------------------------------------
02-FEB-2010 01:40:12 User Tom ( SID= 989 ) with the statemen
t: select * from t where a='sdfannsdfb' for update is blocking the SQL statement on Peter@ ( SID=100 ) blocked SQL -> update t set a='x' where a='sdfannsdfb'




Alternate way:

-----------------

select sid,request,id1,id2 from v$lock where request !=0;

select sid, request, lmode from v$lock where id1 = ' the id1 value from previous query' id2='the id2 value from the previous query'

Here lmode is lockmode if lockmode is 6 then that session has acquired lock;

if request = 6 then it is waiting for the session whose lmode is 6 to release the lock by commiting it.

Performance related views and tables

=================================================================

v$session

v$session_wait

v$session_event

v$lock

dba_blockers;

dbs_waiters;

dba_ddl_locks;

dba_dml-locks;

v$sqltext;

v$sqlarea;

v$locked_object

v$db_object_cache

v$sort_usage

v$sort_segment

dba_rollback_segs;

v$rollstat

v$sysstat

v$system_event

v$process;

====================================================================

Query to find what a session is doing and how much CPU a session consumed

====================================================================

select ss.sid,se.command,ss.value CPU ,se.username,se.program

from v$sesstat ss, v$session se

where ss.statistic# in

(select statistic#

from v$statname

where name = 'CPU used by this session')

and se.sid=ss.sid

and ss.sid>6

order by cpu desc

/

get the sid and use it in the below query to know the wait events related to the session:

select s.sid, event, wait_time, w.seq#, q.sql_text

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr=p.addr and

s.sid=&p and

s.sql_address=q.address;

=====================================================================================

Query to find the sid using the O.S. Process id

1) select sid from v$session where paddr in (select addr from v$process where spid = 'OS processno);

Get the sid and find the sql using the below.

2)select sql_text from v$sqltext where sql_id in (select sql_id from v$session where sid = 152);

Basic sga TUNING

===================================================

TUNING LIBRARY CACHE

***********************************************

SQL> SELECT SUM(reloads) "Cache Misses",

2 SUM(pins) "Executions",

3 100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent"

4 FROM v$librarycache;

Cache Misses Executions Cache Miss Percent

------------ ---------- ------------------

9 2017 .44620724 --- > IF THIS IS MORE THAN 1 TAKE ACTION.

********************************************************

TUNING DATA DICTIONARY CACHE

*******************************************************

SQL> SELECT SUM(getmisses) "Cache Misses",

2 SUM(gets) "Requests",

3 100 * ( SUM(getmisses) / SUM(gets) ) "Cache Miss Percent"

4 FROM v$rowcache;

Cache Misses Requests Cache Miss Percent

------------ -------- ---------- -------

277 2185 12.677346 ---> REPEAT THIS QUERY AND IF YOU OBSERVER 10 TO 15 MISSES MOST OF THE TIME, THEN INCREASE SHARED POOL.

*********************************************************************

TUNING BUFFER CACHE

*********************************************************************

SQL> SELECT name, value

2 FROM v$sysstat

3 WHERE name IN ('db block gets', 'consistent gets', 'physical reads');

NAME VALUE

---------------------------------------------------------------- --------

db block gets 155

consistent gets 5293

physical reads 334

To calculate the cache hit ratio, use this formula:

Cache Hit Ratio = 1 - ( PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS))

Cache Hit Ratio = 1 - (334 / ( 155 + 5293) ) = 1 -(334 / 5448) = 1 - 0.0613 = 0.938

If the cache-hit rate is lower than 70 or 80 percent, you may need to increase the database buffer cache to improve performance.

The buffer cache can be increased by tuning the Oracle initialization parameter DB_BLOCK_BUFFERS.

***************************************************************************

TUNING REDO LOG LATCHES

***************************************************************************

The redo allocation latch controls the writing of redo entries to the redo log buffer.

To write an entry into the redo log buffer, the user process must obtain the redo allocation latch,

allocate space in the redo log buffer, and then copy the entry into the buffer.

Whenever the user process has finished copying into the redo log buffer, it releases the latch,

thus allowing other user processes to use the redo allocation latch.

Because only one redo allocation latch exists, only one user process can write to the log buffer at a time. The maximum amount of data that

can be written into the latch is specified by the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE.

SQL> SELECT SUBSTR(name,1,20), gets, misses, sleeps, immediate_gets, immediate_misses

2 FROM v$latch

3 WHERE name IN ('redo allocation', 'redo copy');

SUBSTR(NAME,1,20) GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

-------------------- -------- -------- -------- -------------- --------------

redo allocation 2744 0 0 0 0

redo copy 0 0 0 0 0

If the ratio of MISSES to GETS exceeds 1 percent or

the ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_MISSES and IMMEDIATE_GETS exceeds 1 percent, contention is probably affecting performance in your system.

reduce the size of LOG_SMALL_ENTRY_MAX_SIZE

OR

Another way to reduce the time a user process holds the latch is by requiring the user process to

prebuild the redo entries before it obtains the latch. This prebuilt entry reduces contention on the

latch by not using CPU time to build the entry while the latch is being held.

By setting the initialization parameter LOG_ENTRY_PREBUILD_THRESHOLD,

any redo entry of a smaller size than this parameter must be prebuilt.

The default for LOG_ENTRY_PREBUILD_THRESHOLD is 0 (that is, it requires no prebuilding).

Questions:

1)command to know the memory usage and top cpu consuming units: top

2)command to know the number of cpu processes: mpstat

3)what is asynchronus i/o, synchronus i/o? How do you enable asynchronus i/o?

a)synchronus i/o:

---------------------

If a process attempts to read or write using the normal, synchronous read() or write() system calls, then it must wait until the hardware has completed the physical I/O, so that it can be informed of the success or failure of the operation (and to receive the results in the case of a successful read).

The execution of the process is blocked while it waits for the results of the system call. This is synchronous or blocking I/O

b)asynchronus i/o:

--------------------

if the process instead uses the asynchronous aio_read() or aio_write() system calls (called aioread() and aiowrite() on some operating systems), then the system call will return immediately once the I/O request has been passed down to the hardware or queued in the operating system, typically before the physical I/O operation has even begun. The execution of the process is not blocked, because it does not need to wait for the results of the system call.

Instead, it can continue executing and then receive the results of the I/O operation later, once they are available. This is asynchronous or non-blocking I/O

c)disk_asynch_io parameter is TRUE

4)what is RAID?

RAID is used for striping and mirroring

RAID-0:RAID 0 implements a striped disk array, the data is broken down into blocks and each block is written to a separate disk drive

I/O performance is greatly improved by spreading the I/O load across many channels and drives

RAID-1:RAID 1 is usually implemented as mirroring; a drive has its data duplicated on two different drives using either a hardware RAID controller or software (generally via the operating system). If either drive fails, the other continues to function as a single drive until the failed drive is replaced

RAID level for OLTP and DW:

RAID10 is one of the combinations of RAID1 (mirroring) and RAID0

(striping) which are possible. This is used for OLTP and DW

How to pin an execution plan: using outlines In Oracle

------------------------------------------------------------------------------------------

Here we go with an example of pinning an execution plan in cursor cache.
1. List all plans in cursor cache, read the stats, and identify a candidate,

eg. with least cost.
select sql_id, hash_value, child_number from v$sql where sql_id=’123aaaaaaaa’;
select * from table(dbms_xplan.display_cursor(’123aaaaaaa’,123);

2. Create a stored outline with this candidate.
alter session set create_stored_outlines = true; # necesssary if < 10.2.0.4 due to a bug
exec dbms_outln.create_outline(hash_value,child#); # Create outline in default category.
alter session set create_stored_outlines = false; # Cancel the session setting

3. Use outline.
alter session set use_stored_outlines = true;
or
alter system set use_stored_outlines = true; # not persistent across DB reboot. Consider a trigger to enable this.

4. Check if outline is in use.
select sql_id, hash_value, PLAN_HASH_VALUE, child_number, outline_sid from v$sql where outline_category=’DEFAULT’;