sql_trace导致数据库提示错误。

最新推荐文章于 2025-02-22 15:36:57 发布

原创最新推荐文章于 2025-02-22 15:36:57 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE 之 error 诊断专栏收录该内容

35 篇文章

订阅专栏

本文介绍了如何通过启用 SQL Trace 功能收集 Oracle 数据库的性能数据，并提供了在生产系统中使用 SQL Performance Analyzer 的步骤。同时，文章详细说明了在遇到 SQL Trace 导致的数据库错误时的解决办法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql_trace导致数据库提示错误。

Oracle9i uses the SQL Trace facility to collect performance data on individual SQL statements. The information generated by SQL Trace is stored in SQL trace files. SQL Performance Analyzer consumes the following information from these files:

SQL text and username under which parse occurred
Bind values for each execution
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Execution plan for each SQL statement (only captured if the cursor for the SQL statement is closed)

Although it is possible to enable SQL Trace for an instance, it is recommended that you enable SQL Trace for a subset of sessions instead. When the SQL Trace facility is enabled for an instance, performance statistics for all SQL statements executed in the instance are stored into SQL trace files. Using SQL Trace in this way can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space. It is required that trace level be set to 4 to capture bind values, along with the execution plans.

For production systems running Oracle Database 10g Release 1, use the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to enable SQL Trace transparently in another session. You should also enable binds explicitly by setting the binds procedure parameter to TRUE (its default value is FALSE).

After enabling SQL Trace, identify the SQL trace files containing statistics for a representative set of SQL statements that you want to use with SQL Performance Analyzer. You can then copy the SQL trace files to the SQL Performance Analyzer system. Once the SQL workload is captured in the SQL trace files, disable SQL Trace on the production system.

eg：

SYS@orcl#startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area  805875712 bytes
Fixed Size                  2148720 bytes
Variable Size             603981456 bytes
Database Buffers          192937984 bytes
Redo Buffers                6807552 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#show parameter sql_trace

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sql_trace                            boolean
FALSE
SYS@orcl#

查看日志：

  diagnostic_dest          = "/opt/oracle"
Deprecated system parameters with specified values:
  sql_trace
End of deprecated system parameter listing
Tue Apr 16 22:16:05 2013
PMON started with pid=2, OS id=17577
Tue Apr 16 22:16:05 2013
VKTM started with pid=3, OS id=17579 at elevated priority
VKTM running at (20)ms precision
Tue Apr 16 22:16:05 2013
DIAG started with pid=4, OS id=17583
Tue Apr 16 22:16:05 2013
DBRM started with pid=5, OS id=17585
Tue Apr 16 22:16:05 2013
PSP0 started with pid=6, OS id=17587
Tue Apr 16 22:16:05 2013
DSKM started with pid=7, OS id=17589
Tue Apr 16 22:16:05 2013
DIA0 started with pid=8, OS id=17591
Tue Apr 16 22:16:05 2013
MMAN started with pid=7, OS id=17593
Tue Apr 16 22:16:05 2013
LGWR started with pid=10, OS id=17597
Tue Apr 16 22:16:06 2013
CKPT started with pid=11, OS id=17599
Tue Apr 16 22:16:06 2013

解决办法：

SYS@orcl#alter system reset sql_trace;

系统已更改。

SYS@orcl#shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@orcl#startup
ORACLE 例程已经启动。

Total System Global Area  805875712 bytes
Fixed Size                  2148720 bytes
Variable Size             603981456 bytes
Database Buffers          192937984 bytes
Redo Buffers                6807552 bytes
数据库装载完毕。
数据库已经打开。
SYS@orcl#