Tobi's Notes: August 2019

Running SLOB On Pluggable Database.

Why this note: as of Version 2.4.2.1, SLOB do not support execution on Oracle PDB. below error generated when you try to execute SLOB against pluggable DB.

NOTIFY  : 2019.08.09-20:20:31 : Testing Admin connect using "sqlplus -L system/orcl@pdb"
FATAL   : 2019.08.09-20:20:36 : 
FATAL   : 2019.08.09-20:20:36 : This version of SLOB does not support Oracle Multitenant Option
FATAL   : 2019.08.09-20:20:36 :

What the Workaround?

edit setup.sh and comment "exit 1" as showed below.

if ( ! f_check_mto "$ADMIN_CONNECT_STRING" 2>&1 >> $LOG)
then
        f_msg FATAL ""

        f_msg FATAL "This version of SLOB does not support Oracle Multitenant Option"

        f_msg FATAL

        #remove Multitenant check
        #exit 1

fi

Problem 2: No AWR report after SLOB executed.

AWR report are not capture at Root Container level, only in PDB (obviously, *_SERVICE_BASE are pointed to PDB). SLOB is designed to run AWR in the non-multitenant and its query DBA_HIST_SNAPSHOT rather than AWR_PDB_SNAPSHOT. AWR snapshot created at PDB level but SLOB execute AWR report at CDB level.

Workaround: this solution going to enable auto collection of AWR at CDB level and you can manually collect AWR PDB report for the same time frame.

there are advantages for collecting at CDB level e.g. having overall performance data, contention between PDB for resource can be spotted with CDB AWR report. Also, I noticed "Write IO (MB):" information is missing from Load Profile in PDB’s AWR report, although write I/O information is at "IOstat Section"

Using external stub in $SLOB/misc/external_script.sh, edit as follows:

#!/bin/bash

case "$1" in

        pre)

sqlplus SYSTEM/orcl@orcl19xxx <<EOF
spool cdb_external_script.log
SET TIME ON
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

EXIT

EOF

 ;;

        post)

sqlplus SYSTEM/orcl@orcl19xxx <<EOF
spool cdb_external_script.log
SET TIME ON
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

EXIT

EOF

 ;;

        end) : ;;

esac

exit 0

without above mechanism, error below will be generated in slob_debug.out. other methods will involve modifying more scripts!

declare

declare

*

*

ERROR at line 1:

ERROR at line 1:

ORA-20200: End Snapshot Id 87 must be greater than Begin Snapshot Id 87

ORA-06512: at line 38

ORA-20200: End Snapshot Id 87 must be greater than Begin Snapshot Id 87

ORA-06512: at line 38

Executing SLOB(summarized): for detail check Kevin Closson blog.

1. Edit slob.conf:

UPDATE_PCT=20
SCAN_PCT=0
RUN_TIME=300
WORK_LOOP=0
SCALE=80M
SCAN_TABLE_SZ=1M
WORK_UNIT=64
REDO_STRESS=LITE
LOAD_PARALLEL_DEGREE=2
THREADS_PER_SCHEMA=1
DATABASE_STATISTICS_TYPE=awr # Permitted values: [statspack|awr]
ADMIN_SQLNET_SERVICE="pdb"
SQLNET_SERVICE_BASE="pdb"
DBA_PRIV_USER="system"              
SYSDBA_PASSWD="orcl"
EXTERNAL_SCRIPT='/home/oracle/SLOB/misc/cdb_external_script.sh'
DO_HOTSPOT=FALSE
HOTSPOT_MB=8
HOTSPOT_OFFSET_MB=16
HOTSPOT_FREQUENCY=3
HOT_SCHEMA_FREQUENCY=0
THINK_TM_FREQUENCY=0
THINK_TM_MIN=.1
THINK_TM_MAX=.5