Oracle TEMP Tablespace keeps on increasing on Oracle 11g and 12c
Platform Notice: Server and Data Center Only. This article only applies to Atlassian products on the server and data center platforms.
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.
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
Please note that this is not a bug in Confluence
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
- Verify that the
all_bjects viewis created. If not, please follow this guide to do so: Database Setup for Oracle
- Make sure that you are running a supported Database JDBC Driver
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;
- 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
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
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:
- 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
- JDBC Configuration is causing the database sessions to misbehave
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
<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