Oracle 19c Statistics Gathering Automation | Real DBA Production Script Explained | stats gathering

This is a practical DBA approach where Linux shell scripting and DBMS_STATS are combined to standardize weekly statistics collection for an application schema, while ensuring execution plan stability and audit visibility.

The script performs the following at an enterprise level:

Sets Oracle runtime environment explicitly for Oracle 19c

Connects using OS authentication as SYSDBA

Dynamically generates SQL commands for statistics collection

Gathers table, index, dictionary, and fixed object statistics

Uses AUTO_SAMPLE_SIZE and parallel degree for performance

Prevents cursor invalidation using NO_INVALIDATE

Logs start and end timestamps for auditing

Extracts ORA errors into a separate file

Sends execution logs via email for operational monitoring

Below is the complete script explained in this video:

ORACLE_HOME=/oracle19c/RDBMS_19C;
ORACLE_SID=proddb1 ; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH ; export PATH
LIBPATH=$ORACLE_HOME/lib:/usr/lib:/lib ; export LIBPATH

SPOOLFILE=/home/Oracle/logs/proddb_statsgather.sql

sqlplus -s "/" as sysdba OF

set echo off
set pages 0
set feedback off
set lines 1000
spool $SPOOLFILE

select 'set time on timing on' from dual;
select 'set echo on;' from dual;
select 'set pages 1000' from dual;
select 'spool /home/Oracle/logs/proddb_statsgather.log;' from dual;
select 'select to_char(sysdate,''dd-mon-yy:hh24:mi'') analyze_started from dual;' from dual;
select 'set feedback on;' from dual;
select 'show user;' from dual;

select 'exec dbms_stats.gather_table_stats(ownname= ''''||lower(owner)||''', tabname= ''''||table_name||''', method_opt = ''for all columns size 1'', estimate_percent=dbms_stats.auto_sample_size, degree = 16, cascade= TRUE, NO_INVALIDATE=TRUE);'
from dba_tables where owner in ('PRODUSER') order by owner;

select 'exec dbms_stats.GATHER_FIXED_OBJECTS_STATS;' from dual;
select 'exec dbms_stats.GATHER_DICTIONARY_STATS (ESTIMATE_PERCENT=100, METHOD_OPT=''for all columns size 1'', DEGREE=32, CASCADE= TRUE);' from dual;
select 'select to_char(sysdate,''dd-mon-yy:hh24:mi'') analyze_ended from dual;' from dual;
select 'spool off;' from dual;

spool off;
@$SPOOLFILE

EOF

exit

This pattern is widely used in banking, financial, and enterprise production environments where optimizer stability, predictability, and traceability are non-negotiable.

Follow for more real Oracle DBA production scripts and deep technical breakdowns.

Hashtags:

#OracleDBA
#Oracle19c
#DBMS_STATS
# #asm #cdb #oracle19c #oracledatabase OraclePerformance
#OracleTuning
#OracleAutomation
#ShellScripting
#LinuxForDBA
#DatabaseMaintenance
#ProductionDBA
#OracleBestPractices
#EnterpriseDatabase
#DBALife
#RashOraTech
#YouTubeShorts
#TechShorts
#ITOperations

Видео Oracle 19c Statistics Gathering Automation | Real DBA Production Script Explained | stats gathering канала Rash OraTech