For some time, the customer has been complaining about poor performance in presenting the Capacity Manager CCR reports.When the customer chooses a specific report, there is a considerable delay for the information to be displayed on the screen.
Is there any way to improve this CCR performance?
Can CCR report performance be improved?
If the Oracle DB is running on a Shared database server, then no.
The best performance from CCR reports will be found when CapMan is the ONLY database on the server.
If CapMan DB is the only DB on the server then you can improve performance with:
1. Enabling Partitioned tables.
2. Converting certain Views into Tables.
3. Building certain Materialized views for specific reports.
Only option 1 above is a feature available in the product that you can implement without a CA Services contract.
Another thing to consider if all CCR reports are slow vs. certain reports; is that could indicate a capacity bottleneck.
Review the performance (CPU, Memory) of the CCR IIS and the Oracle DB server while requesting a report from CCR web page and ensure there isn't a resource constraint.
At times there might be many 'orphaned' ODBC transactions from CCR to the DB. Consider an IIS reset or rebooting the server and see if there is any change in response time.
The responses from Kip_Lamb and dupda03 are correct but before getting too deep into changes on the Oracle, we should ask for a bit more context for what is meant by ‘a specific report'.
There are a couple of potential causes of slow down in CCR and knowing what CCR and Oracle are being asked to do can help narrow down suggested things to look at to address these points.
So, the customer has a database with a lot of data, for example:
Espaço Alocado do Tablespace = Tablespace Alocated Space
Espaço Usado do Tablespace = Tablespace Used Space
As you can see in image, there is a lot of data, around 11 months since february 2017. The customer know about CA recomendation about keep data about 90 days but He has a particularity, he need to keep data of 365 days because there is income tax period.
I'd like to know if there is a some way of improve this performance.
What is the Specific Report?
Please provide the Table Space Report from CCR?
Why are the Table Spaces named "P1, P2,P3, P4" not being used by the PARTITIONING feature?
It appears someone created the table spaces for Partitioning, but never ran the Partitioning job.
Partitioning will allow for faster DISK IO and Compression to save space and time to retrieve data.
I see this is the same as the case you opened...did you want to pursue this here in the communites or direct throuh the case
I opened this question here before, as this issue is impacting the customer, I opened the case.
Given the number of factors involved, I was going to recommend opening a case for Support to review the environment directly.
Glad to see you're ahead of me on that already… :-)
Good luck - it would be interesting if you could please report back when you've found out a recommended way forwards.
Sure! There are a lot of particularity in the customer as:
- Datas more that 90 days; (330 days)
- Database shared with another one;
- Tables not partitionated;
I will recommend the customer, to improve this details and after that I will report for everyone;
Thank you! ;-)