Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can anyone explain this huge increase in logical I./O....?

Re: Can anyone explain this huge increase in logical I./O....?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 23 Oct 2006 12:16:58 -0700
Message-ID: <1161631018.078715.220220@k70g2000cwa.googlegroups.com>


mccmx_at_hotmail.com wrote:
> > In addition to what has already been mentioned, "SQL*Net message from
> > client" is 112.61 seconds for the slow database compared to 69.23
> > seconds for the fast database. The slow database waited 43 seconds
> > longer for responses from the client than the fast database.
> >
>
> Yeah, I noticed this myself, but my main concern is the huge difference
> in logical I/O for the same SQL with the same plan.
>
> > You will also note that there are fewer leaf blocks in the faster
> > database's indexes, which indicates that the index entries are more
> > densely packed than that of the slower database's indexes. Fewer
> > blocks means that there will likely be fewer consistent reads.
>
> I agree but this does not justify the 20 times increase in logical I/O,
> something else is going on here.
>
> > My guess is that the fast database was recently created from an import.
>
> Spot on - I copied the data from the slow DB into the fast DB via
> import/export. Good guess :)
>
> Matt

I suspect that you are losing a fair amount of detail in the TKPROF run. Before executing the query, execute the following: SELECT

  S.SID,
  S.SERIAL#,
  S.USERNAME,

  SN.NAME,
  MS.VALUE
FROM
  V$STATNAME SN,
  V$MYSTAT MS,
  V$SESSION S

WHERE
  MS.SID=S.SID
  AND MS.STATISTIC#=SN.STATISTIC#
ORDER BY
  SN.NAME; Execute your query.

Then execute the following:
SELECT

  S.SID,
  S.SERIAL#,
  S.USERNAME,

  SN.NAME,
  MS.VALUE
FROM
  V$STATNAME SN,
  V$MYSTAT MS,
  V$SESSION S

WHERE
  MS.SID=S.SID
  AND MS.STATISTIC#=SN.STATISTIC#
ORDER BY
  SN.NAME; Compare the VALUE column of first and second run of the above query. Changed values will give you an idea of what is happening in the database, for instance undo being applied to blocks to provide a consistent read. The Oracle manuals explain what each of the statistics mean. For examples:
  CONSISTENT GETS: Statistic counts the number of logical reads in consistent mode.
  CONSISTENT CHANGES: Statistic indicates the number of times a database block has rollback entries applied to perform a consistent read of the block. Work loads that produce a great deal of consistent changes can consume a great deal of resources. The value of this statistic should be small in relation to the 'consistent gets' statistic.
  DATA BLOCKS CONSISTENT READS - UNDO RECORDS APPLIED: Number of undo records applied to data blocks that have been rolled back for consistent read purposes.
  CLEANOUTS AND ROLLBACKS - CONSISTENT READ GETS: Number of consistent gets that require both block rollbacks and block cleanouts.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Oct 23 2006 - 14:16:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US