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_at_trivadis.com> Date: Tuesday, August 22, 2006 4:14 Subject: RE: Slow CREATE BITMAP INDEX and high consistent gets To: tim_at_evdbt.com Cc: oracle-l <oracle-l_at_freelists.org>, thomasjd_at_insightbb.com

> 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
>

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