Retrieve the Values of Bind Variables Used in Windchill SQL Statements

This first step is to set the following properties in 

$WT_HOME/db/db.properties: 

wt.pom.log.file=$(wt.logs.dir)/PersistenceManager.log 
wt.pom.log.enabled=true 
wt.pom.log.SQLStatements=true 

After the relevant actions have been performed in Windchill, 
the $WT_HOME/logs/PersistenceManager.log file will contain entries similar to the following: 

jeu. 5/2/02 12:48:57: RMI TCP Connection(43)-132.253.227.90: 

POM Log: Query(wt.pds.DepthThresholdStatementBuilder)=

SELECT 0 FROM V_WTPart A0, PageResults A1 WHERE (A1.sessionId = ?) AND (A1.rowOrder > ?) 
AND (A1.rowOrder <= ?) AND (ROWNUM < 2) AND (((A0.idA2A2(+) = A1.oid1) AND (A1.className1 = ?))) 
UNION SELECT 1 FROM V_GenericPart A0,PageResults A1 WHERE (A1.sessionId = ?) 
AND (A1.rowOrder > ?) AND (A1.rowOrder <= ?) AND (ROWNUM < 2) 
AND (((A0.idA2A2(+) = A1.oid1) AND (A1.className1 = ?))) 
UNION SELECT 2 FROM V_WTProduct A0,PageResults A1 WHERE (A1.sessionId = ?) 
AND (A1.rowOrder > ?) AND (A1.rowOrder <= ?) AND (ROWNUM < 2) 
AND (((A0.idA2A2(+) = A1.oid1) AND (A1.className1 = ?))) 
UNION SELECT 3 FROM V_WTSerialNumberedPart A0,PageResults A1 
WHERE (A1.sessionId = ?) AND (A1.rowOrder > ?) AND (A1.rowOrder <= ?) 
AND (ROWNUM < 2) AND (((A0.idA2A2(+) = A1.oid1) AND (A1.className1 = ?))); 

Bind Parameters=326207, 0, 20, 
wt.part.WTPart, 326207, 0, 20, 
com.ptc.wpcfg.family.GenericPart, 326207, 0, 20, 
wt.part.WTProduct, 326207, 0, 20, 
wt.part.WTSerialNumberedPart 

All the bind variables are shown as question marks, and their corresponding values are 
listed at the end of the line, in the 'Bind Parameters' section. In this example, 
the first variable "A1.sessionId = ?" should be substituted as "A1.sessionId = 326207", 
the second one "A1.rowOrder > ?", as "A1.rowOrder > 0", etc. 

Note that the bind variable values are passed 'as-is', which means that they do not 
take into consideration the format that needs to be used in SQL. For example, the 
fourth variable "A1.className1 = ?" should be substitued as "A1.className1 = 'wt.part.WTPart'" 
(note the additional single quotes). 

You may refer to the table definitions in the data dictionary in case you have any doubts 
about which format should be used. 

After the substitution has been done, this results in the following SQL statement, that 
can be run directly from SQL*Plus to test the effects of each tuning steps: 

SELECT 0 FROM V_WTPart A0,PageResults A1 WHERE (A1.sessionId = 326207) AND (A1.rowOrder > 0) 
AND (A1.rowOrder <= 20) AND (ROWNUM < 2) AND (((A0.idA2A2(+) = A1.oid1) 
AND (A1.className1 = 'wt.part.WTPart'))) UNION SELECT 1 FROM V_GenericPart A0,PageResults A1 
WHERE (A1.sessionId = 326207) AND (A1.rowOrder > 0) AND (A1.rowOrder <= 20) AND (ROWNUM < 2) 
AND (((A0.idA2A2(+) = A1.oid1) AND (A1.className1 = 'com.ptc.wpcfg.family.GenericPart'))) 
UNION SELECT 2 FROM V_WTProduct A0,PageResults A1 WHERE (A1.sessionId = 326207) 
AND (A1.rowOrder > 0) AND (A1.rowOrder <= 20) AND (ROWNUM < 2) AND (((A0.idA2A2(+) = A1.oid1) 
AND (A1.className1 = 'wt.part.WTProduct'))) UNION SELECT 3 
FROM V_WTSerialNumberedPart A0,PageResults A1 WHERE (A1.sessionId = 326207) 
AND (A1.rowOrder > 0) AND (A1.rowOrder <= 20) AND (ROWNUM < 2) AND (((A0.idA2A2(+) = A1.oid1) 
AND (A1.className1 = 'wt.part.WTSerialNumberedPart'))); 

Please note the two following points: - Depending of the nature of the variables involved 
(binary objects, nested tables, etc), this technique may not work. 

It should however be sufficient in the majority of cases. - If the SELECT statement will return 
a lot of data (many rows, or long rows), then you should be careful because a lot of time will 
be spent displaying the data into your SQL*Plus session, even though the query in Oracle itself 
was very short. It is therefore advised to limit the size of the returned result.