The AR System or ITSM environment is experiencing performance issues.
After analyzing SQL, API, and Filter Logs,  the problem seems to lie with the Oracle Database.
The conclusion that the problem is with the database usually only occurs when the following conditions are true:
1.  There is a general performance issues where many or most SQL statements take much longer to run than expected
2.  Specific SQL statements take longer to run than expect even though there are good index candidates,  a SQL statement with a WHERE clause that is expected to use an index, and good data distribution which should provide a selective qualification.

 

This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

In cases in which the AR System logs seems to indicate a problem at the database server,  BMC Support will give its best effort to help provide the DBA any information he might need to help isolate and remediate the problem.  When working with Support,  depending on the symptoms, there are some common steps that can be taken.

Symptom:  Performance issues with specific SQL statements.
Steps:
1.  Use the AR System SQL and API logging to isolate the SQL statements that are long running. 
     a.  ARLogAnalyzer should be used to identify long running SQL and API calls as well as calls that have not completed. 
2.  Determine if the SQL statement is designed properly to make use of existing indexes.
     a.  This includes making sure the qualification is selective
3.  Determine if appropriate indexes exist.
     a.  Creating a composite indexes is sometimes indicated
     b.  When the qualification is for a NULL value,  adding C1 to the front of a composite index often helps
4.  If none of the above helps isolate the problem, the DBA is requested to provide the following information to Support
     a.   AWR report for a 60 minute period when the system is behaving properly
     b.   AWR report for a 60 minute period when the problem occurs
     c.   SQLT report if a specific SQLID is identified
     d.   Actual Explain plan while running the offending SQL statement
5.  Provide the names and schemaIds from the arschema table.
     a.   spool the following SQL statement to a file and send
            Select schemaId,name from arschema

Symptom:  Database blocking
1.  Use the AR System SQL and API logging to isolate the SQL statements that are causing the blocking
     a.  When using a Server Group,  the logs from all server must be analyzed.
     b.  ARLogAnalyzer should be used to identify long running SQL and API calls or calls that have not completed.  .
     c.  The Server Statistics/Longest SQL and API  feature can be used to identify SQL and API calls that are not completing or taking very long.
2.  Determine if the reason for the block is because the SQL statement simply took a very long time.
    a.  If so,  try tuning the SQL statement.   If the SQL cannot be tuned, discuss with Support the option of modifying the application to avoid or modify the SQL
    b.  If the reason the the block is because of Application design, discuss with Support the option of modifying the application to avoid or modify the SQL
3.  To collect more information about the blocking condition, provide the following to Support
     a.   AWR report for a 30 minute period when the system is behaving properly
     b.   AWR report for a 30 minute period when the problem occurs
     c.  Results from 'hanganalyze'
4.  Provide the names and schemaIds from the arschema table.
     a.   spool the following SQL statement to a file and send
            Select schemaId,name from arschema

Symptom:  General Database Performance Issues
1.  Use the AR System SQL and API logging to isolate specific SQL statements that are long running. 
     a.  ARLogAnalyzer should be used to identify long running SQL and API calls as well as calls that have not completed.  
     b.  Try to identify a pattern related to time-of-day,  tables,  activities
     c.  Discuss with Support the option of modifying the application to avoid or modify the activity, prune data,  or modify the SQL
2.  AWR reports should be analyzed by the DBA to identify tuning opportunities.
     a.  The AWR reports can be provided to Support for further investigation
          1.   AWR report for a 60 minute period when the system is behaving properly
          2.   AWR report for a 60 minute period when the problem occurs
3.  Provide the names and schemaIds from the arschema table.
     a.   spool the following SQL statement to a file and send
            Select schemaId,name from arschema
4.  Review BMC Performance Tuning Best Practices
    a.  Best Practice recommendations on-line documentation
    b.  Basic troubleshooting methodology
    c.  Configuring Oracle Database