CMS Snapshot Analysis - Configure Snapshot into Dashboard Service - Performance Issue

This page is a troubleshooting guide for the problem of Performance issue during the step Configure Snapshot into Dashboard Service

1. Check if the server is configured according to CAST recommendation, for this perform the following:

   1. Run the following query on a postgres session:

      
      

      Query to check if the server is configured according to CAST recommendation

      SELECT NAME,
             setting
      FROM pg_settings

      For query result sample, refer to PG_param.sql

   2. Compare the server configuration retrieved from the previous step with the server configuration recommended by CAST: Server configuration
      If the configurations are not identical then change the parameters in postgresql.conf (the file is in the postgresql directory\db_data) as recommended by CAST then restart PgAdmin Service
2. Depending on the RDBMS perform the optimization of the schema
   1. For CSS - refer the page CSSOptimize
      
3. If the above steps do not resolve your issue then check the performance of your system hosting the database server by looking into Task Manager → Performance

   Connect to the RDBMS Server and check if the memory consumption is very high and if the CPU usage is almost 100%. This could explain the performance issues during this phase.

    

   

    

   Check the reason why the performance issue occur in the Server : may be many processes are running in parallel. If there are many processes running then kill any unwanted processes.
4. Check the character set (for Oracle Only)
   With the result of the first query given in RDBMS Information - Common to all RDBMS - Get the character set check if NLS_CHARACTERSET is supported by the CAST products.
   The list of supported character Sets is listed in Release notes, Section : Supported Platforms> DBMS > Supported Character Sets.

5. Check where the performance issue occurs ,If there is still Performance issues after following the above steps.

   Note

   The "Configure Snapshot into Dashboard Service" step consists of a set of functions and stored procedure (SP) that are run in local database and central database.

   Both schemas have a table called DSS_HISTORY where the actions are logged.

   DSS_HISTORY Tables

   Most of the functions and stored procedure are logged in the DSS_HISTORY table of the Local and Central Databases. The structure of this table is the same for both databases.

   Actions logged in local database end with: End smart ranking computing

   Actions logged in central database start with: Start Local transfer from local_database -

   

   In the description field, there is the status of the stored procedure / function, the name of the function and the parameter used. Then, the structure of the filed will alway be : <Start | End> <Name of the Stored Procedure / function> <Parameters>

   In the action date field, there is the date where the action has been run : Run of the Start of the SP / Function, and the End of the SP / Function.

   In the history_id, there is the id of the action. All the id are ordered by action date.

   This table is very useful when performance issue occurs.

   1. Check the Stored Procedure / Function that is currently running.

      1. In DSS_HISTORY, when an action is currently running, the SP / Function has a Start and No End like below :

         Check the Stored Procedure / Function that is currently running

         SELECT   *
         FROM     DSS_HISTORY
         ORDER BY ACTION_DATE

         

         
         

      2. If the function / stored procedure has an action date far from now, then this is the function that having performance issue
   2. Else,check for the longest function / stored procedure

      1. Run the following query to get the time processed by Stored Procedure / Function.

         Select START_HIST.HISTORY_ID, substr(START_HIST.DESCRIPTION,7), START_HIST.ACTION_DATE Start_date, END_HIST.ACTION_DATE end_date, (END_HIST.ACTION_DATE-START_HIST.ACTION_DATE)
         From DSS_HISTORY START_HIST, DSS_HISTORY END_HIST
         Where START_HIST.HISTORY_ID + 1 = END_HIST.HISTORY_ID And START_HIST.DESCRIPTION like 'Start%'
         And END_HIST.DESCRIPTION like 'End%'
         Order by (END_HIST.ACTION_DATE-START_HIST.ACTION_DATE) desc

         

         
         

   3. Check the Server Status If the DSS_HISTORY does not help,
      1. Using PGAdmin, connect to the CSS server.
      2. Click Tools,
      3. Click Server Status as shown below
         
      4. Then check in the server status the function that are currently in progress and provide to support a screenshot showing the server status with Start of the process and the function.
         
         
         

   4. Find out the very SQL query where the performance issue lies

      Warning

      If you are in Test environment or in Support environment, then you have a way to find out which SQL query is stuck.inside the Stored Procedure or Function you have just spotted in the above paragraph.

      Do not do this in Production environment, as it could result in inconsistencies in the KB or CB.

      1. In Server Status in pgadmin, open the Log view
      2. If the performance issue occurs with procedure AED_INIT_TRAN_VIOL_CNT_TMP_PREV please refer the page CMS Snapshot Analysis - Configure Snapshot into Dashboard Service - Performance Issue - Procedure AED_INIT_TRAN_VIOL_CNT_TMP_PREV
      3. Select the stuck Procedure or Function.
      4. Click the orange button "Cancel query". A stack trace will be generated in the pgadmin Log View, it will give you the very query that was running when you canceled, along with the parameters.

1. Provide the identified query / Stored Procedure and / or the stack trace to CAST Technical Support along with the relevant input
2. If you do not find the information you are looking for or solution for your problem, in this page, contact CAST Technical Support with the relevant input

Relevant Input

• CAST Support Tool (CST) - alias Sherlock with the options Export Logs, Export CAST databases , Export Configuration files checked
• Screenshot showing the problem

• Screenshot of the Task Manager → Performance tab of the system which is hosting the database server

• AedCLI-<yyyymmddhhmmss>.TXT, the location of this log appears in the Cast-MS in the Statistics tab -