Re: TCB

From: Karl Arao <karlarao_at_gmail.com>
Date: Wed, 25 Jul 2012 03:15:16 -0500
Message-ID: <CACNsJnfY6R6SVpdiaVWD2j4Ak3oDh=bBAUB1ja8G1q6mjxcYrg_at_mail.gmail.com>

Hi Niall,
For me this is where the "SQLTXPLAIN-test case builder" shines... I mainly use the SQLT-tc (test case builder) and that is totally different from the TCB (test case builder) which uses the DBMS_SQLDIAG... so in SQLTXPLAIN you've got essentially two tools to create a SQL test case but I find the SQLT-tc more powerful and easy to customize.

In your scenario where you are importing the test case on another environment with a different schema name the SQLT-tc will ask you for the (new) schema name and password and it will do the following on that particular schema:
1) Creates test case user and schema objects connecting as SYSDBA.. let's say your new schema name is TESTUSER

2) Purge pre-existing s<ID> from local SQLT repository connected as SYSDBA
3) Import SQLT repository for s<ID> (provide SQLTXPLAIN password)
4) Restore CBO schema object statistics for test case user connected as

SYSDBA
5) Restore CBO system statistics connected as SYSDBA 6) Set the CBO environment connecting as test case user TESTUSER (include optional test case user suffix)
7) Execute test case

All these steps will be executed when you run the "sqltc.sql" or the "xpress.sql" (on the newer versions)... but each step runs a specific SQL where you can customize or comment if you don't want to run that particular step or any underlying commands on each of those SQLs. And when you are troubleshooting an execution plan difference it could be caused by different factors around that SQL (CBO schema stats, system stats, object differences, environment, etc.) so you can have a layered approach in troubleshooting and turn on/off each step or component on the "sqltc.sql" or the "xpress.sql". I usually run the SQLTcompare first, then figure out what's making it different. Then one by one I'll play around with each of the step of the sqltc.sql (environment, object stats, system stats, and all of them at once).. then you'll get to a point where you'll have the same plan and run time... then you can be creative and start playing with hints, profiles, rewrite, importing the stats, environment variables, etc. to solve the issue.

here's a quick write up
http://karlarao.wordpress.com/2012/02/11/sqltxplain-quick-tips-and-tricks-and-db-optimizer-vst/ here's the SQLT-tc howto - http://goo.gl/FrNBR SQLTXPLAIN scenarios - http://goo.gl/uWrGx SQLTcompare - http://karlarao.tiddlyspot.com/#SQLT-compare

-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com


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

Received on Wed Jul 25 2012 - 03:15:16 CDT