Re: Source of expensive SQL

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: 2006-01-06 19:01:41
Message-id: 43BEB085.50500@centrexcc.com

Not sure if it helps, but wouldn't "bitand(i.flags, 256)" and "bitand(i.flags, 512)" be the same as 0x100 and 0x200 below (from sql.bsq):

flags number not null,

   /* mutable flags: anything permanent should go into property */


                                       /* unusable (dls) : 0x01 */

                                       /* analyzed       : 0x02 */

                                       /* no logging     : 0x04 */

                       /* index is currently being built : 0x08 */
                        /* index creation was incomplete : 0x10 */
                              /* key compression enabled : 0x20 */
                                 /* user-specified stats : 0x40 */
                               /* secondary index on IOT : 0x80 */
                         /* index is being online built : 0x100 */
                       /* index is being online rebuilt : 0x200 */
                                   /* index is disabled : 0x400 */
                                        /* global stats : 0x800 */
                               /* fake index(internal) : 0x1000 */
                          /* index on UROWID column(s) : 0x2000 */
                               /* index with large key : 0x4000 */
                /* move partitioned rows in base table : 0x8000 */
                    /* index usage monitoring enabled : 0x10000 */
         /* 4 bits reserved for bitmap index version : 0x1E0000 */

 From the same source:

type#         number not null,
     /* what kind of index is this? */
                      /* normal : 1 */
                      /* bitmap : 2 */
                     /* cluster : 3 */
                   /* iot - top : 4 */
                /* iot - nested : 5 */
                   /* secondary : 6 */
                        /* ansi : 7 */
                         /* lob : 8 */

    /* cooperative index method : 9 */

PS. Anyone know what a "cooperative index method" is?

Tanel Põder wrote:

> Hi,
>  
> where       (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512)
> 
> and         (not((i.type# = 9) and bitand(i.flags,8) = 8))
> 
>  
>  
> That might be SMON trying to search for some work to do regularly, but 
> hitting a bad execution plan when doing dictionary queries. 
> Unfortunately the catalog views don't have ind$.flags bits representing 
> 256 and 512 documented, so I don't know what is it searching for. Maybe 
> these have something to do with LOB indexes and undo retention, but not 
> sure.
>  

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 06 2006 - 19:01:41 CST