I ve good understanding of pt concepts. i ve hands on experience in pt like to generate awr report , sql query tuning , explain plan analysis . i ve been part of pt team for last 3 4 months. i got the clear understanding of application flow.

we recently faced an issue of 
application latency
blank report
query slowness
memory usage full

for application latency , our dba will be sending the awr reprt to us and i will analyze the report based on the check list give to us. we have standard process to follow for pt, whether we can go for joins instead of sub query , null functions to be replaced by case or decode to be replaced by case .. etc

i will check the explain plan for the query first with screenshot,  then i will analyze which scans have been done by optimizer whether index has been read (if index there), or index required , hints to be forced , sub query to be replaced ... if i found any cost decrease then i will force hint or iniating index .. or gathering table statics

we faced more application slowness (particular page will be blank at specific point of time ) issues.  for that we will be doing 

exec dbms_stats.gather_table_stats('hr','employees');

after running this .. index may go for unusable status so we have to check the index status in user_indexes. if it is in unusable status then

alter index index_name rebuild online; ( if production time issue ) or   
alter index index_name rebuild;

i have some knowledge of analysing awr report

select * from dba_hist_snapshot;  

first of all we need to get the application slowness issue timings from  L1 people
based on that i will be checking in the respective database to get the snap start id , snap end id , db id , instance number to generate awr report.

using  select * from 
table(dbms_workload_repository.awr_report_html(dbid,insnumber,snapsid,snapeid));

i will check top 10 fore ground events and then i can easily identify the query which took more time to execute. if the cost of the query says 100% above then i will tune the query... 

table partitioning
awr report
index
hints
bind variable
avoidance of null functions
use case instead of decode
collections / bulk collect .. 

How to see explain plan

in sqlplus  =>  set autot on
in toad      =>   ctl+e

General way

explain for select * from emp;
select dbms_xplan.display from dual;



access paths => nested loop join / hash join / 
	        sort merge join / table access full
	        index range scan / skip scan / index by rowid
	        index fast full scan

yesterday the application was working fine but today no why ?

what time ? evening 4.30  to  6.30 pm

awr => automatic workload repository ( performance log ) 

dba => he will be configuring the timing to take the snapshot of db performance

select * from dba_hist_snapshot;

select * from table(dbms_workload_repository.awr_report_html(2805820195,1,3155,3161)  );

look into top 10 foreground events -  watch 100% cost queries (sqlid);
select * from v$sql_plan;
select * from v$sqltext;

go for query tuning => modify joins or subquery / case - decode / null function .. etc

before cost  / after cost  - screen shot - approval from manager - implement