[
https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13486993#comment-13486993
]
Stefan Guggisberg commented on JCR-3453:
----------------------------------------
bq. Actually I'm ready to contribute this enhancement to Jackrabbit.
excellent!
bq. If you could modify my attached repository.xml file so that it use
Oracle9FileSystem & Oracle9PersistenceManager and certify that that
configuration is going to work on Oracle 11gR2, I'd like to change this ticket
to improvement.
sorry, i have neither the time nor an oracle install at hand.
> Jackrabbit might deplate the temporary tablespace on Oracle
> -----------------------------------------------------------
>
> Key: JCR-3453
> URL: https://issues.apache.org/jira/browse/JCR-3453
> Project: Jackrabbit Content Repository
> Issue Type: Bug
> Components: jackrabbit-core
> Affects Versions: 2.1.2, 2.5.2
> Environment: Operating system: Linux
> Application server: Websphere v7
> RDBMS: Oracle 11g
> Jackrabbit: V2.1.2 (built into Liferay 6.0 EE SP2)
> Reporter: Laszlo Csontos
> Attachments: repository.xml
>
>
> *** Experienced phenomenon ***
> Our customer reported an issue regarding Liferay’s document library: while
> documents are being retrieved, the following exception occurs accompanied by
> temporary tablespace shortage.
> [9/24/12 8:00:55:973 CEST] 00000023 SystemErr R ERROR
> [org.apache.jackrabbit.core.util.db.ConnectionHelper:454] Failed to execute
> SQL (stacktrace on DEBUG log level)
> java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in
> tablespace TEMP
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> …
> at
> oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
> at
> com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecute(WSJdbcPreparedStatement.java:928)
> at
> com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:614)
> …
> at
> org.apache.jackrabbit.core.util.db.ConnectionHelper.exec(ConnectionHelper.java:328)
> at
> org.apache.jackrabbit.core.fs.db.DatabaseFileSystem.getInputStream(DatabaseFileSystem.java:663)
> at
> org.apache.jackrabbit.core.fs.BasedFileSystem.getInputStream(BasedFileSystem.java:121)
> at
> org.apache.jackrabbit.core.fs.FileSystemResource.getInputStream(FileSystemResource.java:149)
> at
> org.apache.jackrabbit.core.RepositoryImpl.loadRootNodeId(RepositoryImpl.java:556)
> at org.apache.jackrabbit.core.RepositoryImpl.<init>(RepositoryImpl.java:325)
> at org.apache.jackrabbit.core.RepositoryImpl.create(RepositoryImpl.java:673)
> at
> org.apache.jackrabbit.core.TransientRepository$2.getRepository(TransientRepository.java:231)
> at
> org.apache.jackrabbit.core.TransientRepository.startRepository(TransientRepository.java:279)
> at
> org.apache.jackrabbit.core.TransientRepository.login(TransientRepository.java:375)
> at
> com.liferay.portal.jcr.jackrabbit.JCRFactoryImpl.createSession(JCRFactoryImpl.java:67)
> at com.liferay.portal.jcr.JCRFactoryUtil.createSession(JCRFactoryUtil.java:43)
> at com.liferay.portal.jcr.JCRFactoryUtil.createSession(JCRFactoryUtil.java:47)
> at com.liferay.documentlibrary.util.JCRHook.getFileAsStream(JCRHook.java:472)
> at
> com.liferay.documentlibrary.util.HookProxyImpl.getFileAsStream(HookProxyImpl.java:149)
> at
> com.liferay.documentlibrary.util.SafeFileNameHookWrapper.getFileAsStream(SafeFileNameHookWrapper.java:236)
> at
> com.liferay.documentlibrary.service.impl.DLLocalServiceImpl.getFileAsStream(DLLocalServiceImpl.java:192)
> The original size of tablespace TEMP used to be 8Gb when the error has
> occurred for the first time. Later on it was extended by as much as
> additional 7Gb to 15Gb, yet the available space was still not sufficient to
> fulfill subsequent requests and ORA-01652 emerged again.
> *** Reproduction steps ***
> 1) Create a dummy 10MB file
> $ dd if=/dev/urandom of=/path/to/dummy_blob bs=8192 count=1280
> 1280+0 records in
> 1280+0 records out
> 10485760 bytes (10 MB) copied, 0.722818 s, 14.5 MB/s
> 2) Create a temp tablespace
> The tablespace is created with 5Mb and automatic expansion is intentionally
> disabled.
> SQL> CREATE TEMPORARY TABLESPACE jcr_temp
> TEMPFILE '/path/to/jcr_temp_01.dbf'
> SIZE 5M AUTOEXTEND OFF;
> Table created.
> SQL> ALTER USER jcr TEMPORARY TABLESPACE jcr_temp;
> User altered.
> 3) Prepare the test case
> For the sake of simplicity a dummy table is created (similar to Jackrabbit's
> FSENTRY).
> SQL> create table FSENTRY(data blob);
> Table created.
> SQL>
> CREATE OR REPLACE PROCEDURE load_blob
> AS
> dest_loc BLOB;
> src_loc BFILE := BFILENAME('DATA_PUMP_DIR', 'dummy_blob');
> BEGIN
> INSERT INTO FSENTRY (data)
> VALUES (empty_blob())
> RETURNING data INTO dest_loc;
> DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
> DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
> DBMS_LOB.LOADFROMFILE(
> dest_lob => dest_loc
> , src_lob => src_loc
> , amount => DBMS_LOB.getLength(src_loc));
> DBMS_LOB.CLOSE(dest_loc);
> DBMS_LOB.CLOSE(src_loc);
> COMMIT;
> END;
> /
> Procedure created.
> SQL> EXEC load_blob();
> PL/SQL procedure successfully completed.
> 4) Execute the query
> SQL> select nvl(data, empty_blob()) from FSENTRY;
> ERROR: ORA-01652: unable to extend temp segment by 128 in tablespace JCR_TEMP
> 4) Let’s see how temporary tablespace is being used
> Query the size of the original LOB data.
> SELECT s.segment_name,
> s.segment_type,
> SUM(s.blocks) AS blocks
> FROM dba_segments s,
> (SELECT segment_name, index_name
> FROM dba_lobs l
> WHERE l.table_name = 'FSENTRY'
> AND l.column_name = 'DATA') ls
> WHERE s.segment_name = ls.segment_name
> OR s.segment_name = ls.index_name
> GROUP BY s.segment_name, s.segment_type;
> SEGMENT_NAME SEGMENT_TYPE BLOCKS
> ---------------------------------------------------------------------------
> SYS_LOB0000035993C00001$$ LOBSEGMENT 1408
> SYS_IL0000035993C00001$$ LOBINDEX 8
> Query the size of the temporarily created LOB data.
> SELECT s.sql_text, t.segtype, t.blocks
> FROM v$sql s, v$tempseg_usage t
> WHERE s.sql_id = t.sql_id;
> SQL_TEXT SEGTYPE BLOCKS
> ---------------------------------------------------------------------------
> select nvl(data, empty_blob()) from lobtest LOB_DATA 1408 (~11 Mb)
> select nvl(data, empty_blob()) from lobtest LOB_INDEX 128 (~1 Mb)
> LOB index might need some explanation: it's an internal structure that is
> strongly associated with LOB storage (LOB locators point to the top of the
> LOB index tree, where leaf blocks point to the actual LOB chunks). The bottom
> line is that a user may not drop/alter/rebuild LOB indexes in any way.
> As a conclusion we can see here that a temporary LOB has been been created by
> Oracle indeed and its space requirements are quite similar to the original
> one.
> *** Analysis ***
> The aforementioned exception is thrown in the getInputStream(...) method of
> class org.apache.jackrabbit.core.fs.db.DatabaseFileSystem while it’s
> attempting to execute that SQL statement which is denoted by selectDataSQL.
> Based on this particular case, customer has the Jackrabbit repository
> configured to use org.apache.jackrabbit.core.fs.db.OracleFileSystem, which
> implies that the actual value of selectDataSQL is the following.
> SELECT NVL(FSENTRY_DATA, empty_blob())
> FROM J_FSASSSA_1LIFERAYFSENTRY
> WHERE FSENTRY_PATH = :1
> AND FSENTRY_NAME = :2
> AND FSENTRY_LENGTH IS NOT NULL
> The most important point here is that Oracle creates temporary LOBs, if LOB
> columns are used in SQL functions. From the point of view of Oracle, it’s a
> completely logical behaviour, since it has to evaluate the given expression
> and during doing so the database manager also has to store the result of that
> calculation.
> In this case, if column FSENTRY_DATA is null an empty LOB locator is created,
> however it’s unclear why it is functionally required. Interestingly other
> database file system implementations (eg. for DB2) do not use an equivalent
> SQL function at the same place (eg. COALSCE in case of DB2), but they return
> FSENTRY_DATA directly without performing such a pre-processing.
> The second part of the experienced problem is that according to the Oracle
> 11g Database SecureFiles and Large Objects Developer's Guide, if a temporary
> LOB has been returned to the application, it’s the caller (except PL/SQL
> program blocks) responsibility to explicitly free the received object. Having
> this in mind, handling temporary LOBs in a decent way can be accomplished by
> checking and freeing them manually.
> void someMethod() {
> ...
> ResultSet rs = ...
> oracle.sql.BLOB data = (oracle.sql.BLOB) rs.getBlob(...);
> if (data.isTemporary()) {
> data.freeTemporary();
> }
> ...
> }
> Apparently org.apache.jackrabbit.core.fs.db.OracleFileSystem does not take
> care of disposing temporary LOBs and temporary tablespace is being depleted
> this way in a long run.
> *** Conclusion ***
> Jackrabbit definitely should at least free temporary LOBs in OracleFileSystem
> or avoid using the NVL function completely and moving that logic to the
> application instead, would make the whole phenomenon cease to exist.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
