Tim

> Could delayed block cleanout from a massive UPDATE or DELETE be
> causing high LIO? Perhaps someone else can confirm, but I think
> the count for "current" (a.k.a. "db block changes") should be
> pretty high if that were happening. However, it also sounds like
> you've done the CREATE BITMAP INDEX several times, and delayed
> block cleanout probably wouldn't repeat like that.

A couple of comments about that:

* Jeff is experiencing a high number of "query" and not "current".

* "current" are associated to "db block gets" and not "db block changes"
(at least I cannot remember doing another observation, in addition it's
documented in that way as well
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltra
ce.htm#i4241).

* If a statement "suffers" from delayed block cleanout the TKPROF output
shows a higher number of "current" (which is Jeff's case).

* To check delayed block cleanouts it's possible to inspect "redo size"
(the CREATE INDEX contains NOLOGGING) or even better "cleanouts only -
consistent read gets".

Here an example:

* delayed block cleanout in action

SQL> exec sesstat.init('name in (''db block gets'',''consistent
gets'',''db block changes'',''redo size'',''cleanouts only - consistent
read gets'')')
SQL> create bitmap index i on t (n) nologging;
SQL> exec sesstat.print
Name                             Value
---------------------------------------- ------------
db block gets                         723
consistent gets                      10141
db block changes                      5266
redo size                         399204
cleanouts only - consistent read gets        4858

call   count   cpu elapsed disk query current  rows
------- ------ ----- -------- ----- ------ -------- -----
Parse     1  0.02   0.03   0   11     0    0
Execute    1  0.97   3.40 5003 10096    673    0
Fetch     0  0.00   0.00   0    0     0    0
------- ------ ----- -------- ----- ------ -------- -----
total     2  1.00   3.44 5003 10107    673    0

****** no delayed block cleanout

SQL> exec sesstat.init('name in (''db block gets'',''consistent
gets'',''db block changes'',''redo size'',''cleanouts only - consistent
read gets'')')
SQL> create bitmap index i on t (n) nologging;
SQL> exec sesstat.print
Name                             Value
---------------------------------------- ------------
db block gets                         707
consistent gets                       5298
db block changes                       407
redo size                          49080
cleanouts only - consistent read gets          0

call   count   cpu elapsed disk query current  rows
------- ------ ----- -------- ----- ------ -------- -----
Parse     1  0.01   0.01   0    1     0    0
Execute    1  0.61   0.63 5003  5105    673    0
Fetch     0  0.00   0.00   0    0     0    0
------- ------ ----- -------- ----- ------ -------- -----
total     2  0.62   0.65 5003  5106    673    0



HTH
Chris

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