Oracle 解决数据库连接暴增问题办法

创建v$session的镜像表Temp_Session，记录所有时刻连接情况

1、查询“某个程序/机器”连接数

-- 查询“某个程序/机器”连接数
Select s.sql_id,s.machine, s.createdate, count(*)
  From Temp_Session s
 where s.createdate > to_date('2022/1/4 10:42:00', 'yyyy/mm/dd hh24:mi:ss') and s.machine='NT AUTHORITY\SER89'
 group by s.sql_id,s.machine, s.createdate
HAVING count(*) > 1;

结果：

2、查询问题机器SQLID

-- 查询问题机器SQLID
select '''' || t.sql_id || '''' || ','
  from Temp_Session t
 where SQL_ID is not null
   and MACHINE = 'NT AUTHORITY\SER89'
 order by Createdate desc

结果：

3、查询具体SQL，判断问题点

--查询具体SQL，判断问题点
  select * from V$sql where sql_id in('ffbh19jfz8xv6',
'df4fbrc1y3ktf',
'frjgyg84k314h',
'5g4njqyb5dpba',
'bt44yj3pq6q0g',
'g4f9wujgyp08p',
'0f6kgbg0zy90u',
'4sr06baaxn0uh',
'2at4f8puk1f6c',
'cy3gny6kub25k',
'1w16d9sc13hpx',
'34nt97h1dxhgb',
'bgr081bdf7du7',
'dv0qzhp9fzvjd',
'7vmaz0uf75r77',
'dvwx5qk1a4jyc',
'6371nb3cxf67k',
'87vc9a6g7ry7c',
'45qj8ujbhyncs',
'atc7g0xc7ttw7',
'awt2ysqt5vtfb');

结果：