Apptune resource utilization

Version 2
    Share This:

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


    PRODUCT:

    APPTUNE for DB2


    COMPONENT:

    APPTUNE for DB2


    APPLIES TO:

    System and SQL Performance for DB2, BMC Performance for DB2 SQL, SQL Performance for DB2



    PROBLEM:

    How to reduce Apptune resource utilization?
    Why does Apptune trigger early unloads?
    Why is there an increased zIIP usage with Apptune unloads?
    How to avoid Apptune early unloads? How to resolve BMC23202 messages?    
     


    SOLUTION:

    How to reduce Apptune resource utilization?
    These methods can reduce thread and Data Collector utilization, reduce trace volume, and improve reporting responsiveness. Click on each link for additional information.  
      Reduction of thread utilization
      Reduction of Data Collector utilization
      Setting collection keys
      Reduction of trace volume 
     
    Why does Apptune trigger early unloads?

    Apptune summarizes data by collection keys. Each collection key specified adds to the reduction table and might increase overhead. It is possible that turning on a certain collection key could generate a large volume of data if the value tends to be unique with each execution of the application. When there is significant increase in data it may trigger an early unload before the scheduled interval. Apptune issues BMC23202  Early unload needed for ssid, entries=count messages with the number of records unloaded.

    Why is there an increased zIIP usage with Apptune unloads?
    The Apptune unload executes under an preemptible Enclave SRB and it is 100% zIIP-eligible. Apptune unload activites are asynchronously initiated by the Apptune address space in a dependent enclave that is owned by the Apptune address space. The dependent enclave inherits WLM service class of the Apptune address space. This task is dedicated to building records from SQL performance metrics accumulated in memory and sending them to buffers to be written to the LOGSET log files. The APPTUNE unload enclave SRBs are asynchronous, each dedicated to unloading data for a particular DB2. The amount of data being unload by Apptune correlates directly to increase in zIIP utilization. The collection keys in the APPTUNE filter will have the most significant impact to reduce the volume of data and level of activity by the APPTUNE unload task running on zIIP. Refer to Reduction of Data Collector utilization.

    How to avoid Apptune early unloads? How to resolve BMC23202 messages?

      Apptune collection keys and shorter workload intervals can significantly reduce to the volume of entries in the reduction table. Refer to    Setting collection keys and    Reduction of trace volume. Evaluate the impact of the collections keys on statement entry count using the following example.   In this example, the interval has 250,000 entries. A plan has generated over 200,000 entries and there are over 30,000 users executing the plan. In this case, the number of unique users has a great impact than the number of statements executed.
      
       
    1.     
          Select an interval in SQMINTVD for the DB2 with high NUMBER ENTRIES.  
      
      SQMINTVD: SELECT ANALYSIS INTERVAL

       +------------------- INTERVAL -----------------+        NUMBER     ACTIVE 
       BEGIN               END                 DURATION  DB2   ENTRIES    FILTER 
       ------------------  ------------------  --------  ----  -------   --------
    S  09/09/16  07:55:00  09/09/16  07:58:59  00:03:59  DB01   250000   FILT01
      
       
    1.     
          Select Subsystem analysis report.   
    2.  
    3.     
          Select the subsystem with "P"  to see the plans. Sort descending by Apptune Reduction entries (see below).  
      
       
    •     
          Enter "expand 1" on the first report line.   
    •  
    •     
          Place cursor on Apptune Reduction Entries in the expanded section.   
    •  
    •     
          Press PF5 to sort in descending order.   
    •  
    •     
          Enter "expand off"  on the command line.   
      
       
    1.     
          Select the plan on the first report line with "1" expand code to see the Apptune Reduction entries. There are over 200,000 entries.   
    2.  
    3.     
          Select the plan with "U"  to show how many unique user ids executed that plan.  
    4.  
    5.     
          Select the plan with "S" to see statements executed under the plan. There are about 500 statements.   
    6.  
    7.     
          Select the top statement with "U" to see the users. There are over 30,000 users executing the statement.  
    8.  
    9.     
          Alternatively, select the subsystem with "S" to view at a statement level. Sort descending by Apptune Reduction entries.  
    10.  
    11.     
          The top statement has over 30,000 entries and a "U" on the statement shows over 30,000 users executing the statement.  
    12.  
    13.     
          Repeat the same process for some more high volume intervals to determine the high contributor to Apptune reduction entries.    
    The solution is to establish a filter row for the plan and turn user collection key off or collect selectively by plan or program. 

    References:
    Specifying options for a filter option set
        Collection options
        Collection keys
        Resource-saving options
        Thresholds for exceptions and efficient statements
        Negative SQL code options
    Filtering scenarios
        Collecting data for a specific plan
        Suppressing collection for specific plans
        Collecting only exception data
    Quick Courses
       Creating and using filters
       Creating and monitoring exceptions



      

     


    Article Number:

    000122447


    Article Type:

    Solutions to a Product Problem



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