XPLORE Module

For many Optimzer problems in SQL, such as abnormal execution plan, slow SQL, Wrong Result, etc.
If the test environment can be reproduced, it can be through the xplore function of SQLT.
Adjust various parameters and Optimzer's Fix Control to find the avoidance method and Root Cause.

Other references:
Introduction to SQL TXPLAIN (SQLT), an artifact of SQL tuning and diagnosis

Attention points

The following points should be noted when using xplore function:

Use XPLORE only when ALL these conditions are met:
    SQL performs poorly or returns wrong results while using a "bad" plan.
    The bad plan can be reproduced on a test system (no data is preferred).
    A "good" plan can be reproduced on the test system by switching OFE.
    You need to narrow reason to specific parameter or bug fix control.
    You have full access to the test system, including SYS access.
Do not use XPLORE when ANY of these conditions is true:
    The SQL statement may cause corruption or update data.
    There is high volume of data in tables referenced by SQL.
    The execution of the SQL may take longer than a few seconds.

Execution example

The following is an example of how xplore is executed:

SQL*Plus: Release 12.2.0.1.0 Production on Sun May 21 03:16:55 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> START install.sql
Test Case User: testxplore
Password: testxplore
...
Installation completed.
You are now connected as testxplore.
1. Set CBO env if needed
2. Execute @create_xplore_script.sql
SQL> @create_xplore_script.sql
Parameter 1:
XPLORE Method: XECUTE (default) or XPLAIN
"XECUTE" requires /* ^^unique_id */ token in SQL
"XPLAIN" uses "EXPLAIN PLAN FOR" command
Remember EXPLAIN PLAN FOR does not perform bind peeking
Enter "XPLORE Method" [XECUTE]: XECUTE
Parameter 2:
Include CBO Parameters: Y (default) or N
Enter "CBO Parameters" [Y]:
Parameter 3:
Include Exadata Parameters: Y (default) or N
Enter "EXADATA Parameters" [Y]: N
Parameter 4:
Include Fix Control: Y (default) or N
Enter "Fix Control" [Y]: Y
Parameter 5:
Generate SQL Monitor Reports: N (default) or Y
Only applicable when XPLORE Method is XECUTE
Enter "SQL Monitor" [N]: Y
...
Review and execute @xplore_script_1.sql

Before execution, you need to create an object SQL file including /*^ unique_id*/ such as test.sql.
Then plore_script_1.sql is executed
Example:
test.sql
SELECT /* ^^unique_id */ …

Implementation:
SQL>@xplore_script_1.sql
testxplore
Parameter 1:
Name of SCRIPT file that contains SQL to be xplored (required)
Note: SCRIPT must contain comment /* ^^unique_id */
Enter value for 1: test.sql
Parameter 2:
Password for testxplore (required)
Enter value for 2: testxplore

Copyright Statement: This article is the original article of the blogger, reprinted must indicate the source, I reserve all relevant rights! http://blog.csdn.net/lukeunique

Welcome to the Wechat Subscription Number: Oracle Database Technology (Teacher What)

Reference resources:

sqlt/utl/xplore/readme.txt