TThe SQL trace facility writes a trace file containing performance statistics for the SQL statements being executed. These include:w Number of parses, executes, and fetches performed w Various types of CPU and elapsed times w Number of physical and logical reads performed w Number of rows processed w Number of library cache missesThis trace file provides valuable information that you can use to tune your system. The performance impact of globally enabling SQL_TRACE is quite insignificant. Your overall response times may degrade by 5%.The exact form of the trace file name written by SQL_TRACE is system dependent, but usually it is in the form filename.TRC. You can read this file directly, but it is best to run the TKPROF utility against it to produce a summary of the most useful tuning information.To globally enable SQL_TRACE, you must set a number of parameters. These may be session or system modified:Parameter Setting Description SQL_TRACE TRUE Enables the trace for all application users. A setting of FALSE disables the trace. FALSE is the default.USER_DUMP_DEST directory The directory where SQL_TRACE writes the trace file. The default is system dependent, but generally is the directory that holds your system dumps (e.g., $ORACLE_HOME/rdbms/log).TIMED_STATISTICS TRUE Causes the RDBMS to collect additional timing statistics which are useful to SQL_TRACE. The default is FALSE.MAX_DUMP_FILE_SIZE number Limits the physical size of the trace file to the specified number of operating system blocks (or UNLIMITED). If you enable the SQL_TRACE parameter for the entire database, this option helps control the amount of disk space used. To find out what size to specify, find out the number of operating system blocks available in your system. If SQL_TRACE runs out of space, it will truncate your output; you'll have to allocate more space and start again._TRACE_FILES_PUBLIC Changes the protection of the trace files so everyone can read them.The way you invoke SQL_TRACE for individual Oracle tools and user sessions depends on the Oracle program you are running:For: Do this: SQL*Forms RUNFORM formname usercode/password -S (version 3 and later)SQL*Plus ALTER SESSION SET SQL_TRACE TRUEPL/SQL program Generate a trace file for the execution of a specific PL/SQL procedure call: BEGIN DBMS_SESSION.SET_SQL_TRACE(TRUE); plsql_procedure_call; DBMS_SESSION.SET_SQL_TRACE(FALSE); END;SQL*Reportwriter Create a field called SQL_TRACE and a group report with an attribute of CHAR(40). Specify the following statement against the column: &SQL ALTER SESSION SET SQL_TRACE TRUEPro*tools EXEC SQL ALTER SESSION SET SQL_TRACE TRUE
一般还需要设置timed_statistics对dump的跟踪文件上sql性能跟踪
TThe SQL trace facility writes a trace file containing performance statistics for the SQL statements being executed. These include:w Number of parses, executes, and fetches performed
w Various types of CPU and elapsed times
w Number of physical and logical reads performed
w Number of rows processed
w Number of library cache missesThis trace file provides valuable information that you can use to tune your system. The performance impact of globally enabling SQL_TRACE is quite insignificant. Your overall response times may degrade by 5%.The exact form of the trace file name written by SQL_TRACE is system dependent, but usually it is in the form filename.TRC. You can read this file directly, but it is best to run the TKPROF utility against it to produce a summary of the most useful tuning information.To globally enable SQL_TRACE, you must set a number of parameters. These may be session or system modified:Parameter Setting Description
SQL_TRACE TRUE Enables the trace for all application users. A setting of FALSE disables the trace. FALSE is the default.USER_DUMP_DEST directory The directory where SQL_TRACE writes the trace file. The default is system dependent, but generally is the directory that holds your system dumps (e.g., $ORACLE_HOME/rdbms/log).TIMED_STATISTICS TRUE Causes the RDBMS to collect additional timing statistics which are useful to SQL_TRACE. The default is FALSE.MAX_DUMP_FILE_SIZE number Limits the physical size of the trace file to the specified number of operating system blocks (or UNLIMITED). If you enable the SQL_TRACE parameter for the entire database, this option helps control the amount of disk space used. To find out what size to specify, find out the number of operating system blocks available in your system. If SQL_TRACE runs out of space, it will truncate your output; you'll have to allocate more space and start again._TRACE_FILES_PUBLIC Changes the protection of the trace files so everyone can read them.The way you invoke SQL_TRACE for individual Oracle tools and user sessions depends on the Oracle program you are running:For: Do this:
SQL*Forms RUNFORM formname usercode/password -S (version 3 and later)SQL*Plus ALTER SESSION SET SQL_TRACE TRUEPL/SQL program Generate a trace file for the execution of a specific PL/SQL procedure call: BEGIN
DBMS_SESSION.SET_SQL_TRACE(TRUE);
plsql_procedure_call;
DBMS_SESSION.SET_SQL_TRACE(FALSE);
END;SQL*Reportwriter Create a field called SQL_TRACE and a group report with an attribute of CHAR(40). Specify the following statement against the column: &SQL ALTER SESSION SET SQL_TRACE TRUEPro*tools EXEC SQL ALTER SESSION SET SQL_TRACE TRUE