DX Application Performance Management

Expand all | Collapse all

SQL normalization and URL grouping

Jump to Best Answer
  • 1.  SQL normalization and URL grouping

    Posted 09-01-2015 02:46 AM

    Hello All,

     

    I just found SQL metrics are of high count on my environment and thinking to initiate SQL normalization.

    I would like to know the exact process and procedures for doing this. I just found a wiki link which describe the steps

     

    https://wiki.ca.com/display/APMDEVOPS97/Configure+SQL+Monitoring+for+Java#ConfigureSQLMonitoringforJava-HowPoorlyWrittenSQLStatementsCreateMetricExplosions

     

    Can someone tell me whether this information is enough to start? or do we have any other docs available?

     

    Also I see lot of URL's reported like below under front ends. What is the best way to stop receiving this? What about grouping URL's? Will that work in these situations?

     

     

    Thanks,

    Karthik



  • 2.  Re: SQL normalization and URL grouping

    Posted 09-02-2015 01:35 AM

    Hello Community,

     

    Any updates on this?

     

    Thanks,

    Karthik



  • 3.  Re: SQL normalization and URL grouping
    Best Answer

    Posted 09-02-2015 02:22 AM

    Karthik,

     

    The SQL Normalization document from Wiki should be a good place to know how to configure custom SQL normalization. Here is a sample though that may help you, it groups all Select, Insert, Update and Delete queries under a group of each.

     

     

    introscope.agent.sqlagent.normalizer.extension=RegexSqlNormalizer

    introscope.agent.sqlagent.normalizer.regex.matchFallThrough=true

    introscope.agent.sqlagent.normalizer.regex.keys=key_tables

    introscope.agent.sqlagent.normalizer.regex.key_tables.pattern=.*(SELECT|DELETE|INSERT|UPDATE).*

    introscope.agent.sqlagent.normalizer.regex.key_tables.replaceAll=true

    introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=Cumulated $1                  

    introscope.agent.sqlagent.normalizer.regex.key_tables.caseSensitive=false

     

    A more generic URL grouping (to get many URLs under a single group) may be performed to reduce the number of URLs in the Frontend node

     

    https://wiki.ca.com/display/APMDEVOPS97/Configure+URL+Groups

     

    Further you may also choose to configure all URLs under a single group and if using CEM in your APM environment, can view Introscope Metrics for your critical Business Transactions under the Business Segment Node in the Investigator Tree.

     

    Group All URL Metrics in the Frontend Node - CA Application Performance Management - 9.7 - CA Wiki

    View CA CEM Metrics in the Workstation - CA Application Performance Management - 9.7 - CA Wiki



  • 4.  Re: SQL normalization and URL grouping

    Posted 09-03-2015 01:15 AM

    Hi Tanveer,

     

    Thanks for the answer.

     

    Can you explain about introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=Cumulated $1?

    I see $1, $3, etc in examples too. How are we arriving this pattern logic?


    Thanks,

    Karthik



  • 5.  Re: SQL normalization and URL grouping

    Posted 09-03-2015 03:05 AM

    Hi Karthik,

     


    $1, $2, $3 refers to the groups in the pattern, anything you put in () in the pattern becomes a group

     


    introscope.agent.sqlagent.normalizer.regex.key_tables.pattern= (A)(B)(C)

    $1 refers to pattern A

    $2 refers to pattern B ..and so on

     


    introscope.agent.sqlagent.normalizer.regex.key_tables.replaceFormat=$1

    means replace the pattern match with $1 which is A in this example

     


    introscope.agent.sqlagent.normalizer.regex.SELECTS.pattern=(.*)(SELECT)([\\s\\S]*)

    introscope.agent.sqlagent.normalizer.regex.SELECTS.replaceAll=true

    introscope.agent.sqlagent.normalizer.regex.SELECTS.replaceFormat=$2

    introscope.agent.sqlagent.normalizer.regex.SELECTS.caseSensitive=false

     


    means replace the pattern match in the SQL query (basically all SELECT queries) with only "SELECT" after normalization

     


    You may want to look at how Regular Expressions work in Java (the matcher used by APM is java.util.regex)

     


    Hope that helps