Oracle Tablespace Check needs over 10 Minutes

Troubleshooting
1

Hello all,
We have many instances on an Oracle RAC cluster with two nodes. The check “Check_MK” very often fails because it gets no results or the execution time is too long (CRIT - [agent] keepalive timed out CRIT, Got no information from host, execution time 300.0 sec).
I was able to limit the behavior to the oracle_tablespaces plugin. The SQL statement executed there takes more than 10 minutes on one instance.
Some of the databases have many tablespaces (15+) with several data files.
Is there any way to shorten the check for the tablespaces?
The check_mk version we use is 1.6.0p13.

Best regards

Mathias

2

Hi Mathias,
mostly this is relatet to performance issues inside Oracle:

  • disable recyclebin
    The SQLs on dba_free_space are extremly slow in some situations. Oracle released some bugfixes but the best solution ist disabling the reyclebin and purge all objects after then.
  • dictionary Statistics
    Refresh the Statistics inside the Data-Dictionary of Oracle. They are not automatically refreshed in older versions of Oracle.
    Execute: dbms_stats.gather_dictionary_stats

That solved most of all performance issues with the Tablespace-Check at our customers.

Kind Regards
Thorsten

1 Like
3

Hello Thorsten,

thank you for your answer.
I disabled the recyclebin, deleted all objects and recalculated the dictionary stats.
Unfortunately, the problem is still there.

Best regards

Mathias

PS: I will keep testing it

5

Are there BIGFILE Tablespaces in the Database?
Which version of Oracle is in use?
Did you install the latest Release Update/PSU ?

6

Hello Thorsten,
We don’t use a BIGFILE in the tablespaces.
We manage Oracle databases from version 11.2.0.4 to 19c with different patch levels.

Best regards

Mathias

7

Hello Thorsten,

the problem is solved. It was up to us to monitor the tablespace and the RMAN.

With a:
exec dbms_stats.gather_dictionary_stats ();
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();

and executing the following SQL statements on each node of the RAC:
alter system flush shared_pool;
alter system flush buffer_cache;

the execution times have been greatly reduced.

(see my other post: “Problems with rman-checks in Oracle 11.2.0.4.0 and 12.2.0.1”)

Bet regards
Mathias

8

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.