Oracle 10g performance tuning


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

The V$ performance views, the EXPLAIN PLAN command, the SQL TRACE facility, the TKPROF facility, the Autotrace report, and the STATSPACK scripts.

SQL Tuning
SQL Tuning

 

  • 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

     

     

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s