Basic Performance tuning tips reference

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 get the sql text
select sql_text from v$sqltext where sid=153; wait for few minutes and see if it the correct one.
4)select event,total_waits from v$session where sid=153 order by total_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 session 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 statement:
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;