RE: Performance off "count(*)"

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Sun, 20 Jul 2008 00:18:44 +0800
Message-id: <184A355C6722487AA4D6EE9BD1A0895B@windows01>

Jonathan,

I haven't looked into a binary blockdump, but the symbolic dump of a cleaned out block still knows about the rowpointers as they are (the offs= fields below).

Maybe something like you mentioned happens during block coalesce... haven't checked though.

data_block_dump,data header at 0x33a705c

---

tsiz: 0x1fa0
hsiz: 0x18
pbl: 0x033a705c
bdba: 0x00408b4a

     76543210

flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f8e
avsp=0x1f6d
tosp=0x1f7f
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f9a
0x14:pri[1]     offs=0x1f94
0x16:pri[2]     offs=0x1f8e

block_row_dump:
tab 0, row 0, @0x1f9a
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f94
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 2, @0x1f8e
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 04
end_of_block_dump

--
Regards,
Tanel Poder
 <http://blog.tanelpoder.com> http://blog.tanelpoder.com




> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis

> Sent: Saturday, July 19, 2008 21:34

> To: oracle-l_at_freelists.org

> Subject: Re: Performance off "count(*)"

>

>

> Tanel,

>

> I haven't checked this for several years, but I think the

> rowpointers for deleted rows get set to -1 (or some other

> special value) when the block gets cleaned out.

>

> But you've definitely got me on the head row-pieces, I forgot

> to consider the effects of chained rows.

>

> Regards

>

> Jonathan Lewis

> http://jonathanlewis.wordpress.com

>

> Author: Cost Based Oracle: Fundamentals

> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

>

> The Co-operative Oracle Users' FAQ

> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

>




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

Received on Sat Jul 19 2008 - 11:18:44 CDT