Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

Re: *Measuring sql performance (elapsed time and scalability) by number of logical reads

From: Andrey Kriushin <>
Date: Tue, 02 May 2006 23:25:10 +0400
Message-ID: <>

> Is there a way to get information about how (calls to hash function
> or something else) service time (CPU) is used up ?
> Something that makes clear the difference between the two fetch actions.
> Fetch 2 0.26 0.25 0 1725 0 1
> Fetch 2 0.06 0.05 0 7559 0 1
In fact you've already measured it in the trace + tkprof. Look carefully at "Row Source Operation". Distinguish the data access path (which requires LIO) and operations on a row sources themselves which in most cases are NOT.

  3309       HASH JOIN  (cr=1523 r=0 w=0 time=223005 us)
  3309        NESTED LOOPS  (cr=913 r=0 w=0 time=10605 us)
168826        INDEX FAST FULL SCAN XPKAN_PARTS (cr=610 r=0 w=0 time=72513 us)(object id 6761)

Note two row source inputs to HASH JOIN.   First one contained only NESTED LOOPS and access path... And it is good approximation that NL itself doesn't consume too much CPU. So we see total 913 LIO in 10605 us.
  The second is IFFS with 610 LIO in 72513 us. Total is 1523 LIO..... AND 223005 us. Not 83118 us, right? Looks like we've found ~140 msec. And those 140 msec are not just hash value calculation, but also memory management (chunk allocation) in PGA, probably growing PGA itself etc.

Also, as somebody already mentioned, LIO time can vary significantly. 10605/913 ~= 11.6 us and 72513/610 ~= 119 us. The length of hash chains may count, for example. Well, on quiet instance that might be row processing time inside the block as well and not only the time to pin the buffer.

Well I've looked through other LIOs in your report and found that 119 us is very untypical for your setup. Most LIOs take ~10 us. So that is another source of difference. Probably that was just unlucky run? I'd try IFFS on this index. Anyway, these are additional missing 60 msec.

So here is the difference you're looking for - 140+60 = 200 msec. HASH JOIN plus superLIO in IFFS. Something wrong with this math, right? ;-)

Don't try to avoid HASH JOIN blindly. This join method is a stayer not a sprinter. It wins on a long distances ;-)

To summarize:
- in general the number of LIO/exec CAN give you a sign of poorly tuned SQL statement (throw-away rows/late filtering/unselective access path etc) - LIOs by themselves are not final criteria. It is response time that counts.
- Method-R ((c) Cary Millsap) is not "a popular paradigm", but preferred and well established approach to tuning

- Andrey

Received on Tue May 02 2006 - 14:25:10 CDT

Original text of this message