jackrabbit-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Claus Köll (JIRA) <j...@apache.org>
Subject [jira] [Commented] (JCR-3453) Jackrabbit might deplate the temporary tablespace on Oracle
Date Tue, 30 Oct 2012 12:30:12 GMT

    [ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13486832#comment-13486832
] 

Claus Köll commented on JCR-3453:
---------------------------------

Of course you can use the Oracle9FileSystem. You are right the name is not perfect and not
intuitive. 
It seems that someone has tested against Oracle11 and therefore there are two different FileSystem
Impls but it seems that that the assumption about LOB handling is not right.
                
> 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

Mime
View raw message