Performanace tuning by AWR and ADDM.

Snapshots

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/

Extra snapshots can be taken and existing snapshots can be removed, as shown below

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/

Baselines View the difference between two time.

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 210,
end_snap_id => 220,
baseline_name => 'batch baseline');
END;
/

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => 'batch baseline',
cascade => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

DBA_HIST_SNAPSHOT view is use for get the snapshot ID;

Workload Repository Reports
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql


http://www.dbapool.com/analyzer/ Online Analyzer

ADDM report

By script

@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql

BY Pakage

BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '970_1032_AWR_SNAPSHOT',
task_desc => 'Advisor for snapshots 970 to 1032.');

-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '970_1032_AWR_SNAPSHOT',
parameter => 'START_SNAPSHOT',
value => 970);

DBMS_ADVISOR.set_task_parameter (
task_name => '970_1032_AWR_SNAPSHOT',
parameter => 'END_SNAPSHOT',
value => 1032);

-- Execute the task.
DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');
END;
/

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24