Oracle TEMP Tablespace keeps on increasing on Oracle 11g and 12c

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Problem

(warning) This problem is very specific and you will most probably not encounter it. However, in case you are experiencing it, please continue reading

Oracle TEMP tablespace keeps on increasing at a rapid rate without any significant pattern. The TEMP tablespace will only be reduced if you restart the application.

Diagnosis

Environment

Oracle uses TEMP tablespace in order to store data used while executing database queries. It is heavily used for table scans, joins and sorting. All this data is stored in this TEMP tablespace and then cleared out once the database connection session is destroyed.

The problem happens on Confluence 5.10.8 with Oracle 12c database and if you have JDBC Datasource on Tomcat level configured (defined in either server.xml or context.xml)

(warning) Please note that this is not a bug in Confluence


Diagnostic Steps

If you see this behavior, there several steps that you need to check before confirming the problem, as this could be related to many other issues. Here's a checklist of what you need to check

  1. Verify that the all_bjects view is created. If not, please follow this guide to do so: Database Setup for Oracle
  2. Make sure that you are running a supported Database JDBC Driver
  3. You will need to understand what is being stored in the TEMP tablespace. Please use the below query to understand that

    SELECT   S.sid || ',' || S.serial# sid_serial, 
             S.username,
             T.blocks * 8192 / 1024 / 1024 mb_used, 
             T.tablespace,
             Q.sql_fulltext, 
             q.sql_id
    FROM v$sort_usage T
      join v$session S on T.session_addr = S.saddr
      left join v$sqlarea Q on T.sqladdr = Q.address
    ORDER BY mb_used desc, sid_serial;
  4. Check for frequent queries in that list and check whether those are linked to a specific plugin. If yes, try to disable that plugin for a day or two and monitor the size of the TEMP tablespace
  5. You will need to check the objects held in the TEMP tablespace. You can use the below query to inspect those objects as well as the status of those sessions

    select 
       srt.tablespace, 
       srt.segfile#, 
       srt.segblk#, 
       srt.blocks, 
       a.sid, 
       a.serial#, 
       a.username, 
       a.osuser, 
       a.status 
    from 
       v$session    a,
       v$sort_usage srt 
    where 
       a.saddr = srt.session_addr 
    order by 
       srt.tablespace, srt.segfile#, srt.segblk#, 
       srt.blocks;

    P.S. Sessions with status INACTIVE will not be freed from the TEMP tablespace

 

Cause

If you went through the above checklist and the problem is still occurring, this means that we have something going wrong in the JDBC configuration. It could be one of the following:

  1. Database Connections are way less than Tomcat max threads. This will keep database sessions open in order to serve more requests. Those sessions will be holding the objects in the TEMP tablespace and you will see it growing over time
  2. JDBC Configuration is causing the database sessions to misbehave

Resolution

To resolve this issue, you will need to change the JDBC database configuration. If you are using a JDBC Datasource at Tomcat level, revert back to the default Hibernate JDBC configuration found in confluence.cfg.xml

	<property name="hibernate.c3p0.acquire_increment">1</property>
    <property name="hibernate.c3p0.idle_test_period">100</property>
    <property name="hibernate.c3p0.max_size">48</property>
    <property name="hibernate.c3p0.max_statements">0</property>
    <property name="hibernate.c3p0.min_size">20</property>
    <property name="hibernate.c3p0.timeout">30</property>
    <property name="hibernate.connection.SetBigStringTryClob">true</property>
    <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="hibernate.connection.isolation">2</property>
    <property name="hibernate.connection.password">*******</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@ORACLE_DB_HOSTNAME:ORACLE_DB_PORT/SID</property>
    <property name="hibernate.connection.username">confluence-db-user</property>
    <property name="hibernate.dialect">net.sf.hibernate.dialect.OracleIntlDialect</property>
    <property name="hibernate.setup">true</property>

If you are already using the Hibernate JDBC configuration, change it to a JDBC Datasource on Tomcat level

Configuring an Oracle Datasource in Apache Tomcat

 

Last modified on May 2, 2017

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.