Re: latch free and hot blocks

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
Received on Thu Jun 10 2004 - 23:58:43 CDT