Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Can anyone explain this huge increase in logical I./O....?
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,
V$STATNAME SN, V$MYSTAT MS, V$SESSION S
Then execute the following:
SELECT
S.SID, S.SERIAL#, S.USERNAME,
V$STATNAME SN, V$MYSTAT MS, V$SESSION S
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Oct 23 2006 - 14:16:58 CDT