Extreme slowness Portal & Administration

Cognos 8 Business Intelligence Installation and Configuration
1

Hi guys,

       Here we have a situation of slow navigation of the portal, in the execution of Reports, and extreme slowness in accessing the Cognos Administration.     ???

The installation version is 8.4.1.
The thing that struck me was the size of the Content Manager (about 65 Giga), which I think is really exaggerated.
The problem may also be dictated by the fact that most users enter the same credentials, being evidently remain suspended sessions.
The number of active sessions are on average 500, and this number seems too high.
The Db which is below is Oracle.

What do you think? You see some macroscopic problem in this situation that I have described?

Thank you, let me know please!

Ciao,
Andrea

2

Hi Andrea,

65 Gb is pretty large for a content store. There is a script to see what is in there

http://www.cogknowhow.com/index.php/knowledge-articles/36-tools/99-content-store-size-profiling-sql-script

the example is for SQL but there is also a Oracle script CSsize_profiling_oracle.sql. There is also a script called DBclean_oracle.sql, not sure what this script does so mak e backup first :wink:

-- Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.
-- Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated).


--	Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.	
--		Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated). 

CREATE GLOBAL TEMPORARY TABLE CMTMPPROFILE (
	CMID		number(10) not null,
	CLASSID		number(10) not null,
	NOBJECTS	number(10) not null
)  ON COMMIT PRESERVE ROWS;


select count(*) as "Total number of objects" from CMOBJECTS;

select  c.NAME as "class name", count(*) as "number of objects"
from CMOBJECTS o, CMCLASSES c  
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;

select  c.NAME as "class name", count(*) as "number of secured objects"
from CMOBJECTS o, CMCLASSES c, CMPOLICIES p 
where  o.CMID=p.CMID and p.POLICIES is not null
group by o.CLASSID, c.CLASSID, C.NAME
having c.CLASSID=o.CLASSID
order by c.NAME;



select count(*) as "Total number of outputs",
SUM(cast(coalesce(LENGTH(DATAPROP),0) as decimal(19,0))) as "Total size of outputs" 
from CMDATA 
where DATAPROP is not NULL;

select count(*) as "Outputs under 10KB" from CMDATA
where DATAPROP is not NULL and LENGTH(DATAPROP) < 10240;

select count(*) as "Outputs 10KB - 100KB" from CMDATA
where LENGTH(DATAPROP) >= 10240 and LENGTH(DATAPROP) < 102400;

select count(*) as "Outputs 100KB - 1MB" from CMDATA
where LENGTH(DATAPROP) >= 102400 and LENGTH(DATAPROP) < 1048576;

select count(*) as "Outputs 1MB - 10MB" from CMDATA
where LENGTH(DATAPROP) >= 1048576 and LENGTH(DATAPROP) < 10485760;

select count(*) as "Outputs 10MB - 100MB" from CMDATA
where LENGTH(DATAPROP) >= 10485760 and LENGTH(DATAPROP) < 104857600;

select count(*) as "Outputs 100MB - 1GB" from CMDATA
where LENGTH(DATAPROP) >= 104857600 and LENGTH(DATAPROP) < 1073741824;

select count(*) as "Outputs > 1GB" from CMDATA
where LENGTH(DATAPROP) >= 1073741824;


select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS7 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;


select count(*) as "Specifications < 10K" from CMOBJPROPS7
where SPEC is not NULL and LENGTH(SPEC) < 10240;

select count(*) as "Specifications 10KB - 100KB" from CMOBJPROPS7
where LENGTH(SPEC) >= 10240 and  LENGTH(SPEC) < 102400;

select count(*) as "Specifications 100KB - 1MB" from CMOBJPROPS7
where LENGTH(SPEC) >= 102400 and  LENGTH(SPEC) < 1048576;

select count(*) as "Specifications > 1MB" from CMOBJPROPS7
where LENGTH(SPEC) >= 1048576;



select count(*) as "Total number of models" from CMOBJPROPS7
where CMODEL is not NULL;

select count(*) as "Models < 10KB" from CMOBJPROPS7
where LENGTH(CMODEL) < 10240;

select count(*) as "Models 10KB - 100KB" from CMOBJPROPS7
where LENGTH(CMODEL) > 10240 and  LENGTH(CMODEL) < 102400;

select count(*) as "Models 100KB -1MB" from CMOBJPROPS7
where LENGTH(CMODEL) > 102400 and  LENGTH(CMODEL) < 1048576;


select count(*) as "Models >1MB" from CMOBJPROPS7
where LENGTH(CMODEL) > 1048576;


select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS34 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS43 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS66 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.RELATED),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS14 p 
where o.CMID=p.CMID and p.RELATED is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS63 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS42 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS68 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS56 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;

select c.NAME as "class name", count(*) as "Total number of specifications",
SUM(cast(coalesce(LENGTH(p.SPEC),0) as decimal(19,0))) as "Total size of specifications"
from CMOBJECTS o, CMCLASSES c, CMOBJPROPS57 p 
where o.CMID=p.CMID and p.SPEC is not NULL 
group by o.CLASSID, c.CLASSID, C.NAME 
having c.CLASSID=o.CLASSID order by c.NAME;


INSERT INTO CMTMPPROFILE (CMID, CLASSID, NOBJECTS)  
SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID = 1
group by o.PCMID, p.CLASSID;

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from CMTMPPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME,  pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

TRUNCATE table CMTMPPROFILE;


INSERT INTO CMTMPPROFILE (CMID, CLASSID, NOBJECTS)  
SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID=17
group by o.PCMID, p.CLASSID;

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from CMTMPPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME,  pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

TRUNCATE table CMTMPPROFILE;


INSERT INTO CMTMPPROFILE (CMID, CLASSID, NOBJECTS)  
SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID=12 or p.CLASSID=9
group by o.PCMID, p.CLASSID;

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from CMTMPPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME,  pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

TRUNCATE table CMTMPPROFILE;


select TYPE as "schedule type", count(*) "number of schedules"
from CMOBJPROPS2 where ACTIVE=1 and TYPE is not NULL 
group by TYPE;


INSERT INTO CMTMPPROFILE (CMID, CLASSID, NOBJECTS)
SELECT o.PCMID, p.CLASSID, count(*) from CMOBJECTS o left outer join CMOBJECTS p on o.PCMID=p.CMID
where p.CLASSID = 55 and o.CLASSID = 57 or o.CLASSID=17
group by o.PCMID, p.CLASSID;

select c.NAME as "object class", pr.NOBJECTS as "number of children", count(*) as "number of objects"
from CMTMPPROFILE pr left outer join CMCLASSES c on pr.CLASSID=c.CLASSID
group by c.NAME,  pr.NOBJECTS
order by c.NAME, pr.NOBJECTS;

TRUNCATE table CMTMPPROFILE;


select count(*) as "Number of empty policies" from CMPOLICIES
where LENGTH(POLICIES) = 0;

select count(*) as "Policies 1-127 bytes" from CMPOLICIES
where LENGTH(POLICIES) >= 1 and LENGTH(POLICIES) <= 127;

select count(*) as "Policies 128-255 bytes" from CMPOLICIES
where LENGTH(POLICIES) >= 128 and LENGTH(POLICIES) <= 255;

select count(*) as "Policies 256-511 bytes" from CMPOLICIES
where LENGTH(POLICIES) >= 256 and LENGTH(POLICIES) <= 511;

select count(*) as "Policies 512-1023 bytes" from CMPOLICIES
where LENGTH(POLICIES) >= 512 and LENGTH(POLICIES) <= 1023;

select count(*) as "Policies 1K-2K" from CMPOLICIES
where LENGTH(POLICIES) >= 1023 and LENGTH(POLICIES) <= 2047;

select count(*) as "Policies 2K-4K" from CMPOLICIES
where LENGTH(POLICIES) >= 2048 and LENGTH(POLICIES) <= 4095;

select count(*) as "Policies 4K-6K" from CMPOLICIES
where LENGTH(POLICIES) >= 4096 and LENGTH(POLICIES) <= 6143;

select count(*) as "Policies 6K-8K" from CMPOLICIES
where LENGTH(POLICIES) >= 6144 and LENGTH(POLICIES) <= 8191;

select count(*) as "Policies > 8K" from CMPOLICIES
where LENGTH(POLICIES) > 8192;

drop table CMTMPPROFILE;
3

Ok, I will propose to analyze the content store with these scripts! :slight_smile:

Thank you very much! :slight_smile: