Re: TCB

From: Karl Arao <karlarao@xxxxxxxxx>
To: niall.litchfield@xxxxxxxxx
Date: Wed, 25 Jul 2012 03:15:16 -0500
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


--
//www.freelists.org/webpage/oracle-l
References:
- Re: TCB
  - From: Niall Litchfield
Re: TCB

Other related posts:
