Re: tuning sql queries....

From: Job Miller <jobmiller_at_yahoo.com>
Date: Tue, 6 Sep 2011 13:34:54 -0700 (PDT)
Message-ID: <1315341294.49038.YahooMailNeo_at_web162019.mail.bf1.yahoo.com>

 SQLT / COMPARE is designed to identify differences in two execution environments.

SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly [ID 215187.1] COMPARE Method
Use this COMPARE method when you have two similar systems (SOURCES) and the same SQL statement performs fine in one of them but not in the other. This method helps to pin-point the differences between the two SOURCES in terms of plans, metadata, CBO statistics, initialization parameters and bug fix-control. SQLT must be installed first in both, and any of the main methods must have been used on the same SQL in both systems.

The compare can take place on any of the two SOURCES or on a 3rd COMPARE system. The latter should contain the SQLT repositories of the two SOURCES. To import a SQLT repository use the syntax provided in the sqlt_99999_readme.html file generated by any of the main methods.

Once the COMPARE system contains the repositories from both SOURCES, execute sqlt/run/sqltcompare.sql connecting as SYS, SQLTXPLAIN or the application user. A list of STATEMENT_ID is presented, from which you choose which two SQLT stored executions you want to compare. Once you indicate the two STATEMENT_ID you are asked next for particular PLAN_HASH_VALUE from both SOURCES.
# cd sqlt/run
# sqlplus sqltxplain

SQL> START sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2]
SQL> START sqltcompare.sql 92263 72597
SQL> START sqltcompare.sql 

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 06 2011 - 15:34:54 CDT