Oracle Cloud Infrastructure Documentation

Search SQLs

You can specify conditions and filters to search for the SQL statements currently running on the Managed Database or available in retained AWR data.

Note

The Search SQLs feature in Database Management Diagnostics & Management is currently not available for Autonomous AI Databases. Any mention of "Managed Databases" in this section only refers to External Databases and Oracle Cloud Databases for which Diagnostics & Management is enabled.

A dynamic search in the Search SQLs section allows you to query and obtain real-time or historic data in cursor cache, AWR snapshots and baselines, and SQL tuning sets.

Before you search for SQL statements, you must ensure that you've set the Advanced diagnostics preferred credential or a session credential. For information, see Set and Use Credentials.

For information on the privileges required to search for SQLs, see Advanced Diagnostics Credential – User Privileges.

To search for SQL statements:

  1. Go to the Managed database details page and on the left pane under Resources, click Search SQLs.
  2. In the Search SQLs section, specify filter conditions:
    1. Select one of the following data sources in which you want to search for SQL statements:
      • Cursor cache
      • AWR snapshots
      • AWR baseline
      • SQL tuning set

      Depending on the selected data source, you may have to provide additional information. For example, if you select AWR snapshots as the data source, you have to select the duration of the snapshot range in the Time period field.

    2. Select one of the following options to specify filtering criteria:
      • Any: The SQL statements that meet any of the specified criteria are filtered and displayed.
      • All: The SQL statements that meet all the specified criteria are filtered and displayed.
    3. Select a filter attribute or qualifier to filter data, an operator to specify how the filter attribute relates to the value, and enter a value of the specified filter attribute.

      By default, the Parsing schema name, SQL text, SQL ID, Plan hash value, and Elapsed time (sec) filter attributes are listed and you can specify the operator and value. To add an additional filter attribute or a column to be displayed in the search results, click Add a filter or column. You can click the Remove icon (Remove) to remove a single filter attribute or click Remove all to remove all filter attributes.

      For filter attributes such as Parsing schema name and SQL text, the Show columns check box is selected by default and cannot be deselected as this information will be displayed for the SQL statements listed in the search results.

    4. Click Search.
The filtered SQL statements in the selected data source are listed and the default sort order is by elapsed time, in descending order. Information such as the SQL ID, SQL text, and Parsing schema name is displayed by default and additional columns such as CPU time (sec) and Module are displayed if you added them when setting filter attributes and selected the Show column check box. You can use the Search field to search for specific SQL statements.

You can click the Actions icon (Actions) for a SQL statement and perform the following tasks:

  • View SQL details: Click to view SQL details in Performance Hub.
  • Download SQL active report: Click to download the SQL active report.
  • Save to SQL tuning set: Click to save the SQL statement to a new or existing SQL tuning set.