18 Oracle Database Lite Tracing

The Oracle Lite database is used in conjunction with other products such as Oracle forms, SQLJ, Web Servers, and OC4J. When an unexpected error is reported by the software system, users need to identify the location and cause of the error. Errors can be caused due to problems in the application code, Oracle tools—such as forms, SQLJ, OC4J—or in the Oracle Lite database. Errors also occur in simple environments where a user application talks directly to the Oracle Lite database through JDBC or ODBC drivers. It may not be obvious which component is at fault—whether it is the user application, JDBC or ODBC drivers, or the core database runtime system.

If the optimizer spends too much time evaluating alternative plans or collecting index statistics, a query may take a long time for compilation. If the execution plan selected by the optimizer is not optimal, the query may also take a long time during execution. Based on these criteria, the tracing facility provides the compilation time and the execution plan.

The following sections describe how to set and use tracing.

• Section 18.1, "Enabling Trace Output"

• Section 18.2, "Description of Trace Information"

18.1 Enabling Trace Output

To enable Trace output, include the following line in the POLITE.INI configuration file.

OLITE_SQL_TRACE= yes

Note: Any value other than "yes" disables the tracing feature. The parameter value is checked once during database startup. Hence, users must set this value before connecting to the database.

When you enable tracing, the trace information is dumped to a file named oldb_trc.txt in the current working directory of the database process. If the file already exists, then the trace output is appended to the end. If it does not exist, then a new file is automatically created. For a database service on Windows or the Oracle Lite database daemon for a Linux platform, the current working directory is specified by the wdir parameter during startup of the database service or daemon.

Note: To implement the tracing feature, the database process must contain permissions to create the trace file in the current working directory.

18.2 Description of Trace Information

The following trace information is provided:

Table 18-1 Trace Output

Trace Output	Description
Statement Text	Each time a SQL statement is prepared, its text is dumped into the trace file. The SQL statement itself is output without any formatting. If a SQL statement contains a new line character, it is also included in the SQL statement output.
Compilation Time	After the SQL statement is compiled, the compilation time is printed.
Execution Plan	If there are no errors, the execution plan is printed when available. Only statements that contain a WHERE clause generate an execution plan. The printed plan contains the execution order of tables for each sub-select.
Bind Value	If a SQL statement contains markers, then the bind value is printed for every line. Each value for the marker or bind variable is printed on a separate line in the following format. Marker [<number>]: <Value> Where, <number> is the number of the marker and <value> denotes the value of the marker before execution.
Temporary Table Created	Each time a temporary table is created, its name is dumped into the trace file.
Table Access	Each time a table is accessed, the following information is dumped into the trace file: Table Name: The name of the table been accessed is dumped into the trace file. Access Method: The access method used by the database is dumped into the trace file. For a description of how this information is presented, see Section 18.2.1, "Table Name Output".
Temporary Table Sorted	Each time a temporary table is sorted, its name and sorting time (in milliseconds) are dumped into the trace file.
First Fetch Time	If the SQL statement is a SELECT statement, the time spent on fetching the first row is dumped into the trace file.
Tid	The thread ID is dumped into the trace file in front of some of the dumped information. The thread is displayed in the following format: Tid: <thread id>

18.2.1 Table Name Output

The name of the table that is currently being accessed and the method used to access the table are printed in the following formats.

• If the table is accessed sequentially, the format is:

  Table Name: <table name>

  Access Method: Sequential

  Where <table name> is the name of the table being accessed.

• If indices are used, the format is:

  Table Name: <table name>

  Access Method: Term[<number>], Index No: <index number>, IndexName: <index name>

  <table name> is the name of the table being accessed.

  Term[<number>] is the internal representation of the conjunct search conditions in the WHERE clause.

  <index number> is the index number. Each index has an unique number in the database.

  <index name> is the name of the index if any.