Oracle Performace Tuning.

1. Monitoring the database performance using the oracle tools likeOEM,TOAD. And JUtilities like STATSPACK, UTILBSTAT, UTILESTAT and Performance Views like v$sysstat, v$sesstat, ,v$sgastat and Wait events like v$system_event, v$session_event, v$session_wait and Diagnostic and tuning packs also available to determine the Performance of the Database.

STATSPACK:UTILITIES
----------------- ---------------

Installation of STATSPACK
Login as sysdba Run the following Scripts
$ORACLE_HOME/rdbms/admin/spcreate.sql

Dropping the STATSPACK
Login as sysdba Run the following Scripts
$ORACLE_HOME/rdbms/admin/spdrop.sql

Collection of Statistics
Login as Perfstat user Run the following Scripts
Exec STATSPACK.snap

Produce a STATSPACK Report
Login as sysdba Run the following Scripts
$ORACLE_HOME/rdbms/admin/spreport.sql

Automatic Collection of Statistics
Login as sysdba Run the following Scripts
$ORACLE_HOME/rdbms/admin/spauto.sql

TO Automatic the STATSPACK need to set the
DBMS_JOB package and job_queue_process value should be NON-ZERO.

To Collecting timing information set the
TIMED_STATISTICS=TRUE.


ONE MORE WAY OF LEVEL OF TRACING WITH AUTOMATION USING DBMS_JOBS PACKAGE

______________________________________________________________________________________

Scheduling the STATSPACK in database level using dbms_jobs package
___________________________________________________________________

Zero or NoRMAL LEVEL STATSPACK AUTOMATION for EACH 30 MIN WILL RUN PERIODICALLY
_______________________________________________________________________________
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate)+9/24,'SYSDATE+1/48',TRUE,:instno);
end;
/

TO checking the job id and what are the jobs scheduled use the below query
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE,NEXT_SEC,FAILURES,BROKEN,INTERVAL FROM DBA_JOBS;


FIRST TIME USE THE BELOW COMMAND TO INITIATE THE SCHEDULED JOB
EXECUTE DBMS_JOB.RUN(24); --> uSING THE ABOVE COMMAND GET THE JOB ID AND RUN THE SAME ID USING DBMS_JOB.RUN COMMAND

__________________________________________________________________________________________________________________________

LEVEL -7 STATSPACK AUTOMATION for EACH 30 MIN WILL RUN PERIODICALLY
________________________________________________________________________

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno,'statspack.snap(i_snap_level=>7);',trunc(sysdate)+9/24,'SYSDATE+1/48',TRUE,:instno);
end;
/
TO checking the job id and what are the jobs scheduled use the below query
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE,NEXT_SEC,FAILURES,BROKEN,INTERVAL FROM DBA_JOBS;


FIRST TIME USE THE BELOW COMMAND TO INITIATE THE SCHEDULED JOB
EXECUTE DBMS_JOB.RUN(24); --> uSING THE ABOVE COMMAND GET THE JOB ID AND RUN THE SAME ID USING DBMS_JOB.RUN COMMAND

______________________________________________________________________________________________________________________________

LEVEL -12 STATSPACK AUTOMATION for EACH 30 MIN WILL RUN PERIODICALLY
________________________________________________________________________

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno,'statspack.snap(i_snap_level=>12);',trunc(sysdate)+9/24,'SYSDATE+1/48',TRUE,:instno);
end;
/

TO checking the job id and what are the jobs scheduled use the below query
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE,NEXT_SEC,FAILURES,BROKEN,INTERVAL FROM DBA_JOBS;


FIRST TIME USE THE BELOW COMMAND TO INITIATE THE SCHEDULED JOB
EXECUTE DBMS_JOB.RUN(24); --> uSING THE ABOVE COMMAND GET THE JOB ID AND RUN THE SAME ID USING DBMS_JOB.RUN COMMAND
_________________________________________________________________________________________________________

TO checking the job id and what are the jobs scheduled use the below query
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE,NEXT_SEC,FAILURES,BROKEN,INTERVAL FROM DBA_JOBS;

_______________________________________________________________________________
TO Remove the already scheduled use the below query
______________________________________________________________________________

EXECUTE DBMS_JOB.REMOVE(24);

_________________________________________________________________________
Level of Tracing
__________________
exec statspack.snap(i_snap_level=>7);
exec statspack.snap(i_snap_level=>8);
exec statspack.snap(i_snap_level=>12);
_______________________________________