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;

 

Java (1.6) could not be located. OPatch cannot proceed!

We get below java error, when we try to query opatch utility like lsinventory, version, apply, rollback ...etc

follow below steps/workaround for the issue

Java (1.6) could not be located

Details of error are as follows.

[SERVER1]/u01/app$ OPatch/opatch version
Java (1.6) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1
[SERVER1]/u01/app$ 
[SERVER1]/u01/app/OPatch $

 

OPatch cannot proceed!

This errors are related with the Opatch version or opatch utility was downloaded for a wrong platform or using older version of opatch.

Install the latest opatch

OR

Use the JDK option like following.

Use the JDK option like following.

which java

copy the java path and go to jdk location inside java home 

opatch apply -jdk <<FULL_PATH_OF_JDK>

example output as below.

Use the JDK option like following.

[SERVER1]/u01/app$ 
[SERVER1]/u01/app$ OPatch/opatch version
Java (1.6) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1
[SERVER1]/u01/app$ 
[SERVER1]/u01/app/OPatch $ 


[SERVER1]/u01/app/OPatch $ ./opatch lsinventory -jdk /export2/jdk/ -oh /u01/app/
Oracle Interim Patch Installer version 13.9.4.2.4
Copyright (c) 2021, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app
Central Inventory : /u01/app/18c/oraInventory
from : /u01/app//oraInst.loc

OPatch version    : 12.2.0.1.33
OUI version       : 12.2.0.1.4

Log file location : /u01/app/cfgtoollogs/opatch/opatch2021-06-21_14-11-18PM_1.log

Lsinventory Output file location : /u01/app/cfgtoollogs/opatch/lsinv/lsinventory2021-06-21_14-11-18PM.txt

--------------------------------------------------------------------------------
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
[SERVER1]/u01/app/OPatch $