Check if the logs of the local agent on the TSIM/BPPM Server are giving the following type errors: INFO 04/27 09:14:42 MonitorFW [SerialPollEngine-Worker#3-17] 600002 Attempted 1 retries. 0 retries remain
As well as:
ERROR 04/27 09:15:17 OracleMon [TimedRetryExecutor-Worker#124] 2500100 [17] java.lang.NullPointerException ERROR 04/27 09:15:17 OracleMon [TimedRetryExecutor-Worker#110] 2500100 Could not execute SQL.....Reason: Closed Connection: next ,SQLState: 08003 ,Vendorcode: 17008 ,SQL: SELECT SUM ( BYTES ) FROM dba_free_space ERROR 04/27 09:15:17 OracleMon [TimedRetryExecutor-Worker#110] 2500100 Closed Connection: next ERROR 04/27 09:15:17 OracleMon [TimedRetryExecutor-Worker#110] 2500100 [17] java.lang.NullPointerException ERROR 04/27 09:21:42 OracleMon [SerialPollEngine-Worker#3-17] 2500100 Poll Failed for instance id = 17
ERROR 04/27 09:21:42 OracleMon [SerialPollEngine-Worker#3-17] 2500100 [17]Retries-- before giving up = 1 ERROR 04/27 09:21:42 OracleMon [SerialPollEngine-Worker#3-17] 2500100 Oracle DB Server not responding at the IP/Port/Protocol specified or Connect to DB Timedout ERROR 04/27 09:21:42 OracleMon [SerialPollEngine-Worker#3-17] 2500100 [17]DB Connection timed out disconnecting with the database to free up the connection Also have a look at the AWR report from Oracle server: Look for the Topic: "SQL ordered by Elapsed Time". Under this Topic, check for the Column "Elapsed Time per Exec (s)" and look for the query taking highest time. In the problematic scenario, you can see the query taking around 110-180 seconds to execute. Clicking on "SQL Id", will give the exact query taking long time. In this scenario it would be "SELECT SUM ( BYTES ) FROM dba_free_space" |
Please check if this feature is enabled:
show parameter recyclebin
If so then, check how many rows are in the recyclebin:
select COUNT(*) FROM recyclebin;
Then verify who is contributing the most to the recyclebin size with:
SELECT owner,COUNT(*) FROM dba_recyclebin GROUP BY owner;
On Oracle 10.x, if recyclebin has thousands of rows (on Oracle 11.x/12.x it only needs a few rows) it can massively affect the dba_free_space query and therefore have a knock on performance across the rest of the database. The recyclebin can be cleared using the following SQL: purge recyclebin;
Or, as SYSDBA for system wide purging.
purge dba_recyclebin;
Then see how the database and BPPM/TSIM performance is subsequently.Notes, for more details search the web for dba_free_space and recyclebin there will be plenty of pages about this, Oracle Metalink Article Doc ID 271169.1 'Queries on DBA_FREE_SPACE are Slow ' is relevant in this instance. Note, if this is the cause then it is entirely unrelated to BPPM/TSIM as a product but the configuration of Oracle. |