dbms_stats.gather_table_stats

Posted by Laurel on Mar 22, 2025; 9:05pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/dbms-stats-gather-table-stats-tp12958.html

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