Search for a SQL statement to report on

When you create a report to show information about a single SQL statement or a group of SQL statements, you must identify the SQL statements. When you click the Search button on the Reports page or the Select Items to Display dialog, the following Search dialog opens:

To search for SQL statements, complete the following steps.

1. Select a SQL Search option, and then enter a string in the Search String:

   • Name and Hash (selected by default)

     If you named the SQL statement, enter part of the name. If not, enter part of the hash value that DPA uses to identify unnamed SQL statements.

   • SQL Text

     Enter a string that is included in the SQL statement. For example, entering where returns all SQL statements that include a WHERE clause (up to the limit of 1000 search results).

     The search string can include SQL wildcard characters. For example, the following search string uses % as a substitute for 0 or more characters:

     select%Project%where%Status

     It returns all SELECT statements against a table named Project that include a column named Status in the WHERE clause.

   • SQL ID

     For SQL statements that run against an Oracle 10g or later database, enter part of the SQL ID.

2. Click Search.

   By default, the Search Results box lists the name or hash value of each SQL statement, as well as the SQL statement's total wait time for the last seven days. The results are ordered by wait time with the highest waits first.

   

   • To change the amount of wait time shown in the search results, set the advanced option REPORT_SEARCH_WAIT_TIME_DAYS.

   • To list the search results in alphanumeric order without wait times, deselect Order results by wait time. Then click Search again.

3. Locate the SQL statement you want to report on, and select it.

   For more information about any SQL statement, select it and click View SQL Properties to see the SQL text.

4. Click OK to add the SQL statement to the report.