java - Oracle 12c 始终使用 100% CPU

Question

我在我的 Java EE 项目中使用 Oracle 12c 数据库。但是安装 Oracle 数据库后，每次在一定时间(大约 1 周)后开始使用 CPU 100%时，总是在安装后。我尝试使用Windows Server，之后又尝试Suse Linux与oracle 12c Databse一起操作，但结果相同。这是我当前使用 Oracle 12c 和 Suse Linux 的 CPU 详细信息..

这是我的 Java 连接详细信息 -

    String User = "X";
    String Pass = "123";
    String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
    Class.forName("oracle.jdbc.driver.OracleDriver");
    conn = DriverManager.getConnection(Url, User, Pass);
    return conn;

我的数据访问文件进程从我每次调用数据库的地方 -

String status="";
Connection conns= DB_Connection.getConnection();

try{
    ps = conn.prepareStatement("SELECT * FROM TABLE");
    rs = ps.executeQuery();
    if(rs.next()){
        status=rs.getInt("ID");
    }
} catch(Exception e){
    out.print("ERROR"+e);
} finally {
    try { rs.close(); } catch (Exception e) {}
    try { ps.close(); } catch (Exception e) {}
    try { conns.close(); } catch (Exception e) {}
}

那么您能否建议问题出在哪里或者我可以采取什么措施来解决这个问题？

Answer 1

您可以使用以下 SQL 找到 CPU 消耗最高的 session 以及它们正在运行的 SQL:

with sqlarea as (select inst_id , sql_text, hash_value
, CASE WHEN elapsed_time > 2*86399*1000000
THEN '2 ' || to_char(to_date(round((elapsed_time-(2*86399*1000000))/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
WHEN elapsed_time > 86399*1000000
THEN '1 ' || to_char(to_date(round((elapsed_time-(86399*1000000))/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
WHEN elapsed_time <= 86399*1000000
THEN to_char(to_date(round(elapsed_time/decode(executions, 0, 1, executions)/1000000) ,'SSSSS'), 'HH24:MI:SS') 
END
as time_per_execution
from gv$sqlarea a where 1=1 )
, scan as (select max('FULL SCAN') scan, sp.hash_value
from gv$sql_plan sp 
where (sp.operation like '%TABLE%' or sp.operation like '%MAT_VIEW%')
      and sp.options in ('FULL', 'ALL')
group by sp.hash_value
) 
, sess as
(
SELECT sess.sid, sess.serial#, sess.inst_id
     , sess.username
     , OSUSER
     , stat.value sess_CPU
     , sess.status
     , sess.sql_hash_value
     , sess.sql_id
     , sess.machine 
     , sess.logon_time
     , sess.terminal, sess.program, sess.last_call_et
FROM gv$sesstat stat, v$statname name, gv$session sess
WHERE stat.statistic# = name.statistic#
AND sess.sid = stat.sid
AND username is not null
AND sess.inst_id = stat.inst_id
AND name.name='CPU used by this session'
ORDER BY stat.value desc
)
SELECT sess.sid, sess.serial#, sess.inst_id
     , sess.username
     , OSUSER
     , sess.status
     , sess.sql_hash_value
     , sess.sql_id
     , sess.sess_CPU
, time_per_execution time_per_exec
, (select max(scan) from scan where scan.hash_value = sess.sql_hash_value) scan
, sql_text
     , sess.machine MACHINE_NAME
     , TO_CHAR(sess.logon_time,'DD-MON HH24:MI:SS') LOGON_TIME
     , sess.terminal, sess.program, sess.last_call_et
FROM sess, sqlarea
WHERE sess.sql_hash_value = sqlarea.hash_value and sess.inst_id = sqlarea.inst_id 
order by sess.sess_CPU desc
;

此外，根据您获取连接的方式，登录 Storm **可能会导致 CPU 消耗过高。

**Logon Storm: A logon storm is characterized by a sudden spike in the number of client connection requests or an unusually high number of connections. Since the process of starting a database session is CPU intensive, a logon storm will likely result in unexpected consequences such as: connection errors, applications waiting on CPU, timeouts, poor response time, impacts to other DB’s on same machine, elevating wait times for everything running on the instance, … . The root causes of the logon storms are often traced back to improper connection pool settings or applications connecting, using a session, then disconnecting.