APTARE Oracle Database Performance issues and how to diagnose it

APTARE Oracle Database Performance issues and how to diagnose it

Article: 100049259
Last Published: 2020-12-30
Ratings: 0 0
Product(s): APTARE IT Analytics


Step 1:  Clearly define and Categorize the Performance problems and try to identify if the performance problem happens at a specific point in time. Try to avoid working on a 1 line general problem statement "Its slow".

for example:

a) APTARE Web Portal becomes unusable and then after 5 or more minutes wait, becomes usable. During the unusable phase, we are unable to navigate from one point to another in the UI or sometimes cannot even log back into the portal.  

b) Specific Reports DO NOT finish and we receive frequent timeouts for those reports

Step 2: Try to focus on each category and approach the performance issue.

Step 3: If the performance problem is happening at a specific point in time, review the OS level CPU, Memory, Disk I/O info around that time.  Gather snapshots of this info around the time when the last performance problem occurred.  For Unix the perf2018 (step 8) can be used.  For Windows you would need to use various Windows and inhouse tools.

Step 4: Obtain APTARE Database Backup information

a) What time of the day/night does the APTARE Database Backups run?

b) What is the location on the server where the nightly backup files are created?

c) Ensure, the Backup Location is separate from the location where the LIVE database files reside. 

If the observation is slowdowns occur during the Nightly Backup time, change the Backup time and confirm the slowdown carry forward or correlate to the new backup time.

Step 5: Run the System Administration Reports -> "System Health Check Report" and look at the Top Running Reports Summary to identify what reports are running and the execution time. Focus on reports with very few "run counts" but large "Avg. Execution Time". That is a potential indicator of the report needing further attention and potential Query tuning. 

Also, look at the "Report Activity Summary" which is a graphical bar chart report and check for days where there is major spike. A drill down can provide further details on actual reports via the elapsed time.   In short, first try to get a good handle around what reports are running, how long are they taking and see if there are problem areas that need your attention . You may also find reports that should not be even running, in which case if they are scheduled to run automatically, you can turn those off.

Step 6: Try and capture additional information from the Oracle Database.  Information such as,

a) When was the last Statistics Generation ? Could we have stale statistics that may cause Oracle Optimizer to choose incorrect execution plans?

b) When was the last Index Rebuild (Note: This is a APTARE automated job, but if its not running for whatever reasons, on tables with frequent insert, update and deletes, there is a large possibility of skewed indexes causing performance issues.

c) Are there any waits inside database?

d) Are there Queries that are Executing or Queued?

NOTE: Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads at any given point in time. THERE IS NO SUCH LIMIT for Oracle Enterprise Edition.

NOTE:  In case of a situation where APTARE IT analytics UI locks up or very slow in response, one possible reasoning could be all available Oracle Resources are in use. One easy test we could do is , open up a SQL*Plus connection to the database and simply run a query  on one of the  APTARE DB Tables. e.g. SELECT array_name, array_type, array_family FROM aps_v_storage_array ORDER BY array_family;     then observe the behavior and see if the Query goes in WAIT mode (typically this query should run withing 1-3 seconds at the max). If the Query response is as expected but yet the APTARE UI continues to show slowdowns, it is possible avenues other than Oracle resources may need investigation or the issue may be threshold reached inside Oracle resources configured/consumed inside APTARE IT Analytics framework.

e) What are the Queries that are the most execution intensive or I/O intensive?

f) What Queries are doing FULL Table scans?

Step 7: Run and analyze the perf2018.sh performance script (Unix)


Step 8: Run and analyze the db_perfinfo_capture.sh Oracle specific shell script.


Was this content helpful?