Purpose

When using Collaborative Editing, poor performance may be experienced for extremely large pages. In some situations, the editor may be unusable as covered in this bug report:

• CONFSERVER-60057 - Getting issue details... STATUS

In this guide, we'll cover how to find such large pages and how to recover them.

Finding large pages on the database

We will want to find any pages that have more than 5MB of text on them. Different databases require different queries. Please check the queries below depending on the DB in use.

A note about calculating the size: All of the below are going to be approximations. The 5MB limit is on the size of the request to save a page over REST. This includes all the overhead of the Content object, etc. The limit for the size of the body of a page is thus bound by the 5MB besides the overhead. Additionally, the size in the database could be different from the actual size transmitted over REST where the limit is. Thus, please update the values in the queries below to get a better approximation. Perhaps, instead of looking for pages larger than 5MB, it would be better to start looking for pages that are larger than say 4.5 MB, and narrow the search in.
Do not run the queries on a live system with large amounts of data as they could possibly be slow.

SELECT c.contentid, s.spacename, c.title, OCTET_LENGTH(b.body) FROM content c
        JOIN spaces s on c.spaceid = s.spaceid
        JOIN bodycontent b on b.contentid = c.contentid
        WHERE c.contenttype = 'PAGE'
        AND c.content_status = 'current'
        AND c.prevver IS NULL
        AND OCTET_LENGTH(b.body) >= 5 * 1024 * 1024;

SELECT c.contentid, s.spacename, c.title, DATALENGTH(b.body) FROM content c
        JOIN spaces s on c.spaceid = s.spaceid
        JOIN bodycontent b on b.contentid = c.contentid
        WHERE c.contenttype = 'PAGE'
        AND c.content_status = 'current'
        AND c.prevver IS NULL
        AND DATALENGTH(b.body) >= 5 * 1024 * 1024;

Oracle can have two approaches since there isn't a good way to get the size of CLOB field in bytes.

1. Estimate the size in bytes by counting the number of characters and multiplying by some integer K. Note that all English characters can be represented by 1 byte, but the database might store it at a fixed width, depending on the encoding. Multiplying by 2 might give a good estimate. It would at least give an idea of which pages might be close to the limit. At that point you may want to obtain a list of those pages and use the other method to get a more precise number. You could also use the second method to verify your estimate on a subset of pages to see if K needs to be adjusted.

   Oracle (number of characters)

   SELECT c.contentid, s.spacename, c.title, dbms_lob.getlength(b.body) FROM content c
           JOIN spaces s on c.spaceid = s.spaceid
           JOIN bodycontent b on b.contentid = c.contentid
           WHERE c.contenttype = 'PAGE'
           AND c.content_status = 'current'
           AND c.prevver IS NULL
           AND dbms_lob.getlength(b.body) >= 2 * 1024 * 1024;

2. Convert the CLOB to a BLOB and measure the size of that.
   This query is likely not performant. Do not run it against the entire database. First, try to obtain a list of pages that might be close to the limit by using the query above.

   create or replace function cloblengthb(p_clob in clob ) return number
   as
     v_temp_blob BLOB;
     v_dest_offset NUMBER := 1;
     v_src_offset NUMBER := 1;
     v_amount INTEGER := dbms_lob.lobmaxsize;
     v_blob_csid NUMBER := dbms_lob.default_csid;
     v_lang_ctx INTEGER := dbms_lob.default_lang_ctx;
     v_warning INTEGER;
     v_total_size number := 0; -- Return total clob length in bytes
   BEGIN
     IF p_clob is not null THEN
       DBMS_LOB.CREATETEMPORARY(lob_loc=>v_temp_blob, cache=>TRUE);
       DBMS_LOB.CONVERTTOBLOB(v_temp_blob, p_clob,v_amount,v_dest_offset,v_src_offset,v_blob_csid,v_lang_ctx,v_warning);
       v_total_size := DBMS_LOB.GETLENGTH(v_temp_blob);
       DBMS_LOB.FREETEMPORARY(v_temp_blob);
     ELSE
       v_total_size := NULL;
     END IF;
     return v_total_size;
   end cloblengthb;
   /
   SELECT c.contentid, s.spacename, c.title, cloblengthb(b.body) FROM content c
           JOIN spaces s on c.spaceid = s.spaceid
           JOIN bodycontent b on b.contentid = c.contentid
           WHERE c.contenttype = 'PAGE'
           AND c.content_status = 'current'
           AND c.prevver IS NULL
           AND c.contentid IN(<predefined list of ids>)
           AND cloblengthb(b.body) >= 2 * 1024 * 1024;

Recovering large pages to be editable again

1. On a test environment with data cloned from production, switch Collaborative Editing OFF temporarily
2. Attempt to edit the page and split the content into smaller pages
3. Re-enable Collaborative Editing and confirm the split pages can be edited in the test environment
4. Copy the split pages to production and delete the original large page

 Switching Collaborative Editing OFF and back ON is a heavy task, so we do not recommend doing that in a production environment. Instead, follow the steps above to recover and split the page in a test instance.