DX Application Performance Management

Expand all | Collapse all

Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

Jump to Best Answer
  • 1.  Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 07:47 AM

    We are having an issue with one of our applications and I have initiated a trace because we are having queries of up to 9 seconds.

    The issue I have is that some of these queries are way longer that what I believe the limit to be on a metric length so i don't see the end that contains the "FROM table in DB" that the developers will need to fix their code

     

    2 Questions

    Can I increase this limit?

    If I can't is there a way to have a metric with the SQL complete Query

     

    Thanks Dave



  • 2.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit
    Best Answer

    Posted 01-12-2016 07:57 AM

    Hi Dave,

    The limit on the SQL statement is 990 characters.

    It can be increased by setting this property in the agent profile:

    introscope.agent.sqlagent.sql.maxlength=

     

    setting a value higher than 990

    However when we recommend use of that property, it is normally to reduce it. It can have an increased negative impact on the application in terms of response times.

     

    There is a field extension that would show detailed bind parameters in transaction traces which I believe would help for your scenario, I'll see if I can find a link to it, it is on the officially supported list so you would have engineering backup on it.

     

    Thanks,

    David



  • 3.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 08:03 AM

    Hi Dave,

    I can't find an external link to it but the thing you are looking for is called SQLAgent WIth Parameters, if you can reach out to your CA account rep locally, they should be able to sort something out for you.

    Thanks,

    David



  • 4.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 08:13 AM

    this is what we have instrumented at the moment from sqlagent.pbd

     

    #######################

    # Statement instrumentation

    # ================

     

     

    TurnOn: SQLAgentStatements

     

     

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute()Z StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery()Ljava/sql/ResultSet; StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;I)Z StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[I)Z StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[Ljava/lang/String;)Z StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;)Z StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate()I StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;)I StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;I)I StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[I)I StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[Ljava/lang/String;)I StatementBackendTracer "Backends|{database}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeBatch()[I StatementBackendTracer "Backends|{database}"

     

     

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute()Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery()Ljava/sql/ResultSet; DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery()Ljava/sql/ResultSet; ResultSetToSQLMappingTracer SQLAgent

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; ResultSetToDynamicSQLMappingTracer SQLAgent

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;I)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[I)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements execute(Ljava/lang/String;[Ljava/lang/String;)Z DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate()I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;I)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[I)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeUpdate(Ljava/lang/String;[Ljava/lang/String;)I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Update|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeBatch()[I DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

    TraceOneMethodWithParametersIfFlagged: SQLAgentStatements getResultSet()Ljava/sql/ResultSet; ResultSetToSQLMappingTracer SQLAgent



  • 5.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 09:34 AM

    Hi Dave:

      Is David's answer about a configuration setting and looking for a field extension. Or is further assistance is needed?

     

    Thanks

    Ha German



  • 6.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 09:40 AM

    Hi Hallett

    I am still looking for the field extension for the pad file, the manual doesn't offer much in adding sql query metric with the query as a value.

     

    Dave



  • 7.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 08:08 AM

    I don't find it in the agent profile so I'm guessing it needs to be added to the end.

    I also agree it will cause performance degradation under usually circumstances but we have a client facing app with a 2 hour delay at the moment.

     

    Dave



  • 8.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 08:30 AM

    Hi Dave,

    Yes the property needs to be completely added in to the file, it can go at the top, middle or bottom of the profile, the placing is really not important for it to work but if you want to put it at the end so you know where it will be, that's fine.

    The extra thing I'm talking about is an extra pbd and extra jarfiles as well so it's more complicated than just this simple update.

     

    Thanks,

    David



  • 9.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 09:38 AM

    Hi David

    Yes I have a custom.pbd i configure for these situations.



  • 10.  Re: Is there a way to get complete SQL Query that have been truncated to what I believe is the metric length limit

    Posted 01-12-2016 12:45 PM

    HI Dave,

    I'm raising a support case to pass on the custom extension, it's the best way to get this to you at the moment.

    I'll send a mail from there so you have more info about it.

     

    Thanks,

    David