Troubleshoot Oracle performance issues in IT Analytics
IT Analytics Portal installer deploys a diagnostic script during the installation that helps you diagnose and analyze Oracle database performance issues in your environment. The script generates an output file, the contents of which you can analyze to diagnose the issues.
This section provides the commands to run the diagnostic script and provides the guidelines to use the script output to diagnose the Oracle database performance issues.
The diagnostic script is installed with the IT Analytics Portal at the following OS-specific locations.
On Linux:
/opt/aptare/database/toolsOn Windows:
c:\opt\oracle\database\tools
Run this Shell script on your Linux portal server as Oracle user. Permissions available to a IT Analytics Portal user with admin privileges are adequate to run this script.
$ dbinfo.sh
Enter Oracle Database Service Name (for example SCDB), Database Username and Password when prompted by the script.
Run this batch script as an administrator on your Windows portal server.
dbinfo.bat
Enter Oracle Database Service Name (for example SCDB), Database Username and Password when prompted by the script.
The diagnostic Shell or batch script produces a dbperformanceinfo_<date>.txt file, where <date> is a timestamp. The output file is a created at the same location as the script. The file contains the following important sections:
Top 10 queries by highest execution time.
Top 10 queries with full table scans.
Queries with large I/O data buffers.
Report of indexes that require a rebuild.
Look for the following information in the dbperformanceinfo_<date>.txt file to identify the causes of the performance issues:
Look for the most I/O intensive queries within the top 10 queries by execution time. Identify queries taking more than the anticipated time for investigation. Identify ad-hoc queries and tune the queries appropriately. From the MODULE column, verify whether the query was executing simultaneously with the Performance Script run. Also check rows processed and identify the rows with long process duration.
Analyze findings indicated by 'Waits' or 'Blocking' in the output file.
Identify queries doing Full Table Scans. Suppress or avoid running queries performing Full Table Scans.
Identify queries with large I/O buffers.