Archives December 22, 2022

SHELL SCRIPT TO CAPTURE THE SQL INFORMATION FROM CURSOR CACHE AND POPULATE INTO STS

If the database need to be performant proof, certain need to be taken in a proactive manner to prevent future performance issue or analysis of any flaws from historic information. This shell script poll the cursor cache for every 30 seconds , capture the sql, populate the STS, pack the STS information into backup table and export the STS table in a dump file.

Warning: This script may impose heavy load on the database. It is recommended to execute the script during quiet hours of the database.

#!/bin/bash

# Set the environment variables for multiple databases.
_set_env(){

cat /etc/oratab|grep -v '#'|grep -v '^$' > /home/oracle/oratab_new
while read x
   do
     IFS=':' read -r -a array <<< $x
                ORACLE_SID="${array[0]}"
                ORACLE_HOME="${array[1]}"
                echo $ORACLE_SID
                echo $ORACLE_HOME
                export PATH=$PATH:$ORACLE_HOME/bin
   done < /home/oracle/oratab_new


}


#Capture the high load sql to STS and pack them into staging table
_capture_sql_load(){
t=30
loop="true"
c=0
ela_s=$(date +%s)
while [ $loop == "true" ]
        do
        sleep $t

        $ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
        EXEC dbms_workload_repository.create_snapshot();
        conn c##hydra/hydra
        EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
                                                sqlset_name     => 'B4UPGLOAD', -
                                                time_limit      =>  30, -
                                        repeat_interval =>  5);
        conn / as sysdba
        BEGIN
            DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'STS',
            schema_name => 'KISH',
            tablespace_name => 'HYDRA');
        END;
        /
        BEGIN
            DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'B4UPGLOAD',
            sqlset_owner => 'C##HYDRA',
            staging_table_name => 'STS',
            staging_schema_owner => 'KISH');
            END;
         /
exit;
EOF
ela_e=$(date +%s)
c=$c+1
ela_t=$(expr $ela_e - $ela_s)
if [[ $c -gt 30 ]]
    then
    loop=False
elif [[ $c -eq 30 ]]
    then
    _exp_sqlset
    break
fi
done

}


#Export the STS to a dump file out of the database
_exp_sqlset(){
exp USERID=kish/password file=expsts.dmp log=stsbkp.log buffer=500 tables=kish.sts
}

_set_env
_capture_sql_load