Stats better but SQL is slower

I eventually got the tkprof output:

Old

Code:

---

SELECT /*+ USE_HASH(t d) */ t.BUSINESS_DATE, t.SCHEDULE_ID, RANK() OVER
  (PARTITION BY NVL(t.DEAL_ID, '~NVL'),NVL(t.LEG_TYPE_CD, '~NVL') ORDER BY
  t.SRC_SYS_FEED_ROW_ID DESC, t.SCHEDULE_ID  DESC) ERROR_BIT_ID
FROM
 SCHEDULE t, (SELECT /*+ USE_HASH(t d) */ BUSINESS_DATE, SRC_SYS_FEED_ID,
  NVL(t.DEAL_ID, '~NVL') AS DEAL_ID,NVL(t.LEG_TYPE_CD, '~NVL') AS LEG_TYPE_CD
  FROM SCHEDULE t WHERE BUSINESS_DATE = :p_business_date and
  SRC_SYS_FEED_ID = :p_src_sys_feed_id GROUP BY BUSINESS_DATE,
  SRC_SYS_FEED_ID, DEAL_ID, LEG_TYPE_CD HAVING COUNT(*) > 1) d WHERE
  t.BUSINESS_DATE = :p_business_date and t.SRC_SYS_FEED_ID =
  :p_src_sys_feed_id and BITAND(t.ROW_ERROR_CD, 3) = 0 and NVL(t.DEAL_ID,
  '~NVL') = d.DEAL_ID and NVL(t.LEG_TYPE_CD, '~NVL') = d.LEG_TYPE_CD


call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      0.00          0          0          0          0
Execute      1      0.03      0.02          0          0          0          0
Fetch        9    47.00      53.87      58335      43550        29          8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      11    47.03      53.90      58335      43550        29          8

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 670    (recursive depth: 1)

Rows    Row Source Operation
-------  ---------------------------------------------------
      8  WINDOW SORT
      8  FILTER
 548427    SORT GROUP BY
 548435    HASH JOIN
 548427      PARTITION RANGE SINGLE PARTITION: KEY KEY
 548427      TABLE ACCESS FULL SCHEDULE PARTITION: KEY KEY
 548427      PARTITION RANGE SINGLE PARTITION: KEY KEY
 548427      TABLE ACCESS FULL SCHEDULE PARTITION: KEY KEY


Elapsed times include waiting on following events:
  Event waited on                            Times  Max. Wait  Total Waited
  ----------------------------------------  Waited  ----------  ------------
  db file scattered read                      5440        0.01          6.31
  direct path write                            726        0.07        11.82
  direct path read                            1374        0.04          0.50

---

New:

Code:

---

SELECT t.rowid,  RANK() OVER (PARTITION BY DEAL_ID
,LEG_TYPE_CD
 ORDER BY t.SRC_SYS_FEED_ROW_ID DESC, t.SCHEDULE_ID  DESC) AS ERROR_BIT_ID
FROM
(SELECT ROWID, SRC_SYS_FEED_ROW_ID, SCHEDULE_ID,
NVL(t.DEAL_ID, '~NVL') AS DEAL_ID,
NVL(t.LEG_TYPE_CD, '~NVL') AS LEG_TYPE_CD,
COUNT(1) OVER (PARTITION BY NVL(t.DEAL_ID, '~NVL')
,NVL(t.LEG_TYPE_CD, '~NVL')
 ) DUPS FROM SCHEDULE t WHERE t.BUSINESS_DATE = :p_business_date
and t.SRC_SYS_FEED_ID = :p_src_sys_feed_id
and BITAND(t.ROW_ERROR_CD, 3) = 0  ) t
 WHERE DUPS > 1

call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01      0.00          0          0          0          0
Execute      1      0.01      0.00          0          0          0          0
Fetch        1    49.17      58.45      32260      21775        27          8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    49.19      58.46      32260      21775        27          8

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 670    (recursive depth: 1)

Rows    Row Source Operation
-------  ---------------------------------------------------
      8  WINDOW SORT
      8  VIEW
 548427    WINDOW SORT
 548427    PARTITION RANGE SINGLE PARTITION: KEY KEY
 548427      TABLE ACCESS FULL SCHEDULE PARTITION: KEY KEY


Elapsed times include waiting on following events:
  Event waited on                            Times  Max. Wait  Total Waited
  ----------------------------------------  Waited  ----------  ------------
  db file scattered read                      2720        0.03          3.20
  direct path write                            135        0.07          5.24
  direct path read                            5903        0.09          8.98

---

It looks like you need to increase the sort_area_size for your session. The time is being spent on sorts (direct path waits).

I would give that a try and see if it helps. I agree with you, it seems like the second query should be the better approach.

Hope that helps.

I tried doubling the sort_area_size to 131072 and doubling it again to 262144, but there was no discernable change in the run times. Do I need to change anything else e.g. sort_area_retained_size (currently set to 0) ?

Set the sort area size to 8M and sort area retained size to 1MB.
If you use LMT TEMPORARY TABLESPACE, set it to uniform size of 8MB.

What is the HASH area size?
Do you use PGA?
What is the value for db_file_multiblock_read_count?

How many CPUs does this box have?

Tamil

After trying out your suggestions, the time for the job has come down by about 15 seconds, so that looks quite good. Many thanks for your help. On your other questions:

db_file_multiblock_read_count=8
hash_area_size=131072
cpu_count=3

I'm not sure what you mean by "Do you use PGA ?". I thought PGA was private global area - just the bit of memory assigned to the user.

Check the setting for workarea_size_policy. You will need to set this to manual during your session, otherwise Oracle automatically determines the size for the _size parameters.

It looked to me like the disk activity was coming from sorting and increasing the sort_area_size should move that activity into memory instead.

Quote:

---

Originally Posted by Scorby

After trying out your suggestions, the time for the job has come down by about 15 seconds, so that looks quite good. Many thanks for your help. On your other questions:

db_file_multiblock_read_count=8
hash_area_size=131072
cpu_count=3

I'm not sure what you mean by "Do you use PGA ?". I thought PGA was private global area - just the bit of memory assigned to the user.

---

By increasing db_file_multiblock_read_count to 32 or 64 at the session level, you can reduce "db file scattered wait event". Also, hash_area_sise is too small. Set it to 8MB.

Tamil

I think my speed improvement must have been coincidental. pga_aggregate_target is set to 104857600 and workarea_size_policy is set to auto, so settings like sort_area_size should be ignored anyway.

I've tried setting:

alter session set workarea_size_policy=manual;
alter session set sort_area_size=8388608;
alter session set sort_area_retained_size=1048576;
alter session set db_file_multiblock_read_count=64;
alter session set hash_area_size=8388608;

Timings then went backwards, with the jobs definitely taking longer.

What does the trace file say now? Hash_area_size shouldn't be a factor anymore, it is not doing a hash join in the second version of the query.

I would test increasing the sort_area_size to larger amounts and see if there is any difference in performance. Try 20 Mb and see if your direct path waits go down.