2016-05-04

Capture an Optimizer trace for an already existing SQL statement

Стырено отсюда

If you don’t control the SQL execution then you can still create an Optimizer trace file if you know the SQL_ID of the SQL statement. The SQL_ID can then be added to the DBMS_SQLDIAG.DUMP_TRACE
command (added to DBMS_SQLDIAG in Oracle Database 11g R2). To create an Optimizer trace for any SQL statement that has been run and is in the shared pool.
Note that this procedure will automatically trigger a hard parse of the statement.
The following will show an example for SQL_ID 1n482vfrxw014

begin
DBMS_SQLDIAG.DUMP_TRACE(
p_sql_id=>'1n482vfrxw014',
p_child_number=>0,
p_component=>'Compiler',
p_file_id=>'MY_SPECIFIC_STMT_TRC');
end;
/

After running the procedure above you can find the trace file in the USER_DUMP_DEST directory.
To make it easier to find the trace file you should set the P_FILE_ID parameter to a string that starts with an alphabetic character and does not contain any leading or trailing white space

Комментариев нет: