Prev: Understanding this query
Next: Our customer websites are modern, fresh and accessible.
From: Pratap on 2 Jun 2010 08:10
On May 28, 10:28 pm, "Jonathan Lewis" <jonat...(a)jlcomp.demon.co.uk>
wrote:
> "Pratap" <pratap.deshm...(a)gmail.com> wrote in message
>
> news:1d9841d6-9a79-4b0f-b49e-cf2e83e02ade(a)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!
First  |  Prev  | 
Pages: 1 2
Prev: Understanding this query
Next: Our customer websites are modern, fresh and accessible.