Clean SYSAUX Tablespace, purge data from WRI$_ADV_OBJECTS

============

Issue 

============

Tablespace SYSAUX nearly 100% full because table WRI$_ADV_OBJECTS and related indexes are very big

This is a know issue in Oracle 12.2

============

Solution

============

Purge Data from WRI$_ADV_OBJECTS.

Current space usage:

TABLESPACE_NAME FREE_SPACE_MB USED_SPACE_MB     MAX_SPACE_MB  
--------------- ------------- ----------------- ------------- 
SYSAUX                     46              7954         8000 

 
SQL> select * from v$version;
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production                0
PL/SQL Release 12.2.0.1.0 - Production                               

Top objects in SYSAUX Tablespace

                     0
OWNER   TABLESPACE_NAME SEGMENT_NAME                USED_MB
------- --------------- --------------------------- -------
SYS     SYSAUX          WRI$_ADV_OBJECTS               4145
SYS     SYSAUX          WRI$_ADV_OBJECTS_IDX_01        1666
SYS     SYSAUX          WRI$_ADV_OBJECTS_PK            1104
SYS     SYSAUX          SYS_LOB0000164068C00005$$       160


COL TASK_NAME FOR A50

SELECT task_name, count(*) cnt 

  FROM dba_advisor_objects 

GROUP BY task_name 

ORDER BY cnt DESC;

TASK_NAME                 CNT
------------------------- ----------
AUTO_STATS_ADVISOR_TASK   11061381
ADDM:1066102538_1_28416   13
ADDM:1066102538_1_28062   13
ADDM:1066102538_1_27837   12
ADDM:1066102538_1_28086   11
ADDM:1066102538_1_28302   11
ADDM:1066102538_1_27885   11
ADDM:1066102538_1_28134   10

==========================================

Reference for cleanup from WRI$_ADV_OBJECTS:

==========================================

Reference:
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)

If there are too many records in the table WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK, then huge UNDO would be required.
In such cases, following method can be implemented to purge the data by avoiding excessive redo/undo generation.

CREATE TABLE WRI$_ADV_OBJECTS_NEW 
AS SELECT * FROM WRI$_ADV_OBJECTS 
WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SELECT COUNT(* )FROM WRI$_ADV_OBJECTS_NEW;

COUNT(*)

--------

       2

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

Note - In case of error ORA-54013: INSERT operation disallowed on virtual columns - see below #Handle ORA-54013

commit;

DROP TABLE WRI$_ADV_OBJECTS_NEW;

--Reorganize the indexes
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

SYSAUX Tablespace usage after  cleanup:

TABLESPACE_NAME FREE_SPACE_MB USED_SPACE_MB     MAX_SPACE_MB  
--------------- ------------- ----------------- ------------- 
SYSAUX                   6960              1040          8000 

============

Prevent issue from happen again

============

Reference: 

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

COL task_name FORMAT a25

COL parameter_name FORMAT a35

COL parameter_value FORMAT a20

SET lines 120

SELECT task_name, parameter_name, parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME            PARAMETER_VALUE

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

AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE  UNLIMITED

BEGIN

 DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 30);

END;

/

SELECT task_name, parameter_name, parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME            PARAMETER_VALUE

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

AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE  30

Now, all the statistics advisor execution records older than 30 days are marked as expired and would be purged automatically through the Auto-Purge window.

To purge manually using the below command instead of Auto-Purge window:-

conn / as sysdba

BEGIN

 PRVT_ADVISOR.delete_expired_tasks;

END;

/

 Check status after purge

COL EXECUTION_NAME FOR A30

SELECT task_id,task_name,execution_name ,execution_start 

  FROM DBA_ADVISOR_EXECUTIONS 

 WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

ORDER BY  execution_start;

   TASK_ID TASK_NAME                 EXECUTION_NAME  EXECUTION_START

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

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15898      01-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15899      01-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15912      02-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15913      02-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15930      03-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15931      03-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15948      04-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15960      05-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_15983      06-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16010      07-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16027      08-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16059      09-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16084      10-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16107      11-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16140      12-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16168      13-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16200      14-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16222      15-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16248      16-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16270      17-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16295      18-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16318      19-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16349      20-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16375      21-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16398      22-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16421      23-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16443      24-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16459      25-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16488      26-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16501      27-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16529      28-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16543      29-MAR-21

      8491 AUTO_STATS_ADVISOR_TASK   EXEC_16558      30-MAR-21

========================

Appendix

========================

1. crontab script to automate purge

crontab

2 7 * * * bash -l /software/oracle/oracle/scripts/space/space_cleanup/delete_expired_tasks.sh

delete_expired_tasks.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/space/space_cleanup

. /etc/sh/orash/oracle_login.sh igt

ORACLE_HOME=/software/oracle/1910

ORACLE_SID=igt

PATH=${PATH}:${ORACLE_HOME}/bin

cd ${WORK_DIR}

sqlplus / as sysdba @delete_expired_tasks.sql

delete_expired_tasks.sql

BEGIN

 PRVT_ADVISOR.delete_expired_tasks;

END;

/

EXIT;

2. Handle ORA-54013: INSERT operation disallowed on virtual columns 

SQL> INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

SELECT column_name, virtual_column

  FROM user_tab_cols

 WHERE table_name = 'WRI$_ADV_OBJECTS'

COLUMN_NAME                    VIRTUAL_COLU

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

ID                             NO

TYPE                           NO

TASK_ID                        NO

EXEC_NAME                      NO

ATTR1                          NO

ATTR2                          NO

ATTR3                          NO

ATTR4                          NO

ATTR5                          NO

ATTR6                          NO

ATTR7                          NO

ATTR8                          NO

ATTR9                          NO

ATTR10                         NO

ATTR11                         NO

ATTR12                         NO

ATTR13                         NO

ATTR14                         NO

ATTR15                         NO

ATTR16                         NO

ATTR17                         NO

ATTR18                         NO

ATTR19                         NO

ATTR20                         NO

OTHER                          NO

SPARE_N1                       NO

SPARE_N2                       NO

SPARE_N3                       NO

SPARE_N4                       NO

SPARE_C1                       NO

SPARE_C2                       NO

SPARE_C3                       NO

SPARE_C4                       NO

SQL_ID_VC                      YES

Need to specify field by field

COL column_name  FOR A40

SELECT  LOWER(column_name)||',' column_name 

  FROM USER_TAB_COLS 

 WHERE table_name = 'WRI$_ADV_OBJECTS_NEW' 

ORDER BY column_id;

INSERT INTO WRI$_ADV_OBJECTS

(

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

)

SELECT

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

 FROM WRI$_ADV_OBJECTS_NEW;

commit;

=======================

Create a job to cleanup 

=======================

delete_expired_tasks.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/space

. /etc/sh/orash/oracle_login.sh igt

ORA_VER=1910

ORACLE_SID=igt

ORACLE_HOME=/software/oracle/1910

cd ${WORK_DIR}

sqlplus / as sysdba @delete_expired_tasks.sql

delete_expired_tasks.sql

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

--WRI$_ADV_OBJECTS

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

CREATE TABLE WRI$_ADV_OBJECTS_NEW 

AS SELECT * FROM WRI$_ADV_OBJECTS 

WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT INTO WRI$_ADV_OBJECTS

(

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

)

SELECT

id,

type,

task_id,

exec_name,

attr1,

attr2,

attr3,

attr4,

attr5,

attr6,

attr7,

attr8,

attr9,

attr10,

attr11,

attr12,

attr13,

attr14,

attr15,

attr16,

attr17,

attr18,

attr19,

attr20,

other,

spare_n1,

spare_n2,

spare_n3,

spare_n4,

spare_c1,

spare_c2,

spare_c3,

spare_c4

 FROM WRI$_ADV_OBJECTS_NEW;

commit;

DROP TABLE WRI$_ADV_OBJECTS_NEW;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

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

--WRI$_ADV_OBJECTS

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

BEGIN

 PRVT_ADVISOR.delete_expired_tasks;

END;

/

EXIT;

Optionally, stop package AUTO_STATS_ADVISOR_TASK from running.

DECLARE

 v_tname VARCHAR2(32767);

BEGIN

 v_tname := 'AUTO_STATS_ADVISOR_TASK';

 DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) can be recreated any time by running the following if DBA(s) wants to use the Statistics Advisor.

BEGIN

 DBMS_STATS.INIT_PACKAGE();

END;

/

GG Restart from crontab

=========================
General
=========================

 Restart GG processes from crontab

=========================
Code
=========================
crontab entry
1 06 1 * * /software/oracle/oracle/scripts/gg_restart/gg_restart_all_groups.sh

gg_restart_all_groups.sh
#!/bin/bash

WORK_HOME=/software/oracle/oracle/scripts/gg_restart

LOG_FILE=gg_restart.log

cd $WORK_HOME

./gg_restart_log.sh "${WORK_HOME}" "${LOG_FILE}" "START"

./gg_restart_group.sh EXT_S "ext ext_s*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh EXT_P "ext ext_p*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh EXT_I "ext ext_i*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh DPM_S "ext dpm_s*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh DPM_P "ext dpm_p*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh DPM_I "ext dpm_i*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh REP_S "rep rep_s*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh REP_P "rep rep_p*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh REP_I "rep rep_i*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_log.sh "${WORK_HOME}" "${LOG_FILE}" "FINISH" 

gg_restart_log.sh

#!/bin/bash

WORK_HOME=$1

LOG_FILE_NAME=$2

VERB=$3

cd $WORK_HOME

LOG_FILE=${WORK_HOME}/${LOG_FILE_NAME}

RUN_DATE=`date "+%Y%m%d"_"%H%M%S"`

touch $LOG_FILE

if [[ $VERB == "START" ]]; then

 echo "" >>$LOG_FILE

 echo ========================================== >>$LOG_FILE

 echo Start at $RUN_DATE >>$LOG_FILE

 echo ========================================== >>$LOG_FILE

 echo "" >>$LOG_FILE

 echo "Before Restart: " >>$LOG_FILE

 echo "" >>$LOG_FILE 

 free -m >> $LOG_FILE

 echo "" >> $LOG_FILE

 sar -r >> $LOG_FILE

  echo "" >>$LOG_FILE 

fi

if [[ $VERB == "FINISH" ]]; then

  echo "" >>$LOG_FILE

  echo "---------------------------" >>$LOG_FILE

  echo "After Restart: " >>$LOG_FILE

  echo "---------------------------" >>$LOG_FILE  

  echo "" >>$LOG_FILE  

  free -m >> $LOG_FILE

  echo "" >> $LOG_FILE

  sar -r >> $LOG_FILE

  echo "" >>$LOG_FILE    

  echo ========================================== >>$LOG_FILE

  echo Finished at $RUN_DATE >>$LOG_FILE

  echo ========================================== >>$LOG_FILE

  echo "" >>$LOG_FILE

fi

exit 0 

gg_restart_group.sh

#!/bin/bash

GROUP_NAME=$1

RESTART_GROUP=$2

WORK_DIR=$3

LOG_FILE_NAME=$4

#GROUP_NAME=EXT_S

#RESTART_GROUP="ext ext_s*"

LOG_FILE=${WORK_DIR}/${LOG_FILE_NAME}

clear

echo "Restarting ${GROUP_NAME} ${RESTART_GROUP}" >> ${LOG_FILE}

echo "Log File is: ${LOG_FILE}"

exit 0

. /etc/profile > /dev/null 2> /dev/null

TEMP_LOG_FILE="/tmp/restart_temp.tmp"

RUN_DATE=`date "+%Y%m%d"_"%H%M%S"`

# Setting Oracle GoldenGate environments

if [ "$OGGSH" = "" ]; then

   OGGSH=$INFRA_ROOT/oggsh

fi

if [ "$ORACLE_SID" = "" ]; then

   INSTANCE=igt

else

   INSTANCE=$ORACLE_SID

fi

. $OGGSH/gg_login.sh $INSTANCE

touch $LOG_FILE

touch $TEMP_LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Start Restart for ${GROUP_NAME} group ${RESTART_GROUP} at $RUN_DATE >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Initial State >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat  $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

echo about to do  "stop ext $RESTART_GROUP" >>$LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOF

stop ${RESTART_GROUP}

exit

EOF

cat  $TEMP_LOG_FILE >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo After Stop >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat  $TEMP_LOG_FILE | grep ${GROUP_NAME}  >>$LOG_FILE

echo "Starting  ${RESTART_GROUP}" >> $LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOF

start ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo After Start >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat  $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

NOT_RUNNING=`grep STOPPED $TEMP_LOG_FILE |grep ${GROUP_NAME} | wc -l`

if [ $NOT_RUNNING -gt 0 ]; then

echo "Some Processes were not started. Attempting restart" >>$LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOF

start ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

fi

#============================================================

# Second restart

#============================================================

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat  $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

NOT_RUNNING=`grep STOPPED $TEMP_LOG_FILE | wc -l`

if [ $NOT_RUNNING -gt 0 ]; then

echo "Some Processes were not started. Attempting restart" >>$LOG_FILE

$GG_HOME/ggsci > $TEMP_LOG_FILE <<EOF

start ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat  $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

fi

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Finished Restart for $GROUP_NAME $RESTART_GROUP at $RUN_DATE >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

 

sftp script example

sftp script example

sftp user@111.222.333.444 << EOF
cd /some/path/on/target/server
put /some/path/on/from/server/some_file.*.dat
exit
EOF

expdp is slow. AWR showing Streams AQ: enqueue block as main wait event

===============

Issue

===============
expdp is running for several hours

AWR is showing Streams AQ: enqueue blocked as main Foreground Wait Event

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 

===============
AWR Evidences

===============

Foreground Wait Events                   DB/Inst: IGT/igt  Snaps: 39049-39050
-> s  - second, ms - millisecond -    1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0
                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
Streams AQ: enqueue blocke        1,767     0      2,656    1503      0.2   95.2
db file sequential read          74,898     0          8       0      6.8     .3
log file sync                     2,564     0          2       1      0.2     .1
enq: RO - fast object reus          216     0          1       5      0.0     .0
SQL*Net message to client     2,475,101     0          1       0    224.9     .0
local write wait                    504     0          0       1      0.0     .0
library cache: mutex X            4,931     0          0       0      0.4     .0
SQL*Net more data from cli       21,140     0          0       0      1.9     .0

SQL ordered by Elapsed Time              DB/Inst: IGT/igt  Snaps: 39049-39050

-> Resources reported for PL/SQL code includes the resources used by all SQL

   statements called by the code.

-> % Total DB Time is the Elapsed Time of the SQL statement divided

   into the Total Database Time multiplied by 100

-> %Total - Elapsed Time  as a percentage of Total DB time

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Captured SQL account for    3.0% of Total DB Time (s):           2,789

-> Captured PL/SQL account for  445.8% of Total DB Time (s):           2,789

        Elapsed                  Elapsed Time

        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id

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

         3,536.5              0           N/A  126.8     .6     .0 bja07yskmzxn1

 BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_01', 'SHDAEMON', 0); END;

         3,482.5          1,769          1.97  124.9     .1     .0 8rgw5q94paqsg

Module: Data Pump Master

BEGIN sys.kupc$que_int.send(:1, :2, :3); END;

         2,660.5              0           N/A   95.4     .1     .0 a103q4nq8cdj7

 BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_01', 'SHDAEMON', 0, 0); END;

         2,592.6          1,718          1.51   93.0     .0     .0 87nt40c5wj7y5

Module: Data Pump Master

BEGIN sys.kupc$que_int.put_status(:1, :2, :3); END;

====================

What to check

====================

1. streams_pool_size Parameter

COL name FOR A30

COL value FOR A30

SELECT name, value 

  FROM V$PARAMETER WHERE name LIKE '%stream%';

NAME                VALUE

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

streams_pool_size   0

2. streams pool size Hidden Parameters

set pagesize 35

set linesize 150

col NAME format a40

col VALUE format a20

col DESCRIPTION format a60

set pause on

set pause 'Hit enter to continue'

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION

FROM x$ksppi x, x$ksppcv y

WHERE x.inst_id = userenv('Instance')

AND y.inst_id = userenv('Instance')

AND x.indx = y.indx

AND SUBSTR(LOWER(x.ksppinm),1,1) = '_'

AND x.ksppinm LIKE '%streams%'

ORDER BY 1;

NAME                                     VALUE                DESCRIPTION

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

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

__streams_pool_size                      33554432             

Actual size in bytes of streams pool

_disable_streams_diagnostics             0                    

streams diagnostics

_disable_streams_pool_auto_tuning        FALSE                

disable streams pool auto tuning

_memory_broker_shrink_streams_pool       900                  

memory broker allow policy to shrink streams pool

_streams_pool_max_size                   0                    

streams pool maximum size when auto SGA enabled

_streams_pool_size is set to 32 Mb which is too small for this big DB

 

====================

Solution

====================

Although streams_pool_size is set to zero, Oracle does not really tune streams_pool_size.

It just is using the default value of hidden parameter __streams_pool_size.

Option A - Set a high value to streams_pool_size to override Oracle default.

SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=MEMORY;

System altered.

And check results

or

ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=SPFILE;

And restart Instance.

Option B - Alter hidden parameter _disable_streams_pool_auto_tuning set value to TRUE. 

This is will disable  Oracle tuning to streams pool size.

ALTER SYSTEM SET "_disable_streams_pool_auto_tuning"=TRUE SCOPE=SPFILE;

And restart Instance.

Backup to pfile:

CREATE PFILE = '/software/oracle/112/dbs/igt_init_pfile_20210202.ora' FROM SPFILE;

File created.

!ls /software/oracle/112/dbs/igt_init_pfile_20210202.ora

/software/oracle/112/dbs/igt_init_pfile_20210202.ora

====================

Oracle Metalink #1596645:
====================

Oracle has this issue documented:

EXPDP And IMPDP Slow Performance In 11R2 and 12R1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

Version: Oracle Release 11.2.0.4 

Issue:

There is high wait for "Streams AQ: enqueue blocked on low memory"

Solution:

Increase streams_pool_size.  Say to 256M

CONNECT / as sysdba

ALTER SYSTEM SET streams_pool_size=256m SCOPE=both;

If above does not help, perform:

ALTER SYSTEM SET "_disable_streams_pool_auto_tuning"=TRUE SCOPE=spfile;

SHUTDOWN IMMEDIATE

STARTUP

If increasing streams_pool_size, you may have to increase your SGA_TARGET or MEMORY_TARGET also.

SGA_TARGET or MEMORY_TARGET should be high enough

SELECT name, value 

  FROM V$PARAMETER 

WHERE name LIKE '%target%';

NAME                           VALUE

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

sga_target                     0

memory_target                  8589934592

memory_max_target              8589934592

SELECT component, 

       ROUND(current_size/1024/1024)current_size_Mb, 

       ROUND(MAX_SIZE/1024/1024)max_size_mb, 

       ROUND(USER_SPECIFIED_SIZE/1024/1024) user_def_size_mb

  FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT               CURRENT_SIZE_MB MAX_SIZE_MB USER_DEF_SIZE_MB

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

shared pool                        2944        2944                0

large pool                          640         768                0

java pool                           384         512                0

streams pool                       1152        1152              512

DEFAULT buffer cache              21376       21504                0

KEEP buffer cache                     0           0                0

RECYCLE buffer cache                  0           0                0

DEFAULT 2K buffer cache               0           0                0

DEFAULT 4K buffer cache               0           0                0

DEFAULT 8K buffer cache               0           0                0

DEFAULT 16K buffer cache           6144        6144             6144

DEFAULT 32K buffer cache              0           0                0

Shared IO Pool                      512         512              512

Data Transfer Cache                   0           0                0

In-Memory Area                        0           0                0

ASM Buffer Cache                      0           0                0

Memory Usage Monitoring script. For SWAP using processes, Golden Gate, Top 20 oracle processes.

crontab

 0,15,30,45 * * * * /software/oracle/oracle/scripts/memory_usage/gg_mem_usage_wrapper.sh

gg_mem_usage_wrapper.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/memory_usage

LOG_FILE=gg_memory.log

RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`

cd ${WORK_DIR}

touch ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "=================" >> ${LOG_FILE}

echo ${RUN_DATE} >> ${LOG_FILE}

./gg_mem_usage.sh >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

free -m >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "Top 20 Processes" >> ${LOG_FILE}

ps -eo rss,pid,euser,lstart,args:100 --sort %mem | grep -v grep | sort -n | tail -20 | awk '{printf $1/1024 "MB"; $1=""; print }' >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "Top SWAP Using Processes" >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "SWAP Usage from free -m" >> ${LOG_FILE}

free -m | grep Swap >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "SWAP Usage from /proc" >> ${LOG_FILE}

find /proc -maxdepth 2 -path "/proc/[0-9]*/status" -readable -exec awk -v FS=":" '{process[$1]=$2;sub(/^[ \t]+/,"",process[$1]);} END {if(process["VmSwap"] && process["VmSwap"] != "0 kB") printf "%10s %-30s %20s\n",process["Pid"],process["Name"],process["VmSwap"]}' '{}' \; | awk '{print $(NF-1),$0}' | sort -hr | head | cut -d " " -f2- >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

exit

gg_mem_usage.sh

#!/bin/bash

###############################

# determine the OS type

###############################

OSNAME=`uname`

case "$OSNAME" in

  "SunOS")

    echo "OSNAME = $OSNAME"

    ;;

  "Linux")

    echo "OSNAME = $OSNAME"

    ;;

  "*")

    echo "This script has not been verified on $OSNAME"

    exit 1

    ;;

esac

###############################

# set the temp file

###############################

TMPFILE=/tmp/pmem.tmp

if [ -f $TMPFILE ]

then

  rm -f $TMPFILE

fi

################################

# loop over the gg process types

################################

PROCESSES="extract replicat"

for PROCESS in $PROCESSES

do

  FLAG=""

  FLAG=`ps -ef | grep $PROCESS`

  if [ -z "FLAG" ]

  then

    echo "No $PROCESS processes found"

  else

    echo

    echo "#####################################"

    echo "# Individual $PROCESS Process Usage #"

    echo "#####################################"

    case "$OSNAME" in

      "Linux")

        ps -C $PROCESS -O rss > $TMPFILE

        cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2

        ;;

      "SunOS")

        ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE

        cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2

        ;;

      "*")

        echo "This script has not been verified on $OSNAME"

        exit 1

        ;;

    esac

    rm -f $TMPFILE

    echo

    echo "#####################################"

    echo "#   Total $PROCESS Process Usage    #"

    echo "#####################################"

    case "$OSNAME" in

      "Linux")

        ps -C $PROCESS -O rss > $TMPFILE

        cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \

          { print "Number of processes      =",count; \

          print "AVG Memory usage/process =",sum/1024/count, "MB"; \

          print "Total memory usage       =", sum/1024,  " MB"}'

        ;;

      "SunOS")

        ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE

        cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \

          { print "Number of processes      =",count; \

          print "AVG Memory usage/process =",sum/1024/count, "MB"; \

          print "Total memory usage       =", sum/1024,  " MB"}'

        ;;

      "*")

        echo "This script has not been verified on $OSNAME"

        exit 1

        ;;

    esac

    rm -f $TMPFILE

  fi

done

exit

=================

Sample output

=================

20210118_181502

OSNAME = Linux

#####################################

# Individual extract Process Usage #

#####################################

38.7109 MB DPM_I_01

42.125 MB DPM_P_01

41.8672 MB DPM_S_01

70.8867 MB EXT_I_01

78.9297 MB EXT_P_01

361.016 MB EXT_S_01

#####################################

#   Total extract Process Usage    #

#####################################

Number of processes      = 6

AVG Memory usage/process = 105.589 MB

Total memory usage       = 633.535  MB

#####################################

# Individual replicat Process Usage #

#####################################

32.4219 MB REP_I_01

37.2383 MB REP_P_01

34.8789 MB REP_S_01

#####################################

#   Total replicat Process Usage    #

#####################################

Number of processes      = 3

AVG Memory usage/process = 34.8464 MB

Total memory usage       = 104.539  MB

             total       used       free     shared    buffers     cached

Mem:         64175      62568       1607       9373        384      57889

-/+ buffers/cache:       4294      59880

Swap:        10239        122      10117

Top 20 Processes

361.016MB 30432 oracle Sat Jan 16 20:46:19 2021 /software/ogg/1212/extract PARAMFILE /software/ogg/1212/dirprm/ext_s_01.prm REPORTFILE /software/ogg/1212/dirrpt/EXT_S_01.rpt PROCESSID EXT_S_01 USESUBDIRS

371.129MB 30853 oracle Sat Jan 16 20:46:24 2021 ora_ms03_igt

392.086MB 30851 oracle Sat Jan 16 20:46:24 2021 ora_ms02_igt

488.648MB 28170 oracle Sat Jan 16 20:46:05 2021 ora_smon_igt

512.262MB 28454 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

607.949MB 28162 oracle Sat Jan 16 20:46:05 2021 ora_mman_igt

682.445MB 28392 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

724.746MB 29669 oracle Sat Jan 16 20:46:13 2021 oracleigt (LOCAL=NO)

972.762MB 30606 oracle Sat Jan 16 20:46:19 2021 oracleigt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

1046.21MB 29663 oracle Sat Jan 16 20:46:13 2021 oracleigt (LOCAL=NO)

1173.61MB 32712 oracle Sat Jan 16 20:46:47 2021 oracleigt (LOCAL=NO)

1208.2MB 20575 oracle Sun Jan 17 09:59:59 2021 oracleigt (LOCAL=NO)

1278.24MB 28830 oracle Sat Jan 16 20:46:11 2021 oracleigt (LOCAL=NO)

1934.22MB 30150 oracle Sat Jan 16 20:46:16 2021 oracleigt (LOCAL=NO)

2697.98MB 28386 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

3556.99MB 28390 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

5853.8MB 28388 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

5938.82MB 28443 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

6263.12MB 28298 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

6995.96MB 28164 oracle Sat Jan 16 20:46:05 2021 ora_dbw0_igt

Top SWAP Using Processes

SWAP Usage from free -m

Swap:        10239        122      10117

SWAP Usage from /proc

     30649 splunkd                                    49264 kB

      8260 vxconfigd                                  24152 kB