Re: latch free and hot blocks

As a general guideline, bind variables
and cursor_sharing=force are not a
good idea on DW or DSS systems.

Typically, DW and DSS systems execute
a relatively small number of queries that are likely to be quite costly to run - therefore the penalty of optimizing a minor component of the total cost, the small number of queries means the contention is irrelevant, and the extra precision available to the optimizer is a big benefit.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"hrishy" <hrishys_at_yahoo.co.uk> wrote in message
news:4ef2a838.0406102058.400b9f7_at_posting.google.com...


> Hi All

>

> I started digging deeper into the application sql's and found that it

> is using cursor_sharing_force there are full table scans table names

> are like mytable_FACT_something queries are being generated by

> application just suspecting that this might be a DSS type of

> application and i am wundering the use of cursor_sharing here .

>

> can cursor_sharing=force contribute to latch free and hot blocks ?

>

> regards

> Hrishy

>

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

news:<c9idv3$e4a$1_at_sparta.btinternet.com>...


> > Do check that the event 'latch free' is actually

> > losing a worthwhile amount of time before you

> > try fixing the issue.  If it is then:

> >

> > select * from (

> > select addr, sleeps from v$latch_children

> > where name = 'cache buffers chains'

> > order by sleeps desc

> > )

> > where rownum <= 10

> > ;

> >

> > select ts#, file#, dbarfil, dbablk, obj, class, state, tch

> > from x$bh

> > where hladdr = {one of the addr values}

> > order by tch desc

> > /

> >

> > (Have to be connected as SYS to run the above).

> >

> > Blocks with a TCH (touch count) value that

> > goes into double figures or better are possible

> > targets for hot blocks.

> >

> > The OBJ is the data_object_id from dba_objects,

> > but if it's over ca. 4 million it's a temporary object

> > or a rollback segment.

> >

> > -- 

> > Regards

> >

> > Jonathan Lewis

> >

> > http://www.jlcomp.demon.co.uk

> >

> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html

> > The Co-operative Oracle Users' FAQ

> >

> > http://www.jlcomp.demon.co.uk/seminar.html

> > Optimising Oracle Seminar - schedule updated May 1st

> >

> >

> > "hrishy" <hrishys_at_yahoo.co.uk> wrote in message

> > news:4ef2a838.0406010729.3fef58ae_at_posting.google.com...

> > > Hi Gurus

> > >

> > > I am performing a health check on my database and the latch free event

> > > is significant..i investigated for various types of latch misses and

> > > the one that is signifcant seems to be cache buffers chain.The oracle

> > > docs says i need to find hot blocks..Now how do i find these hot

> > > blocks ?

> > >

> > > regards

> > > Hrishy