CA Tuesday Tip: All About Clarity SQL Tracing

Discussion created by Shawn_Moore Employee on Feb 28, 2011
Latest reply on Oct 3, 2016 by navzjoshi00
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 3/1/2011

Hi Folks,

Based on some recent feedback, I decided to devote some time this week to SQL tracing. I originally planned to write an article to cover all the aspects of SQL Tracing, but then realized we have some extensive articles on So I'll list these out and provide some supplemental comments.

What Are the Different Levels for SQL Tracing
refer to TEC513073 - Using Clarity SQLTrace - Explanation of Logging Levels

This article explains the bit mask that is used to form the the sql trace values. Typically Clarity support will request a level 7 for general query timing issues or situations where an error may be visible in the SQL Trace, but is less clear in the app-niku.log. A level 10 is typically requested if the engineer needs to review result set data.

Note: A SQL Trace level of 2 includes SQL Statements, which is level 1. So some levels will be the same as others. (i.e. a 6 and a 7 should be the same)

How to interpret the breakdown in timings for a SQL Trace:
refer to TEC439280 - How to read SQL Trace Log Timings (Niku KB ID: 7807)

This article describes the break down of timings and explains open, execute and non-sql times. General rule of thumb is that the execute time what happens on the db server. The open time, if much higher than the execute time will typically indicate that the bulk of the processing is in fetch time.

Reading(interpreting) Clarity SQL Traces
refer to TEC435531 - Using Clarity SQL Trace

This extensive article explains how to activate and deactivate SQL Trace files. It also covers finding the log files, interpreting the content within and much more.

If there are additional questions not covered in the above articles, I would be happy to answer them in this thread.