Oracle execution plan changes cause Incomplete Data Processing

Use use_hashhint or outlines to stabilize the execution plan. For time reasons, the hint method is used. Hashjoin not only solves nestedloops connection

Use use_hash hint or outlines to stabilize the execution plan. For time reasons, the hint method is used. Hash join not only solves nested loops connection

Symptom: the job is always running, and the extraction is always running. The log record in the process is located because the data of the X system is always stuck. However, the SQL statement being executed does not have any results. The data extraction has been running for one day and is not complete yet.

Because no SQL statement is running is located, stop the job and manually run the data extraction. The running time is similar to the normal extraction time. Check the system cpu, i/O is normal, and it is suspected that this part of SQL Execution Plan is changed.
Compare the execution plan in the calling system with the execution plan of the manually run SQL statement. The execution plan of an SQL statement is changed from hash join to nested loops at the beginning of the month.
-- 1. Because the SQL statement is located, query the SQL _id of the SQL statement.
Select SQL _id, address, plan_hash_value, hash_value, child_number, SQL _text from v $ SQL
Where SQL _text like 'insert INTO XXX %'

-- 2. The dba_hist_ SQL _plan view displays the historical execution plan. The TIMESTAMP field indicates the time when the execution plan was generated.
Select * from dba_hist_ SQL _plan h where SQL _id = '7sjn92j0k8csm ';
Each different plan_hash_value represents a different execution plan. This corresponds to two execution plans. At the beginning of the month, a new plan was created (the connection method is nested loops), and Oracle used the latest execution plan.

-- 3. Display All detailed execution plans
Select plan_table_output from table (dbms_xplan.display_awr ('7xh0wcw1qt6d7 '));

Solution: Use use_hash hint or outlines to stabilize the execution plan. For time reasons, the hint method is used. Hash join not only solves the problem of a large number of random reads in the nested loops connection, but also solves the problem that the sorting cost in the SORT merge connection is too high. Hash join is different from the nested loop in the random read mode. It needs to combine the connected objects in advance and then connect them in the memory by hash. For sorting and merging connections, refer:

You can view a scheduler in either of the following ways:
DBMS_XPLAN.DISPLAY_AWR (
SQL _id IN VARCHAR2,
Plan_hash_value in number default null,
Db_id in number default null,
Format IN VARCHAR2 default typical );

SQL _id
Specifies the SQL _ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL _ID in DBA_HIST_SQLTEXT.

DBMS_XPLAN.DISPLAY_CURSOR (
SQL _id IN VARCHAR2 DEFAULT NULL,
Child_number in number default null,
Format IN VARCHAR2 DEFAULT 'typical ');

SQL _id
Specifies the SQL _ID of the SQL statement in the cursor cache. you can retrieve the appropriate value by querying the column SQL _ID in V $ SQL or V $ SQLAREA. alternatively, you cocould choose the column PREV_ SQL _ID for a specific session out of V $ SESSION. this parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.