From a VBS Script to a 10046 Trace and Back into a VBS Script

12032010

March 12, 2010

I thought that we would try something a bit more difficult today. In previous articles I showed how to generate and read 10046 trace files using various methods, and I also showed a couple of different VBS scripts that could interact with an Oracle database. With some effort we could even read through a 10046 to pull out bits of information, much like TKPROF, but it probably does not make much sense to reproduce what TKPROF already accomplishes. I thought instead what I would do is to create a VBS script that generates a 10046 trace file at level 4, while executing a couple of SQL statements. A second VBS script will read the raw 10046 trace file and convert that trace file back into a VBS script, complete with bind variables. The code for the second VBS script is based on some of the code in my Toy project for performance tuning – something that I originally created just to see if it could be done.

First, we need a table to use as the data source for the first VBS script – this is the test table used in this blog article:

In the above, replace MyDB with a valid database name from the tnsnames.ora file, MyUsername with a valid username, and MyPassword with the password for the user. The script starts by starting a transaction (the default behavior is an implicit commit), a row is inserted into the test table, and then a ROLLBACK is performed. The script then submits a SQL statement that retrieves a list of 5 employees from the test table. For each of the (up to) 5 employees a second SQL statement is executed with two different bind variable sets to determine the number of each week day the employee has been out of work on an excused (EXCUSE) or unexcused (ABS) absence. If I were writing a real program to accomplish this task I would combine the three SELECT statements into a single SELECT statement, but I want to demonstrate how the second VBS script handles multiple SQL statements that are open at the same time.

Running the above script generated a trace file when executed against Oracle Database 11.2.0.1: or112_ora_5482_VBS2TRACE2VBS.trc (save as C:\or112_ora_5482_VBS2TRACE2VBS.trc – Windows users can view the file with Wordpad and convert the file into a plain text file that can be opened with Notepad). The goal is to take the trace file and transform it back into a VBS script, ignoring SQL statements that appear in the trace file at a depth greater than 0.

The output of the VBS script that reads the 10046 trace file and generates a VBS file should look something like this:

If you compare the original TestScript.vbs with the above output, we see that the two scripts are similar, but with a couple of distinct differences:

It is not necessarily easy to determine when a transaction starts, but it is possible to determine when a transaction ends. The script that reads the trace file inserts ‘dbDatabase.BeginTrans where it believes that a transaction should start – remove the ‘ if that is the correct starting point for the transaction.

Looping structures with nested SQL statements (the retrieval of the employee list from the EMPLOYEE_RECORD_TEST table and the probing of matching rows for each of those employees) cannot be reproduced automatically – you will have to recognize when one SQL statement is feeding the bind variable values of a second SQL statement.

The VBS script assumes that up to 20 cursors will be open at any one time, but will automatically handle many more than 20 simultaneously open cursors. The resulting VBS file should be cleaned up to remove the unneeded comData and snpData objects.

There are spurious snpDatan.Close statements – see the suggestions for improvements.

SQL statements submitted without bind variables will not have code written to execute those statements in the generated VBS file – see the suggestions for improvements.

Bind variables that are submitted as VARCHAR (adVarchar) are written to the trace file as if the bind variables were declared as CHAR (adChar) – while this does not appear to cause a problem, it might appear to be an unexpected change when comparing the test script with the automatically generated script.

Suggestions for improvement:

Recognize the EXEC line in the 10046 trace and use that to actually indicate that a SQL statement should execute in the generated script, rather than executing the SQL statement in response to the submission of bind variables.

Allow submitting the source trace file name and the destination (generated) VBS filename on the command line.

Allow submitting the username, password, and database name on the command line or in a web-based user interface.

Correct the script so that it does not attempt to close recordsets when those recordsets were never opened at dep=0 – this is caused by the script seeing a recursive SQL statement that is preparing to open with that cursor number.

The VBS script that converts 10046 trace files to VBS script files may be downloaded here: TraceToVBS.vbs (save as TraceToVBS.vbs). There may be bugs in the script, but it should be close enough to provide some degree of educational benefit.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: