Sorry I didn't notice it earlier: Executing storedprocedures with clob as argument in a tight loop causes temp tablespace to fill up (maybe similar to http://forums.devart.com/viewtopic.php?t=20809).
Here's a small code sample to reproduce the problem with Devart 7.2.114, 7.9.333 and 8.3.115:
Code: Select all
CREATE OR REPLACE PROCEDURE P_DEVART_CLOB_TEST (
P_CONTENT CLOB)
IS
BEGIN
null;
END;
/
Code: Select all
class TempTableSpaceTestDevart
{
private const string SqlSelectTempSpace = @"SELECT nvl(SUM (vsort_usage.blocks) * TBS.block_size / 1024 / 1024,0) mb_used
FROM v$session vsession
LEFT OUTER JOIN v$sort_usage vsort_usage on (vsession.saddr = vsort_usage.session_addr)
LEFT OUTER JOIN dba_tablespaces TBS on (vsort_usage.tablespace = TBS.tablespace_name)
WHERE vsession.SID = sys_context('USERENV','SID')
AND tbs.tablespace_name = 'TEMP'
GROUP BY vsession.SID, TBS.block_size, TBS.tablespace_name";
private const string SqlSpDevArtClobSp = "P_DEVART_CLOB_TEST";
private const string SqlSpDevArtClobSpParamName = "P_CONTENT";
private const int Executions = 500;
private const int StatementsPerLoop = 1000;
private const string ConnectionString = "Data Source=YOURSERVER;Persist Security Info=True;User ID=YOURUSERID;Password=YOURPASSWORD;Pooling=false";
private const int ClobLength = 99000;
static void Main(string[] args)
{
Console.WriteLine("Start {0} Exections, each {1} statements on clob with size {2}", Executions, StatementsPerLoop, ClobLength);
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
string[] spParamValue = new string[StatementsPerLoop];
Parallel.For(0, Executions, current => { spParamValue[current] = RandomString(ClobLength); });
for (int currentLoop = 0; currentLoop < Executions; currentLoop++)
{
Stopwatch stopExecution = Stopwatch.StartNew();
using (OracleTransaction transaction = connection.BeginTransaction())
{
using (OracleCommand command = new OracleCommand(SqlSpDevArtClobSp, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Transaction = transaction;
command.Parameters.Add(new OracleParameter(SqlSpDevArtClobSpParamName,
OracleDbType.Clob,
null,
ParameterDirection.Input));
command.Parameters[SqlSpDevArtClobSpParamName].Value = spParamValue;
command.ExecuteArray(StatementsPerLoop);
}
transaction.Commit();
}
stopExecution.Stop();
int currentSize = TempDbSizeForConnection(connection);
Console.WriteLine("Loop # {0} took {1} ms - {2}Mb temp tablespace",
currentLoop,
stopExecution.ElapsedMilliseconds,
currentSize);
}
}
}
private static string RandomString(int size)
{
Random random = new Random();
char[] characters = new char[size];
Parallel.For(0, size, currentIndex =>
{
characters[currentIndex] = Convert.ToChar(Convert.ToInt32(Math.Floor(26*random.NextDouble() + 65)));
});
return new string(characters);
}
private static int TempDbSizeForConnection(OracleConnection connection)
{
int currentSize = 0;
using (OracleCommand command = new OracleCommand(SqlSelectTempSpace, connection))
{
using (OracleDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
currentSize = reader.GetInt32(reader.GetOrdinal("mb_used"));
}
}
}
return currentSize;
}
}
Code: Select all
Start 100 Exections, each 1000 statements on clob with size 99000
Loop # 0 took 2152 ms - 59Mb temp tablespace
Loop # 1 took 1986 ms - 118Mb temp tablespace
Loop # 2 took 1998 ms - 177Mb temp tablespace
...
Loop # 67 took 2940 ms - 3986Mb temp tablespace
Loop # 68 took 3174 ms - 4044Mb temp tablespace
Unhandled Exception: Devart.Data.Oracle.OracleException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
at Devart.Data.Oracle.a1.c(Int32 A_0)
at Devart.Data.Oracle.ag.b(Int32 A_0, Byte[] A_1, Int32 A_2, Int32 A_3)
at Devart.Data.Oracle.OracleLob.WriteLobCache(Boolean clearLob)
at Devart.Data.Oracle.OracleLob.e()
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, ObjectA_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleTypeA_13, p A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(by& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, p A_5, Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(ak A_0, Int32 A_1, OracleParameterCollection A_2, p A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior,Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at Devart.Data.Oracle.OracleCommand.a(Int32 A_0, Boolean A_1, Int64[]& A_2)
at Devart.Data.Oracle.OracleCommand.ExecuteArray(Int32 iters)
at TempDbTests.TempTableSpaceTestDevart.Main(String[] args)
Code: Select all
Start 100 Exections, each 1000 statements on clob with size 99000
Loop # 0 took 425 ms - 13Mb temp tablespace
Loop # 1 took 277 ms - 13Mb temp tablespace
...
Loop # 98 took 302 ms - 13Mb temp tablespace
Loop # 99 took 284 ms - 13Mb temp tablespace