Oracle Cloud Infrastructure Documentation

Real-time SQL Activity

To view Real-time SQL Activity:

  1. Open the Performance Hub page from the database details page, if not already open.

    Note

    For more information about how to navigate to the Performance Hub page, see:

  2. Use Quick Select, Time Range, Time Zone, and time slider to set the time period of the SQL activity that you want to monitor.

You can view and monitor real-time SQL activity for databases in Oracle Cloud Infrastructure using the ASH Analytics tab or the SQL Monitoring tab.

View Real-time SQL Data from ASH Analytics

  1. On the Performance Hub page, click ASH Analytics to display the ASH Analytics tab, if not already open.

  2. Scroll down to the table containing the list of SQL queries.

  3. Click an ID number in the SQL ID column to display detailed information about a specific SQL statement. It opens the Performance Hub SQL Details page.

    The Performance Hub SQL Details page contains the following tabs:

    • Summary - shows the overall information about the SQL statement and the SQL plans.

    • ASH Analytics - shows the active sessions and two tables. The default information shown for the SQL query is Wait Class and User Session. You can click the menu containing the Wait Class option and select a specific parameter and subparameter to display the associated SQL data. Click a session in the User Session column to display the SQL activity during the selected user session.

    • Execution Statistics - shows the SQL statement execution in Tabular Execution Plan or Graphical Explain Plan.

      For more information, see Graphical Explain Plan.

    • SQL Monitoring - displays SQL data for the selected SQL statement. Click an SQL ID to display detailed data about the SQL statement. Depending on the database selected, the Details section includes Plan Statistics, Parallel, SQL Text, and Activity tabs.

      In the Details section of the page:

      • Click Plan Statistics to view the Tabular Execution Plan and Graphical Explain Plan.

        For more information, see Graphical Explain Plan.

      • Click Parallel to view the Parallel Server and the Instances and Parallel Groups it contains.

      • Click SQL Text to view the text of the active SQL query.

      • Click Activity to view the active sessions of the database that is selected. Use the menu on the Details page to view either the Active Sessions Resource Type or Plan Line of the selected SQL operation.

    • Plan Control - contains details about existing SQL profiles, SQL patches, and SQL plan baselines for a SQL statement.

      • SQL Profiles and SQL Patches

        SQL profiles are a collection of statistics about a SQL statement that can help the query optimizer to select the best plan.

        SQL patches are a mechanism to tell the optimizer to change the plan to address poor performance or errors.

        The table in the SQL Profiles and SQL Patches tab displays name, type, creation method, category, enabled status, force matching, created timestamp, last modified timestamp, and container name fields.

      • SQL Plan Baselines

        SQL plan baselines are a set of one or more accepted plans for a SQL statement. Each accepted plan contains a set of hints, a plan hash value, and other plan-related information.

        The table in the SQL plan baselines tab displays name, SQL handle, accepted status, enabled status, fixed status, optimizer cost, auto purge status, origin, version, created timestamp, last modified timestamp, and container name fields.

  4. Click the Back arrow at the top-left corner of the page to go back to the ASH Analytics tab.

View Real-time SQL Data from SQL Monitoring

  1. On the Performance Hub page, click SQL Monitoring to display the SQL monitoring tab.

  2. In the SQL ID column, click an ID to display detailed information about the specific SQL statement execution.

    This action displays the Performance Hub Real-time SQL Monitoring page.

  3. Scroll down to display the SQL activities.

    Depending on the database selected, the Details section includes Plan Statistics, Parallel, SQL Text, and Activity tabs.

    In the Details section of the page:

    • Click Plan Statistics to view the Tabular Execution Plan and Graphical Explain Plan.

      For more information, see Graphical Explain Plan.

    • Click Parallel to view the Parallel Server and the Instances and Parallel Groups it contains.

    • Click SQL Text to view the text of the active SQL query.

    • Click Activity to view the active sessions of the database that is selected.

      Use the menu on the details page to view either the Active Sessions Resource Type or Plan Line of the selected SQL operation.

  4. Click the Back arrow at the top-left corner of the page to go back to the SQL Monitoring tab.