After we upgraded to IFS10 we are struggling with slowness in our Oracle DB. We haven’t been able to pinpoint the cause of our problems. But very often (5-10 times each week), we see that the CPU goes to 100% and doesn’t let go. Every user in our company are then struggling with slowness when this happens. As an example, saving a time registration can take 3-4 minutes.
Today, a restart of the DB server was necessary to get it back to normal.
We have used the query below to find CPU consuming operations, and have also checked background jobs and DMBS jobs. Nothing special has been running when the problems occur, or at least nothing that helps us to conclude on a specific problem.
select s.username,
s.inst_id,
s.sid,
s.serial#,
s.status,
(select value from gv$sesstat natural join v$statname where name = 'physical reads' and sid=s.sid)disk_reads,
(select value from gv$sesstat natural join v$statname where name = 'session logical reads' and sid=s.sid) logical_reads,
(select value/100 cpu_sec from gv$sesstat natural join v$statname where name = 'CPU used by this session' and sid=s.sid)cpu_sec,
s.logon_time,
s.program,
s.osuser,
s.machine,
p.pid,
p.spid,
s.client_identifier,
s.client_info,
s.module,
s.action,
s.event,
s.wait_class,
case
when s.status = 'ACTIVE' then
s.last_call_et
else
null
end wait_sec,
s.blocking_session_status,
s.blocking_instance,
s.blocking_session,
s.sql_id,
s.sql_hash_value,
s.sql_address,
s.prev_sql_id,
s.prev_hash_value,
s.prev_sql_addr,
s.sql_child_number,
s.prev_child_number,
s.server
from gv$session s join gv$process p on (s.inst_id = p.inst_id and s.paddr = p.addr)
where s.type = 'USER' and s.status='ACTIVE'
order by disk_reads descWe have integrations and scheduled tasks running, all which have been running fine in IFS 8.1 for years.
After googling, we some people says that Oracle 12.2 is not as effective as 11, but we can’t verify this.
Does anyone have some tips to solve these things, or at least find the root cause when the problems occur? Appreciate all help we can get.
Some facts:
- Around 5-700 concurrent users
- Using Azure AD for authentication
- Using around major 10 Modules in IFS (DocMan, Finance, Engineering, SCM, etc)
- DB size around 600GB
- Running reports with Crystal Reports (set up via web, running queries towards IFS)