Leave feedback
  • Question

    DocumentBrokerPlus and temp tablespace usage in Oracle (version 5.5)

Enter a new topic
  • Jesper Werge Jesper Werge StreamServe Employee
    0 likes 911 views

    Hi all,

    We moved from POET to DB+ in the beginning of this year and have had great performance benifits.

    however it seems that on very large batches the TEMP tablespace within Oracle is being used alot, which is fair as we do sorting ect. when extracting the documents from the BD+, however the Oracle DBA is concern as alot of sessions from the DataAndQueue user from the StreamServe database is really high in number and the space used is over 150 MB per. session, and almost all are in status inactive. The DBA was expecting the sessions to be closed when they are not used anymore

     

    Does anyone have any suggestions to where to look or how to control this if possible from StreamServe side or Oracle side, please?

    /Jesper

    Wednesday 10 December, 2014
  • Enrik Beste Enrik Beste StreamServe Employee
    0 likes

    StreamServe uses a pool of sessions. There is no simple rule how many session are in use as this depents on different factors, e.g. number of applications. For more details see
    "OpenText StreamServe 5.6.1 Performance Guidelines Reference Guide", chapter "Tuning the database connection pools"

    You may consider disabling the use of temporary session:
    "We recommend to deny temporary growth (that is, change the allowtemporaryobjects="true" attribute to "false")."

    For monitoring you can run the following sql regularly:

    set heading off
    set linesize 200
    set pagesize 0
    set colsep ";"
    set feedback off
    select count(username) AS "TOTAL", username, osuser, machine, status from v$session group by username, osuser, machine, status order by machine;

    Wednesday 10 December, 2014
  • Jesper Werge Jesper Werge StreamServe Employee
    0 likes

    Hi Enrik,

    thank you for your reply.

    We already do not allow temporary objects to growth, so this is set to false.

    the biggest issue is that the sessions are inactive and are never disappering, they might if we stop the services, but should the temp tablespace not be released at some point?

    /Jesper 

    Wednesday 10 December, 2014
  • Enrik Beste Enrik Beste StreamServe Employee
    0 likes

    From my understanding tablespaces do never shring automatically. You can find out what operation(s) does require such amount of space. There are some articles around on the internet. If you shrink the table space and the sorting or something similar is performed again it comes back... In general each session has a temp tablespace allocated.

    The sessions are part of the session pool and will not be closed automatically.

    Wednesday 10 December, 2014
  • Jesper Werge Jesper Werge StreamServe Employee
    0 likes

    Hi Enrik

    about the tablespace, i will never go for a auto shrink, that was not my idea of the question, sorry for not beeing clear on that part.

    the sessions are part of a session pool, that I understand, but if the inactive sessions are never closed automatically, can we force the DBA's (the oracle instance) to do the removal of inactive sessions after X amount of time, or would that cause the StreamServer to break down?

    /Jesper

    Tuesday 23 December, 2014
  • Jesper Werge Jesper Werge StreamServe Employee
    0 likes

    Hi

    We used the ExObjectPool2 in the repositorymanager.xml and where you can add an flush of your database pools.

    It is documented in version 5.6.2.

    /Jesper 

    Monday 09 January, 2017