Steps for tuning the sql statement

Posted on 2004-04-04
Last Modified: 2007-12-19
Steps for tuning the sql statement. Can anybody tell me the steps for sql statement and what should be checked??
Question by:nag0452
  • 2
LVL 21

Accepted Solution

oleggold earned 500 total points
ID: 10751742
You can do this in several ways
(and whatever You do fisrstable You need to make a valid <a href="">EXPLAIN PLAN</a>).
THE MOST common ways are :(explanations follow)
1.with Oracle Sql Analyzer:(will be enhanced by an example,meantime follow the link bellow)
2.Using Statspack or/and BSTAT/ESTAT:
1)insert SQLPLUS
2)        To enable the SQL trace facility for your session                 issue the following SQL


3)Enabling TIMED_STATISTICS ------------------------- The parameter TIMED_STATISTICS can be

enabled/disabled dynamically by using                                               ALTER SYSTEM    

                      SET TIMED_STATISTICS = TRUE;
Trace Files ----------- Oracle will generate trace (.trc) files for every session where the value

of SQL_TRACE = TRUE and write them to the USER_DUMP_DEST destination. If tracing has been enabled

for the instance then individual trace files will be generated for each session, unless otherwise

disabled (see above). Note, that the generated files may be owned by an operating system user other

than your own so you may have to get this user to grant you access before you can use TKPROF to

format them.  
3.The simpliest and most common interplatform way,USING SQLPLUS AUTOTRACE FEATURE:
(To make it work)
cd $oracle_home/rdbms/admin
log into sqlplus as system
run SQL> @utlxplan
run SQL> create public synonym plan_table for plan_table
run SQL> grant all on plan_table to public
exit sqlplus and cd $oracle_home/sqlplus/admin
log into sqlplus as SYS
run SQL> @plustrce
run SQL> grant plustrace to public

LVL 21

Expert Comment

ID: 10752232
If You were never involved in any tuning activities ,the following way seems to best for You,
It's based on example from a real world,If You've Discoverer installed ,You can even test it on Your DB

Steps for tuning the sql statement Using the Oracle Sql Analyze'S SQL Tuning Wizard:
- preliminary steps:
-a.there must be a repository user in your system for the utility running with a global catalog

role,select any table/dictionary,execute any procedure open wizard your sql must be allready selected in Sql Analyze window(choose open->Your sql

file(see ex.sql lower)
1).choose from the Oracle Sql Analyze Select Tools=>SQL Tuning Wizard. This launches the SQL Tuning



The evaluation process identifies inefficiencies in the way that your SQL statement is written. The

SQL Tuning Wizard provides a graph with a projected improvement percentage that is based on a

modified version of the SQL.

The SQL Tuning Wizard projected improvement graph is derived from the information collected by the

system optimizer. In some cases the SQL Tuning Wizard may detect inefficiencies in the way that the

statement was written, but may not be able to predict the degree of performance improvement. It may

still be worthwhile to look at the modified SQL statement, however, to see if the changes have

improved the overall performance of the SQL statement.
Test Case :Protected Improvement - 19 %


The recommendation review process allows you to see which rules have been violated by the SQL

statement. For each rule that is checked, the SQL Tuning Wizard provides a recommendation that

improves the SQL statement. You can also view the Rule Details for each of the rules listed. You

may chose to accept (checked) or decline (unchecked) recommendations for any of the rules.

By default, a rule is checked only if the recommendation is guaranteed to return the same result

set as the original SQL statement.

Test Case : The Wizard Recomendations details as follows:


"MINUS Consolidation using a NOT EXISTS condition"

Rule Description:


This rule will rewrite a MINUS query as a single query containing an additional
NOT EXISTS where clause condition.


SQL Analyze recommends rewriting the MINUS of two queries as a correlated
subquery using NOT EXISTS.  This can avoid unnecessary sorts.

      1.3)Click the Explain Plan Comparison :

The comparison process allows you to compare the original SQL statement to the modified statement

to verify the actual performance improvements for all of the recommendations you accepted. You can

compare the changes to the actual SQL statements before choosing to accept the modified statements.

Once you have verified the performance improvements, you can Execute the modified SQL statements.

Test Case : Presents You with the Original and the modified SQL and the original and the modified

explain plans ,instead of (-) YOU can see an inlist sort there representing
NOT EXIST clause

You can click execute to see how much time it really gets and whether really there's any

improvement..Test Case :there was 100% inprovement in physical reads

Hope this is OK,OLEG

SELECT ver.ver_release schema_version, ver.ver_min_code_ver min_dll_version,
       ver.ver_eul_timestamp eul_timestamp, appstest.apps_param apps_param,
       priv_test.priv_count ts
    FROM discusr.eul4_versions ver, (SELECT COUNT(*) apps_param
                                         FROM discusr.eul4_app_params app2
                                         WHERE app2.app_id(+) = 1016) appstest,
         (SELECT COUNT(*) priv_count
              FROM discusr.eul4_app_params app1, discusr.eul4_access_privs ap
              WHERE ap.gp_app_id = app1.app_id
                AND ap.gp_app_id = 1000
                AND ap.ap_eu_id IN ((SELECT eu_id
                                         FROM discusr.eul4_eul_users
                                         WHERE (eu_username = 'DISCUSR'
                                           OR  eu_username IN ('R_DISCUSR',
                                                 'RDWH_SUPER', 'RESOURCE',
                                           AND eu_use_pub_privs = 0)
                    SELECT eu_id
                        FROM discusr.eul4_eul_users
                        WHERE eu_username = 'PUBLIC'
                          AND NOT EXISTS (SELECT NULL
                                              FROM discusr.eul4_eul_users
                                              WHERE eu_username = 'DISCUSR'
                                                AND eu_use_pub_privs = 0)))

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle sql and I am using Aqua studio. 8 72
Oracle function works in 11g but not in 12c 21 70
oracle query help 36 82
Oracle - Stored Procedure Privilge access 3 38
Article by: Swadhin
From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now