Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with explain plans so I call for help.

Here is some data about these tables:

• history_state_table 7.424.65 rows (of which only 13.412 are left after t1.alarm_type = 'AT1' )

• costumer_price_history 448.284.169 rows

• cycle_table 215 rows

This would be the query (don't mind the logic, is just for the reference):

```
SELECT t1.id_alarm, t2.load_id, t2.reference_date
FROM history_state_table t1,
(SELECT op_code, contract_num,
COUNT (DISTINCT id_ponto) AS num_pontos,
COUNT
(DISTINCT CASE
WHEN vlr > 0
THEN id_ponto
ELSE NULL
END
) AS bigger_than_zero,
MAX (load_id) AS load_id,
MAX (reference_date) AS reference_date
FROM costumer_price_history
WHERE load_id IN
(42232, 42234, 42236, 42238, 42240, 42242, 42244) / arbitrary IDs depending on execution/
AND sistema = 'F1' / Hardcoded filters /
AND rec_type = 'F3' / Hardcoded filters /
AND description = 'F3' / Hardcoded filters /
AND extract_type IN
('T1', 'T2', 'T3')
GROUP BY op_code, contract_num) t2
WHERE t1.op_code = t2.op_code
AND t1.contract_num = t2.contract_num
AND t1.alarm_type = 'AT1'
AND t1.alarm_status = 'DONE'
AND ( ( t1.prod_type = 'COMBO'
AND t2.bigger_than_zero = t2.num_pontos - 1
)
OR ( t1.prod_type != 'COMBO'
AND t2.bigger_than_zero = t2.num_pontos
)
)
/ arbitrary filter depending on execution/
AND t1.data_tratado BETWEEN (SELECT data_inicio

Explain plan does not tell you what is actually the most costly "operation". The "Cost" column is a guess - it is a value estimated by optimizer. So is "Cardinality" column and "Bytes" column. http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i18300

In your example, your optimizer tells you: I decide to use this plan because I guess that looping would cost about 5,483. And I hope this would be the most costly part of the execution, but I can't guarantee this.

The same applies recursively to all the depths of the tree.

If you go in-depth to the lowest levels (that is by intuition most-looped, most-executed levels) you see that the operation that especially sticks out, both in terms of expected cost and expected number of elements, is the

6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068

So, optimizer guessed that optimal execution of this query is to loop a lot around a poor workhorse RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM. I really cannot see which part of your query directly relates to it, but I suspect t1.data_tratado condition. And, again, I cannot see if it is really the most costly part.

I'll try to translate the syntax of loops in the explain plan to procedural pseudo-code:

/* begin step 13 (by "step 13" I mean a line that reads "   13 NESTED LOOPS") */
  /* begin step 7 */
    do step 5
    myresult = rows from step 5
    for each row from myresult {
       do step 6
       for each row from step 6 {
           join to a row from myresult the matching row from step 6
       }
    }
  /* end step 7 */
  for each row from myresult {
     do step 12
     for each row from step 12 {
         join to a row from myresult the matching row from step 12
     }
  }
/* end step 13 */
return myresult

Seems complicated, but really aim of each "nested loop" is to create a join (a single table made of two tables) in the most naive way, a loop-inside-a-loop.

StackExchange Database Administrators Q#23965, answer score: 3