Oracle 参数 SQL_TRACE 官方解释，作用，如何配置最优化建议
本站中文解释
SQL_TRACE：SQL跟踪，是指可以收集SQL语句的执行信息并将每次执行的sql语句和其关联的变量写入到一个特殊的文件中，用于查询SQL语句的执行状态和性能调优的一种功能。
设置SQL_TRACE的正确方式：
1. 设置数据库参数：首先需要在sqlplus中使用alter system命令，设置数据库层面的sql_trace参数：
Alter system set sql_trace=true
2. 设置会话参数：这一步需要在sqlplus中使用alter session命令，设置会话级别的sql_trace参数：
Alter session set sql_trace=true
3. 使用TKPROF：完成前两步设置之后，可以使用TKPROF工具分析输出结果，获取跟踪文件。该工具是Oracle专有的。
官方英文解释
SQL_TRACE enables or disables the SQL trace facility.
| Property | Description |
|---|---|
|
Parameter type |
Boolean |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
Basic |
No |
Setting this parameter to true provides information on tuning that you can use to improve performance.
Note:
Using this initialization parameter to enable the SQL trace facility for the entire instance can have a severe performance impact. Enable the facility for specific sessions using the ALTER SESSION statement. If you must enable the facility on an entire production environment, then you can minimize performance impact by:
-
Maintaining at least 25% idle CPU capacity
-
Maintaining adequate disk space for the
USER_DUMP_DESTlocation -
Striping disk space over sufficient disks
Note:
The SQL_TRACE parameter is deprecated. Oracle recommends that you use the DBMS_MONITOR and DBMS_SESSION packages instead. SQL_TRACE is retained for backward compatibility only.
See Also:
-
“USER_DUMP_DEST”
-
Oracle Database PL/SQL
Packages and Types Reference for more information about theDBMS_MONITORpackage -
Oracle Database PL/SQL
Packages and Types Reference for more information about theDBMS_SESSIONpackage -
Oracle Database
Performance Tuning Guide for more information about performance diagnostic tools
