The Oracle agent DBO Datafiles collection times out and does not complete. When run manually directly against the database it takes more than 20 minutes at which point it doesn't complete do the user kills the SQL query.
ERROR [AGENTNAME-highPriorityPool-1024-[DBO_TablespacesStorage][]] com.quest.qsi.fason.oracle.collection.processor.DBOInstanceProcessor - Failed to run DBODataFileProcessor processor of DBO_TablespacesStorage collection of AGENTNAME instance.
Caused by: java.lang.RuntimeException: ORA-01013: user requested cancel of current operation
Caused by: com.quest.qsi.fason.framework.connections.common.exceptions.FrameworkQueryException: ORA-01013: user requested cancel of current operation
Caused by: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
Caused by: Error : 1013, Position : 0, Sql = SELECT datafile_name,
Optimizer OPTIMIZER_ADAPTIVE_FEATURES causing the performance issues on internal SQLs
In Oracle Database versions 12.2 and later, the OPTIMIZER_ADAPTIVE_PLANS parameter increases system overhead due to it choosing a better plan on the fly during each query execution, which sometimes causes a performance issue.
As per Oracle metalink Support article 2966854.1
Workaround
Oracle Database 12.1 and earlier:
alter system set OPTIMIZER_ADAPTIVE_FEATURES=FALSE SCOPE=BOTH;
Oracle Database 12.2 and later:
alter system set OPTIMIZER_ADAPTIVE_PLANS=FALSE SCOPE=BOTH;
