?
Solved

Steps for tuning the sql statement

Posted on 2004-04-04
4
Medium Priority
?
2,385 Views
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??
0
Comment
Question by:nag0452
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
oleggold earned 2000 total points
ID: 10751742
You can do this in several ways
(and whatever You do fisrstable You need to make a valid <a href="http://www.adp-gmbh.ch/ora/explainplan.html">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)
http://download-west.oracle.com/docs/cd/B10501_01/em.920/a86647/vmqintro.htm#998510
2.Using Statspack or/and BSTAT/ESTAT:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm#36007
3.TKPROF and SQL TRACE :
1)insert SQLPLUS
2)        To enable the SQL trace facility for your session                 issue the following SQL

statement:
    ALTER SESSION   SET SQL_TRACE = TRUE;   - WORKS ON LOCAL SESSION ONLY(DON'T USE IT ON SYSTEM -

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

enabled/disabled dynamically by using                                               ALTER SYSTEM    

                      SET TIMED_STATISTICS = TRUE;
or                      
(BETTER - WORKS ON LOCAL SESSION ONLY)   ALTER SESSION     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:
http://asktom.oracle.com/~tkyte/article1/autotrace.html:
(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

0
 
LVL 21

Expert Comment

by:oleggold
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
-b.to 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

Wizard:

      1.1)Evaluation

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 %

      1.2)Recommendations

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:

Rule:

"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.


Implication:
------------

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

--
ex.sql:
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',
                                                 'R_PROJECT'))
                                           AND eu_use_pub_privs = 0)
                    MINUS
                    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)))
         priv_test
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.
Suggested Courses

765 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