SAP Basis Tech Supports

Symptom
You want to analyze a critical SQL statement more closely.

Reason and Prerequisites
Although the tools (for example, the shared cursor cache in transaction ST04/DBACOCKPIT) provided by SAP are usually sufficient for the analysis and optimization of simple SQL statements, it usually takes more effort to solve more complex problems.
As of Oracle 10g, many additional details for SQL statements are available within the features, for example, the Automatic Workload Repository (AWR) and the Active Session History (ASH). This note provides a command that gathers lots of useful information for a specific SQL statement.
To use this command, the SQL_ID of the SQL statement must be known. As of Oracle 10g, the SQL_ID is a unique identifier for an SQL statement that is contained in all of the relevant database views and, with more recent SAP releases, is displayed within the DBACOCKPIT Explain plans.
In addition, the Oracle Diagnostic Package must be licensed since the script accesses AWR tables and ASH tables. For more information, see Note 740897.
The display of the script contains the following information for the relevant SQL statement:
Complete SQL statement textShared cursor cache information (current and in history)View definitions (if views are used during the access)Execution plans (current and historical)SQL work areas (current and historical)Wait situationsObjects that were processed by the SQL statementPeak timesSegment statistics for the affected tables and indexesIndex informationCBO statistics for indexes, tables and columnsDML operations since the last time the statistics were created.Fragmentation information for tables and indexesSegment information for the affected tables and indexesLOB informationRelevant parameter settings and parameter changesBind variable content
Solution
Proceed as follows if you want to use the attached data collection command:
1. Download the attached file sql.txt. This file contains the data collection command.2. Determine the SQL_ID of the SQL statement that is to be analyzed.3. Proceed as described in the header of the script to execute the script in transaction DBACOCKPIT or using SQLPLUS.4. Runtimes of a few minutes are often unavoidable. If the access runs for longer, refer to the notes in the header of sql.txt.5. To save the result that is displayed in RSORADJV or DBACOCKPIT, choose “Print Preview” or “List Output”, depending on the release. Then enter “%pc” in the OK code field and download the result into a file or into the clipboard.
A typical result of the command is contained in the attachment as sql_example.txt.