Performance bottleneck often called by following reasons
Memory contention occurs when processes require more memory than is available
Disk I/O contention is caused by poor memory management, poor distribution of tablespaces and files across disks
SQL Tunining tools
V$ performance views, the
EXPLAIN PLAN command, the
SQL TRACE facility, the
TKPROF facility, the Autotrace report, and the
- Oracle provides dbms_sqltune package it has ability to analyze a SQL statement. It allows you to look at a SQL statement and see how many I/Os and CPU cycles it has used during the execution on a repeating basis. It helps to find where it is in the execution cycle and what might be causing performance issues.
- ADDM (Automatic Database Diagnostic Monitor) can be describe as the database’s doctor. It allows an Oracle database to diagnose itself and determine how potential problems could be resolved. ADDM runs automatically after each AWR (Automatic Workload Repository) statistics capture, making the performance diagnostic data readily available.
- Statspack is another set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt
- TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the ORACLE HOME/bin directory.
The only problem for TKProf is it creates a binary file on server location, which you need to ask your DBA to provide you. To check exact location on server, please run this query
select * from v$spparameter where name = ‘user_dump_dest’
To get the client idetifier you require to use in TKProf run this query
select CLIENT_IDENTIFIER, sess.* from V$SESSION sess where MACHINE like ‘%COMPUTER_NAME%’
provide computer name where from you execute the query.
My favoirite sql client is windows command promt, run *sqlplus