Temp table space

Temp table space

Postby klaus linzner » Sat 08 Mar 2014 13:40

Hello again,
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;
    }
}


The code results in the following output:
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)


Although not sure I don't think it's an environment / misconfiguration problem. Pretty much the same code (except changes for array execution) compiled against Oracle.DataAccess runs well and gives the following output:
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


Anyway - the application is run as 64bit .NET 4.5 assembly on Server 2008 R2 against Oracle 11.2.0.3 database (running on Linux as far as I know)
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Temp table space

Postby Pinturiccio » Wed 12 Mar 2014 14:25

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44

Re: Temp table space

Postby Pinturiccio » Wed 26 Mar 2014 12:22

We have fixed the bug with clearing temporary tablespace when inserting multiple CLOB, NCLOB, or BLOB values using array binding in the OCI mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44

Re: Temp table space

Postby Pinturiccio » Thu 27 Mar 2014 14:51

New build of dotConnect for Oracle 8.3.125 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=29243
Pinturiccio
Devart Team
 
Posts: 1955
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle