You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jackrabbit.apache.org by "Laszlo Csontos (JIRA)" <ji...@apache.org> on 2012/10/30 11:22:12 UTC
[jira] [Created] (JCR-3453) Jackrabbit might deplate the temporary
tablespace on Oracle
Laszlo Csontos created JCR-3453:
-----------------------------------
Summary: 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
Affects Versions: 2.5.2, 2.1.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
*** 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
[jira] [Updated] (JCR-3453) Jackrabbit might deplate the temporary
tablespace on Oracle
Posted by "Claus Köll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Claus Köll updated JCR-3453:
----------------------------
Component/s: jackrabbit-core
> 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
>
> *** 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
[jira] [Updated] (JCR-3453) Jackrabbit might deplate the temporary
tablespace on Oracle
Posted by "Laszlo Csontos (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Laszlo Csontos updated JCR-3453:
--------------------------------
Description:
*** 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.
was:
*** 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.
> 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
> 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
>
> *** 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
[jira] [Commented] (JCR-3453) Jackrabbit might deplate the
temporary tablespace on Oracle
Posted by "Laszlo Csontos (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13486943#comment-13486943 ]
Laszlo Csontos commented on JCR-3453:
-------------------------------------
Hi Stefan,
In the Javadoc you mentioned there is something important: "The distinction between file and folder entries is based on FSENTRY_LENGTH being null/not null rather than FSENTRY_DATA being null/not null because FSENTRY_DATA of a 0-length (i.e. empty) file is null in Oracle."
org.apache.jackrabbit.core.fs.db.DatabaseFileSystem#getInputStream(...) searches for a file entry by using the following query.
SELECT NVL(FSENTRY_DATA, empty_blob())
FROM J_FSASSSA_1LIFERAYFSENTRY
WHERE FSENTRY_PATH = :1
AND FSENTRY_NAME = :2
AND FSENTRY_LENGTH IS NOT NULL
This way FSENTRY_LENGTH is checked for not being NULL, thus a proper distinction can be made between files and folders. So I do not see any other reason for applying NVL to FSENTRY_DATA, but to ensure that getBinaryStream(...) return a valid input stream instead of null.
If doing so is indeed a functional requirement, I think I/O performance improvement could be made, if the query above were re-written in the following way ...
SELECT FSENTRY_DATA, NVL2(FSENTRY_DATA, 0, 1) AS IS_EMPTY
FROM J_FSASSSA_1LIFERAYFSENTRY
WHERE FSENTRY_PATH = :1
AND FSENTRY_NAME = :2
AND FSENTRY_LENGTH IS NOT NULL
... and emptiness of FSENTRY_DATA were handled within the filesystem implementation logic (practically in OracleFileSystem) instead at SQL level.
Actually I'm ready to contribute this enhancement to Jackrabbit.
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.
Thanks,
Laszlo
> 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
[jira] [Commented] (JCR-3453) Jackrabbit might deplate the
temporary tablespace on Oracle
Posted by "Stefan Guggisberg (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Updated] (JCR-3453) Jackrabbit might deplate the temporary
tablespace on Oracle
Posted by "Laszlo Csontos (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Laszlo Csontos updated JCR-3453:
--------------------------------
Description:
*** 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.
was:
*** 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.
> 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
> 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
>
> *** 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
[jira] [Commented] (JCR-3453) Jackrabbit might deplate the
temporary tablespace on Oracle
Posted by "Stefan Guggisberg (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13486897#comment-13486897 ]
Stefan Guggisberg commented on JCR-3453:
----------------------------------------
FWIW:
bq. * Actually why do you need to use NVL(...) in the column list? Other DB filesystem implementations do not have this workaround.
because oracle is AFAIK the only rdbms which doesn't distinguish empty strings or empty lob's from NULL...
for more detailed information have a look at the javadoc ([0]).
[0] http://jackrabbit.apache.org/api/2.1/org/apache/jackrabbit/core/fs/db/OracleFileSystem.html#buildSQLStatements()
> 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
[jira] [Commented] (JCR-3453) Jackrabbit might deplate the
temporary tablespace on Oracle
Posted by "Claus Köll (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (JCR-3453) Jackrabbit might deplate the
temporary tablespace on Oracle
Posted by "Laszlo Csontos (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13486855#comment-13486855 ]
Laszlo Csontos commented on JCR-3453:
-------------------------------------
Hi Claus,
I'm a little bit confused about using Oracle9FileSystem or OracleFileSystem. It seems that Oracle9FileSystem has been introduced in Jackrabbit 2.0, as the release notes (http://svn.apache.org/viewvc/jackrabbit/tags/2.0.0/RELEASE-NOTES.txt?view=markup) states the following:
"OracleFileSystem class does not use special blob handling anymore as it is not required for Oracle versions since 10R1. Use the Oracle9FileSystem class if you need support for Oracle 9 or earlier."
However even in Oracle 11g, temporary LOBs must be still freed explicitly, as "Oracle® Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2)" (http://docs.oracle.com/cd/E11882_01/appdev.112/e18294.pdf) states, page #240:
"In OCI and Java, the returned temporary LOB must be explicitly freed. Without proper de-allocation of the temporary LOBs returned from SQL queries, temporary tablespace is filled and you may observe performance degradation."
Regards,
Laszlo
> 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
[jira] [Commented] (JCR-3453) Jackrabbit might deplate the
temporary tablespace on Oracle
Posted by "Claus Köll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13486782#comment-13486782 ]
Claus Köll commented on JCR-3453:
---------------------------------
Whats your configuration in the repository.xml ? Have you looked at Oracle9FileSystem ?
After short look i can see that there the Blobs are freed correctly.
> 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
>
> *** 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
[jira] [Updated] (JCR-3453) Jackrabbit might deplate the temporary
tablespace on Oracle
Posted by "Laszlo Csontos (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Laszlo Csontos updated JCR-3453:
--------------------------------
Attachment: repository.xml
Hi,
Thanks for your quick response.
I've just looked into Oracle9FileSystem and it uses some helper class Oracle10R1ConnectionHelper, which calls freeTemporary(...) though reflection.
To be honest, I did not consider Oracle9FileSystem as a viable replacement in place of OracleFileSystem, because of its version number (our customer is using 11g), but at a glance Oracle10R1ConnectionHelper looks like a proper solution.
* Can I recommend Oracle9FileSystem to our customer?
* Actually why do you need to use NVL(...) in the column list? Other DB filesystem implementations do not have this workaround.
Regards,
Laszlo
> 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