ARERR 94 timeout during database query.

Version 1
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    APPLIES TO:

    BMC Remedy AR System Server



    PROBLEM:

    ARERR 94 Timeout during database query...


    CAUSE:

    Long running SQL statements are the likely cause of this error.


    SOLUTION:

     

    What to do: First enable API/SQL logs, you want to enable these logs because we want to know what SQL is causing us issue and what API call is generating it. While the logs are enabled go through the steps of reproducing the error.

    What to do with logs: Once the logs have been gathered you have two options 

      
       
    1.  You can submit the logs for BMC Support to conduct the analysis. 
    2.  
    3.  You can go to EPD and download the log analyzer to analyze the logs yourself. 
      
      Whichever option you choose to approach this issue, at the end of the analysis you should end up with a longest running SQL statement: (for this example im going to use a SQL from our lab environments)  
      
    SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (SELECT T1200.C200000020, T1200.C210000000, T1200.C490021100, T1200.C260100004, T1200.C200000003, T1200.C200000004, T1200.C200000005, T1200.C240001002, T1200.C7, T1200.C200000001, T1200.C240001008, T1200.C240001003, T1200.C200000007, T1200.C1, T1200.C179, T1200.C400079600, T1200.C260000001, T1200.C400129200, T1200.C240001005, T1200.C8, T1200.C1000000150, T1200.C200000012, T1200.C260000005, T1200.C260000004, T1200.C260000002, T1200.C1000000001 FROM T1200 WHERE ((T1200.C400127400 = 'BMC.ASSET') AND (T1200.C400079600 = 'BMC_COMPUTERSYSTEM') AND ((T1200.C7 = 3) OR (T1200.  C7 IS NULL)) AND (T1200.  C1000000001 = 'BMC Company')) ORDER BY T1200.C1 ASC) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > 0) AND (AR_RowNumber_Alias$1 < 152) AND (rownum < 152)) 

    This specific query takes about 58 seconds. If we dissect the query we can see the bolded fields will need an index C100000001 because its not selective enough and C7 should be part of it since is qualifying against a NULL.  

    The index that helps in this example is   C1000000001, C7 in this exact order.  

     


    Article Number:

    000038355


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles