How to Submit a Testcase to Oracle Support for Reproducing an Execution Plan (Doc ID 390610.1)

Last updated on SEPTEMBER 18, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Oct-2011***


Goal

During the investigation of a SQL Tuning issue, it is often useful for Oracle Support to be able to work directly on a reproduction of the execution plan in a testing environment.

To accomplish this, a number of items from the original environment can be collected and uploaded to the Service Request.

These items will enable the Support Engineer working the case to build a testcase schema where the Optimizer produces the same execution plan as occurs on the original system. This can be used to analyze the issue in depth, find a root cause, develop and test workarounds and can also be used in a defect report, should the problem prove to be bug-related.

The easiest way to collect a Testcase is to use the SQLTXPLAIN Tool which automatically produces testcases in a number of different formats (With and without SQLT dependencies and using DBMS_SQLDIAG) as well as helping to diagnose SQL statements that are performing poorly. See:

<Document  215187.1> SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly

<Document 1470811.1> How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data
<Document 1465741.1> How to use SQLT (SQLTXPLAIN) to create a testcase containing application data

You can also create a testcase separately using the DBMS_SQLDIAG package as outlined in:

<Document 727863.1> How to Create a SQL-testcase Using the DBMS_SQLDIAG

In cases where parsing fails due to internal errors ORA-600, core dumps, ORA-7445 or other ORA-nnn errors, SQLTXPLAIN cannot be used as it relies on successful parsing before it can extract and generate testcase files. The current article is therefore needed for such cases.
An  alternative is to use the SQLtestcase builder to generate a testcase from ADRCI:

<Document 1174105.1> How to Run SQL Testcase Builder from ADRCI [Video]


Instructions for collecting and providing statistics to support can be found in:

<Document 242489.1> Transferring Optimizer Statistics to Support

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms