Jump to content

jsfchen@yahoo.com

Members
  • Content count

    2
  • Joined

  • Last visited

Community Reputation

0 Neutral

About jsfchen@yahoo.com

  • Rank
    Newbie
  1. jsfchen@yahoo.com

    Why such a simple Join SQL statment gets performance issue

    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
  2. 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
×