TKprof-StatsPack-Addm reports

Tkproof:-(Transient Kernel Profile)

• The TKPROF program converts Oracle trace files into a more readable form.
• If you have a problem query you can user TKPROF to get more information.
• TKPROF accepts as input a trace file produced by the SQL Trace facility, and it produces a formatted output file. 
• TKPROF can also be used to generate execution plans.

Note if you want to enable this features:-
USER_DUMP_DEST set this parameters in 10g
diagnostic_dest set this parameter in 11g .

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

ALTER SESSION SET SQL_TRACE = TRUE;

Go to the dump destionation:-

TKPROF <trace-file> <output-file> explain=user/password@service table=sys.plan_table

 tkprof student_arc3_5247.trc 123.txt explain=syste/manager table=dinesh.emp

TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)



Statspack in Oracle(Oracle8i)

• Statspack is a set of performance monitoring, diagnosis and reporting utility provided by Oracle.
•  Statspack provides improved UTLBSTAT/UTLESTAT functionality, as it’s successor, though the old BSTAT/ESTAT scripts are still available.
•  Statspack collects more data, including high resource SQL (and the optimizer execution plans for those statements).

The BSTAT-ESTAT utilities capture information directly from the Oracle's in-memory structures and then compare the information from two snapshots in order to produce an elapsed-time report showing the activity of the database.
e.g. V$SYSSTAT.
SQL> insert into stats$begin_stats select * from v$sysstat;
SQL> insert into stats$end_stats select * from v$sysstat;

To create Statspack:-
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql;
Note:-default tablespace 500mb
Note:- assing temp more than 200mb

Snapshot Level:-

$ sqlplus perfstat/perfstat
SQL> exec statspack.snap;


SQL> exec statspack.snap(i_snap_level=>10);
instruct statspack to do gather more details in the snapshot.

SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;

To get Report:-
SQL> @?/rdbms/admin/spreport.sql

EXECUTE STATSPACK.SNAP(i_snap_level=>6);

Purge Snapshot:-
SQL> @?/rdbms/admin/sppurge.sql;

Truncate Snapshot:-
SQL> @?/rdbms/admin/sptrunc.sql

Drop or Uninstall Snaps:-
 @?/rdbms/admin/spdrop.sql


ADDM Report:-
The ADDM analysis includes the following.

1. CPU load
2. Memory usage
3. I/O usage
4. Resource intensive SQL
5. Resource intensive PL/SQL and Java
6. RAC issues
7. Application issues
8. Database configuration issues
9. Concurrency issues
10. Object contention

The recommendations may include:

1. Hardware changes
2. Database configuration changes
3. Schema changes
4. Application changes
5. Using other advisors

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

To views of ADDM:-

DBA_ADVISOR_TASKS - Basic information about existing tasks.
DBA_ADVISOR_LOG - Status information about existing tasks.
DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.