dbms_stats.gather_table_stats

[Laurel]

Hi Dear Erman!
How are you ? Hope all is fine and spring is coming:)

We upgraded to 19.25 EBS 12.2.14
We gather stats using traditional away /by concurrent request for All schema
After 19c , noticed that dbms_stats.gather_table_stats spawning  too much, kicking in
So i disabled on CDB and PDB level auto stats gathering job (19c) .
But still table level stats occure too much.
Any advice ?
Thx alot!
Br,Laurel
===========
SQL> SELECT CLIENT_NAME, STATUS
 FROM DBA_AUTOTASK_CLIENT
 WHERE CLIENT_NAME='auto optimizer stats collection';  2    3

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  DISABLED

SQL> COL owner        FORMAT a25
 COL program_name FORMAT a25
 SELECT owner, program_name, enabled FROM dba_scheduler_programs;SQL> SQL>

OWNER                     PROGRAM_NAME              ENABL
------------------------- ------------------------- -----
SYS                       PURGE_LOG_PROG            TRUE
SYS                       GATHER_STATS_PROG         TRUE
SYS                       AUTO_SPACE_ADVISOR_PROG   TRUE
SYS                       ORA$AGE_AUTOTASK_DATA     TRUE
SYS                       HS_PARALLEL_SAMPLING      TRUE
SYS                       AQ$_PROPAGATION_PROGRAM   TRUE
SYS                       BSLN_MAINTAIN_STATS_PROG  TRUE
SYS                       PMO_DEFERRED_GIDX_MAINT   TRUE
SYS                       ORA$PREPLUGIN_BACKUP_PRG  TRUE
SYS                       AUTO_SQL_TUNING_PROG      TRUE
SYS                       FILE_WATCHER_PROGRAM      TRUE

11 rows selected.

SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>  EXEC DBMS_SCHEDULER.disable (name => 'GATHER_STATS_PROG');
BEGIN DBMS_SCHEDULER.disable (name => 'GATHER_STATS_PROG'); END;

*
ERROR at line 1:
ORA-27479: Cannot disable "SYS"."GATHER_STATS_PROG" because other objects depend on it
ORA-06512: at "SYS.DBMS_ISCHED", line 3229
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2966
ORA-06512: at line 1


SQL> show parameter autostats

NAME_COL_PLUS_SHOW_PARAM                                                         TYPE
-------------------------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_optimizer_autostats_job                                                         boolean
FALSE

SQL> COL CLIENT_NAME FORMAT a25
SQL> COL WINDOW_NAME FORMAT a15
COL WINDOW_START_TIME FORMAT a15
SQL> SQL> COL WINDOW_DURATION FORMAT a15
COL JOB_NAME FORMAT a15
COL JOB_STATUS FORMAT a10
COL JOB_START_TIME FORMAT a15
COL JOB_DURATION FORMAT a15
COL JOB_ERROR FORMAT 999999999
COL JOB_INFO FORMAT a15
SELECT
    CLIENT_NAME, WINDOW_NAME, WINDOW_START_TIME, WINDOW_DURATION,
    JOB_NAME, JOB_STATUS, JOB_START_TIME, JOB_DURATION,
    JOB_ERROR, JOB_INFO
FROM
    DBA_AUTOTASK_JOB_HISTORY
WHERE
    CLIENT_NAME = 'auto optimizer stats collection'
    AND JOB_START_TIME >= SYSDATE - 1
ORDER BY
    JOB_START_TIME DESC;

no rows selected

[ErmanArslansOracleBlog]

Hi Laurel,

Yes I hope everything will be fine:)

There are some dependencies related to that GATHER_STATS_PROG.
ORA-27479 is a proof of that.

So,

Problem:
An attempt was made to drop or disable a scheduler object that has jobs associated with it without specifying the force option.

Action:
Alter the associated jobs so they do not point to the scheduler object being dropped or disabled and then reissue the command.


*you queried by the program name, check the associated job name. You may need to disable the associated job before that. That may be the GATHER_STATS_JOB.

In any case, you should find the dependent object, evaluate it, and then if it is ok you should disable it and then disable that failing scheduler prog.

[Laurel]

Hi Erman!
That sys gather_stats_job was disabled, that was using GATHER_STATS_PROG.   So its not the cause of frequent dbms_stats.gather_table_stats

I checked  in EBS that dont have any debug profiles enabled.
Still comparing to 12.2.4 12 c and 12.2.14 19c alot of increase of dbms_stats.gather_table_stats and those takes alot of IO as well.

So have you seeing such behavior  in 19c ? Its not exadata and i have disabled all autostats. So is there any  19c feature that kicks in ?

Any idea :)?
Thx!
Br,Laurel

[Laurel]

Hi again!
Could it be this ?
======
SQL> select dbms_stats.get_prefs('AUTOSTATS_TARGET') from dual ;

DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')
--------------------------------------------------------------------------------
AUTO

SQL>exec DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE')
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.


BUT I already disabled autotask previously... by :
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> commit;

Its not good that it takes so much IO by those dbms_stats calls. Have you seeing such behavior ?
Thx alot!
Br,Laurel

[ErmanArslansOracleBlog]

Check -> AUTOSTATS_TARGET : Setting and Effects (Doc ID 276358.1) -- for setting it. (for future)

but you already disabled auto stats as you mentioned.

You know say that you see "dbms_stats.gather_table_stats" running.

1)May there be a custom program , or external tool (like an ETL tool) that is executing this. Did you check the session/process info of those gather_table_stats sessions?

2)Statistics gathering, when you have lots of stale(s) may be a heavy process. It depends on its parameters/arguments.. Which table, what size does it have? What is the mode of the gather_table_stats / the arguments of it, and so on..

3)If you have EM, check that as well.. There may be something-related scheduled there.

4)Check -> Auto Optimizer Stats Collection Is Disabled and Yet Still Runs During Maintenance Window (Doc ID 2652183.1).. This may give you some other help, about detecting manual executions of these stats collection tasks.

5)Check the real time statistics as well.. I don't think it is, but still it is good to check ->Disabling Real-time Statistics In 19c (Doc ID 2577100.1)