Resolution
Access Governance Suite makes used of CLOBs within it's schema to support extensible requirements for managing customer data. The CLOB in many cases allows a flexible modeling of data without requiring a lengthy schema design phase when deploying.
Within Oracle, CLOBs can be treated differently than other table column values. CLOBs are typically not indexed and if they exceed 3964 characters in size they are stored in an external segment and not the table. The de-facto schema for Access Governance Suite doesn't specify management values around CLOBs, the table below represents the identity object structure within the database.
table identityiq.spt_link (
id varchar2(128 char) not null,
created number(19,0),
modified number(19,0),
owner varchar2(128 char),
assigned_scope varchar2(128 char),
assigned_scope_path varchar2(450 char),
key1 varchar2(450 char),
key2 varchar2(255 char),
key3 varchar2(255 char),
key4 varchar2(255 char),
extended1 varchar2(450 char),
extended2 varchar2(450 char),
extended3 varchar2(450 char),
extended4 varchar2(450 char),
extended5 varchar2(450 char),
display_name varchar2(128 char),
instance varchar2(128 char),
native_identity varchar2(256 char) not null,
last_refresh number(19,0),
last_target_aggregation number(19,0),
manually_correlated number(1,0),
identity_id varchar2(128 char),
application varchar2(128 char),
attributes clob,
component_ids varchar2(256 char),
attribute_meta_data clob,
idx number(10,0),
primary key (id)
);
You'll notice in the DDL for Access Governance Suite, that CLOB columns are used in a multitude of tables. Without special treatment of this information we can quickly get into a performance quagmire.
If in this one table the value for attributes is less than 3964, than we are able to take advantage of Oracle caching to store and retrieve the data. If however this value is exceeded, then oracle throws the entire value for a given table entry into an automatically allocated segment. The end result is a segment with a file reference in the table, but no caching of this information on the Oracle side.
To overcome this the CLOB values can be managed more efficiently. Doing so though requires altering the schema and may require a rebuild of the database. In the example below the identity link table is altered to improve performance:
TABLE IDENTITYIQ.SPT_LINK
CLOB (ATTRIBUTES)
STORE AS BASICFILE identityiq_link_attrs
(TABLESPACE identityiq_link_attrs_clob
CHUNK 16384
PCTFREE 10
CACHE READS
NOLOGGING
STORAGE (MINEXTENTS 2)
INDEX identityiq_link_attrs_idx (
TABLESPACE identityiq_clob_index
STORAGE (MAXEXTENTS UNLIMITED)));
Here two additional tablespaces are in use. One 'identityiq_clob_index' is a reserved tablespace for CLOB indexing. The other tablespace 'identityiq_link_attrs' is specific to this one instance of the clob attribute. From a best practice perspective separate I/O paths for the clob tablespace and index are preferred. The BASICFILE directive indicates the storage method for the CLOB data. The CHUNK value allows tuning of the I/O impact to retrieving components of the CLOB. Ideally the CHUNK would encompass the entire value of the clob for a record.
Other tables within Access Governance Suite can benefit from the adjustment depending on the nature of the stored data and the usage of the system. Typically when making this adjustment the tables that would be impacted include:
SPT_CERTIFICATION
SPT_CERTIFICATION_ENTITY
SPT_CERTIFICATION_ITEM
SPT_ENTITLEMENT_SNAPSHOT
SPT_ACCOUNT_GROUP
SPT_ENTITLEMENT_GROUP
SPT_IDENTITY_SNAPSHOT
SPT_JASPER_PAGE_BUCKET
SPT_JASPER_RESULT