1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle TEMP tablespaces in RAC environment

Discussion in 'Server Administration and Options' started by prashant, Jun 16, 2011.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    We have just one TEMPORARY tablespace "TEMP" in our 2-node 10gR2 (Sun Sparc) RAC database.

    The size of the only tempfile in our ASM is 64GB.

    When i query gv$sort_segment it shows that each of the instance is allocated a total of nearly 32GB.

    Also, i learnt from some metalink notes that an instance can use sort segment space from the other node if its local temp segment space is full and the space is available in other node(s).

    So, is Oracle dividing one single file between 2 instances for its processing?

    I would be thankful if someone can throw some light on shared TEMP tablespace in a RAC environment, and how it works.

    The reason is , we have hit ORA-1652 a few days back in production for which we need to analyze what went wrong and what preventive & corrective measures can be taken for the same.

    Thank you.

    Best Regards,
    Prashant
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    ORA-01652 means what it says: you've used all of the allocated temp space and need to allocate more. Oracle is not dividing your temp tablespace in half; what you are seeing in gv$sort_segment is the per-instance usage which just happens to be half of the total allocated space. Each instance can access all of the temp tablespace if that space is available. Instances 'own' the temp segments as long as they are using them and release them when they are done. There were bugs in 9iR2 that would affect RAC but those have been fixed in the release you're using. You simply need to allocate more temp space as you've exhausted all of the available bytes.
     
  3. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi David,

    Thank you for your Response.

    As per Oracle Metalink Note 280578.1

    If instances own temp segments as long as they are using them and release once done, then as per scenario 2 mentioned in metalink note why is there a need of inter-node co-ordination? Also, if the allocation is not fixed for each node, how does instances co-ordinate for space requirements?

    Thank you in advance.

    Best Regards,
    Prashant
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Inter-node coordination always occurs in a RAC environment due to cache fusion (the temp blocks are in the global cache). If another instance has freed the temp blocks it was using but the global cache has not been updated another instance in the RAC environment will need to wait for the resources to be released however this shouldn't result in ORA-01652 errors as it should increase the wait time statistic for physical writes direct temporary tablespace and increase the time the query takes to complete. After testing on a three-node RAC with no used blocks from any instance running the following query:

    select * from dba_objects a, dba_objects b, dba_objects c order by 1;

    I see that the temporary tablespace usage is not tied to a specific instance as Oracle uses all currently allocated blocks to an instance, allocates any unallocated blocks to that instance then allocates blocks once used by the other instances to the current query and keeps running until the space is physically exhausted:

    Code (SQL):
    PSP_LT DBA > SELECT inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
      2  FROM gv$sort_segment;
     
       INST_ID TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
    ---------- ------------------------------- ------------ ----------- -----------
             1 TEMP                                 3645440     3645440           0
             3 TEMP                                       0           0           0
             2 TEMP                                 1597184           0     1597184
    PSP_LT DBA >
    The query is still running as I write this and has yet to generate an ORA-01652 error. Such an error will not be generated until all space is exhausted meaning that one instance has consumed it all or it has been consumed across all RAC nodes. I am testing on 10.2.0.3 so any release later than that should also behave in the same manner.
     
    prashant likes this.
  5. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi David,

    Thank you for your response.

    After analysing for sometime the problem came out to be a bit complex.

    Here is the scenario:-

    We have 8 JDBC connections (pooling) from our application to the database. Our Java code invokes a procedure (part of a plsql package) for some processing in which it creates TEMPORARY LOB and populates it. Once the LOB is completely populated its contents are fetched by Java code(java.sql package). These TEMP LOBs persist in the database because:-

    a) JDBC sessions are persistent
    b) We did not do a dbms_lob.freetemporary as we need the data back in our Java code for further processing. Thus LOB is not marked as invalid and cannot be reused.

    Due to the above reasons, with each transaction the TEMP usage goes up and results in ORA-1652.

    I would be thankful, if some pointers could be shared in order to address this scenario.

    Best Regards,
    Prashant
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to read here:

    http://oratips-ddf.blogspot.com/2008/02/temporary-tablespace-insanity.html

    Possibly you should implement a temporary tablespace group with two new temporary tablespaces each sized at 64GB. Each instance would then have a full 64GB to use for temporary lob storage, and it would be easy to add a third or fourth temporary tablespace to that group to extend that storage even further. The problem lies in the application design, relying on temporary lobs to process data. Your database wasn't designed to handle large volumes of data in these temporary lobs that persist across jobs. And as I said before the issue is that you are running out of space; the only solution to that is to add more; how you do that is up to you but I think that using tablespace groups may provide a bit more speed and a better way to manage that space.
     
  7. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    We have resolved this issue.

    The issue was NOT with insufficient Temp space.

    As Temp LOBS were never freed, TEMP usage will always go up with time and will eventually result in ORA-1652.

    Since we could not free TEMP lobs using freetemporary() in our PLSQL code as CLOB was an OUT variable which was processed at Java layer, we implemented following solution at JAVA Layer:-

    Original Code:
    =========

    Clob result = jdbcSp.getClob(15);
    if ( result == null || result.length() == 0 )
    returnValue = null;
    else
    {
    returnValue = result.getSubString(1L, (int)result.length());
    }

    Changed Code:-
    ==========

    Clob result = jdbcSp.getClob(15);

    if ( result == null || result.length() == 0 )
    returnValue = null;
    else
    {
    returnValue = result.getSubString(1L, (int)result.length());
    result.free(); //To Free CLOB at Java Layer
    }

    We faced one issue with above implementation. Basically result.free() is a new feature with Java 1.6 and we are using 10.2.0.4 RAC database with our Application over JDBC. Oracle 10gR2 database by default uses ojdbc14.jar which is Java 1.4 compatible and is incompatible with Java 1.6.

    To circumvent this issue, we downloaded latest ojdbc6.jar from OTN and updated our CLASSPATH accordingly.

    Now TEMP CLOB space is reused by each transaction and TEMP tablespace usage is stabilized.

    Hope this helps.

    Best Regards,
    Prashant