Capturing DBMS Server Trace Information
A .NET application using the .NET Data Provider can capture DBMS trace information from a DBMS Server of Release 9.1 (2006r2) or later. The DBMS trace information can include a Query Execution Plan (QEP), trace points, lock traces, and more. The application directs the data provider to capture the trace messages using the .NET application configuration file with a key of Ingres.trace.dbms.
For example:
<?xml version="1.0" encoding = "utf-8" ?>
<configuration>
<appSettings>
<add key="Ingres.trace.log" value="C:\temp\Ingres.trace.log" />
<add key="Ingres.trace.dbms" value="1" />
</appSettings>
</configuration>
A sample code fragment that directs the DBMS Server to return trace information to the client may look like the following:
static void TraceQEPLock_Trace(string connstring)
{
DbCommand cmd;
using (IngresConnection conn = new IngresConnection(connstring))
{
conn.Open();
cmd = conn.CreateCommand();
// SET PRINTQRY statement displays each query and its
// parameters as it is passed to the DBMS Server for processing.
cmd.CommandText = "set printqry";
cmd.ExecuteNonQuery();
// SET LOCK_TRACE statement enables the display of locking
// activity for the current session, including locks taken
// and locks released.
cmd.CommandText = "set Lock_Trace";
cmd.ExecuteNonQuery();
// SET QEP statement displays diagrammatic representation
// of the query execution plan chosen for the query
// by the optimizer.
cmd.CommandText = "set qep";
cmd.ExecuteNonQuery();
// SET TRACE POINT QE90 statement to display
// the estimated vs. actual cost in disk and
// CPU time of running the query.
cmd.CommandText = "set trace point qe90";
cmd.ExecuteNonQuery();
// SET PRINTRULES statement causes the DBMS Server to send
// a trace message to the application each time a rule is fired.
cmd.CommandText = "set printrules";
cmd.ExecuteNonQuery();
cmd = conn.CreateCommand();
cmd.CommandText =
"SELECT * FROM mytable";
cmd.ExecuteReader();
} // end using (IngresConnection conn)
}
The program output of such a code fragment may look like:
QUERY BUFFER:
set Lock_Trace
QUERY PARAMETERS:
QUERY BUFFER:
set qep
QUERY PARAMETERS:
QUERY BUFFER:
set trace point qe90
QUERY PARAMETERS:
QUERY BUFFER:
set printrules
QUERY PARAMETERS:
QUERY BUFFER:
SELECT * FROM mytable
QUERY PARAMETERS:
QUERY PLAN 1,1, no timeout, of main query (output from SET QEP)
Proj-rest
Sorted(name)
Pages 1 Tups 1
D1 C0
/
mytable
B-Tree(NU)
Pages 5 Tups 1 (output from SET LOCK_TRACE)
LOCK: MVCC PHYS Mode: IX Timeout: NOWAIT
Key: (mydatabase,mytable)
LOCK: TABLE PHYS Mode: IS Timeout: NOWAIT
Key: (mydatabase,mytable)
LOCK: PAGE PHYS,LOCL,NOIN,QUTM Mode: S Timeout: 0
Key: (mydatabase,mytable,0.0)
UNLOCK: PAGE Key: (mydatabase,mytable,0.0)
----------
| 0|
ORIG (output from SET TRACE POINT QE90)
at 1
et 1
ad 0
ed 1
ac 0
ec 1
et 0
----------