SQL normalization and URL grouping

[nkarthik]

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

[nkarthik]

Hello Community,

 

Any updates on this?

 

Thanks,

Karthik

[danta01]

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

[nkarthik]

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

[danta01]

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