How to view the accuracy of the Execution Plan and the Execution Plan

The following execution plan is printed out. Many friends still don't know. In fact, there are only three statements:

Explain plan for the SQL statement you want to view; commit; select * from table (dbms_xplan.display );

----- Split line ----------------------------------

First, let us know a principle,When you look at the execution plan, start from the first line to the right down, until the rightmost. If there is a parallel relationship, go up and down first. If not, run the command first on the right.

Let's talk less about it first:

This is a simple SQL Execution Plan. This execution plan tells us that the first execution of ID = 2 is id = 3, and then the execution of ID = 1.

First, perform a full table scan for test. In this step, rows = 2 is returned, and the CPU consumption is 2. Next, perform a full table scan on test1. The returned rows are 2, and the CPU consumption is 3. Next, perform a hash join on the two results, and return rows = 1. Here, the CPU consumption is 6, but note that this time my statement is too small, 6 = 2x3, but the CPU cost required for the hash connection is definitely not the product of the CPU cost for the previous operation. So far, our Oracle execution plan for this statement has ended.

How can this execution plan be obtained? Since it is a plan, it is definitely not to execute this statement first, and then calculate it. In this case, this execution plan will become Zhuge bright afterwards. This execution plan is obtained by Oracle based on statistics. This execution plan may not be accurate. Please check my statements and the execution results:

SELECT A.SER_ID, B.OWNER FROM TEST A, TEST1 B WHERE A.AREA_ID = B.OWNER;

Result

How is it? It's definitely not six lines of stuff, right? The statistical information of this table seems very old. So I re-calculate the two tables:

ANALYZE TABLE TEST COMPUTE STATISTICS;

ANALYZE TABLE TEST1 COMPUTE STATISTICS;

Then let's look at the execution plan:

Is it so small? Test returns a result with 12 Mb rows. The owner field of test1 has only two records: 911 and 290. So how many records are there in area_id = 290/911 in the corresponding test? Count: 8485760. Why is it 12 Mb? Because full table scan:

SELECT COUNT(*)/1024/1024 FROM TEST;

The result is 12.

The accuracy of the Execution Plan (mainly index data return and data size) is determined by the accuracy of the statistical information.