Recommendations for Setting Event Logging and SQL Tagging Issue

Siebel event log levels are explained in Siebel System Monitoring and Diagnostics Guide in Siebel Bookshelf. To get full SQL information, including the bind variables, a command like the following could be used for all Siebel Servers.

change evtloglvl %SQL%=4 for component sccobjmgr_enu server <siebel server name>

In a production environment, the above command will produce large log files, which are hard to visualize, parse, and analyze. Also, setting this parameter to values of 4 or 5 has a performance impact. So, if evtloglvl is set to any value other than 1, it is important to use those settings cautiously, for very limited duration, and in a controlled manner.

Note: The above setting implicitly activates the Siebel SQL Tagging which adds a “:1” bind variable to the original SQL statement, hence the Optimizer consider all SQLs statements as new and hard parses them and might generate sub-optimal execution plans. Additionally, because these SQL statements are considered different, SPM baselines, SQL Profiles and Stored Outlines will not be used. If you enable SQL Event level logging to 4 or 5 it is a must to set back ObjMgrSQLTag = 1.

Sample SQL-Tagged Code

The following is a sample of how a tagged SQL statement might appear in a log file when the SQL statement was generated from a Siebel Call Center Object Manager component (for example, SCCObjMgr_enu, for U.S. English) and an Oracle database. The changes made by the SQL tagging feature appear in italics.

SELECT
FIRST_NAME,
LAST_NAME,
…,
:1
FROM
TBO.S_CONTACT
…
WHERE
LAST_NAME LIKE:2
ORDER BY
…
Bind variable 1:
SCCObjMgr_enu,sdchs20i046,10485776,SADMIN,00000089489108a8:50557,Account,Account,
Account List View
Bind variable 2: Foo*

The above information provides the source of SQL in the Siebel CRM application, and thus facilitates the analysis and implementation of possible changes that could be made to address the performance issues.

Note: Due to the addition of ”:1” in the query, the Oracle RDBMS considers the SQL SELECT statement to be a new and different statement compared to the base version, when this feature isn'tON. Therefore, Stored Outlines and SPM Plans that were captured without ‘:1’ aren't used.