CODEWITHSUNDEEP
sqloracle-databaseperformance
Ivan Petrov
Ivan Petrov

Reputation: 17

SQL nested loops are too costly

I have an sql query like this:

select w.name, c.address, b.salary, a.product, d.contract_amount
from w
left join c c.id = w.id
left join b b.id = w.id
left join a a.id = w.id and a.date > sysdate-30
left join d d.id = w.id
where w.id = '12345';

And it's plan:

-----------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost   | Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |   849 |18896868| 00:01:14 |
|   1 |  NESTED LOOPS OUTER           |       |     1 |   849 |18896868| 00:01:14 |
|   2 |   NESTED LOOPS OUTER          |       |     1 |   849 |18896868| 00:01:14 |
|   3 |    NESTED LOOPS OUTER         |       |     1 |   670 |18896868| 00:01:14 |
|   4 |     NESTED LOOPS OUTER        |       |     1 |   596 |18896868| 00:01:14 |
|   5 |  TABLE ACCESS STORAGE FULL    |   w   |     1 |   415 |     20 | 00:00:01 |
|   6 |  TABLE ACCESS BY INDEX ROWID  |   c   |     1 |    22 |      3 | 00:00:01 |
|   7 |   INDEX UNIQUE SCAN           |c_id_nd|     1 |       |        | 00:00:01 |
|   8 |  TABLE ACCESS BY INDEX ROWID  |   b   |     1 |    66 |      2 | 00:00:01 |
|   9 |   INDEX UNIQUE SCAN           |b_id_nd|     1 |       |        | 00:00:01 |
|  10 |  TABLE ACCESS BY INDEX ROWID  |   a   |     1 |    11 |      3 | 00:00:01 |
|  11 |   INDEX UNIQUE                |a_id_nd|     1 |       |        | 00:00:01 |
|  12 |  TABLE ACCESS BY INDEX ROWID  |   d   |     1 |    25 |      1 | 00:00:01 |
|  13 |   INDEX UNIQUE                |d_id_nd|     1 |       |        | 00:00:01 |
-----------------------------------------------------------------------------------

Now its work about for 15-18 seconds and it's too long. I am new at tuning and I don't know how to improve its performance. Actually, all tables have about 33-54 millions of rows and all id columns have indexes. Also statistics was gathered for tables and i'm not able to use parallel hint. What optimizations can I do?

Upvotes: 0

Views: 5335

Answers (4)

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

I think the problem lies on cardinality estimator. Due to multiple left joins from - probably - master to detail 'type' tables there is wrong assumption of rows return. A poor cardinality estimation may lead to poor plan selection. I suggest a try with isolated selects as proposed by Mike and compare timings. I am not sure how smart CTEs perform in Oracle so i recommend surely isolated statements even if you have to use temp or memory tables. Select each table alone using your id value and put results into a temporary table. Then perform the final select on those temporary tables.

Upvotes: 0

Mike Twc
Mike Twc

Reputation: 2355

I guess there is nothing wrong with your query, I'd think the bad execution plan was generated initially and it is still sitting in cache.You can overwrite query in a different way and probably you'll get a better plan (eg with CTE). You can also try to filter id before joining. Try smth like this

with 
 W as (select id, name from w where w.id = '12345')
,C as (select id, address from C where c.id = '12345')
,B as (select id, salary from B where b.id = '12345')
,A as (select id, product from A where a.id = '12345' and a.date > sysdate - 30)
,D as (select id, contract_amount from D where d.id = '12345')

select w.name, c.address, b.salary, a.product, d.contract_amount
from w
left join c on c.id = w.id
left join b on b.id = w.id
left join a on a.id = w.id
left join d on d.id = w.id

Or this:

with 
 W1 as (select w.id, w.name from w where w.id = '12345')
,W2 as (select w1.* , c.address from W1 left outer join C on w1.id = c.id)
,W3 as (select w2.*, b.salary from W2 left outer join B on w2.id = b.id)
,W4 as (select w3.*, a.product from W3 left outer join A on w3.id = a.id and a.date > sysdate - 30)
Select w4.*, d.contract_amount from W4 left outer join D on w4.id = d.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

For this query:

select w.name, c.address, b.salary, a.product, d.contract_amount
from w left join
     c
     on c.id = w.id left join
     b
     on b.id = w.id left join
     a
     on a.id = w.id and a.date > sysdate-30 left join
     d
     on d.id = w.id
where w.id = '12345';

You want indexes on w(id), c(id),b(id), a(id, date), and d(id).

Upvotes: 1

George Joseph
George Joseph

Reputation: 5932

With 35 million records in tables. Are the tables partitioned.? If so are the query ensuring partition pruning

Upvotes: 0

Related Questions