Further investigation shows the problem seems related to memory allocated
for 'gcs dynamic resources fg'.
Note test instance I am using is RAC enabled binary, but have only created
one instance.
I believe "gcs" points to this being RAC related.
Interesting the issue seems directly related to the size of the source
table.
If this is over about 4GB we get this behaviour.
This seems independant of the in-memory size (confirmed by creating tables
with uniform values vs random strings to affect in-memory compression).
I have tried setting trace for these events to see if I can get any useful
debug info out but nothing so far:
ORA-10426: enable ges/gcs reconfiguration event trace
ORA-10427: enable global enqueue service traffic controller event trace
ORA-10430: enable ges/gcs dynamic remastering event trace
Struggling a little as I don't have a strong understanding of RAC
architecture, only reason we are using RAC enabled binaries is because we
are on exadata.
Obviously the 16MB allocation I show isn't a huge concern, althoug it is a
bit worrying it is never released.
However in our production test databases this ends up eating all our SGA.
Below is simplest test case I can provide so far, appreciate any ideas for
further investigation.
[oracle@hkexdb01 trace]$ srvctl stop database -database IMTEST
[oracle@hkexdb01 trace]$ srvctl start database -database IMTEST
[oracle@hkexdb01 trace]$ sqlplus dba_patrickjolliffe
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 16 14:28:26 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Mar 16 2016 14:25:57 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Advanced Analytics and Real Application Testing options
DBA_PATRICKJOLLIFFE@IMTEST1> select * from v$version;
BANNER
CON_ID
--------------------------------------------------------------------------------
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production 0
PL/SQL Release 12.1.0.2.0 -
Production 0
CORE 12.1.0.2.0
Production 0
TNS for Linux: Version 12.1.0.2.0 -
Production 0
NLSRTL Version 12.1.0.2.0 -
Production 0
5 rows selected.
DBA_PATRICKJOLLIFFE@IMTEST1> DROP TABLE IMTEST3 PURGE;
Table dropped.
DBA_PATRICKJOLLIFFE@IMTEST1> CREATE TABLE IMTEST3 AS SELECT 'AAAAAAAAAA'
COL1, 'BBBBBBBBBB' COL2, 'CCCCCCCCCC' COL3, 'DDDDDDDDDD' COL4 from dual
connect by level < 20000000;
Table created.
DBA_PATRICKJOLLIFFE@IMTEST1> INSERT INTO IMTEST3 SELECT /*+ APPEND */* FROM
IMTEST3 UNION ALL SELECT /*+ APPEND */* FROM IMTEST3 UNION ALL SELECT /*+
APPEND */* FROM IMTEST3;
59999997 rows created.
DBA_PATRICKJOLLIFFE@IMTEST1> COMMIT;
Commit complete.
DBA_PATRICKJOLLIFFE@IMTEST1> ALTER TABLE IMTEST3 INMEMORY PRIORITY HIGH;
Table altered.
DBA_PATRICKJOLLIFFE@IMTEST1> SELECT ROUND(bytes/1024/1024) FROM V$SGASTAT
WHERE NAME = 'gcs dynamic resources fg';
no rows selected
DBA_PATRICKJOLLIFFE@IMTEST1> SELECT COUNT(*) FROM IMTEST3;
COUNT(*)
----------
79999996
1 row selected.
DBA_PATRICKJOLLIFFE@IMTEST1> SELECT segment_name, inmemory_size, bytes,
populate_status FROM V$IM_SEGMENTS;
SEGMENT_NAME
INMEMORY_SIZE
--------------------------------------------------------------------------------------------------------------------------------
-------------
BYTES POPULATE_
---------- ---------
IMTEST3
287768576
4496293888 COMPLETED
1 row selected.
DBA_PATRICKJOLLIFFE@IMTEST1> SELECT ROUND(bytes/1024/1024) FROM V$SGASTAT
WHERE NAME = 'gcs dynamic resources fg';
ROUND(BYTES/1024/1024)
----------------------
16
1 row selected.
DBA_PATRICKJOLLIFFE@IMTEST1> ^C
