Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Source of expensive SQL

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US