4 Replies Latest reply: Feb 8, 2013 12:57 AM by user470804 RSS

    Speed problem

    user470804
      I have code1
      SELECT
      count(*)
      FROM AIM01.T_PD_PHYS_HDR hdr,
      AIM01.T_PD_PHYS_DTL DTL
      where DTL.SAK_PROCEDURE in (26972, 29743, 716, 12949, 25505, 7865, 7900, 13458, 7843, 22094, 812, 6510, 27341, 604, 27675,
      905, 30646, 7704, 30413, 12443, 25484, 25499, 25500, 25508, 7867, 7895, 14131, 7807, 7844, 7845,
      7955, 26968, 29744, 6975, 22533, 29742, 901, 23610, 25062, 14430, 30645, 906, 897, 32346, 419,
      25374, 26974, 29741, 12444, 27168, 715, 717, 722, 25485, 25507, 7490, 7868, 23783, 13459, 7151,
      7153, 7217, 7717, 14090, 7848, 7926, 26973, 13463, 12321, 29125, 30643, 2920, 1580, 27201, 12345,
      14387, 31394, 30090, 14251, 22716, 24232, 29740, 7842, 30414, 12948, 25501, 25503, 25506, 25509, 7697,
      7698, 28284, 7660, 7716, 7718, 14093, 20419, 7445, 7847, 6976, 7709, 14068, 12943, 25051, 27496, 2919,
      30644, 603, 607, 898, 418, 25044, 26967, 7841, 26965, 720, 725, 20212, 25502, 7866, 14127, 7149, 7218,
      7834, 7837, 22534, 26969, 7219, 6509, 910, 714, 12322, 30093, 713, 24952, 608, 20172, 14385, 30412, 7840,
      14132, 7656, 22513, 14092, 13446, 14086, 27223, 23609, 13973, 30057, 2738, 27499, 2917, 2921, 899, 420,
      14117, 20070, 718, 719, 723, 25504, 7883, 7894, 7150, 7836, 7886, 6977, 27222, 25059, 606, 609, 20173,
      27676, 421, 26966, 721, 12946, 12947, 25497, 25498, 7864, 7869, 7152, 7715, 14091, 20420, 7835, 7838, 7846,
      13469, 6511, 13716, 25070, 2945, 2946, 2918, 30091, 30092, 610, 14386, 12942, 900, 31096)--PROC.SAK_PROCEDURE
      AND DTL.SAK_CLAIM = HDR.SAK_CLAIM;

      Takes about 2 mins to run

      I have code2
      with t_tmp_proc as
      (select /*+ DRIVING_SITE (A) */distinct c.sak_procedure
      from t_audit_proc@remote_db A, t_error_disp@remote_db B, t_proc@remote_db C
      where A.sak_esc = B.sak_esc
      and A.sak_procedure = C.sak_procedure
      and B.dsc_error_stat like '%LIFE%')
      SELECT
      count(*)
      FROM AIM01.T_PD_PHYS_HDR hdr,
      AIM01.T_PD_PHYS_DTL DTL,
      T_TMP_PROC PROC
      where DTL.SAK_PROCEDURE =PROC.SAK_PROCEDURE
      AND DTL.SAK_CLAIM = HDR.SAK_CLAIM;

      Takes very long time. Doesn't finish before I kill it. I've waited 30 mins.

      The select within the with as clause runs fast by itself, and it's supposed to bring the hard coded values I have in sql code1.

      Would someone be able to explain to me why this is happening and how can I improve it?

      Thanks,
        • 1. Re: Speed problem
          JustinCave
          What is the query plan for the second query?
          How long does it take just to run the query in the WITH clause?

          Have you taken a look at the entry in the FAQ on SQL and PL/SQL FAQ?

          Justin
          • 2. Re: Speed problem
            user470804
            This is what the explain plan for the second sql looks like

            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            Plan hash value: 989933722

            ----------------------------------------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
            ----------------------------------------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT REMOTE | | 1 | 52 | | 552K (1)| 01:50:25 | | |
            | 1 | SORT AGGREGATE | | 1 | 52 | | | | | |
            |* 2 | HASH JOIN | | 139M| 6908M| | 552K (1)| 01:50:25 | | |
            | 3 | VIEW | | 17117 | 217K| | 1139 (3)| 00:00:14 | | |
            | 4 | HASH UNIQUE | | 17117 | 1186K| 8712K| 1139 (3)| 00:00:14 | | |
            | 5 | NESTED LOOPS | | 110K| 7692K| | 287 (7)| 00:00:04 | | |
            | 6 | NESTED LOOPS | | 110K| 7151K| | 271 (1)| 00:00:04 | | |
            |* 7 | TABLE ACCESS FULL | T_ERROR_DISP | 87 | 4872 | | 9 (0)| 00:00:01 | KSMIS~ | |
            | 8 | TABLE ACCESS BY INDEX ROWID| T_AUDIT_PROC | 1283 | 12830 | | 9 (0)| 00:00:01 | KSMIS~ | |
            |* 9 | INDEX RANGE SCAN | X_AUDIT_PROC_E | 1283 | | | 2 (0)| 00:00:01 | KSMIS~ | |
            |* 10 | INDEX UNIQUE SCAN | I_PROC | 1 | 5 | | 0 (0)| 00:00:01 | KSMIS~ | |
            |* 11 | HASH JOIN | | 139M| 5181M| 2042M| 549K (1)| 01:49:58 | | |
            | 12 | REMOTE | T_PD_PHYS_HDR | 85M| 1062M| | 57190 (1)| 00:11:27 | ! | R->S |
            | 13 | REMOTE | T_PD_PHYS_DTL | 139M| 3454M| | 137K (1)| 00:27:36 | ! | R->S |
            ----------------------------------------------------------------------------------------------------------------------------


            The query in the with clause run is 0.498 secs. Let me take a look at that post you mentioned.

            Edited by: user470804 on Feb 7, 2013 12:46 PM
            • 3. Re: Speed problem
              user470804
              I think I have it figured out. I need to remove the hdr table.

              what I did is I use the dtl table and the proc table in teh WITH clause and the hdr in teh actual select. Ran in 4.5 mins.

              thanks.
              • 4. Re: Speed problem
                user470804
                Thanks