2

While generating a report based on few tables out of which some are really huge(1+ billion records) and some pretty small (100 records) it is taking 17 minutes. but where I added a condition to the where clause it started running for 1+ hours.

The table1 has 1+ billion records and the condition added is "and line_ind in ('Y', '*')". Question is why is the CBO going for a full table scan when it could search from the result set of the joins. Ordered hint doesn't seem to help and so wondering how to make this select run faster.?

here are the record counts for the tables

table1(a) 1,234,432,435
table2(b) 1,234,432,435
table3(c) 900
table4(d) 602,364,856
table5(e) 80
table6(f) 50
table7(g) 264,938,373
table8(h) 14,827



SELECT DISTINCT c_cd,
                  ci_nbr,
                  c.s_id1,
                  d.d_cd,
                  SUM (CASE WHEN (D_CD = 'MAILED') THEN 1 ELSE 0 END) AS MAILED
    FROM table1 a
         LEFT OUTER JOIN table2 b ON (a.o_id = b.o_id AND a.co_sid = b.co_sid)
         INNER JOIN table3 c
            ON c.c_sid = a.c_sid AND c.c_cd IN ('CC364', 'CC552')
         INNER JOIN table4 c ON a.sc_sid = c.sc_sid
         INNER JOIN table5 d ON d.d_SID = b.d_SID
         INNER JOIN table6 e ON e.cc_sid = b.cc_sid
         INNER JOIN table7 f ON f.ci_sid = b.ci_sid
         INNER JOIN table8 g ON g.dt_sid = a.dt_sid
GROUP BY dt_cd,
         c_nbr,
         c.s_id1,
         d.d_cd
 where dt_cd in ('c6543-010114','c5653-010514') and line_ind in ('Y', '*') and c_nbr='728246'

Explain Plan 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |    811 |   296K|   304K  (3)| 01:11:02 |       |       |        |      |            |
|   1 |  PX COORDINATOR                                   |                                |        |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                             | :TQ10009                       |    811 |   296K|   304K  (3)| 01:11:02 |       |       |  Q1,09 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                                  |                                |    811 |   296K|   304K  (3)| 01:11:02 |       |       |  Q1,09 | PCWP |            |
|   4 |     PX RECEIVE                                    |                                |    811 |   296K|   304K  (3)| 01:11:02 |       |       |  Q1,09 | PCWP |            |
|   5 |      PX SEND HASH                                 | :TQ10008                       |    811 |   296K|   304K  (3)| 01:11:02 |       |       |  Q1,08 | P->P | HASH       |
|   6 |       HASH GROUP BY                               |                                |    811 |   296K|   304K  (3)| 01:11:02 |       |       |  Q1,08 | PCWP |            |
|*  7 |        HASH JOIN                                  |                                |    811 |   296K|   304K  (3)| 01:11:02 |       |       |  Q1,08 | PCWP |            |
|   8 |         PX RECEIVE                                |                                |    802 |   278K|   277K  (4)| 01:04:39 |       |       |  Q1,08 | PCWP |            |
|   9 |          PX SEND BROADCAST                        | :TQ10007                       |    802 |   278K|   277K  (4)| 01:04:39 |       |       |  Q1,07 | P->P | BROADCAST  |
|* 10 |           HASH JOIN                               |                                |    802 |   278K|   277K  (4)| 01:04:39 |       |       |  Q1,07 | PCWP |            |
|  11 |            PX RECEIVE                             |                                |    802 |   177K|   225K  (4)| 00:52:44 |       |       |  Q1,07 | PCWP |            |
|  12 |             PX SEND BROADCAST                     | :TQ10006                       |    802 |   177K|   225K  (4)| 00:52:44 |       |       |  Q1,06 | P->P | BROADCAST  |
|* 13 |              HASH JOIN BUFFERED                   |                                |    802 |   177K|   225K  (4)| 00:52:44 |       |       |  Q1,06 | PCWP |            |
|  14 |               PX RECEIVE                          |                                |     96 |  4800 |     2   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|  15 |                PX SEND BROADCAST                  | :TQ10004                       |     96 |  4800 |     2   (0)| 00:00:01 |       |       |  Q1,04 | P->P | BROADCAST  |
|  16 |                 PX BLOCK ITERATOR                 |                                |     96 |  4800 |     2   (0)| 00:00:01 |       |       |  Q1,04 | PCWC |            |
|  17 |                  TABLE ACCESS STORAGE FULL        | TABLE5                         |     96 |  4800 |     2   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
|* 18 |               HASH JOIN                           |                                |    802 |   138K|   225K  (4)| 00:52:44 |       |       |  Q1,06 | PCWP |            |
|  19 |                BUFFER SORT                        |                                |        |       |            |          |       |       |  Q1,06 | PCWC |            |
|  20 |                 PX RECEIVE                        |                                |     51 |   561 |     5   (0)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|  21 |                  PX SEND HASH                     | :TQ10000                       |     51 |   561 |     5   (0)| 00:00:01 |       |       |        | S->P | HASH       |
|  22 |                   TABLE ACCESS STORAGE FULL       | TABLE6                         |     51 |   561 |     5   (0)| 00:00:01 |       |       |        |      |            |
|  23 |                PX RECEIVE                         |                                |    802 |   130K|   225K  (4)| 00:52:44 |       |       |  Q1,06 | PCWP |            |
|  24 |                 PX SEND HASH                      | :TQ10005                       |    802 |   130K|   225K  (4)| 00:52:44 |       |       |  Q1,05 | P->P | HASH       |
|* 25 |                  HASH JOIN                        |                                |    802 |   130K|   225K  (4)| 00:52:44 |       |       |  Q1,05 | PCWP |            |
|  26 |                   PX RECEIVE                      |                                |    802 |   101K|   104K  (5)| 00:24:25 |       |       |  Q1,05 | PCWP |            |
|  27 |                    PX SEND BROADCAST              | :TQ10003                       |    802 |   101K|   104K  (5)| 00:24:25 |       |       |  Q1,03 | P->P | BROADCAST  |
|* 28 |                     HASH JOIN                     |                                |    802 |   101K|   104K  (5)| 00:24:25 |       |       |  Q1,03 | PCWP |            |
|  29 |                      PX RECEIVE                   |                                |      2 |    24 |    17   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  30 |                       PX SEND BROADCAST           | :TQ10001                       |      2 |    24 |    17   (0)| 00:00:01 |       |       |  Q1,01 | P->P | BROADCAST  |
|  31 |                        PX BLOCK ITERATOR          |                                |      2 |    24 |    17   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |
|* 32 |                         TABLE ACCESS STORAGE FULL | TABLE3                         |      2 |    24 |    17   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 33 |                      HASH JOIN                    |                                |  68190 |  7857K|   104K  (5)| 00:24:25 |       |       |  Q1,03 | PCWP |            |
|  34 |                       PX RECEIVE                  |                                |      2 |    34 |   276   (0)| 00:00:04 |       |       |  Q1,03 | PCWP |            |
|  35 |                        PX SEND BROADCAST          | :TQ10002                       |      2 |    34 |   276   (0)| 00:00:04 |       |       |  Q1,02 | P->P | BROADCAST  |
|  36 |                         PX BLOCK ITERATOR         |                                |      2 |    34 |   276   (0)| 00:00:04 |       |       |  Q1,02 | PCWC |            |
|* 37 |                          TABLE ACCESS STORAGE FULL| TABLE8                         |      2 |    34 |   276   (0)| 00:00:04 |       |       |  Q1,02 | PCWP |            |
|  38 |                       PX BLOCK ITERATOR           |                                |     22M|  2177M|   104K  (5)| 00:24:21 |     1 |1048575|  Q1,03 | PCWC |            |
|* 39 |                        TABLE ACCESS STORAGE FULL  | TABLE1                         |     22M|  2177M|   104K  (5)| 00:24:21 |     1 |1048575|  Q1,03 | PCWP |            |
|  40 |                   PX BLOCK ITERATOR               |                                |    969M|    32G|   121K  (3)| 00:28:15 |     1 |1048575|  Q1,05 | PCWC |            |
|* 41 |                    TABLE ACCESS STORAGE FULL      | TABLE2                         |    969M|    32G|   121K  (3)| 00:28:15 |     1 |1048575|  Q1,05 | PCWP |            |
|  42 |            PX BLOCK ITERATOR                      |                                |    398M|    47G| 50955   (1)| 00:11:54 |       |       |  Q1,07 | PCWC |            |
|  43 |             TABLE ACCESS STORAGE FULL             | TABLE7                         |    398M|    47G| 50955   (1)| 00:11:54 |       |       |  Q1,07 | PCWP |            |
|  44 |         PX BLOCK ITERATOR                         |                                |    589M|    10G| 27170   (2)| 00:06:21 |     1 |    13 |  Q1,08 | PCWC |            |
|  45 |          TABLE ACCESS STORAGE FULL                | TABLE4                         |    589M|    10G| 27170   (2)| 00:06:21 |     1 |    13 |  Q1,08 | PCWP |            |
user3570198
  • 61
  • 1
  • 7
  • How in the world should we know? Please show explain plan or other relevant data other than just the query. – OldProgrammer Sep 08 '14 at 18:50
  • 1
    run explain plan on both the versions and see what the difference is. Then we will have a better idea of what is taking most amount of time. – Chip Sep 08 '14 at 18:55
  • One very bad trick would be to to move your new condition into a HAVING clause. This will run the clause after your WHERE clause and you will probably get back to 17 mins. This is totally NOT recommended. But if you are breaking in production and need a very quick fix, it might be a stopgap measure till you figure out the real problem. – Chip Sep 08 '14 at 18:57
  • @Chip No The explain plan doesn't show me any difference in the steps only a bit more of carnality and cost. Thats the reason I couldn't figure out. – user3570198 Sep 08 '14 at 19:01
  • Have you collected statistics recently? – Chip Sep 08 '14 at 19:04
  • Without knowing too much about your system, my guess would be that initially it was doing some sort of parallel execution on table1 but since you put a where clause it thinks its better to do a full table scan. Also, you explain plan is hard to read .. could you use DBMS_XPLAN.DISPLAY or similar to get a more readable output? – Chip Sep 08 '14 at 19:09
  • Yes I did collect the stats recently. its just that I don't understand why the extra condition putting my sql into a limbo and how to tune it for gaining performance. I have pasted he formatted text but still that is how it shows up :( – user3570198 Sep 08 '14 at 19:19
  • here is the only difference before adding the condition and after. | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | 0 | SELECT STATEMENT | 811 | 296K| 303K (3)| 1:10:47 41 | TABLE ACCESS STORAGE FULL | TABLE2 | 1454M| 48G| 119K (2)| 00:27:58 and after adding | 0 | SELECT STATEMENT | 811 | 296K| 304K (3)| 01:11:02 | 41| TABLE ACCESS STORAGE FULL TABLE2 | 969M| 32G| 121K (3)| 00:28:15 – user3570198 Sep 08 '14 at 19:29
  • Are you *sure* the plans are the same before and after? The poor formatting implies that execution plans were not carefully copied and pasted. Look at the *entire* execution plan, and compare them in a tool like WinMerge to be sure there are no significant differences. If that doesn't work the next step is a tool like Real-Time SQL Monitoring: `select dbms_sqltune.report_sql_monitor(sql_id => '') from dual;`. That will tell you exactly where the time is spent, and will also provide the degree of parallelism and other important runtime information. – Jon Heller Sep 09 '14 at 02:14
  • @Chip I added the HAVING clause and now it runs faster. it took 12 minutes. SO the condition is in place and somehow the time took without the condition is 17 minutes and this time it is even less(12 minutes). I will still work on this and will let you know if I can gather more information. – user3570198 Sep 10 '14 at 20:20

1 Answers1

0

Get 10053 traces for both queries. This is the optimizer trace for Oracle. You will see why oracle is picking different plans, you can not miss it.

I think this link will helpyou out https://blogs.oracle.com/optimizer/entry/how_do_i_capture_a you can google for other tutorials on 10053 if you prefer.

bubooal
  • 621
  • 3
  • 8