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:
CREATE TABLE EMPLOYEE_RECORD_TEST AS SELECT DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)), 0,'MIKE', 1,'ROB', 2,'SAM', 3,'JOE', 4,'ERIC') EMPLOYEE_ID, TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE, DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)), 0,'VAC', 1,'HOL', 2,'BEREAVE', 3,'JURY', 4,'ABS', 5,'EXCUSE', 6,'MIL', 'OTHER') INDIRECT_ID FROM DUAL CONNECT BY LEVEL<=1000;
With the test table built, we execute the following simple VBS script (using either CSCRIPT or WSCRIPT on a Windows client):
Const adCmdText = 1
Const adNumeric = 131
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adParamInput = 1
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase
Dim dbDatabase
Dim snpDataEmployees
Dim comDataEmployees
Dim snpDataAttend
Dim comDataAttend
Dim snpDataEmpRecord
Dim comDataEmpRecord
Set dbDatabase = CreateObject("ADODB.Connection")
Set snpDataEmployees = CreateObject("ADODB.Recordset")
Set comDataEmployees = CreateObject("ADODB.Command")
Set snpDataAttend = CreateObject("ADODB.Recordset")
Set comDataAttend = CreateObject("ADODB.Command")
Set snpDataEmpRecord = CreateObject("ADODB.Recordset")
Set comDataEmpRecord = CreateObject("ADODB.Command")
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code
dbDatabase.Execute "ALTER SESSION SET TRACEFILE_IDENTIFIER = 'VBS2TRACE2VBS'"
dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'"
strSQL = "INSERT INTO EMPLOYEE_RECORD_TEST(" & VBCrLf
strSQL = strSQL & " EMPLOYEE_ID," & VBCrLf
strSQL = strSQL & " SHIFT_DATE," & VBCrLf
strSQL = strSQL & " INDIRECT_ID)" & VBCrLf
strSQL = strSQL & "VALUES(" & VBCrLf
strSQL = strSQL & " ?," & VBCrLf
strSQL = strSQL & " ?," & VBCrLf
strSQL = strSQL & " ?)"
With comDataEmpRecord
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
.Parameters.Append .CreateParameter("employee_id", adVarChar, adParamInput, 15, "TEST")
.Parameters.Append .CreateParameter("shift_date", adDate, adParamInput, 8, Date)
.Parameters.Append .CreateParameter("indirect_id", adVarchar, adParamInput, 15, "HOL")
End With
'Rollback Test
dbDatabase.BeginTrans
comDataEmpRecord.Execute
dbDatabase.RollbackTrans
strSQL = "SELECT DISTINCT" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " SHIFT_DATE>= ?" & vbCrLf
strSQL = strSQL & " AND INDIRECT_ID= ?" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID"
With comDataEmployees
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
.Parameters.Append .CreateParameter("shift_date", adDate, adParamInput, 8, DateAdd("d", -90, Date))
.Parameters.Append .CreateParameter("indirect_id", adVarChar, adParamInput, 15, "VAC")
End With
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'2',1,0)) MON_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'3',1,0)) TUE_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'4',1,0)) WED_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'5',1,0)) THU_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'6',1,0)) FRI_COUNT" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID= ?" & vbCrLf
strSQL = strSQL & " AND INDIRECT_ID= ?"
With comDataAttend
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
.Parameters.Append .CreateParameter("employee_id", adVarChar, adParamInput, 15, "HOOPER")
.Parameters.Append .CreateParameter("indirect_id", adVarChar, adParamInput, 15, "EXCUSE")
End With
Set snpDataEmployees = comDataEmployees.Execute
If Not (snpDataEmployees Is Nothing) Then
Do While Not snpDataEmployees.EOF
comDataAttend("employee_id") = snpDataEmployees("employee_id")
comDataAttend("indirect_id") = "EXCUSE"
Set snpDataAttend = comDataAttend.Execute
If Not snpDataAttend.EOF Then
'Do Something with the data
End If
snpDataAttend.Close
comDataAttend("indirect_id") = "ABS"
Set snpDataAttend = comDataAttend.Execute
If Not snpDataAttend.EOF Then
'Do Something with the data
End If
snpDataAttend.Close
snpDataEmployees.MoveNext
Loop
snpDataEmployees.Close
End If
dbDatabase.Close
Set snpDataEmployees = Nothing
Set snpDataAttend = Nothing
Set comDataEmployees = Nothing
Set comDataAttend = Nothing
Set snpDataEmpRecord = Nothing
Set comDataEmpRecord = Nothing
Set dbDatabase = Nothing
(TestScript.vbs - save as TestScript.vbs)
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:
'Source File:C:\or112_ora_5482_VBS2TRACE2VBS.trc
HyperactiveTrace
Sub HyperactiveTrace()
Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adChar = 129
Const adUseClient = 3
Dim i
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase
Dim dbDatabase
Set dbDatabase = CreateObject("ADODB.Connection")
Dim snpData1
Dim comData1
Set snpData1 = CreateObject("ADODB.Recordset")
Set comData1 = CreateObject("ADODB.Command")
Dim snpData2
Dim comData2
Set snpData2 = CreateObject("ADODB.Recordset")
Set comData2 = CreateObject("ADODB.Command")
Dim snpData3
Dim comData3
Set snpData3 = CreateObject("ADODB.Recordset")
Set comData3 = CreateObject("ADODB.Command")
Dim snpData4
Dim comData4
Set snpData4 = CreateObject("ADODB.Recordset")
Set comData4 = CreateObject("ADODB.Command")
Dim snpData5
Dim comData5
Set snpData5 = CreateObject("ADODB.Recordset")
Set comData5 = CreateObject("ADODB.Command")
Dim snpData6
Dim comData6
Set snpData6 = CreateObject("ADODB.Recordset")
Set comData6 = CreateObject("ADODB.Command")
Dim snpData7
Dim comData7
Set snpData7 = CreateObject("ADODB.Recordset")
Set comData7 = CreateObject("ADODB.Command")
Dim snpData8
Dim comData8
Set snpData8 = CreateObject("ADODB.Recordset")
Set comData8 = CreateObject("ADODB.Command")
Dim snpData9
Dim comData9
Set snpData9 = CreateObject("ADODB.Recordset")
Set comData9 = CreateObject("ADODB.Command")
Dim snpData10
Dim comData10
Set snpData10 = CreateObject("ADODB.Recordset")
Set comData10 = CreateObject("ADODB.Command")
Dim snpData11
Dim comData11
Set snpData11 = CreateObject("ADODB.Recordset")
Set comData11 = CreateObject("ADODB.Command")
Dim snpData12
Dim comData12
Set snpData12 = CreateObject("ADODB.Recordset")
Set comData12 = CreateObject("ADODB.Command")
Dim snpData13
Dim comData13
Set snpData13 = CreateObject("ADODB.Recordset")
Set comData13 = CreateObject("ADODB.Command")
Dim snpData14
Dim comData14
Set snpData14 = CreateObject("ADODB.Recordset")
Set comData14 = CreateObject("ADODB.Command")
Dim snpData15
Dim comData15
Set snpData15 = CreateObject("ADODB.Recordset")
Set comData15 = CreateObject("ADODB.Command")
Dim snpData16
Dim comData16
Set snpData16 = CreateObject("ADODB.Recordset")
Set comData16 = CreateObject("ADODB.Command")
Dim snpData17
Dim comData17
Set snpData17 = CreateObject("ADODB.Recordset")
Set comData17 = CreateObject("ADODB.Command")
Dim snpData18
Dim comData18
Set snpData18 = CreateObject("ADODB.Recordset")
Set comData18 = CreateObject("ADODB.Command")
Dim snpData19
Dim comData19
Set snpData19 = CreateObject("ADODB.Recordset")
Set comData19 = CreateObject("ADODB.Command")
Dim snpData20
Dim comData20
Set snpData20 = CreateObject("ADODB.Recordset")
Set comData20 = CreateObject("ADODB.Command")
On Error Resume Next
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code
'dbDatabase.BeginTrans
'Transaction Committed and NO Records were Affected, Need to determine transaction start
dbDatabase.CommitTrans
'dbDatabase.BeginTrans
Set comData3 = CreateObject("ADODB.Command")
strSQL = "INSERT INTO EMPLOYEE_RECORD_TEST(" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & " SHIFT_DATE," & vbCrLf
strSQL = strSQL & " INDIRECT_ID)" & vbCrLf
strSQL = strSQL & "VALUES(" & vbCrLf
strSQL = strSQL & " ?," & vbCrLf
strSQL = strSQL & " ?," & vbCrLf
strSQL = strSQL & " ?)"
With comData3
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
End With
'comData3.Execute
With comData3
.Parameters.Append .CreateParameter("B1", adChar, adParamInput, 4, "TEST")
.Parameters.Append .CreateParameter("B2", adDate, adParamInput, 7, "3/11/2010 0:0:0")
.Parameters.Append .CreateParameter("B3", adChar, adParamInput, 3, "HOL")
End With
comData3("B1") = "TEST"
comData3("B2") = cDate("3/11/2010 0:0:0")
comData3("B3") = "HOL"
comData3.Execute
'Transaction Rolled Back and Records Should have been Affected, Need to determine transaction start
dbDatabase.RollbackTrans
'dbDatabase.BeginTrans
'Cursor 2 Closing
If snpData2.State = 1 Then
snpData2.Close
End If
Set comData2 = Nothing
Set comData2 = CreateObject("ADODB.Command")
strSQL = "SELECT DISTINCT" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " SHIFT_DATE>= ?" & vbCrLf
strSQL = strSQL & " AND INDIRECT_ID= ?" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID"
With comData2
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
End With
'Set snpData2 = comData2.Execute
'Cursor 4 Closing
If snpData4.State = 1 Then
snpData4.Close
End If
Set comData4 = Nothing
'Cursor 4 Closing
If snpData4.State = 1 Then
snpData4.Close
End If
Set comData4 = Nothing
'Cursor 4 Closing
If snpData4.State = 1 Then
snpData4.Close
End If
Set comData4 = Nothing
With comData2
.Parameters.Append .CreateParameter("B1", adDate, adParamInput, 7, "12/11/2009 0:0:0")
.Parameters.Append .CreateParameter("B2", adChar, adParamInput, 3, "VAC")
End With
comData2("B1") = cDate("12/11/2009 0:0:0")
comData2("B2") = "VAC"
Set snpData2 = comData2.Execute
If Not (snpData2 Is Nothing) Then
Do While Not snpData2.EOF
snpData2.MoveNext
Loop
End If
'Cursor 4 Closing
If snpData4.State = 1 Then
snpData4.Close
End If
Set comData4 = Nothing
Set comData4 = CreateObject("ADODB.Command")
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'2',1,0)) MON_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'3',1,0)) TUE_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'4',1,0)) WED_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'5',1,0)) THU_COUNT," & vbCrLf
strSQL = strSQL & " SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'6',1,0)) FRI_COUNT" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " EMPLOYEE_ID= ?" & vbCrLf
strSQL = strSQL & " AND INDIRECT_ID= ?"
With comData4
'Set up the command properties
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.ActiveConnection = dbDatabase
'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
End With
'Set snpData4 = comData4.Execute
With comData4
.Parameters.Append .CreateParameter("B1", adChar, adParamInput, 4, "ERIC")
.Parameters.Append .CreateParameter("B2", adChar, adParamInput, 6, "EXCUSE")
End With
comData4("B1") = "ERIC"
comData4("B2") = "EXCUSE"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
'Cursor 5 Closing
If snpData5.State = 1 Then
snpData5.Close
End If
Set comData5 = Nothing
comData4("B1") = "ERIC"
comData4("B2") = "ABS"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
'Cursor 5 Closing
If snpData5.State = 1 Then
snpData5.Close
End If
Set comData5 = Nothing
comData4("B1") = "JOE"
comData4("B2") = "EXCUSE"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
comData4("B1") = "JOE"
comData4("B2") = "ABS"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
comData4("B1") = "MIKE"
comData4("B2") = "EXCUSE"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
comData4("B1") = "MIKE"
comData4("B2") = "ABS"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
comData4("B1") = "SAM"
comData4("B2") = "EXCUSE"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
comData4("B1") = "SAM"
comData4("B2") = "ABS"
Set snpData4 = comData4.Execute
If Not (snpData4 Is Nothing) Then
Do While Not snpData4.EOF
snpData4.MoveNext
Loop
End If
'Transaction Committed and NO Records were Affected, Need to determine transaction start
dbDatabase.CommitTrans
'dbDatabase.BeginTrans
'Cursor 5 Closing
If snpData5.State = 1 Then
snpData5.Close
End If
Set comData5 = Nothing
'*************************************************************
'Maximum Recordset Number Used is 5 - Adjust the Code at the Start Accordingly
'*************************************************************
If snpData1.State = 1 Then
snpData1.Close
End If
Set snpData1 = Nothing
If snpData2.State = 1 Then
snpData2.Close
End If
Set snpData2 = Nothing
If snpData3.State = 1 Then
snpData3.Close
End If
Set snpData3 = Nothing
If snpData4.State = 1 Then
snpData4.Close
End If
Set snpData4 = Nothing
If snpData5.State = 1 Then
snpData5.Close
End If
Set snpData5 = Nothing
Set comData1 = Nothing
Set comData2 = Nothing
Set comData3 = Nothing
Set comData4 = Nothing
Set comData5 = Nothing
dbDatabase.Close
Set dbDatabase = Nothing
End Sub
(TraceToVBSOutput.vbs – save as TraceToVBSOutput.vbs)
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.
Related Blog Articles:
10046 Extended SQL Trace Interpretation
Automated DBMS_XPLAN, Trace, and Send to Excel
Database Inpector Gadget
Simple VBS Script to Retrieve Data from Oracle
Toy Project for Performance Tuning 2
Recent Comments