Re: Source of expensive SQL

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
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l


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


Other related posts: