SQL Performance (MOSC)

MOSC Banner

Query on DBA_IND_STATISTICS is very slow

edited Jan 30, 2016 4:00AM in SQL Performance (MOSC) 3 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center