Jump to content
jsfchen@yahoo.com

Why such a simple Join SQL statment gets performance issue

Recommended Posts

Any suggestions for this case will be highly appreciated:

 

table a with key id for example has 1 record

table b unique key (id, rowid), foreign key id in table a has 2 records

table c unique key (id, rowid), foreign key id in table a has 3 records

table d unique key (id, rowid), foreign key id in table a has 4 records

 

Oracle built indexes for unique key automatically.

 

select count(*) from a, b, c, d where (a.id = '1') and (a.id = b.id) and (a.id = c.id) and (a.id = d.id);

 

problem: the result record returned is 1*2*3*4 and when the record numbers from table b, c, d gets a certain large amount... the performance is terrible....

 

COUNT(*) ---------- 71530004 Elapsed: 00:00:02.31

COUNT(*) ---------- 3009369454 Elapsed: 00:01:44.76

COUNT(*) ---------- 1620429706 Elapsed: 00:00:50.66

COUNT(*) ---------- 4.2131E+10 Elapsed: 00:22:08.56

 

Oracle "plan" looks fine, no table scan at all.

 

My case has 6 tables like above and the bigger the count, the worse the performance.

 

Actually, my application has been using similar sql like following sql (actually more fields from each tables with outer join conditions to reduce returned records to be like 1+2+3+4 instead of 1*2*3*4) for many years, but just now getting big numbers to hit this performance issue (can this be Oracle limitation?). I guess this issue is the result of "product" (multiple each numbers).

 

select a.id, b.rowid, c.rowid, d.rowid from a, b, c, d where (a.id = '1') and (a.id = b.id) and (a.id = c.id) and (a.id = d.id);

 

Is it possible that either this SQL statement is missing some points or the Oracle indexes statistics somewhere need to be tuned? Or how to select all related fields/records from different tables in one sql statement without hitting this kind of performance issue ? I know I can use Union for some cases, but would not be able to resolve all other queries with big numbers.... and why Oracle plan shows fine but come out with this kind of performance?

 

Thanks,

- Jeff Chen

 

 

Share this post


Link to post
Share on other sites

Hi Jeff, and welcome to the forum!

 

You say that the elapsed time gets bad when the tables grow large, right?

 

First, test the queries with "set Autotrace on" to ensure that the plans did not change when the tables grew large.

 

What release are you on?

 

Changes in CBO stats will only serve to change plans.

 

Do you have dynamic sampling enabled?

 

http://www.dba-oracle.com/art_dbazine_orac...mpling_hint.htm

 

There are lots of optimizer bugs, and knowing your release will help!

 

Also, get the plan from the okay query and force that plan with hints when running against the large tables.

 

Please advise!

Share this post


Link to post
Share on other sites

Thanks for your response, sir!

 

My Oracle is 11.2.0.3.0, so I think dynamic sampling enabled is default while I see no difference for the respond time with table sequences changed.

 

Also double checked the set autotrace on for different numbers of table B, C, D, E, F to involved different counts. Here is the large one output:

 

COUNT(*)

----------

4.2131E+10

 

Elapsed: 00:19:42.83

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2315010172

 

---------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 36 | 698 (0)| 00:00:05 |

| 1 | SORT AGGREGATE | | 1 | 36 | | |

| 2 | MERGE JOIN CARTESIAN | | 6874 | 241K| 698 (0)| 00:00:05 |

| 3 | MERGE JOIN CARTESIAN | | 320 | 9600 | 58 (0)| 00:00:01 |

| 4 | MERGE JOIN CARTESIAN | | 19 456 | 20 (0)| 00:00:01 |

| 5 | MERGE JOIN CARTESIAN| | 6 | 108 | 8 (0)| 00:00:01 |

| 6 | NESTED LOOPS | | 2 | 24 | 4 (0)| 00:00:01 |

|* 7 | INDEX UNIQUE SCAN | PK_AA_ID | 1 | 6 | 2 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | BB_ID_NEW_IDX | 2 | 12 | 2 (0)| 00:00:01 |

| 9 | BUFFER SORT | | 3 | 18 | 6 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN | CC_ID_NEW_IDX | 3 | 18 | 2 (0)| 00:00:01 |

| 11 | BUFFER SORT | | 3 | 18 | 18 (0)| 00:00:01 |

|* 12 | INDEX RANGE SCAN | DD_ID_IDX | 3 | 18 | 2 (0)| 00:00:01 |

| 13 | BUFFER SORT | | 17 | 102 | 56 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | EE_ID_NEW_IDX | 17 | 102 | 2 (0)| 00:00:01 |

| 15 | BUFFER SORT | | 22 | 132 | 696 (0)| 00:00:05 |

|* 16 | INDEX RANGE SCAN | FF_ID_IDX | 22 | 132 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

7 - access("AA"."ID"=1028972)

8 - access("BB"."ID"=1028972)

10 - access("CC"."ID"=1028972)

12 - access("DD"."ID"=1028972)

14 - access("EE"."ID"=1028972)

16 - access("FF"."ID"=1028972)

 

 

Statistics

----------------------------------------------------------

85 recursive calls

0 db block gets

50 consistent gets

16 physical reads

0 redo size

531 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

 

Still, I wonder why the count(*) comes result as the product of each table records instead of sum. it not for outer join. All tables have indexes....

 

Thanks again,

-Jeff

 

 

 

 

Share this post


Link to post
Share on other sites

Hi Jeff,

 

Something is very wrong here!

 

All of those rows counted with only 50 consistent gets?

 

And why would it take 19 minutes?

 

I smell a bug!

 

Check MOSC for bugs on this:

 

http://support.oracle.com

 

The merge join Cartesian operations are sometimes a cause for concern, please read:

 

http://www.dba-oracle.com/t_sql_merge_join_cartesian.htm

 

I would disable dynamic sampling, and use the use_nl hint to force nested loops joins.

 

http://www.dba-oracle.com/art_dbazine_orac...mpling_hint.htm

 

http://www.dba-oracle.com/t_tuning_sql_nes...loops_joins.htm

 

It looks like your plan changes when the tables grow large!

 

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×