Oracle Database is taking too much waiting time


Yesterday I have found our database was taking too much wait time. It is a single instance Oracle 12c with around 55gb of SGA memory and 12 CPU cores.

enter image description here

On Enterprise Manager, I have found 5 ADDM findings

enter image description here

The daily expdp job is scheduled to run every day at that time and a few SQL and PL/SQL statements were also running and most of them were Oracle's own of the sysman user queries, and most of them are taking more than 5 minutes. There were more than 300+ sessions with the database and most of them were using the Java environment but after a few hours eventually, the database was backed off to the normal state. I haven't understood some of the ADDM analysis. Is there anyone who could help me with that, so that in future I can prevent database from going into the waiting time?

enter image description here

Best Answer

Try running an AWR report for the time period that the excessive waits exist. The report will let you see the exact wait type and that will help understand the cause. Searching for the cause using the wait name in Oracle support website or even Google is usually helpful.