000035616 - RSA Identity Governance and Lifecycle database exhibits poor performance then displays an ORA-01654 error

Document created by RSA Customer Support Employee on Oct 18, 2017
Version 1Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000035616
Applies ToRSA Product Set: Identity Governance and Lifecycle
RSA Version/Condition: 7.0.2
 
IssueWhile performing bulk data collection or provisioning, a significant drop in performance is observed.  For example, instead of taking minutes, provisioning takes tens of minutes or hours, and then the following error is reported and then bulk provisioning fails:

ORA-01654: unable to extend index AVUSER.<object name> by 8192 in tablespace <tablespace name>

While the <object name> reported in the ORA-01654 error can be either an AVUSER table or index, the <tablespace name> should be one of the following standard AVUSER Tablespace names.

Description  Tablespace Name
-------------------------------- ------------------------
DATA 256K Tablespace  :DATA_256K
DATA 1M Tablespace    :DATA_1M
DATA 25M Tablespace   :DATA_25M
DATA 50M Tablespace   :DATA_50M
INDEX 256K Tablespace :INDX_256K
INDEX 1M Tablespace   :INDX_1M
INDEX 25M Tablespace  :INDX_25M
INDEX 50M Tablespace  :INDX_50M
CauseThe primary reason for poor performance followed by the ORA-01654 error is that the datafiles for the AVUSER tablespaces were undersized.
The ORA-01654: unable to extend ... error indicates that the datafile has been growing until it ran out of space.   Extending a datafile is an I/O intensive operation, and this would affect the performance of any database operation that adds data to the database.
A secondary issue may be that the physical disk or ASM disk group has run out of space.
The following explanation is taken from the Oracle Support Note 146595.1 - Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s (To access this note in full, an Oracle Support login is required):

This error does not necessarily indicate whether or not you have enough space in the tablespace, it merely indicates that Oracle could not find a large enough area of free contiguous space in which to fit the next extent.
ResolutionTo solve this issue, the Oracle database needs to be modified, where this is typically performed by an Oracle DBA.
The following solutions are taken from the Oracle Support Note 146595.1 - Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s (To access this note in full, an Oracle Support login is required):
Possible solutions: 
  • Manually coalesce adjacent free extents:



ALTER TABLESPACE <tablespace name> COALESCE;


The extents must be adjacent to each other for this to work.

  • Add a datafile:

ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> <k|m>;

  • Resize the datafile:

ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>;

  • Enable autoextend:

ALTER DATABASE DATAFILE '<full path and file name>' 
AUTOEXTEND ON
MAXSIZE UNLIMITED;

  • Defragment the Tablespace
  • Lower "next_extent" and/or "pct_increase" size:

ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m>
pctincrease <integer>);


 

The following SQL statements may also assist when diagnosing Tablespace space issues.

  • Show Tablespace settings:

SELECT tablespace_name, contents, allocation_type, extent_management, segment_space_management FROM dba_tablespaces;

  • Show the bytes used by the datafiles for a given Tablespace.

col bytes format 999999999999999999
col maxbytes format 999999999999999999
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name='<tablespace name>';
Notes

Notes on resizing


The actual amount to add or resize the datafile depends on data volumes.  As data volumes vary from customer to customer, a specific recommendation cannot be provided.  So, in general, try increasing the existing datafile size by 50% to 100%.
However, a Aveksa Statistics Report (ASR) can be generated to determine existing data volumes. 
  1. Examine the Identity Data Collector Information in the ASR, where the No of Collected Users will tell you how many people the application has collected. 
  2. Then, predict how many more people still need to be collected, then use that as a factor when resizing the Oracle database datafiles.
For example, if 100,000 users have already been collected, and the DATA_1M tablespace exceeded 100Mb, but another 100,000 users still need to be collected, increase the size allocated to the DATA_1M tablespace to 300Mb (to allow for growth after bulk collection and provisioning).

Attachments

    Outcomes