Query on DBA_IND_STATISTICS is very slow
We are running Oracle Linux 5, database 11.2.0.4. When I run a query against either DBA_IND_STATISTICS or DBA_TAB_STATISTICS it can take several minutes to return.
SQL> SELECT OUNT(*)
2 FROM DBA_TAB_STATISTICS;
COUNT(*)
------------------
941958
1 row selected.
Elapsed: 00:00:20.39
SQL> EDIT
Wrote file afiedt.buf
SQL> SELECT COUNT(*)
2* FROM DBA_IND_STATISTICS;
COUNT(*)
------------------
7789357
1 row selected.
Elapsed: 00:00:49.03
SQL> SELECT COUNT(*) "STALE", OWNER
2 FROM DBA_IND_STATISTICS
3 WHERE STALE_STATS = 'YES'
4 AND STATTYPE_LOCKED IS NULL
5 GROUP BY OWNER;
STALE OWNER
---------- ------------------------------
33 EMIS_VRF
15177 EMIS_POST
47 DW
2 ILRC2015
20 EMIS_VALID
16 ILRC_MSTR
11 EDEN_ADMIN
16 TREND_ADMIN
15 MSTR_ENT_MGRINT
1 DBSNMP