ALTER SESSION SET SQL_TRACE TRUE'的若干问题

[esunny]

'ALTER SESSION SET SQL_TRACE TRUE'中SQL_TRACE有何含义，为何要使用它，对系统的性能又有什么影响?具体表现

[jlandzpa]

To enable the SQL trace facility for your current session.

[hanps]

kyi可以说明一下具体用法吗？谢谢

[zhoubf725]

会话级设置sql的跟踪。
一般还需要设置timed_statistics

对dump的跟踪文件上sql性能跟踪

[LoveGirl]

对性能影响不大

这个语句多半是优化用的，看看优化方面的书，我也不董。

[black_snail]

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 misses

This 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 TRUE

PL/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 TRUE

Pro*tools EXEC SQL ALTER SESSION SET SQL_TRACE TRUE