Oct
30
Written by:
Quest Field Team
10/30/2008 9:09 PM
Hello,
My name is Tim Fritz. I am a Solution Architect Manager at Quest Software.
I wrote this blog entry to describe how to see the SQL text of a resource-intensive statement that has been identified by the Foglight Oracle agent’s “SQL Hog” collection.
As you may know, you can set thresholds for CPU and I/O that, if exceeded, will identify an executing session as a “SQL Hog”. The statement text itself is not stored in the Foglight repository (as of Foglight release 5.2.3). But the statement can be seen if you have told the Oracle instance agent via the Properties to ‘Save detected SQL to file’.
-
First, tell Foglight that you want this agent to save detected resource-intensive SQL statements to a file.
-
Drill into the data browser to see information about the session that was running the SQL Hog statement. Note date/time and high CPU usage.
-
Details about the session can be found in the data browser, including the program name and the machine from which the connection to Oracle was made.
-
Finally, view the problem SQL statement text in the file $FGLHOME\logs\SQLlog. The SQL text entry in the file will be shown with a timestamp that matches when the Fogligth Oracle instance agent identified the statement's session as a "CPU Hog" or "I/O Hog".
Tags: