Exclude SQL statements from DPA

Certain long-running SQL statements might not be candidates for tuning (for example, SQL statements associated with database backups, replication, or data loads). To prevent these statements from dominating Trends charts or producing tuning advisors that are not actionable, you can exclude them from DPA.

Before excluding SQL statements, consider the possible impacts. If an excluded SQL statement begins affecting your database performance, you will not see the issue in DPA because of the exclusion.

Determine which option meets your needs

DPA provides three options for excluding SQL statements. Use this section to determine which option meets your needs, and then see the following sections for implementation instructions.

Option 1: Exclude a specific SQL statement from DPA charts and analysis

Use the SQL Properties dialog box to exclude the selected SQL statement from DPA Trends charts, DPA tuning advisors, or both. When you exclude a statement from tuning advisors, DPA does not generate query advisors for it or consider it when generating table tuning advisors.

DPA recommends using this method if possible because it is safe (there is no risk of losing data) and easy (it is done through the interface).

Data about the statement is excluded from:	All DPA Trends charts that represent one or more days, including charts that represent previous periods All tuning advisors, including those generated for previous periods
Data about the statement is still included in:	Charts that represent less than one day Reports Anomaly detection If the statement runs on a regular schedule with a predictable amount of wait time, it would not cause DPA to detect an anomaly during the period when it runs. Higher wait times would be normal during that period.
If you revert the exclusion:	DPA charts and advisors for the exclusion period show data about the excluded statement again. With this method, DPA continues to collect and store data about the SQL statement, and so the data is available.

Option 2: Prevent DPA from storing data about a specific SQL statement

Run a statement against the DPA repository database that prevents DPA from storing the data that it collects about the specified SQL statement. The excluded SQL statement is identified by its DPA hash value.

This option for excluding SQL statements requires admin privileges.

Data about the statement is excluded from:	Charts, tuning advisors, and reports that represent time periods after you ran the SQL statement Anomaly detection
Data about the statement is still included in:	Charts, tuning advisors, and reports that represent periods before you ran the statement
If you revert the exclusion:	DPA charts and advisors for the exclusion period do not show data about the statement because that data is not stored in the DPA repository database.

Option 3: Exclude SQL statements from collection based on criteria in the WHERE clause

Modify the WHERE clause of the DPA quickpoll query to prevent DPA from collecting information about SQL statements that meet certain criteria. With this method, you can exclude statements that come from a certain program, user, host, or a combination of factors. For example, you can exclude a specific SQL statement when it is run by a certain user, or you can exclude all SQL statements coming from a user on a certain computer.

This method cannot be used for statements that run on a Sybase monitored database instance.

Data about the statement is excluded from:	Charts, tuning advisors, and reports that represent time periods after you modified the WHERE clause Anomaly detection
Data about the statement is still included in:	Charts, tuning advisors, and reports that represent periods before you modified the WHERE clause
If you revert the exclusion:	DPA charts and advisors for the exclusion period do not show data about the statement because that data was never collected.

Option 1: Exclude a specific SQL statement from DPA charts and analysis

1. In any chart legend, click the name or hash value that represents the SQL statement.

   The Query Details page displays information about the SQL statement.

2. In the upper-right corner, click SQL Properties.

   The SQL Properties dialog opens.

3. Under Advanced Settings, clear one or both of the following options:

   • Clear the Show in Trends charts setting to remove the SQL statement from multi-day or one-day Trends charts. If you drill in to a time period less than one day, charts include the SQL statement.

   • Clear the Enable advisor analysis setting to exclude this statement from the analysis that DPA runs to generate query advisors and table tuning advisors. When analysis is disabled, DPA does not detect problems with the SQL statement.

     When you clear the Show in Trends charts setting, both options are cleared. DPA does not perform analysis on SQL statements that are not shown in the Trends charts.

   

4. Click Save.

You can add excluded SQL statements back to Trends charts and analysis at any time.

Option 2: Prevent DPA from storing data about a specific SQL statement

1. Log in to DPA using an account with admin privileges.

2. Get the hash value that identifies the SQL statement:

   1. In any DPA chart legend, click the name or hash value that represents the SQL statement.

      The Query Details page displays information about the SQL statement.

   2. In the upper-right corner, click SQL Properties.
   3. Copy the hash value from the top of the SQL Properties dialog box.
3. Open the DB query tool in DPA: 

   1. From the DPA menu in the upper-right corner, click Options.

   2. Under Support > Utilities, click DB Query tool.

4. To get the database ID, enter the following query and click Execute Query:

   select ID, name from cond;

   The query returns the names and IDs of all monitored database instances.

   

5. Enter the following query (replacing the variables with your database ID and SQL hash value), and click Execute Query:

   INSERT INTO con_qp_exclude (dbid, type, value, origin) VALUES (databaseID, 'H', 'sqlHash', 'U');

   All future data collection excludes this SQL statement. Past data is not purged, so DPA charts and reports that represent previous time periods will still include the SQL statement.

Option 3: Exclude SQL statements from collection based on criteria in the WHERE clause

1. From the DPA menu in the upper-right corner, click Options.

2. Under Administration > Configuration, click Advanced Options.

   The System Options tab lists options that apply to all database instances.

3. Click DB Instance Options and select the database instance on which the SQL statements run.
4. Select Support Options.
5. Click the name of the QUICKPOLL_WHERE_CLAUSE to open the Edit Option dialog.
6. Enter the phrase to include in the WHERE clause that specifies the SQL statements that should not be collected. Use the syntax appropriate for the database type. See the examples in the following sections.

7. Click Update, and then restart DPA.

   All future data collection excludes the SQL statements. Past data is not purged, so DPA charts and reports that represent previous time periods will still include the statements.

To revert the exclusion, repeat this procedure to remove the criteria from the quickpoll WHERE clause.

Examples for SQL Server

Example 1: Exclude all SQL statements from the TSQL program logging in from the server HPSERVER:

and not (s.program_name='TSQL' and s.hostname='HPSERVER')

Example 2: Exclude the specified SQL statement if it comes from a certain user, but do not exclude it if it comes from other users. Use the SQL_handle (not the DPA hash value) to identify the SQL statement.

and not (s.loginame='Bob' and s.sql_handle=0x00987097097897)

Example 3: Exclude all SQL statements executed in the master database by the dataload program:

and not (db_name(s.dbid)='master' and s.program_name='dataload')

Examples for Oracle

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER.

and not ("u".ksusepnm ='SAP.exe' and "u".ksusemnm='HPSERVER')

Example 2: Exclude the specified SQL statement if it comes from a certain user, but do not exclude it if it comes from other users.

and not ("u".ksuudlna='Bob' and "u".ksusesqh =97097897)

Examples for Db2

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER:

and not (ai.appl_name ='SAP.exe' and ai.client_nname='HPSERVER')

Example 2: Exclude a dynamic SQL statement if it comes from a certain user, but do not exclude it if it comes from other users.

and not (ai.auth_id='Bob' and s.stmt_text like '%insert into bad_table%')

Example 3: Exclude a static SQL statement if it comes from a certain user, but do not exclude it if it comes from other users.

and not (ai.auth_id='Bob' and st.text like '%insert into bad_table%')

Examples for MySQL

Example 1: Exclude all SQL statements from the SAP.exe program logging in from the server HPSERVER:

and not (program_name = 'SAP.exe' AND host='HPSERVER')

Example 2: Exclude a dynamic SQL statement if it comes from a certain user, but do not exclude it if it comes from other users:

and not (user = 'BOB' AND statement_sql like '%insert into bad_table%')

Examples for PostgreSQL

Example 1: Exclude the queries coming from a certain client address:

and a.client_addr != '10.140.66.28'

Example 2: Exclude a wait event type timeout that is coming from a client backend:

and a.wait_event_type != 'Timeout' and a.backend_type != 'client backend'