达梦超出全局hash join空间的问题处理

1 应用连接达梦数据库报超出hash_join的错误

2  修改HJ_BUF_GLOBAL_SIZE参数

alter SYSTEM set ‘HJ_BUF_GLOBAL_SIZE’=30000;

3  查看最占hash jion 空间的sql,对sql进行优化

SELECT TOP 50 TYPE$ AS 节点类型,EXEC_ID AS 执行ID,SQL_TEXT AS SQL语句,MERGE_USED AS 使用缓存 FROM V$HASH_MERGE_USED_HISTORY;

4 根据 v$runtime_err_history历史作业信息之后,还是有很多报hash join空间不足的错误

5  查看当前有很多活动会话,每个会话都会消耗hash join空间

 select state,

                'sp_close_session('

                ||sess_id

                ||');'                                      ,

                thrd_id                                     ,

                sess_id                                     ,

                trx_Id                                      ,

                datediff(ss, last_recv_time, sysdate) MsgTRs, --已执行时间 s

                to_char(sf_get_session_sql(sess_id)) "SQL"  , --完整sql

                curr_sch                                    ,

                user_name                                   ,

                clnt_host                                   ,

                clnt_ip                                     ,

                clnt_type                                   ,

                osname                                      ,

                left(last_send_time, 19)

        from

                v$sessions

      where state='ACTIVE'

        order by

                5 desc;

 6 将活动会话杀掉之后,没有出现该报错,原因是应用定时频繁调用这些统计类会话,一下子占用太多hash join缓存导致

 

posted @   fangzpa  阅读(1610)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示