X

An Oracle blog about Oracle Text Index

  • January 18, 2018

Oracle Text Indexes - New logging and tracing methods in 12cR2

Roger Ford
Product Manager

Prior to Oracle 12cR2, getting a log of your index session required you to call something like this before starting the index creation:

ctx_output.start_log ('myfilename.log')

That would then create the output in a file $ORACLE_HOME/ctx/log/myfilename.log.  If I'm using PARALLEL 2 in index creation, I'll also see files myfilename.log_0 and myfilename.log_1.

There are a few problems with this approach:

  1. It doesn't work in a "read only Oracle Home" environment
  2. The user needs to have direct access to the $ORACLE_HOME/ctx/log/ directory to access the log files
  3. It gets messy and difficult in a cloud and/or PDB environment
  4. There's no way to start logging mid-session.  If you're wondering how far your indexing has got after several hours and you didn't turn on logging, your only option is kill indexing, drop the part-builstart logging and create the index from scratch.

So in 12cR2 (12.2.01) we have moved all logging and tracing to the standard database framework.  In some respects, this is a little more complex to deal with, but it offers powerful and secure features for analyzing logs.

Logging the current session

Assuming we can remember to turn logging on before we start the indexing job, we can use:

alter session set events 'TRACE[CONTEXT_INDEX] disk=low';

Naturally this does require ALTER SESSION privilege. The final word can be low, medium or high - we'll cover the meanings later.

To find out where the logging is going to, we can use:

select value from v$diag_info where name = 'Default Trace File';

(note that case is important).  In my case that returned:

VALUE
-------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/r12201/r12201/trace/r12201_ora_17260.trc

We can also get the same information from the package ctx_output - we have to call start_log, then logfilename:

SQL> exec ctx_output.start_log('anystringhere')
PL/SQL procedure successfully completed.

SQL> select ctx_output.logfilename from dual;

LOGFILENAME
-------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/r12201/r12201/trace/r12201_ora_17260.trc

So if we have OS access to the machine, we can just go and look at that file. If we don't have OS access, we just need to know the file name (not the path) and we can use either internal views, or the snappily-named Automatic Diagnostic Repository Command Interpreter (adcri).

Using ADRCI

The Automatic Diagnostic Repository Command Interpreter is a command-line tool for analyzing diagnostic files which was introduced in 11g.  It requires shell access to the database server, so is not much use in a Cloud DBaaS environment. However, it does have powerful capabilities. Look to the doc for details, but we'll cover the very basics here.

adrci> SHOW TRACE (co, op) <trcfilename> -xp "[co='CONTEXT_INDEX']"

‘show trace’ outputs the data to a file (*.ado) by default. You can push the output to a terminal with the “-term” switch
SHOW TRACE (co, op) x3_ora_26659.trc -term -xp "[co='CONTEXT_INDEX']"

To list all the parallel indexing trace file names, sorted by timestamp in reverse order:

adrci> SHOW TRACEFILE %p00% -rt

Note: To view query logs, use component name 'CONTEXT_QUERY' rather than 'CONTEXT_INDEX'.

Using Views:

There are new views in 12.2 to allow you to examine database trace files without having OS acces.

Once you've established the name of the tracefile (see above) you can run this:

select PAYLOAD from V$DIAG_TRACE_FILE_CONTENTS where COMPONENT_NAME = 'CONTEXT_INDEX' and TRACE_FILENAME = 'r12201_ora_17260.trc';

Note that 'ordinary' users can't SELECT from V$DIAG_TRACE_FILE_CONTENTS, you must be a DBA or have SELECT on that table explicitly granted to you.

I guess in theory you should add 'order by timestamp' to the end of that statement, but in fact they do seem to come out in timestamp order by default - though by normal SQL rules that's not guaranteed.

Logging another Session

If you've already started an indexing job, and need to know how far it's got, you won't be able to run 'ALTER SESSION' for that session. So instead you must do an ALTER SYSTEM call instead, which will affect all processes.

alter system set events 'TRACE[CONTEXT_QUERY] disk=low';

Finding the name of the tracefile can be challenging. You can't use v$diag_info as above, so instead you must construct the file name yourself.  The file name will normally take the form <SID>_ora_<procid>.trc. The value for <procid> can be found by looking up the spid column in V$PROCESS. Determining which row in V$PROCESS is the one you're looking for is beyond the scope of this article.

Alternatively, if you have OS access to the system in question, you can just go to the trace file directory (find it with > select value from v$diag_info where name = 'Default Trace File') and look for recent files, or grep for the string(s) you're expecting to find.

Tracing levels

There are three levels of tracing you can use: low, medium and high.

Trace level low:

alter session set events 'TRACE[CONTEXT_INDEX] disk=low';

This logs summary indexing information only (eg start, finish, no. of rows), and intermediate writes to disk

Trace level medium:

alter session set events 'TRACE[CONTEXT_INDEX] disk=medium';

This logs summary information and logs every 100 rows processed (similar to ctx_output.start_log)

Trace level high:

alter session set events 'TRACE[CONTEXT_INDEX] disk=high';
As medium plus it logs individual rowids and tokens. The following events are enabled:
  • CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID
  • CTX_OUTPUT.EVENT_OPT_PRINT_TOKEN
  • CTX_OUTPUT.EVENT_INDEX_PRINT_TOKEN
  • CTX_OUTPUT.EVENT_DRG_DUMP_ERRORSTACK (for all errors)

Query Logging

Logging is most commonly used for index creation and optimization. But you can also log Oracle Text queries as well. For this we use the key string CONTEXT_QUERY.  There is only one setting, low.

alter session set events 'TRACE[CONTEXT_QUERY] disk=low';
This Log query expressions only (similar to ctx_output.start_query_log).

Join the discussion

Comments ( 6 )
  • Zig Friday, February 8, 2019
    Hello,

    thank you for your help but I always have a problem. On 11g I did the following in order to log text indexing :

    EXECUTE ctx_output.start_log('file.log');
    EXECUTE ctx_output.add_event(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID);

    I understand that now the "file.log" does not exists anymore and the file is the one you find with the "select ctx_output.logfilename from dual;" command.

    But my problem is that nothing is logged in this file. Can you help me to understand how I can log the same thing as in 11g. (EVENT_INDEX_PRINT_ROWID) ?

    Thank you in advance for your help.

    zigune
  • Roger Ford Friday, February 8, 2019
    So "select ctx_output.logfilename from dual" returned a file name, and that filename exists, but is empty, even after creating an index in the same session? Is that correct?
  • Zig Friday, February 8, 2019
    Yes the file exists but no lines are appearing during the indexation. In 11g everything that was being indexed was visible in the log file...
  • Roger Ford Friday, February 8, 2019
    I suspect you failed to use the command:
    alter session set events 'TRACE[CONTEXT_INDEX] disk=medium';

    You need that as well as the ctx_output.start_log('...') command - the latter only sets up the name of the log file so you can fetch it with "select ctx_output.logfilename ...".
  • Zig Thursday, February 28, 2019
    Thank you for your answer... But it's not working... I even tried to set the "disk=high" events and nothing is appearing in the log file...
  • Roger Ford Thursday, February 28, 2019
    Please raise a ticket with Oracle Support, or email me rogerdotford at oracledotcom, and we'll try to help. We'll want to see a complete transcript of the session so we can see exactly what you're entering.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.