Solved

Steps for tuning the sql statement

Posted on 2004-04-04
4
2,360 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
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
oleggold earned 500 total points
Comment Utility
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
Comment Utility
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

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

763 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

11 Experts available now in Live!

Get 1:1 Help Now