Query optimization

Quote:

---

Originally posted by badrinathn
14924 TABLE ACCESS BY INDEX ROWID LU_ACCOUNT_LOD6 (cr=3091 r=1113 w=0 time=15486382 us)
14924 INDEX RANGE SCAN LOD6_OBJ_IDX_01 (cr=48 r=45 w=0 time=429005 us)(object id 42908)
13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15168234 us)


23049 TABLE ACCESS BY INDEX ROWID OBJ#(42907) (cr=3282 r=1887 w=0 time=9997512 us)
23049 INDEX RANGE SCAN OBJ#(42908) (cr=74 r=71 w=0 time=522124 us)(object id 42908)
13293 NESTED LOOPS (cr=1337371 r=0 w=0 time=15700208 us)

---

Instead of NESTED LOOP those joins try hash, almost 50% of LIO are from these inefficient nested loop joins

But with the hash joins, I was seeing a full tablescans. I thought the full table scans made the LIO more .

well depends how big are your tables. full table scan doesnt mean bad always

For example if I need to read 100000 blocks to perform a FTS a nested loop might read twice or even more

also if you wanna post a tkprof output use quotes ([ code ] [/ code ])to make it at least it's readble on forum and use explain=user/password otehrwise we cannot see the explain plan properly

IMHO you shoudl try full scan the table LU_ACCOUNT

Code:

---

  13293    NESTED LOOPS  (cr=1337371 r=0 w=0 time=15115938 us)
 391755    TABLE ACCESS BY INDEX ROWID AGGR_PL_FISCAL_PERIOD (cr=162104 r=0 w=0 time=2573807 us)
 391755      BITMAP CONVERSION TO ROWIDS (cr=538 r=0 w=0 time=218600 us)
    190      BITMAP INDEX RANGE SCAN AGGR_PL_FISCAL_INDX1 (cr=538 r=0 w=0 time=13603 us)(object id 49228)
  13293    TABLE ACCESS BY INDEX ROWID LU_ACCOUNT (cr=1175267 r=0 w=0 time=11337688 us)
 391755      INDEX UNIQUE SCAN SYS_C0014576 (cr=783512 r=0 w=0 time=3733693 us)(object id 42896)

---

Here is an example, a horrible peoplesoft query, check the attached file


Hash Join performs much better.

Code:

---


HASH JOIN                  NESTED LOOP/SORT JOIN
-------------------------  ---------------------------
2476559  consistent gets    24168645  consistent gets
3216467  physical reads    5274424  physical reads

---

Try this:

Code:

---

select /*+ ordered */ a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
      a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
      a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
      sum(a11.sum_acct_amt) WJXBFS1
from ( select /*+ no_merge */ account_id, time_period_ind_id,
              ledger_type_id , fiscal_period_id,
              sum(pl_period_amt_local) sum_acct_amt
        from AGGR_PL_FISCAL_PERIOD ax
      where ax.LEDGER_TYPE_ID = 1877
        and ax.TIME_PERIOD_IND_ID = 1
        and ax.FISCAL_PERIOD_ID between
      TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm')
  and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
      group by account_id,
              time_period_id,
              ledger_type_id,
              fiscal_period_id
    ) a11,
    ( select /*+ no_merge */ account_id, lod6_id
        from  LU_ACCOUNT ay
        where ay.company_id = 3023
          and ((ay.BUSINESS_UNIT_ID in
                (156569, 156590, 156591,
                156947, 156948, 156949, 156950, 156951))
              or (ay.BUSINESS_UNIT_ID in
                  (156571, 156989, 156990,
                    156991, 157007, 157008, 157009, 157010))
              or (ay.BUSINESS_UNIT_ID in
                  (156607, 157011, 157027,
                  157028, 157029, 157030, 157031, 157047))
              or (ay.BUSINESS_UNIT_ID in
                  (156570, 156967, 156968,
                    156969, 156970, 156971, 156987, 156988))
              or (ay.BUSINESS_UNIT_ID in
                    (157514, 157497, 157498,
                    157499, 157500, 157501, 157512, 157513))
              or (ay.BUSINESS_UNIT_ID in
                  (156568, 156608, 156609,
                    156610, 156611, 156587, 156588, 156589)
                ))
    ) a12,
    ( select /*+ no_merge */ lod6_id
      from LU_ACCOUNT_LOD6
      where LOD6_OBJECT_ACCOUNT_SRCCD = '65100' ) a13
where a11.ACCOUNT_ID = a12.ACCOUNT_ID
  and a12.LOD6_ID = a13.LOD6_ID
group by a11.TIME_PERIOD_IND_ID,
        a11.LEDGER_TYPE_ID,
        a11.FISCAL_PERIOD_ID
;

---

I think the above code may choose hash join for the 3 in-line views.
If not, Add a hash hint.
/*+ ordered use_hash(a12) use_hash(a13) */

Tamil