CR blocks visited

From: Paul van den Bogaard <paul_at_vdbogaard.com>
Date: Fri, 09 Dec 2005 16:24:41 +0100
Message-ID: <4399A1B9.9020206@vdbogaard.com>

doing some tests in an isolated environment I see a statement is performing on average 66 CRs (no CU). Executing this same query from SQLplus with autotrace on tells me it uses only 3 CRs (no CU).

The Query is like:

SELECT /*+ index(ATTVALUE PK_INDEX0) */ MIN(DATE1) FROM TABLE1 ATTVALUE

WHERE ATTR1 = :B3
  AND ATTR2 = :B2
  AND DATE1 > :B1

  AND ATTR3 <= 0

I checked the table after the test and all (ATTR1, ATTR2) combinations are present just once: this duo is already unique. The index PK_INDEX0 is defined on the four columns: (ATTR1, ATTR2, ATTR3, DATE). A snapshot tells me CPU usage is 90% of the used time. There is no significant parsing and this query is number one when looking at buffer_gets. About everything is in the SGA, there is almost no read IO.

The only other statement on this table is an insert of a single record. Indeed this insert is also in the top of most executed statements.

This setup runs on a machine with 32 CPUs running Oracle 9.2.0.7 32bits. 64 sessions all doing the same work.

Question: why so many CRs? Especially when compared to what I get when execing the Q from sqlplus. Is this purely due to the amount of parallellisme of so many concurrent queries and inserts?

What could be helpfull to reduce CPU consumption (reduce logical IO)?

Is there a way to see the CR blocks visited?

Thanks
Paul

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

Received on Fri Dec 09 2005 - 09:24:58 CST