526互联

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

发布时间 2023-08-10 15:38:33作者: fangzpa

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缓存导致