签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c CLIENT_STATISTICS_LEVEL 特性说明(建议关闭)

2024-01-12 17:31 242 0 原创 Oracle 19c
作者: dave

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.http://www.cndba.cn/dave/article/131494

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.http://www.cndba.cn/dave/article/131494

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

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/131494http://www.cndba.cn/dave/article/131494

官方建议关闭该特性:

http://www.cndba.cn/dave/article/131494
http://www.cndba.cn/dave/article/131494http://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参数,但是修改的时发现改不了,所以还是按静态参数来操作,修改后重启实例生效。

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

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

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2237
    原创
  • 2
    翻译
  • 535
    转载
  • 185
    评论
  • 访问:6531633次
  • 积分:4229
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ