Example 1: Tracing a multistatement procedure

    SQL> ATTACH 'FILENAME MF_PERSONNEL';
    SQL> SET FLAGS 'TRACE';
    SQL> DECLARE :i INTEGER;
    SQL> BEGIN
    cont>    WHILE :i <= 10
    cont>     LOOP
    cont>         TRACE ':i is', :i;
    cont>         SET :i = :i +1;
    cont>     END LOOP;
    cont> END;
    ~Xt: :i is 0
    ~Xt: :i is 1
    ~Xt: :i is 2
    ~Xt: :i is 3
    ~Xt: :i is 4
    ~Xt: :i is 5
    ~Xt: :i is 6
    ~Xt: :i is 7
    ~Xt: :i is 8
    ~Xt: :i is 9
    ~Xt: :i is 10

    Example 2: Generating a query outline when the TRACE statement is
    disabled

    SQL> DECLARE :LN CHAR(40);
    SQL> SET FLAGS 'NOTRACE';
    SQL> BEGIN
    cont> TRACE 'Jobs Held: ',
    cont>     (SELECT COUNT(*)
    cont>         FROM JOB_HISTORY
    cont>         WHERE EMPLOYEE_ID = '00201');
    cont> SELECT LAST_NAME
    cont>     INTO :LN
    cont>     FROM EMPLOYEES
    cont>     WHERE EMPLOYEE_ID = '00201';
    cont> END;
    -- Oracle Rdb Generated Outline :
    create outline QO_A17FA4B41EF1A68B_00000000
    id 'A17FA4B41EF1A68B966C1A0B083BFDD4'
    mode 0
    as (
      query (
    -- Select
        subquery (
          EMPLOYEES 0     access path index       EMPLOYEES_HASH
          )
        )
      )
    compliance optional     ;
    SQL>

    If the query outline is generated with TRACE enabled, then
    two queries appear; the first is for the subquery in the TRACE
    statement and the other is for the singleton SELECT statement.

    If this second query outline is used at run time with the TRACE
    statement disabled, then it cannot be applied to the query.
    Because the outline was created with compliance optional, the
    query outline is abandoned and a new strategy is calculated. If
    compliance is mandatory, then the query fails. See Example 3.

    SQL> DECLARE :LN CHAR(40);
    SQL> SET FLAGS 'TRACE';
    SQL> BEGIN
    cont> TRACE 'Jobs Held: ',
    cont>     (SELECT COUNT(*)
    cont>         FROM JOB_HISTORY
    cont>         WHERE EMPLOYEE_ID = '00201');
    cont> SELECT LAST_NAME
    cont>     INTO :LN
    cont>     FROM EMPLOYEES
    cont>     WHERE EMPLOYEE_ID = '00201';
    cont> END;
    -- Oracle Rdb Generated Outline :
    create outline QO_A17FA4B41EF1A68B_00000000
    id 'A17FA4B41EF1A68B966C1A0B083BFDD4'
    mode 0
    as (
      query (
    -- Trace
        subquery (
          JOB_HISTORY 0   access path index       JOB_HISTORY_HASH
          )
        )
      query (
    -- Select
        subquery (
          EMPLOYEES 0     access path index       EMPLOYEES_HASH
          )
        )
      )
    compliance optional     ;
    ~Xt: Jobs Held: 4
    SQL>

    Example 3: Using an Outline with Tracing Enabled That Was Created
    with Tracing Disabled

    This example shows that enabling the TRACE statement may affect
    query outlines defined when TRACE was disabled.

    SQL> DECLARE :LN CHAR(40);
    SQL>
    SQL> BEGIN
    cont> TRACE 'Jobs Held: ',
    cont>     (SELECT COUNT(*)
    cont>         FROM JOB_HISTORY
    cont>         WHERE EMPLOYEE_ID = '00201');
    cont> SELECT LAST_NAME
    cont>     INTO :LN
    cont>     FROM EMPLOYEES
    cont>     WHERE EMPLOYEE_ID = '00201';
    cont> END;
    ~S: Outline QO_A17FA4B41EF1A68B_00000000 used
    ~S: Outline/query mismatch; assuming JOB_HISTORY 0 renamed to EMPLOYEES 0
    ~S: Full compliance with the outline was not possible
    Get     Retrieval by index of relation EMPLOYEES
      Index name  EMPLOYEES_HASH [1:1]       Direct lookup