RE: 10g Performance: its crawling

I usually get events like this when a wrong index is being used. Single block reads are index reads and you're reading a key from index. And repeating the opearation many times. Many, many times.

Mladen Gogala
Sr. Oracle DBA
1500 Broadway, 6th floor
New York, NY 10036
(212) 329-5201
www.vmsinfo.com

The Leader in Integrated Media Intelligence Solutions

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-
> bounce_at_freelists.org] On Behalf Of MVR
> Sent: Thursday, December 28, 2006 3:14 PM
> To: oracle-l
> Subject: 10g Performance: its crawling
>
> Hello,
>
> After 10g upgrade, one job started performing very bad, real slow.
> It
> used to complete in an hour and its taking around 4 hours now. I
> have
> checked wait history from DBA_HIST_ACTIVE_SESS_HISTORY for last
> night's run. Here is a top one event, ordered by TIME_WAITED Column.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> Wait Event: db file sequential read,
> Wait Class: User I/O
>
> p1text : file# , p1: 328
> p2text : block #, p2: 421640
> p3text : blocks , p3: 1
>
> TIME_WAITED: 1327817
> ~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Apparently it looks like its trying to read a single index block
> from
> file 328(afaik). But this time_waited column is driving me crazy. I
> thought If I assume that this is an I/O issue, it makes sense, but
> we
> never had these issues in 9206... so this assumption may not be
> right.
> How can we figure out Segment name with block# ? I know vetarans use
> x$ tables to get this. Anyone any ideas please ?
>
> Thanks
> --
> http://www.freelists.org/webpage/oracle-l

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