Oracle SQL Execution Plan Difference

Question

I have the below query and I'm generating the execution plan for it:

(EDIT: I'm using SQL Developer).

EXPLAIN PLAN
    FOR
WITH aux AS (
    SELECT
        *
    FROM
        cdc.uap_fkkvkp@rbip
    WHERE
        ezawe = 'D'
)
SELECT /* FULL(a) FULL(b) */
--    COUNT(1)
    b.zzpayment_plan,
    b.vkont,
    b.gpart,
    a.opbel,
    a.opupw,
    a.opupk,
    a.opupz,
    a.blart,
    a.betrw
FROM
    cdc.uap_dfkkop@rbip   a
    JOIN aux                   b ON b.vkont = a.vkont
WHERE
    a.augst IS NULL
    AND a.xanza IS NULL
    AND a.stakz IS NULL
    AND a.augrs IS NULL
    AND a.abwtp IS NULL;
    
SELECT
    *
FROM
    TABLE ( dbms_xplan.display );

It gives the below plan:

Plan hash value: 289441478
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |  4404K|   634M|       |  6752K  (1)| 00:04:24 |
|*  1 |  HASH JOIN                           |                        |  4404K|   634M|   291M|  6752K  (1)| 00:04:24 |
|*  2 |   TABLE ACCESS STORAGE FULL          | UAP_FKKVKP             |  4487K|   239M|       |   559K  (1)| 00:00:22 |
|*  3 |   TABLE ACCESS BY INDEX ROWID BATCHED| UAP_DFKKOP             |  4404K|   399M|       |  6160K  (1)| 00:04:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS       |                        |       |       |       |            |          |
|   5 |     BITMAP AND                       |                        |       |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE       | UAP_DFKKOP_AUGST_2     |       |       |       |            |          |
|*  7 |      BITMAP INDEX SINGLE VALUE       | UAP_DFKKOP_NEW_STAKZ_2 |       |       |       |            |          |
|*  8 |      BITMAP INDEX SINGLE VALUE       | UAP_DFKKOP_AUGRS_2     |       |       |       |            |          |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("UAP_FKKVKP"."VKONT"="A"."VKONT")
   2 - storage("EZAWE"=U'D')
       filter("EZAWE"=U'D')
   3 - filter("A"."XANZA" IS NULL AND "A"."ABWTP" IS NULL)
   6 - access("A"."AUGST" IS NULL)
   7 - access("A"."STAKZ" IS NULL)
   8 - access("A"."AUGRS" IS NULL)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

However, if I generate the plan for the first part of the code, but for the COUNT(1) uncommented (and the other selections commented), the below shows (it's exactly the same as the previous execution plan):

Plan hash value: 2732266276
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |     1 |    66 |       |  6736K  (1)| 00:04:24 |
|   1 |  SORT AGGREGATE                       |                        |     1 |    66 |       |            |          |
|*  2 |   HASH JOIN                           |                        |  4404K|   277M|   171M|  6736K  (1)| 00:04:24 |
|*  3 |    TABLE ACCESS STORAGE FULL          | UAP_FKKVKP             |  4487K|   119M|       |   559K  (1)| 00:00:22 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| UAP_DFKKOP             |  4404K|   159M|       |  6160K  (1)| 00:04:01 |
|   5 |     BITMAP CONVERSION TO ROWIDS       |                        |       |       |       |            |          |
|   6 |      BITMAP AND                       |                        |       |       |       |            |          |
|*  7 |       BITMAP INDEX SINGLE VALUE       | UAP_DFKKOP_AUGST_2     |       |       |       |            |          |
|*  8 |       BITMAP INDEX SINGLE VALUE       | UAP_DFKKOP_NEW_STAKZ_2 |       |       |       |            |          |
|*  9 |       BITMAP INDEX SINGLE VALUE       | UAP_DFKKOP_AUGRS_2     |       |       |       |            |          |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("UAP_FKKVKP"."VKONT"="A"."VKONT")
   3 - storage("EZAWE"=U'D')
       filter("EZAWE"=U'D')
   4 - filter("A"."XANZA" IS NULL AND "A"."ABWTP" IS NULL)
   7 - access("A"."AUGST" IS NULL)
   8 - access("A"."STAKZ" IS NULL)
   9 - access("A"."AUGRS" IS NULL)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

If I try to execute both queries, the first one takes about 3 secs while the second one takes almost 8 mins.

Why is there such a difference between the two and why is it not captured in the execution plan?
Is my full table scan here correctly applied? If not, what would be the best hint?
What would be the best (up to date) book/documentation/online tutorials to upgrade my SQL performance skills? So far, I've seen that Oracle has a dev gym for performance and there are also some books like Advanced Oracle SQL Tuning which looks interesting.

How are you measuring the elapsed time? The time required to fetch the first row? Or the time to fetch the last row? Oracle has to materialize all the rows (estimated to be 4.4 million) in order to count them in the second query. In the first query, Oracle can start sending rows to the client much more quickly but it may take more than 3 minutes to send all the results to the client. — Justin Cave
Oh, ok so Oracle retrieving the 50 first rows in 3 seconds does not mean retrieving all in the same time. I was confused since, in my short experience, the time taken to retrieve the first rows is usually slightly shorter (though not such a difference) than the one elapsed if I create a table with the select statement. — Javi Torre
Also keep in mind that writing output to a screen is a relatively slow process. It will often take longer to simply read 10k rows and write them to the screen than to read the same 10k rows and do some work with them internally, with no screen output. That's why it is important to consider exactly how you are measuring time differences, and that you are comparing apples to apples. — EdStevens

Performance Performance · Accepted Answer · 2021-03-26T17:06:17

With explain there is no real execution. You will receive a lot more details with real execution after turning statistics_level to ALL on session level. But run it in SQLPLUS:

set serverout off
alter session set statistics_level=all;

<execute the query here without explain>

select * from table(dbms_xplan.display_cursor(null,null,'RUNSTATS_LAST'));

If you provide two such plans instead of EXPLAIN PLAN it will be easier for your readers. Short description of important columns(Tanel Poder site is good):

Starts - number of execution of the step
A-Rows - actual number of rows received from this step
Buffers - number of reads from RAM - you have to decrease this value
Reads - number of reads from disk
Writes - number of writes to disk - different from 0 - mainly for SELECTs when TEMP is used

This method for plan usage is shown for example here : https://tanelpoder.com/files/Oracle_SQL_Plan_Execution.pdf

Oracle SQL Execution Plan Difference

1 Answers