RE: Slow CREATE BITMAP INDEX and high consistent gets

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