DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS gathers different stats than DBMS_STATS.GATHER_TABLE
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)