Oracle Partitioning a solution for handling cumbersome transaction log?

[Joseph_Malecki]

Necessary modification agreements not withstanding, would Oracle partitioning (available in 11g) be a viable solution for taming our fast growing Transactionxlog?

We would be interested in hearing how other organizations are managing growth of this table.
Does anyone have a policy of purging certain records after a period of time?
For example, records for an action which their organization deems benign (e.g. "viewed note").
Again, I am not suggesting any violation of the Database Use Policy. 

Would love to see a class at OTTC on this topic!

[lboening]

We have used OnBase since 2005. We have about 750k in "documents" in OnBase. The count of  rows in  TRANSACTIONXLOG is 5.009 million. The Oracle instance is told to expand to an unlimited size per database space. The space taken by TRANSACTIONXLOG in DBSPACE1 is 1413 MB. The space taken by the two indexes supporting the table is 91.6 and 136.2 MBs. The actual space used by the files in tablespace DPSPACE1 is 2568 MB.

I asked our trained Oracle DBA for an opinion on this table. He said we could partition the table by year. Thus we might end up with seven years worth of partitions.

Apparently, querying  this table through the OnBase Client would not change. Theoretically, results could return faster, especially if a Date Range were specified.

I just queried OnBase (using Workstation Client) for all the documents I added in year 2009. I specified my username. It took 3 seconds to return. I then looked for all documents stored in year 2009 and omitted my username. It took about 20 seconds to return.

I took a look at SYS.V_$SQL and see that the 20 second query actually "finished" in 9 seconds. The other 11 seconds must have been spent  assembling information and presenting it to me.

We have no direct policy about anything written in this log. Little of our information is of sensitive nature. In six years we have not had a single audit of this table in the manner performed by an external "auditor".

On the technical side, I thought Oracle partitioning wouldn't buy much speed, so we haven't partitioned this table by year.

[Ansley_Ingram]

Hi Joseph,

I apologize for the delay in a response from Hyland on this topic.

We have had several inquries over the years regarding Oracle partitioning, especially for the hsi.transactionxlog table. I'm personally unaware if any of our customers has actually implemented partitioning of this table but in theory, it could be a very good solution to maintaining the table and improving performance. The concept of partitioning would fall under the umbrella of disk and file management for the database and would not require a modification agreement. (That being said, if you do implement this, we'd love to hear any feedback you have about the change.)

Amongst our customer base, there are a variety of practices for managing this table - mostly driven by retention policies. Some organizations are required to keep the document (or folder) history for a set number of years while others are required to keep the information forever. I have typically only seen retention of this table based on logdate but we have on occasion had customers that wanted to remove only certain types of information, similar to your example.

The table does not have an index on logdate, so purging from the table or removing data from a date range out to another table is often a slow process. A custom index on logdate (which Hyland would want to approve) can speed up this process. In SQL Server implementations we recommend a clustered index on logdate but we have yet to make a recommendation on having this be an Index Organized Table in Oracle implementations (mostly because that setting would have to be set at the creation of the table rather than after the fact). Still, a standard index on logdate should show significant improvement. There are also not indexes on the actionnum or subactionnum columns, so additional indexes might be needed if you were to want to purge certain types of activity.

The 2012 OTTC class entitled 'Report Generation Best Practices' that will have some detailed information on the contents of this table but probably won't focus much on the table's maintenance. I will see what I can do to get some of that content into the DBA-type classes at OTTC and/or TechQuest.

Hope that helps!

Ansley