22 Gathering Diagnostic Data with SQL Test Case Builder

A SQL test case is a set of information that enables a developer to reproduce the execution plan for a specific SQL statement that has encountered a performance problem. SQL Test Case Builder is a tool that automatically gathers information needed to reproduce the problem in a different database instance.

This chapter contains the following topics:

22.1 Purpose of SQL Test Case Builder

SQL Test Case Builder automates the sometimes difficult and time-consuming process of gathering and reproducing information about a problem and the environment in which it occurred.

The output of SQL Test Case Builder is a set of scripts in a predefined directory. These scripts contain the commands required to re-create all the necessary objects and the environment. After the test case is ready, you can create a zip file of the directory and move it to another database, or upload the file to Oracle Support.

22.2 Concepts for SQL Test Case Builder

This section contains the following topics:

22.2.1 SQL Incidents

In the fault diagnosability infrastructure of Oracle Database, an incident is a single occurrence of a problem. A SQL incident is a SQL-related problem. When a problem (critical error) occurs multiple times, the database creates an incident for each occurrence. Incidents are timestamped and tracked in the Automatic Diagnostic Repository (ADR). Each incident is identified by a numeric incident ID, which is unique within the ADR.

SQL Test Case Builder is accessible any time on the command line. In Oracle Enterprise Manager Cloud Control (Cloud Control), the SQL Test Case pages are only available after a SQL incident is found.

See Also:

22.2.2 What SQL Test Case Builder Captures

SQL Test Case Builder captures permanent information about a SQL query and its environment.

The information includes the query being executed, table and index definitions (but not the actual data), PL/SQL packages and program units, optimizer statistics, SQL plan baselines, and initialization parameter settings. Starting in Oracle Database 12c, SQL Test Case Builder also captures and replays transient information, including information only available as part of statement execution.

SQL Test Case Builder supports the following:

  • Adaptive plans

    SQL Test Case Builder captures inputs to the decisions made regarding adaptive plans, and replays them at each decision point. For adaptive plans, the final statistics value at each buffering statistics collector is sufficient to decide on the final plan.

  • Automatic memory management

    The database automatically handles the memory requested for each SQL operation. Actions such as sorting can affect performance significantly. SQL Test Case Builder keeps track of the memory activities, for example, where the database allocated memory and how much it allocated.

  • Dynamic statistics

    Regathering dynamic statistics on a different database does not always generate the same results, for example, when data is missing. To reproduce the problem, SQL Test Case Builder exports the dynamic statistics result from the source database. In the testing database, SQL Test Case Builder reuses the same values captured from the source database instead of regathering dynamic statistics.

  • Multiple execution support

    SQL Test Case Builder can capture dynamic information accumulated during multiple executions of the query. This capability is important for automatic reoptimization.

  • Compilation environment and bind values replay

    The compilation environment setting is an important part of the query optimization context. SQL Test Case Builder captures nondefault settings altered by the user when running the problem query in the source database. If any nondefault parameter values are used, SQL Test Case Builder re-establishes the same values before running the query.

  • Object statistics history

    The statistics history for objects is helpful to determine whether a plan change was caused by a change in statistics values. DBMS_STATS stores the history in the data dictionary. SQL Test Case Builder stores this statistics data into a staging table during export. During import, SQL Test Case Builder automatically reloads the statistics history data into the target database from the staging table.

  • Statement history

    The statement history is important for diagnosing problems related to adaptive cursor sharing, statistics feedback, and cursor sharing bugs. The history includes execution plans and compilation and execution statistics.

22.2.3 Output of SQL Test Case Builder

The output of the SQL Test Case Builder is a set of files that contains the commands required to re-create all the necessary objects and the environment. By default, SQL Test Case Builder stores the files in the following location, where incnum refers to the incident number and runnum refers to the run number:

$ADR_HOME/incident/incdir_incnum/SQLTCB_runnum

For example, a valid output file name could be as follows:

$ORACLE_HOME/log/diag/rdbms/dbsa/dbsa/incident/incdir_2657/SQLTCB_1

You can specify a nondefault location by creating an Oracle directory and invoking DBMS_SQLDIAG.EXPORT_SQL_TESTCASE, as in the following example:

CREATE OR REPLACE DIRECTORY my_tcb_dir_exp '/tmp';
 
BEGIN 
  DBMS_SQLDIAG.EXPORT_SQL_TESTCASE (
    directory => 'my_tcb_dir_exp'
,   sql_text  => 'SELECT COUNT(*) FROM sales'
,   testcase  => tco
);
END;

See Also:

Oracle Database Administrator's Guide to learn about the structure of the ADR repository

22.3 User Interfaces for SQL Test Case Builder

You can access SQL Test Case Builder either through Cloud Control or using PL/SQL on the command line.

22.3.1 Graphical Interface for SQL Test Case Builder

Within Cloud Control, you can access SQL Test Case Builder from the Incident Manager page or the Support Workbench page.

22.3.1.1 Accessing the Incident Manager

This task explains how to navigate to the Incident Manager from the Incidents and Problems section on the Database Home page.

To access the Incident Manager:

  1. Log in to Cloud Control with the appropriate credentials.

  2. Under the Targets menu, select Databases.

  3. In the list of database targets, select the target for the Oracle Database instance that you want to administer.

  4. If prompted for database credentials, then enter the minimum credentials necessary for the tasks you intend to perform.

  5. In the Incidents and Problems section, locate the SQL incident to be investigated.

    In the following example, the ORA 600 error is a SQL incident.

  6. Click the summary of the incident.

    The Problem Details page of the Incident Manager appears.

    The Support Workbench page appears, with the incidents listed in a table.

See Also:

22.3.1.2 Accessing the Support Workbench

This task explains how to navigate to the Incident Manager from the Oracle Database menu.

To access the Support Workbench:

  1. Log in to Cloud Control with the appropriate credentials.

  2. Under the Targets menu, select Databases.

  3. In the list of database targets, select the target for the Oracle Database instance that you want to administer.

  4. If prompted for database credentials, then enter the minimum credentials necessary for the tasks you intend to perform.

  5. From the Oracle Database menu, select Diagnostics, then Support Workbench.

    The Support Workbench page appears, with the incidents listed in a table.

See Also:

Online help for Cloud Control

22.3.2 Command-Line Interface for SQL Test Case Builder

The DBMS_SQLDIAG package performs tasks relating to SQL Test Case Builder.

This package consists of various subprograms for the SQL Test Case Builder, some of which are listed in Table 22-1.

Table 22-1 SQL Test Case Functions in DBMS_SQLDIAG

Procedure Description

EXPORT_SQL_TESTCASE

Exports a SQL test case to a user-specified directory

EXPORT_SQL_TESTCASE_DIR_BY_INC

Exports a SQL test case corresponding to the incident ID passed as an argument

EXPORT_SQL_TESTCASE_DIR_BY_TXT

Exports a SQL test case corresponding to the SQL text passed as an argument

IMPORT_SQL_TESTCASE

Imports a SQL test case into a schema

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SQLDIAG package

22.4 Running SQL Test Case Builder

This tutorial explains how to run SQL Test Case Builder using Cloud Control.

Assumptions

This tutorial assumes the following:

  • You ran the following EXPLAIN PLAN statement as user sh, which causes an internal error:

    EXPLAIN PLAN FOR
      SELECT unit_cost, sold
      FROM   costs c,
             ( SELECT /*+ merge */ p.prod_id, SUM(quantity_sold) AS sold
               FROM   products p, sales s
               WHERE  p.prod_id = s.prod_id
               GROUP BY p.prod_id ) v
      WHERE  c.prod_id = v.prod_id;
    
  • In the Incidents and Problems section on the Database Home page, a SQL incident generated by the internal error appears.

  • You access the Incident Details page, as explained in "Accessing the Incident Manager".

To run SQL Test Case Builder:

  1. Click the Incidents tab.

    The Problem Details page appears.

  2. Click the summary for the incident.

    The Incident Details page appears.

  3. In Guided Resolution, click View Diagnostic Data.

    The Incident Details: incident_number page appears.

  4. In the Application Information section, click Additional Diagnostics.

    The Additional Diagnostics subpage appears.

  5. Select SQL Test Case Builder, and then click Run.

    The Run User Action page appears.

  6. Select a sampling percentage (optional), and then click Submit.

    After processing completes, the Confirmation page appears.

  7. Access the SQL Test Case files in the location described in "Output of SQL Test Case Builder".

See Also:

Online help for Cloud Control