Mailing-List: contact dev-help@jackrabbit.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@jackrabbit.apache.org
Date: Tue, 30 Oct 2012 13:18:12 +0000 (UTC)
From: "Laszlo Csontos (JIRA)" <jira@apache.org>
To: dev@jackrabbit.apache.org
Message-ID: <1985061959.44360.1351603092377.JavaMail.jiratomcat@arcas>
In-Reply-To: <1375255183.43995.1351592532277.JavaMail.jiratomcat@arcas>
Subject: [jira] [Commented] (JCR-3453) Jackrabbit might deplate the
 temporary tablespace on Oracle
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/JCR-3453?page=3Dcom.atlassian.j=
ira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D134868=
55#comment-13486855 ]=20

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/RELE=
ASE-NOTES.txt?view=3Dmarkup) 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 cla=
ss if you need support for Oracle 9 or earlier."

However even in Oracle 11g, temporary LOBs must be still freed explicitly, =
as "Oracle=C2=AE Database SecureFiles and Large Objects Developer's Guide 1=
1g 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. With=
out proper de-allocation of the temporary LOBs returned from SQL queries, t=
emporary tablespace is filled and you may observe performance degradation."

Regards,
Laszlo
               =20
> 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=E2=80=99s document libra=
ry: while documents are being retrieved, the following exception occurs acc=
ompanied by temporary tablespace shortage.
> [9/24/12 8:00:55:973 CEST] 00000023 SystemErr     R ERROR [org.apache.jac=
krabbit.core.util.db.ConnectionHelper:454] Failed to execute SQL (stacktrac=
e 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)
> =E2=80=A6
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePrepar=
edStatementWrapper.java:1374)
> at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecute(WSJdbcPre=
paredStatement.java:928)
> at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPrepar=
edStatement.java:614)
> =E2=80=A6
> at org.apache.jackrabbit.core.util.db.ConnectionHelper.exec(ConnectionHel=
per.java:328)
> at org.apache.jackrabbit.core.fs.db.DatabaseFileSystem.getInputStream(Dat=
abaseFileSystem.java:663)
> at org.apache.jackrabbit.core.fs.BasedFileSystem.getInputStream(BasedFile=
System.java:121)
> at org.apache.jackrabbit.core.fs.FileSystemResource.getInputStream(FileSy=
stemResource.java:149)
> at org.apache.jackrabbit.core.RepositoryImpl.loadRootNodeId(RepositoryImp=
l.java:556)
> at org.apache.jackrabbit.core.RepositoryImpl.<init>(RepositoryImpl.java:3=
25)
> at org.apache.jackrabbit.core.RepositoryImpl.create(RepositoryImpl.java:6=
73)
> at org.apache.jackrabbit.core.TransientRepository$2.getRepository(Transie=
ntRepository.java:231)
> at org.apache.jackrabbit.core.TransientRepository.startRepository(Transie=
ntRepository.java:279)
> at org.apache.jackrabbit.core.TransientRepository.login(TransientReposito=
ry.java:375)
> at com.liferay.portal.jcr.jackrabbit.JCRFactoryImpl.createSession(JCRFact=
oryImpl.java:67)
> at com.liferay.portal.jcr.JCRFactoryUtil.createSession(JCRFactoryUtil.jav=
a:43)
> at com.liferay.portal.jcr.JCRFactoryUtil.createSession(JCRFactoryUtil.jav=
a:47)
> at com.liferay.documentlibrary.util.JCRHook.getFileAsStream(JCRHook.java:=
472)
> at com.liferay.documentlibrary.util.HookProxyImpl.getFileAsStream(HookPro=
xyImpl.java:149)
> at com.liferay.documentlibrary.util.SafeFileNameHookWrapper.getFileAsStre=
am(SafeFileNameHookWrapper.java:236)
> at com.liferay.documentlibrary.service.impl.DLLocalServiceImpl.getFileAsS=
tream(DLLocalServiceImpl.java:192)
> The original size of tablespace TEMP used to be 8Gb when the error has oc=
curred for the first time. Later on it was extended by as much as additiona=
l 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=3D/dev/urandom of=3D/path/to/dummy_blob bs=3D8192 count=3D1280
> 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 intentional=
ly disabled.
> SQL> CREATE TEMPORARY TABLESPACE jcr_temp
> =09TEMPFILE '/path/to/jcr_temp_01.dbf'
> =09SIZE 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 Jackrabbi=
t's FSENTRY).
> SQL> create table FSENTRY(data blob);
> Table created.
> SQL>
> CREATE OR REPLACE PROCEDURE load_blob
> AS
>     dest_loc  BLOB;
>     src_loc   BFILE :=3D 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 =3D> dest_loc
>         , src_lob  =3D> src_loc
>         , amount   =3D> 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=E2=80=99s 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 =3D 'FSENTRY'
>            AND l.column_name  =3D 'DATA') ls
>  WHERE s.segment_name =3D ls.segment_name
>     OR s.segment_name =3D 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 =3D t.sql_id;
> SQL_TEXT                                        SEGTYPE      BLOCKS
> -------------------------------------------------------------------------=
--
> select nvl(data, empty_blob()) from lobtest     LOB_DATA     1408  (~11 M=
b)
> 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 bot=
tom 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 create=
d by Oracle indeed and its space requirements are quite similar to the orig=
inal one.
> *** Analysis ***
> The aforementioned exception is thrown in the getInputStream(...) method =
of class org.apache.jackrabbit.core.fs.db.DatabaseFileSystem while it=E2=80=
=99s attempting to execute that SQL statement which is denoted by selectDat=
aSQL.
> Based on this particular case, customer has the Jackrabbit repository con=
figured to use org.apache.jackrabbit.core.fs.db.OracleFileSystem, which imp=
lies that the actual value of selectDataSQL is the following.
> SELECT NVL(FSENTRY_DATA, empty_blob())
>   FROM J_FSASSSA_1LIFERAYFSENTRY
>  WHERE FSENTRY_PATH  =3D :1
>    AND FSENTRY_NAME    =3D :2
>    AND FSENTRY_LENGTH IS NOT NULL
> The most important point here is that Oracle creates temporary LOBs, if L=
OB columns are used in SQL functions. From the point of view of Oracle, it=
=E2=80=99s a completely logical behaviour, since it has to evaluate the giv=
en expression and during doing so the database manager also has to store th=
e result of that calculation.
> In this case, if column FSENTRY_DATA is null an empty LOB locator is crea=
ted, however it=E2=80=99s unclear why it is functionally required. Interest=
ingly other database file system implementations (eg. for DB2) do not use a=
n equivalent SQL function at the same place (eg. COALSCE in case of DB2), b=
ut they return FSENTRY_DATA directly without performing such a pre-processi=
ng.
> The second part of the experienced problem is that according to the Oracl=
e 11g Database SecureFiles and Large Objects Developer's Guide, if a tempor=
ary LOB has been returned to the application, it=E2=80=99s the caller (exce=
pt PL/SQL program blocks) responsibility to explicitly free the received ob=
ject. Having this in mind, handling temporary LOBs in a decent way can be a=
ccomplished by checking and freeing them manually.
> void someMethod() {
>   ...
>   ResultSet rs =3D ...
>   oracle.sql.BLOB data =3D (oracle.sql.BLOB) rs.getBlob(...);
>   if (data.isTemporary()) {
>     data.freeTemporary();
>   }
>   ...
> }
> Apparently org.apache.jackrabbit.core.fs.db.OracleFileSystem does not tak=
e care of disposing temporary LOBs and temporary tablespace is being deplet=
ed this way in a long run.
> *** Conclusion ***
> Jackrabbit definitely should at least free temporary LOBs in OracleFileSy=
stem or avoid using the NVL function completely and moving that logic to th=
e 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 administrato=
rs
For more information on JIRA, see: http://www.atlassian.com/software/jira