START_QUERY_LOG

Begin logging query requests. Starting with Oracle Database 12c Release 2 (12.2), the query logs are written to the database trace files.

Use CTX_OUTPUT.END_QUERY_LOG to stop logging queries. Use CTX_REPORT.QUERY_LOG_SUMMARY to obtain reports on logged queries, such as which queries returned successfully the most times.

The query log includes the query string, the index name, and the timestamp of the query, as well as whether or not the query successfully returned a hit. A successful query for the phrase Blues Guitarists made at 6:46 (local time) on November 11th, 2003, would be entered into the query log in this form:

<QuerySet><TimeStamp>18:46:51 02/04/03</TimeStamp><IndexName>
IDX_SEARCH_TABLE</IndexName><Query>Blues
Guitarists</Query><ReturnHit>Yes</ReturnHit></QuerySet>

Syntax

CTX_OUTPUT.START_QUERY_LOG(logfile in varchar2, overwrite in default true);

logfile

Specify the name of the query log file. Starting with Oracle Database 12c Release 2 (12.2), the logfile parameter is ignored. The logs are appended to the database trace files instead. Use the dictionary views such as V$DIAG_INFO and V$PROCESS to find the path to your current session’s trace file or to the trace file for each Oracle AI Database process.

The Automatic Diagnostic Repository Command Interpreter (ADRCI) utility can also be used to access the trace files.

overwrite

Specify whether you want to overwrite or append to the original query log file specified by logfile, if it already exists. Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. By default, all logs are appended to the database trace file.

Example

begin

CTX_OUTPUT.START_QUERY_LOG('mylog1');
     < get queries >
CTX_OUTPUT.END_QUERY_LOG;

end;

Notes

No logs are written if the PDB lockdown profile CTX_LOGGING is enabled.

Filenames used in CTX_OUTPUT.START_QUERY_LOG are restricted to the following characters: alphanumeric, minus, period, space, hash, underscore, single and double quotes. Any other character in the filename will raise an error.

Logging is associated with a session-it can log operations that take place within a single session, and, conversely, cannot make measurements across sessions.