Re: RE: Slow CREATE BITMAP INDEX and high consistent gets

Just determined that we have all tablespaces defined with ASSM in what is 
essentially a data warehouse type database.     I seem to recall some issues 
with ASSM with 9i and bitmap indexes and FTS.


----- Original Message -----
From: Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>
Date: Tuesday, August 22, 2006 4:14
Subject: RE: Slow CREATE BITMAP INDEX and high consistent gets
To: tim@xxxxxxxxx
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>, thomasjd@xxxxxxxxxxxxx

> 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/sqltrace.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 - 
> consistentread 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 - 
> consistentread 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
>

Other related posts: