How to check if BSI table are glowing full

Products

• CA Business Service Insight

Issue/Introduction

ENV: patched to version 8.3.5.4

---------------------------------

Slowdowns & Getting error "Communication Error: Error reading from socket, Reporter :ScriptHost and :Unknown 

We are getting a repeat error on the "LogServer.log" file (SystemDrive\Program Files (x86)\CA\Cloud Insight\Log):

The same error seems to be happening in the system log, where many translation scripts report that they stopped due to script execution timeout.

There also seems to be a very large slowdown when first accessing the website, either via IP on a local level or via web server localhost access. There also seem to be slowdowns while accessing contract parameters and some other pages, but only on first access.

Cause

Oracle Table space is growing full and noted that both the tables TBS_PSL_INDEX_TAB_02 and TBS_PSL_DATA_TAB_02 are closing on the table space.
There seems to be performance deterioration that might be caused due to a big or nearly full table space.

Environment

Release : 8.3.5.4

Component : CA Business Service Insight

Resolution

To check the table spaces on Oracle DB run below commands and consult DB administrator to review further if there are any tables running full.

<<<<TablespacesUsage.sql<<<<

SELECT d.status "Status",d.tablespace_name "Name",d.contents "Type",d.extent_management "Extent Management",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,'99999999.999') || '/' ||
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990.00') "Used %"
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes FROM dba_data_files group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes FROM dba_free_space group by tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND
       NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",d.tablespace_name "Name",d.contents "Type",d.extent_management "Extent Management",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
       TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') || '/' ||
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes FROM dba_temp_files group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) bytes FROM v$temp_extent_pool group by tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
       d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'

>>>>TablespacesUsage.sql>>>>