SQL Performance (MOSC)

MOSC Banner

DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS gathers different stats than DBMS_STATS.GATHER_TABLE

edited Nov 15, 2016 4:00AM in SQL Performance (MOSC) 5 commentsAnswered

We are on Oracle Database 12.1.0.2.0.  Query performance is severely degraded for a particular select statement after DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS gathers its nightly stats.

Here is the query:

   SELECT DISTINCT ur.role_id,

                   u.user_id,

                   ac.account_year,

                   ac.account_id,

                   'Y'

     FROM shr.accounts ac,

          adm.user_roles ur,

          adm.users u,

          shr.account_security_mask_mv asm

    WHERE     (ac.account_year = :b3 OR :b3 IS NULL)

          AND u.active_user = 'Y'

          AND ur.user_id = u.user_id

          AND ur.user_id = :b2

          AND ur.role_id = NVL ( :b1, ur.role_id)

          AND u.user_id = DECODE (asm.user_id, 'ALL', u.user_id, asm.user_id)

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