CA Clarity Tuesday Tip: The "Surgical SQL Trace"

[Anon Anon]

Title: CA Clarity Tuesday Tip: The "Surgical SQL Trace"

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 08/01/2011

A little trick that I find useful when performing SQL Traces is what I call the "Surgical SQL Trace."

Basically, our objective is to get the smallest SQL Trace of a page without having all other other actions and page's SQL Tracing present in the file. This can be done rather easily.

Here's how it's done:

1) Navigate to the page in Clarity which you would like to trace.
2) Copy the URL from the browser.
3) Log out.
4) Activate SQL Tracing
5) Paste the URL into your browser URL field. (this will cause a login prompt to appear)
6) Specify your login credentials (You will then be redirected to the specific page you were at.)
7) Now you can log out. (You will then have a small SQL Trace file with just the page of tracing you specified)

This is great when trying to examine the SQL Trace data as it reduces file size and wading through many queries. (The StartAction text does help when searching, but I like this technique even more. :happy )

-shawn

[Anon Anon]

Shawn_Moore wrote:

7) Now you can log out. (You will then have a small SQL Trace file with just the page of tracing you specified)

Doesn't that trace also include your "logon" processing and also the processing required to display the screen that you are trying to trace? (not a great problem though!).

I tend to turn just tracing on, navigate to the screen I want to trace, then delete the trace file on the server, then perform the action I want to trace..... similar resulting small trace file. cool)

[mansriva]

If we are doing the sql trace at 10 we'll have the very big file created.
Instead of going with the loging navigating to the required page.
I think it is better to paste the url.

[Anon Anon]

OK for sure paste the URL rather than navigating around the system screen by screen; just DELETE the trace file AFTER you have pasted the URL and BEFORE you perform the action that you want to trace; thats really my point!

[navzjoshi00]

A better approach could be to create sql file for each activity. Cut and paste the sql log file, to another folder (rename it), as per the activity performed.

This way u can get to know as to what SQL was generated when u performed a particular activity.

NJ

[System]

Great tip Shawn and thanks everyone for giving Shawn some feedback!

[Deactivated User]

You don't need to copy paste the URL

Just go to the page you want to trace.
Log out
Go back (the page will be displayed from browser cache)
Refresh (login will be displayed)
Put the trace on
Login (the page you want to trace is displayed)
Put the trace off
Log out

Martti K.

[Legacy User]

Good suggestions.

I have a couple ideas that I've been using a long time:
1-after navigating in the UI to the point where I need to take the trace, I open the trace file, delete its contents and save it. That way, the trace file will only contain the results of my next click.
2-if I need to capture several steps, before each step is initiated, I will open the trace file and insert characters that I can later search for (e.g. "#####ClickonResuorcesLink") and then save the file.

Dale

[skchourey]

I follow the same thing what Dale mentioned here..

In some situations, following are the issues with Copy/Paste URL / Login/Logout method :
1.Clarity doesn't put all the info in URL but in hidden variables as well ( like partitionCode ). If paste URL after logout, we lose that info..
2. If you need to trace an Insert/update then you will be 2 piece of info in Trace. .. First page load and second the Insert/update action.

[ CA should provide an Trace interface for admins, eliminating need to log into app server and view trace files. ( Just like NSA shows logs but Trace one needs Session/ON/OFF control features )
Like all active user sessions should be listed and when Admin wants he can start/stop trace for that session and immediately view trace file. Like NSA Performance tracker does.. ]

- Sangeet

[Anon Anon]

I agree with Sangeet - CA should provide a Trace interface on the Admin side of Clarity.
It seems most people in this conversation have direct access to the App server.
In our case (as it should be with any Production application), the servers are in a completely controlled environment, and things can't just be "played" with.
Our Admin users are not DBA's or Server admin people - just part of the PMO office and 'own' the application, create queries/portlets/provide first line support, etc.
-Marlon.

[Anon Anon]

Wow, a lot of good comments!

We definitely know there is a need for improvement in SQL Tracing. Stay tuned, we've been spending a lot of time with development talking about SQL Traces and supportability. But I can't divulge anything at this time. :wink:

-shawn

[Anon Anon]

Hi,

Please guide in what scenerios we use the trace 1, 2, 4, 8 and I have seen the use of 7 also.

Regards
Mridula

[Anon Anon]

mrid wrote:

Please guide in what scenerios we use the trace 1, 2, 4, 8 and I have seen the use of 7 also.

Perhaps you could just read the TEC article concerning SQL trace? TEC435531