Re: Bitmap index partitions and recursive calls

From: Pratap <pratap.deshmukh_at_gmail.com>
Date: Wed, 2 Jun 2010 05:10:10 -0700 (PDT)
Message-ID: <59e2b7c0-7a73-4a79-8adb-09a657dbb7ff_at_v18g2000vbc.googlegroups.com>

On May 28, 10:28 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Pratap" <pratap.deshm..._at_gmail.com> wrote in message
>
> news:1d9841d6-9a79-4b0f-b49e-cf2e83e02ade_at_c22g2000vbb.googlegroups.com...
>
> > Hi,
>
> > Oracle 10.2.0.4
>
> > What is the relation between partitioned bitmap indexes and number of
> > recursive calls shown by Autotrace? I have seen that as the number of
> > partitions on a table increase, for a query using bitmap indexes, the
> > number of recursive calls also increase. When the number of partitions
> > on the table are reduced, the same query shows lower recursive calls.
>
> > I have discounted query parsing by running the queries 3 times before
> > observing the recursive calls.
>
> Have you tried enabling SQL trace and seeing if those recursive
> calls appear as SQL in the trace file ?
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com

On top of what I reported about $OR$IDX$PART$NUM, when the query is fired for the first time, I see lot of recursive calls to indsubpart$

select obj#, dataobj#, subpart#, hiboundlen, hiboundval, flags, ts#, file#,
  block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt,
  blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare2,   length(bhiboundval), bhiboundval
from
 indsubpart$ where pobj# = :1 order by subpart#

This query executes 1008 times and takes 76.47

and

select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,   NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),   NVL(scanhint,0)
from
 seg$ where ts#=:1 and file#=:2 and block#=:3

This query executes 93010 times and takes 19.49 seconds overall.

The actual non-recursive query takes just 6 seconds! Received on Wed Jun 02 2010 - 07:10:10 CDT