RE: Slow CREATE BITMAP INDEX and high consistent gets
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <tim@xxxxxxxxx>
- Date: Tue, 22 Aug 2006 10:13:47 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
