Last updated on SEPTEMBER 18, 2014
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Oct-2011***
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 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:
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:
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms