Tuning Toolkit

The Oracle database has many tools you can use for performance tuning, so which tool should you use? This depends on the question you have.

1. DBMS_XPLAN

explain plan for SELECT count(*) from abrech;

select plan_table_output from table( dbms_xplan.display( 'plan_table', null, 'serial' ) ) ;

=> Show execution plan

With the plan shown here you may find out what the problem with the statement is - however, it's not easy to figure out a better plan than the optimizer has. If you have an idea, you can use hints.

2.  /*+ GATHER_PLAN_STATISTICS */

see http://www.myoraclecollection.blogspot.de/p/beschreibung-warum-ist-dieses-sql.html

=> Execution plan shows
- estimated and actual rows
- actual time
- actual buffer gets
- actual disk reads

This is really helpful because real data is shown, not only estimated. So you have all data you need to optimize an SQL-Statement.

3. TKPROF

alter session set timed_statistics=true;

alter session set events '10046 trace name context forever, level 12';

See http://myoraclecollection.blogspot.de/p/tuning-tl.html for detailed commands. 

=> Generate a trace file showing what a session is doing on the database (including CPU times). 

4. Statspack

=> Generate a trace file showing what is happening on the database, e.g. in a time window of 15 minutes ("snapshot").

5. AWR

= Automatic Workload Repository: Using the Oracle Enterprise Manager you can see what's happening and what was happening in the database ("Repository").

In the Enterprise Manager, you can use ADDM, the Automatic Database Diagnostic Monitor you can analyze data in the AWR to identify potential performance bottlenecks.

AWR was introduced in Oracle 10g, it should replace Statspack - if you have licensed the "Diagnostic and Tuning Pack" (only possible when having licensed the Oracle Enterprise Edition). If not, you can still install Statspack to create your own repository.

6. DBMS_PROFILER

=> Compare the performance of PL/SQL procedures.