Performance Issues
Performance can be impacted by a number of issues. Use the following checklist to help you isolate problems.
• Check the number of backup jobs that have been processed in the last 24-hour period.
• Determine the level of database logging that has been enabled in scon.log. If DBG level messages have been enabled, this can negatively impact performance. INFO and WARNING messages have negligible impact.
• Check if anything else is running on the server.
• Note if performance suffers at specific times of the day and determine which processes are running during those times.
• Verify the server’s configuration: memory and CPU size
• Check if Oracle has been tuned or if the default file (initscdb.ora) is in use.
• On Windows, by default, Oracle can use only 1 GB of memory. If the system has more memory available, use the /3GB option to tell windows to allocate 2 GB to the Oracle process.
• Determine the top running processes—run top.
• What are the top running processes and what is the average CPU Utilization?
• Is a lot of Virtual Memory being consumed?
• Is there a high I/O Wait? (This implies a disk bottleneck.)
• If oraclescdb is the top running process, using a lot of CPU resources:
Run: sqlplus portal/portal @/opt/aptare/database/tools/list_quiery_by_pid
This task will prompt for the process id (pid) and show the query that is running. If you run this several times over the course of 5-10 minutes and it returns the same query, you have a long-running query. Note that this will only work if you use process IDs associated with oraclescb processes.
• Query the report database for long-running jobs.
• Select from apt_query_execution table to see what reports have been running and for how long. This will help identify a report that someone may have inadvertently scheduled to run every five minutes.
• In /opt/aptare/database/tools, use other sql queries:
list_long_running_queries.sql
list_running.sql
list_running_queries.sql
long_running.sql
Run the above queries and capture the output to send to the APTARE Global Support Services.