Only enable 10046 trace for a specific SQL statement

Only enable 10046 trace for a specific SQL statement

Recently, I encountered an interesting problem: there is an SQL statement that runs for dozens of milliseconds most of the time, but sometimes the execution time of a certain time is longer than 2 seconds. Because the application is very sensitive to the execution time of this statement, we must diagnose why it occasionally takes longer than 2 seconds.

Why is this question challenging? It is difficult for us to collect the 10046 trace at a slow time: First, we do not know when this problem will occur or in which session it will occur. If 10046 trace is enabled for all sessions throughout the day, many large traces will be generated and the overall performance of the database will be affected.

Fortunately, this database is 11 GB. In 11g, the event ++ feature allows us to collect only 10046 trace for a specific SQL statement. 10046 trace is enabled when this SQL statement is run, and 10046 trace is disabled after this SQL statement is run. this can significantly reduce the size of the generated trace. However, we cannot determine which session will cause problems, so any session that runs this SQL statement will generate a trace file.

To enable this function, replace awsh60c8mpfu1 with the SQL SQL _ID ):

Alter system set events 'SQL _ trace [SQL: awsh60c8mpfu1] level 12 ';

To close the SQL statement, replace awsh60c8mpfu1 with the SQL _ID of the SQL statement ):

Alter system set events 'SQL _ trace [SQL: awsh60c8mpfu1] off ';

After many 10046 traces are collected and formatted using tkprof (you must specify AGGREGATE = NO so that tkprof generates a summary report for each execution ), we finally found that it took more time for the SQL statement to read the physical block when the problem occurred.