Oracle Tunning and Performance

[anu_uma]

Hi All,

Need answers for the beow given Questions : -

I want to see what is happening on the Oracle server right now... or few hours before;
I need to find which SQL statements were responsible for the recent spike of server activity and response time degradation, see their SQL text, what they were waiting for, etc;
I want to know what this long running query is doing now;
I want to get a feeling for how the server behaves in response to your SQL changes;

[santanasan]

all sql's ( during runtime ) taking >6 s are logged into v$session_longops
run statspack.snapshots are desired intervals using dbms_job or crond
you will get top sqls ( exec time / parse time ) in any given timeframe.

Statspack is your answer , I guess.
rgds
Santosh

[anu_uma]

Hi santanasan,

I need some explanation how to check it as i am not a technical person.
how to check v$session_longops and also the statspack.snapshots


TIA

[santanasan]

unfortunately, there's no quickfix solution to that.
Setting up statspack and scheduling snapshot during your peak workloads and interpreting report requires knowledge and exp.

sorry.. you gotta get a DBA
regards,
santosh