1 CLIENT_STATISTICS_LEVEL 参数说明

Oracle 在 19c 中引入了新参数：client_statistics_level， 官网对该参数的说明如下：http://www.cndba.cn/dave/article/131494

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/CLIENT_STATISTICS_LEVEL.html

http://www.cndba.cn/dave/article/131494

CLIENT_STATISTICS_LEVEL controls whether database clients report network statistics to the database. This parameter applies only to clients that use Oracle Call Interface (OCI) to connect to the database.

Database clients sometimes have performance issues when interacting with the database over a network (LAN or WAN). You can use this parameter to enable clients to collect network statistics and periodically send them to the database, where the statistics are displayed in AWR and V$ views. These statistics include TCP and SQL*Net statistics. They complement existing database statistics and can be used to analyze, troubleshoot, and tune potential network performance or configuration issues.

该参数默认是TYPICAL，客户端能够收集网络统计信息并将其定期发送到数据库，在该数据库中统计信息显示在AWR和V$视图中。这些统计信息包括TCP和SQL * Net统计信息，可用于分析，故障排除和调整潜在的网络性能或配置问题。http://www.cndba.cn/dave/article/131494http://www.cndba.cn/dave/article/131494

SQL> set pages 200;
SQL> SELECT name FROM V$STATNAME WHERE name LIKE 'Client%' ORDER BY name;

NAME
--------------------------------------------------------------------------------
Client Advertised Receive Window
Client Advertised Send Window
Client Data Segments In
Client Data Segments Out
Client Lost Packets
Client Path Maximum Transmission Unit(MTU)
Client Send Congestion Window
Client Time (usec) Busy Sending Data
Client Time (usec) Busy Sending Data under Congestion
Client Time (usec) Last Ack Received
Client Time (usec) Last Ack Sent
Client Time (usec) Last Data Received
Client Time (usec) Last Data Sent
Client Time (usec) Limited by Receive Window
Client Time (usec) Limited by Send Buffer
Client Time (usec) Round Trip Time
Client Time (usec) Round Trip Time Variance
Client Total Bytes Acked
Client Total Bytes Received
Client Total Number of Retransmitted Packets

20 rows selected.

2 关闭该特性

该特性的出发点是好的，但是目前看并不太稳定，可能会导致 JDBC 连接缓慢、SQLPLUS 登录和退出缓慢的问题。 http://www.cndba.cn/dave/article/131494

JDBC 19c is Slow When Establishing Database Connections (Doc ID 2882288.1)
SQL*PLUS 19.0 Waits 1 Second to Exit from Database 19.0 (Doc ID 2673358.1)

http://www.cndba.cn/dave/article/131494

官方建议关闭该特性：http://www.cndba.cn/dave/article/131494

SQL>  show parameter 'CLIENT_STATISTICS_LEVEL';

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
client_statistics_level              string                            TYPICAL
SQL> 

SQL> alter system set client_statistics_level=OFF scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2634021864 bytes
Fixed Size                  9173992 bytes
Variable Size             973078528 bytes
Database Buffers         1627389952 bytes
Redo Buffers               24379392 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show parameter 'CLIENT_STATISTICS_LEVEL';

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
client_statistics_level              string
OFF
SQL>

注意：

官方文档说这个参数是DEFERRED参数，但是修改的时发现改不了，所以还是按静态参数来操作，修改后重启实例生效。 http://www.cndba.cn/dave/article/131494

SQL> alter system set client_statistics_level=OFF scope=both;
 alter system set client_statistics_level=OFF scope=both
                                              *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

版权声明：本文为博主原创文章，未经博主允许不得转载。