xhume 11.4.4.1 2012/01/02 carlos.sierra

Exhumes TC schema object statistics to discover plans


When to use:
~~~~~~~~~~~
Use xhume only when ALL these conditions are met:
1. SQL produces unstable plans. Parameters and binds have been ruled out.
2. A known plan can be reproduced on TC with or without setting CBO env.
3. You need to dig into past of statistics trying to discover old plans.
4. You have full access to the test system, including SYS access.


When NOT to use:
~~~~~~~~~~~~~~~
Do not use xhume when ANY of these conditions is true:
1. The SQL statement may cause corruption or update data.
2. There is high volume of data in tables referenced by SQL.
3. The execution of the SQL may take longer than a few seconds.


Pre-installation:
~~~~~~~~~~~~~~~~
1. Implement a TC using SQLT.

2. Review and execute sqlt/utl/xhume/sqltrstobj.sql

   Be aware that sqltrstobj.sql updates object creation date in the
   data dictionary, which leaves your database in an unsupported state.

   As an alternative you can set the OS time to a prior point before
   creating the test case. This way there is no need to use script
   sqltrstobj.sql. If you use this alternate method, you may want to
   set the OS clock to one year ago, or at least one month ago. Then,
   after you create your test case, you can reset the OS clock to
   current time.

   To use the sqltrstobj.sql script follow these steps:

   # cd sqlt/utl/xhume
   # sqlplus / as sysdba
   SQL> START sqltrstobj.sql

   P1. Source Statement ID (required)
       A list of available statement_ids is presented.

   P2. Testcase User (required and case sensitive)
       Test Case user (TC99999sfx) as per metadata script.

   Notes:
   a. Never use this script on a production environment.
   b. This script must only be used on a test environment.


Install:
~~~~~~~
1. Review and execute pre-installation steps.

2. Connect as SYS and execute install script:

   # sqlplus / as sysdba
   SQL> START install.sql

   Note:
   You will be asked for the test case user and password.
   Test case user should exist already. Include suffix if any.
   xhume has no dependencies on SQLT.


Use:
~~~
1. Review the xhume_script generated by last step of installation.

2. Set the CBO environment ONLY if needed to reproduce a known plan.

   Notes:
   You may need to issue some ALTER SESSION commands.
   For example: STATISTICS_LEVEL or "_COMPLEX_VIEW_MERGING".

   # sqlplus <user>
   SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL; -- example

3. Execute generated xhume_script. It will ask for two parameters:

   P1. Name of the script to be executed.

       Note:
       Your SQL must include token ^^unique_id within a comment.
       This token should be in the first line of your sql text.
       Example: SELECT /* ^^unique_id */ t1.col1, etc.

   P2. Password for <user>

   # sqlplus <user>
   SQL> START xhume_script_1.sql your_own_script.sql <pwd>


Uninstall:
~~~~~~~~~
1. Connect as SYS and execute uninstall script:

   # sqlplus <user>
   SQL> START uninstall.sql

   Note:
   You will be asked for the test case user.


Feedback:
~~~~~~~~
carlos.sierra@oracle.com
