RE: Latch Free Problem - Need Help

This may not help, but have you tried replacing "AND ((IW_WK_CAT_STORE_SALES_T.PERIOD_KEY > :b1 OR IW_WK_CAT_STORE_SALES_T.PERIOD_KEY = :b1 ) AND (IW_WK_CAT_STORE_SALES_T.PERIOD_KEY < :b3 OR IW_WK_CAT_STORE_SALES_T.PERIOD_KEY
= :b3 ))"

with "AND IW_WK_CAT_STORE_SALES_T.PERIOD_KEY BETWEEN :b1 AND :b3"

I replaced your table names with ones that seem to work in my system, and using the between gave me an explain plan that used the indices. The original query produced full table scans for me as well.

HTH, Diana

-----Original Message-----
From: Sandy Druar [mailto:sdruar_at_eckerd.com] Sent: Wednesday, August 30, 2000 2:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: Latch Free Problem - Need Help

The partition key is by period_key and the index is by period_key,product_key,org_key.

SELECT IW_WK_CAT_STORE_SALES_T.ORG_KEY,IW_PRODUCT_T.SBU_NO,IW_PRODUCT_T.SBU, IW_PRODUCT_T.BUSINESS_UNIT,
SUM(IW_WK_CAT_STORE_SALES_T.REGULAR_SALES + IW_WK_CAT_STORE_SALES_T.AD_SALES )
SALES,SUM(IW_WK_CAT_STORE_SALES_T.REGULAR_COST + IW_WK_CAT_STORE_SALES_T.AD_COST
) COST
FROM IW_WK_CAT_STORE_SALES_T,IW_PRODUCT_T WHERE (IW_PRODUCT_T.PRODUCT_KEY = IW_WK_CAT_STORE_SALES_T.PRODUCT_KEY ) AND ((IW_WK_CAT_STORE_SALES_T.PERIOD_KEY > :b1 OR IW_WK_CAT_STORE_SALES_T.PERIOD_KEY = :b1 ) AND (IW_WK_CAT_STORE_SALES_T.PERIOD_KEY < :b3 OR IW_WK_CAT_STORE_SALES_T.PERIOD_KEY
= :b3 ))
GROUP BY IW_WK_CAT_STORE_SALES_T.ORG_KEY,IW_PRODUCT_T.SBU_NO, IW_PRODUCT_T.SBU,IW_PRODUCT_T.BUSINESS_UNIT We sent 3 system state dumps to Oracle, and they are saying The main latch free was cache buffer chains latch. This is not uncommon when the buffer cache is getting hit hard.

I should run a bstat/estat for 2 hours during peak processing to see if there is any tuning that we can do that will help alleviate this issue.

Thanks, Sandy

Diana Duncan wrote:

> What's the select statement, what is the partition key and what are your
> indices?
>
> If the partition key is not the first element in the index, or if you do
not
> reference the partition key, you'd get this behavior.
>
> -----Original Message-----
> Sent: Wednesday, August 30, 2000 1:11 PM
> To: Multiple recipients of list ORACLE-L
>
> Hi List,
>
> We are running this job which is talking over 24 hours to process.
> This job usually takes 1 hour to process with running parallel 4.
> But for some reason the select is not using the index and this table
> has 43 partitions. The following is the explain plan through top
> session.
>
> ----Select Statement
> |-->Sort (group by)
> |-->Hash Join
> |-->Partition (Concatenated)
> |--> Table Access (Full)
> |--> Table Access (Full)
>
> I did a select on v$session_wait and noticed that all four sids had a
> latch free for several hours. What can I do to fix this problem?
>
> Any help would be appreciated.
>
> Thanks
> Sandy Druar
> Oracle DBA
> sdruar_at_eckerd.com
>
> --
> Author: Sandy Druar
> INET: sdruar_at_eckerd.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Author: Diana Duncan
> INET: Diana_at_fileFRENZY.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Author: Sandy Druar
  INET: sdruar_at_eckerd.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may