GATHER STATS for TABLE/SCHEMA/DATABASE/SYSTEM and LOCK STATS scripts

GATHER STATS for TABLE/SCHEMA/DATABASE/SYSTEM and LOCK STATS scripts

 TABLE STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

exec dbms_stats.gather_table_stats(ownname=>'&Schema_name',tabname=>'&Table_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree =>4);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&OWNER' , tabname => '&TAB_NAME',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1',granularity => 'ALL', degree => 1);

INDEX STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK');

exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);

SCHEMA STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);

exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);

DATABASE STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE AUTO');

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,CASCADE => TRUE,degree => 4);

DICTIONARY STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXEC DBMS_STATS.gather_dictionary_stats;

EXEC DBMS_STATS.gather_system_stats;

EXEC DBMS_STATS.gather_fixed_objects_stats;

lock/unlock statistics on table

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

exec dbms_stats.lock_table_stats('&OWNER', '&TAB_NAME');

exec dbms_stats.unlock_table_stats('&OWNER', '&TAB_NAME');

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';

CHECK STALE STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT owner, table_name, last_analyzed, stale_stats

FROM dba_tab_statistics

WHERE table_name='EMPLOYEES'

and owner='HR';

SELECT owner, table_name, index_name last_analyzed, stale_stats FROM dba_ind_statistics 

WHERE table_name='EMPLOYEES'

and owner = 'HR';

Linux Script to Gather Stats

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#!/bin/bash

. /home/oracle/.bash_profile

export ORACLE_HOME=/u01/app/oracle/product/19.3/db_home

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=orcl

export DATE=$(date +%y-%m-%d_%H%M%S)

#### Gather HR schema stats ####

sqlplus / as sysdba << EOF > /tmp/HR_stats_gather_$DATE.log

EXEC DBMS_STATS.gather_schema_stats('HR');

EOF

echo "Stats gathered succeeded"

to CHECK & Pause/Resume/Kill a Running RMAN Backup

TO VIEW BACKUP STATUS for RMAN BACKUP Processes    

 SELECT b.opname,b.SID, b.SERIAL#, a.status,b.CONTEXT, b.SOFAR, b.TOTALWORK,

ROUND (b.SOFAR/b.TOTALWORK*100, 2) "% COMPLETE"

FROM gV$SESSION_LONGOPS b, gv$session a

WHERE b.OPNAME LIKE 'RMAN%' 

AND b.OPNAME NOT LIKE '%aggregate%' 

and a.sid=b.sid 

AND b.TOTALWORK! = 0 AND b.SOFAR <> b.TOTALWORK;

    

TO GENERATE KILL SESSION scripts for RMAN  BACKUP Processes    

select 'alter system kill session '''||sid||'',''||serial#||''' immediate;' FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

1. Pause/Resume/Kill a Running RMAN Backup

To Check if there is an RMAN backup is currently running:

col START_TIME for a15

col END_TIME for a15

col TIME_TAKEN_DISPLAY for a10

col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10

col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11

col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10

col output_device_type heading "Device_TYPE" for a11

SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME,to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,input_type, output_device_type,input_bytes_display, output_bytes_display,output_bytes_per_sec_display,COMPRESSION_RATIO COMPRESS_RATIO FROM v$rman_backup_job_details WHERE status like 'RUNNING%';

2. To Pause an already running RMAN backup: [This is only applicable for Linux OS]

set pages 0 feedback off 

select 'TO PAUSE THE RUNNING RMAN BACKUP RUN THIS OS COMMAND:=>    kill -STOP '||listagg (p.spid, ' ')  WITHIN GROUP (ORDER BY p.spid) from v$session s, v$process p where s.program like 'rman@%' and p.addr=s.paddr;

3. To Resume an already "Paused" RMAN backup: [This is only applicable for Linux OS]

set pages 0 feedback off 

select 'TO RESUME A "PAUSED" RMAN BACKUP RUN THIS OS COMMAND:=>  kill -CONT '||listagg (p.spid, ' ')  WITHIN GROUP (ORDER BY p.spid) from v$session s, v$process p where s.program like 'rman@%' and p.addr=s.paddr;

4. To Terminate an already running RMAN backup: [This is only applicable for Linux OS]

set pages 0 feedback off 

select 'TO KILL  THE RUNNING RMAN BACKUP RUN THIS OS COMMAND:=>  kill -9 '||listagg (p.spid, ' ')  WITHIN GROUP (ORDER BY p.spid) from v$session s, v$process p where s.program like 'rman@%' and p.addr=s.paddr;