RE: prefixed index - high clustering factor & high cpu

First, I'm curious - what is the purpose of using two different bind variables as filters to the same column? Are my eyes fooling me or are both :1 and :2 required to be equal to transc_id? (And therefore equal to each other, so why use both?

Next, are you saying that all of these values pretty much start with

811120980.2007? (That's lookin' like a YYYYMMDDHH24MISS after the . to me. Is it? If so, then what is the 81120980 part? Does it vary?)

Anyway, this looks like a composite key stored in a single column. Maybe you can't change that at this time, but it is usually a bad idea.

Now you say this is a unique index, so it must be nullable since you've got more rows than distinct keys. That shouldn't hurt an update looking for a specific row. You've got a little over 151 keys per block, but you should only have to look in one leaf block since it is unique.

What does the plan look like? Is the CBO somehow being tricked out of using that index because it is used twice?

If you're not getting an index lookup I suggest you Wolfgang that query (10053 trace). Or if I'm right and there just is no purpose to using the same column in an and with two bind variables, then just repair the query and see if it speeds things up.

I'd suggest where (:1 = :2) and transc_id = :1 or something like that if you can't short circuit the application to not even go to the database when :1 is different from :2.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ujang Jaenudin
Sent: Wednesday, October 24, 2007 2:18 AM To: oracle-l_at_freelists.org
Subject: Re: prefixed index - high clustering factor & high cpu

sorry i forgot to tell you about

the wait event of cbc (cache buffers chains) is within the query also.

regards
ujang

On 10/24/07, Ujang Jaenudin <ujang.jaenudin_at_gmail.com> wrote:
> all,
>
> I have varchar2 column (transc_id) with the value something like
> this...and this column was indexed (unique index)
>
> 811120980.20070920033742
> 811120980.20071021183003

>
> UPDATE c_transaction SET pgi_status =
> decode(c_status,0,1,1,1,2,1,3,3), ne_status = 1, messagetracking_id =
> NULL, last_processing_dtm = SYSDATE WHERE transc_id = :1 and transc_id
> = :2
>
> at the tkprof the cpu cost is very high , its about 30k, rows returned
> is 1 , io cost is 4 , the cost itself only 4.
>
> the dba_indexes says:
> num_rows = 2195991
> distinct_key = 1507195
> clustering_factor = 1476573
> blevel = 2
> leaf_blocks = 14486
>
> histogram is already there with 254 bucket....
>
> I'm guessing to turn index to reverse key, is it has the benefit impact?
>
> thanks in advance
>
> --
> regards
> ujang
>

-- 
regards
ujang
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l