Oracle 12c Using 100% CPU Alltime

Question

I am using Oracle 12c Database with my Java EE project. But after installing Oracle Database every time it start using CPU 100% after a certain time(around 1 week) always after setup. I tried using Windows Server and after also I tried Suse Linux as a operating with oracle 12c Databse , but same result. Here is my Currently using CPU Details for Oracle 12c and Suse Linux ..

Here is my Java Connection details -

    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;

And my Data Access File process from where i calling database every time -

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) {}
}

so can you please suggest where the problem is or what I can do to resolve this issue?

Answer 1

You can find the Top CPU consuming Sessions and what SQL they are running with the following 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
;

Also, depending on how you are getting your connections, logon storms** can be a source of high CPU consumption.

**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.