create table x (a number, b varchar2(10));
SQL> insert into x values(1,'a');
1 row created.
SQL> insert into x values (2,'b');
1 row created.
SQL> insert into x values(1,'c');
1 row created.
SQL> insert into x values(2,'d');
1 row created.
SQL> commit;
SQL> select a,wm_concat(b) as concats from x group by a;
A CONCATS
---------- ----------
1 a,c
2 b,d
However wm_concat is an undocumented function not for direct use by customers (more on this later on) but that doesn't prevent developers from using this function.However there's a danger that extensive invocation of this function could exhaust the temporary tablespace. Primary reason for this is that wm_concat has a sort aggregation in it that result in temporary tablespace segments being used
SQL> explain plan for select wm_concat('abc'||'def') from dual;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 3910148636
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
(no matter what, tested db instance had 7G SGA, 3G PGA and this was the only test case running, plenty of memory to do the sort in memory) but these are not released until the connection is physically close, which means exit from sql*plus or closing a connection pool in a the case of jdbc connection pools. This effect could be demonstrated with following test case using 11gR2 (11.2.0.3.3) Linux 64-bit instance.Create a temporary tablespace of 5M and assign it as the default temporary tablespace for the test user
create temporary tablespace temp5m TEMPFILE size 5m autoextend on next 1m maxsize 5m; alter user asanga temporary tablespace temp5m;Run the sql with wm_concat from 5 different sql*plus session and monitor the temporary tablespace segment usage.
Temporary tablespace usage before test starts
SELECT A.inst_id,
A.tablespace_name TABLESPACE,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM gv$sort_segment A,
(SELECT B.INST_ID,
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM gv$tablespace B,
gv$tempfile C
WHERE B.ts# = C.ts#
AND c.inst_id=b.inst_id
GROUP BY B.INST_ID,
B.name,
C.block_size
) D
WHERE A.tablespace_name = D.name
and a.tablespace_name='TEMP5M'
AND A.inst_id =D.inst_id
GROUP BY a.inst_id,
A.tablespace_name,
D.mb_total
ORDER BY 1,2;
INST_ID TABLESPACE MB_TOTAL MB_USED MB_FREE
-------- ---------- ---------- ---------- ----------
1 TEMP5M 5 0 5
Run sql on first sql*plus sessionSQL> select wm_concat('abc'||'def') as X from dual;
X
--------
abcdef
Temporary segment usage INST_ID TABLESPACE MB_TOTAL MB_USED MB_FREE
-------- ---------- ---------- ---------- ----------
1 TEMP5M 5 1 4
The above sql could be run on the same sql*plus session multiple times without increasing the temporary segment usage. As soon as the sql is run from a different sql*plus session the temporary segment usage increase. After running on second sql*plus session INST_ID TABLESPACE MB_TOTAL MB_USED MB_FREE
-------- ---------- ---------- ---------- ----------
1 TEMP5M 5 2 3
Similarly 4 sql*plus session could be used to run the above mentioned sql and at the end of running the sql on 4th sqlplus session temporary segment usage will be INST_ID TABLESPACE MB_TOTAL MB_USED MB_FREE
-------- ---------- ---------- ---------- ----------
1 TEMP5M 5 4 1
Running on the 5th sql*plus session will result in the following errorSQL> select wm_concat('abc'||'def') as X from dual;
ERROR:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP5M
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 31
Use the following sql to verify the sql statments using temporary segments (SQL from 317441.1) SQL> SELECT a.username,
2 a.sid,
3 a.serial#,
4 a.osuser,
5 b.tablespace,
6 b.blocks,
7 b.segtype,
8 c.sql_text
9 FROM v$session a,
10 v$tempseg_usage b,
11 v$sqlarea c
12 WHERE a.saddr = b.session_addr
13 AND c.address = a.sql_address
14 AND c.hash_value = a.sql_hash_value
15 ORDER BY b.tablespace,
16 b.blocks;
USERNA SID SERIAL# OSUSER TABLESPACE BLOCKS SEGTYPE SQL_TEXT
------ ---------- ---------- ------ ---------- ---------- --------- --------------------------------------------------
ASANGA 9 6487 oracle TEMP5M 128 LOB_DATA select wm_concat('abc'||'def') as X from dual
ASANGA 202 209 oracle TEMP5M 128 LOB_DATA select wm_concat('abc'||'def') as X from dual
ASANGA 136 3207 oracle TEMP5M 128 LOB_DATA select wm_concat('abc'||'def') as X from dual
ASANGA 78 377 oracle TEMP5M 128 LOB_DATA select wm_concat('abc'||'def') as X from dual
The temporary segments will not be released until sql*plus session is close (by exit). Even though the table has no LOB column the temp segments are shown as LOB_DATA. Lobs are used within the wm_concat it is why the temp segment appear as LOB_DATA.On java applications that use connection pool, (UCP or DataSource) connections are not physically closed when the connection close function is called. The close is a logical close and physical connection remains open in the connection pool ready to be used by another session. As such the temporary segment usage will keep on increasing until the temporary tablespace is exhausted since connection pool is never closed except when the application (or application server in most cases) is restarted,dies or crashed.
Following java code could be used to demonstrate how this could affect java application using connection pool. Class imports are not shown
public class DBConnection {
private PoolDataSource pds = null;
public DBConnection(String username,String password, String host,String port, String sid) {
try {
pds = PoolDataSourceFactory.getPoolDataSource();
pds.setUser(username);
pds.setPassword(password);
pds.setURL("jdbc:oracle:thin:@"+host+":"+port+":"+sid);
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setInitialPoolSize(10);
pds.setMinPoolSize(10);
pds.setMaxPoolSize(15);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return pds.getConnection();
}
}
public class TempSegThread extends Thread {
private DBConnection pool;
public TempSegThread(DBConnection pool) {
this.pool = pool;
}
@Override
public void run() {
try {
for (int i = 0; i < 1; i++) {
int j = 0;
Connection con = pool.getConnection();
PreparedStatement pr = con.prepareStatement("select wm_concat('abc'||'def') from dual");
ResultSet rs = pr.executeQuery();
while (rs.next()) {
System.out.println(getName() +" "+ rs.getString(1));
}
rs.close();
pr.close();
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(TempSegThread.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
public class ThreadExec {
public static void main(String[] args) {
// if (args.length != 5) {
//
// System.out.println("usage: java ThreadExec username password host port sid");
// } else {
try {
DBConnection pool = new DBConnection("asanga","asa","192.168.0.66","1521","ent11g2");
//Change to TempSegThread[4] to run without unable to extend temp segment
TempSegThread [] tempThread = new TempSegThread[5];
for(int i = 0 ; i < tempThread.length; i++){
tempThread[i] = new TempSegThread(pool);
tempThread[i].start();
}
for(TempSegThread t : tempThread){
t.join();
}
Thread.sleep(5 * 60 * 60 * 1000);
} catch (Exception ex) {
ex.printStackTrace();
}
// }
}
}
Program run the same sql used in sql*plus test and run it in 5 threads using 5 different connections. One of the threads will fail with unable to extend temporary segment while others finish without error. Changing the thread count to 4 will allow 4 threads to run without error and at the end of their execution program will sleep for 5 minutes giving enough time to execute temporary segment usage SQL to see that temporary segments are still being held and usage hasn't gone down even after connections are closed.As mentioned in metalink note 1384829.1 using "60025 trace name context forever" is of no help in this case even though temporary segments are classified as lob_data.
Oracle has mentioned in many documents that wm_concat "is not meant to be used by customers directly, and could be changed/updated without notice by Oracle Development. Do not use the WMSYS.WM_CONCAT view in your application" (From 1300595.1). So the best case is not to use it as if there's any issue it's unlikely this would qualify for support. (When a SR was raised this is exactly what Oracle said, wm_concat is not for end users).
The other versions tested 11gR1 (11.1.0.7.12) didn't have this issue but 10gR2 (10.2.0.5.8) did have the same issue as 11gR2.
Useful metalink notes
WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function [ID 1336219.1]
Problem with WMSYS.WM_CONCAT Function after Upgrading [ID 1300595.1]
SQL USING WM_CONCAT RUNS SLOWER IN 10.2.0.5 and 11.2.0.2 [ID 1393596.1]
How to Release Temporary LOB Segments without Closing the JDBC Connection [ID 1384829.1]
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 [ID 802897.1]
Related Post
WM_CONCAT Removed From 12c Workspace Manager?
