Search for SQL statements in DPA

Use the Find SQL feature to search for any SQL statement based on what you know about it. You can specify a time range and then apply any combination of filters and search strings:

• Even if you don't know anything about the SQL text, you can apply filters to locate SQL statements that were run by a certain user, as part of a certain application, from a certain computer, or against a specific database.

• If you know something about the SQL text, you can enter search strings such as table names or the operation being performed.

The Find SQL feature is available in DPA 2021.1 and later.

See the following sections: 

Example use cases

Examples of possible use cases include:

• A user with the user name jsmith complained about the performance of the application acme_ecommerce, which he ran at about 10:00 AM yesterday. To investigate, set the time period, and then filter by user and application.

• You tuned a SQL statement, and it no longer appears in the Trends charts. You want to open the tuned version in the Query Details page to see the results of your tuning. Because you are familiar with the statement, you can search for terms or phrases from the SQL text.

• Deleting orders from a third-party application is suddenly very slow. You do not have access to the code, but you want to analyze DELETE statements related to the ORDERS table. You can search for SQL statements that include DELETE and ORDERS. To further narrow the results, you can also filter by the application name.

Open the Find SQL page

1. From the DPA home page, click the name of the database instance you want to search.

   The Trends tab lists the SQL statements with the highest wait times.

2. At the top of the page, click Find SQL.

   The Find SQL page opens. If the Find SQL feature is not enabled for the selected database instance, the Find SQL page displays a message. You can enable the Find SQL feature for all database instances or for a specific database instance.

Select a time period

DPA searches for SQL statements that ran during the selected time period, which is displayed at the top of the page. When you open the Find SQL page, it defaults to the last 24 hours. You can select a different time period.

1. Click the date range at the top of the page to open the date picker.

2. Select a predefined time period or enter specific dates.

   The advanced option CLEAN_DAYS_OF_FIND_SQL_DATA specifies the maximum number of days Find SQL data is stored (by default, 30 days). On the Find SQL page, if you specify a time period that is larger than the CLEAN_DAYS_OF_FIND_SQL_DATA value, DPA changes the selected period to the maximum number of days.

   If you increase the value of CLEAN_DAYS_OF_FIND_SQL_DATA to more than 30 days, be aware that larger values require more disk space and might affect performance. The maximum value is 90 days.

   

3. Click Search to repeat the current search in the selected time period.

Apply filters

Depending on what type of database instance is selected, some or all of the following filter categories are available:

• Database user: The user ID that ran the SQL statement.
• Program: The application that ran the SQL statement.
• Database: The database that the SQL statement queried.
• Machine: The computer from which the SQL statement ran.

To apply filters:

1. In the upper-left corner, click Filters.

   The filter categories available for the selected database instance are expanded by default.

   Click the vertical ellipsis () in the upper-right corner of the Filters box to expand or collapse all filter categories.

2. To search for a value, enter the search string in the Filters Search field.

   Only values that include the search string are displayed.

   

3. If a filter category includes more than 10 items, click the Show All link.

   

   A dialog opens, from which you can page through to see all items, change the sort order, or search.

   

4. Select one or more filters, and click Search to apply them.

   The search results include only SQL statements that match all filters. If no search terms are applied with the filters, results are ordered by wait time.

   The applied filters are listed above the Filter button and Search bar.

   

   Click the X beside a filter to remove it, or click Clear All to remove all filters.

Enter search strings

If you know any terms that are likely to be part of the SQL text, enter them in the Search box and click Search. For example, you can search for table names, column names, or the type of operation. If you have named the SQL statement, you can search for the SQL statement name. You can also search for the SQL statement hash.

When you enter search terms, the results are ordered by relevance. For example, if you enter multiple terms, SQL statements with all of the terms are listed before those with only some of the terms.

Simple and advanced search modes

When you enter search terms, you can choose between two modes:

• Simple mode is similar to an internet search engine. You can enter individual terms, or you can use double quotes to identify a phrase. In simple mode, you can enter multiple terms but no more than one phrase. All entries are separated by an implicit OR, and so statements that include any of the entries are returned.

• In advanced mode, you can use Boolean operators, wildcards, grouping, and other advanced features to refine your search. You can also enter multiple phrases.

For examples of phrases and terms you can enter in either mode, and for descriptions of the features available in advanced mode, see Find SQL search rules.

For example, in simple mode you can enter the following search terms to find information about INSERT and SELECT operations against the ORDERS table:

insert select orders

SQL statements with INSERT or SELECT operations against the ORDERS table would be ranked higher because they contain more of the search terms, but the results would also include SQL statements that performed DELETE operations against the ORDERS table, and SQL statements that performed INSERT or SELECT operations against other tables.

To narrow the search results, you can change the search mode to advanced. The following example uses grouping and Boolean operators to limit the results to SQL statements that include INSERT or SELECT and ORDERS:

(insert OR select) AND orders

If you have contextual information such as what user, application, or machine executed the SQL statement, you can apply filters in addition to search terms to narrow the search results. Each time you add or change search terms and filters, click Search again to refresh the results.

Change the search mode

When you access the Find SQL page, simple mode is selected by default. To change the mode:

1. Move your mouse pointer over the mode name in the search box to display descriptions of each search mode.

2. In the description popup, click Enable to change modes.

   

Get more information about a SQL statement

Click the blue arrow to the right of the Wait Time to display statistics about the SQL statement and the complete SQL text.

Click the SQL hash or SQL name to display detailed information about the SQL statement on the Query Details page.

Share search parameters

Click Share at the top of the page to generate a link that other users can click to display the current search results for the selected database. The link is saved to the clipboard. You can paste it into an email or other document.