EVENT 10046 at System Level with Levels (4, 8 or 12) always Generated at Level

EVENT 10046 at System Level with Levels (4, 8 or 12) always Generated at Level 1

SYMPTOMS

When attempting to set the 10046 event at system level with level more than 1 (4, 8, or 12), always generated at level one.

Where 10046 EVENT levels:
1 -  Enable standard SQL_TRACE functionality (Default)
4 -  As Level 1 PLUS trace bind values
8 -  As Level 1 PLUS trace waits
12- As Level 1 PLUS both trace bind values and waits

CHANGES

The SQL_TRACE parameter is set to true.

CAUSE

The SQL_TRACE is 10046 EVENT but with level 1, with this level of tracing the WAIT statements/BIND statements are not generated.
But the question will be why when we set the 10046 EVENT with levels more than 1 (4, 8 or 12) the WAIT statements/BIND statements are not generated.

The reason for this is that when you set the event you used the following statements:

alter system set sql_trace = true scope=spfile;
alter system set event = '10046 trace name context forever, level 8' scope=spfile;

So the SQL_TRACE(10046 level 1) overwrite the set EVENT statement (i.e. : 10046 level 8), so the trace
file will be generated with level 1(no WAIT statements)

SOLUTION

1- Set the SQL_TRACE to false when setting EVENT 10046 with level higher than 1:
alter system set sql_trace = false scope=spfile;
alter system set event = '10046 trace name context forever, level 8' scope=spfile;

REFERENCES

NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

=============================================================