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

Last updated on OCTOBER 30, 2019

Applies to:

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:

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

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:

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

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.