===============
Issue
===============
expdp is running for several hours
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
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
No comments:
Post a Comment