Re: Shared Pool Issues possibly caused by In-Memory
Date: Wed, 16 Mar 2016 14:52:12 +0800
Message-ID: <CABx0cSVa-EbACD+z3M-_YU=FqArdZJmtAFWRWqt-pbs4YP0AiA_at_mail.gmail.com>
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_at_hkexdb01 trace]$ srvctl stop database -database IMTEST
[oracle_at_hkexdb01 trace]$ srvctl start database -database IMTEST
[oracle_at_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_at_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_at_IMTEST1> DROP TABLE IMTEST3 PURGE;
Table dropped.
DBA_PATRICKJOLLIFFE_at_IMTEST1> CREATE TABLE IMTEST3 AS SELECT 'AAAAAAAAAA' COL1, 'BBBBBBBBBB' COL2, 'CCCCCCCCCC' COL3, 'DDDDDDDDDD' COL4 from dual connect by level < 20000000;
Table created.
DBA_PATRICKJOLLIFFE_at_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_at_IMTEST1> COMMIT;
Commit complete.
DBA_PATRICKJOLLIFFE_at_IMTEST1> ALTER TABLE IMTEST3 INMEMORY PRIORITY HIGH;
Table altered.
DBA_PATRICKJOLLIFFE_at_IMTEST1> SELECT ROUND(bytes/1024/1024) FROM V$SGASTAT WHERE NAME = 'gcs dynamic resources fg';
no rows selected
DBA_PATRICKJOLLIFFE_at_IMTEST1> SELECT COUNT(*) FROM IMTEST3;
COUNT(*)
79999996
1 row selected.
DBA_PATRICKJOLLIFFE_at_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_at_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_at_IMTEST1> ^C
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 16 2016 - 07:52:12 CET