RE: Explanation of index statistics
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 May 2004 13:33:51 -0500
Richard - Thanks for posting this paper. It looks really great.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Richard Foote
Sent: Friday, May 14, 2004 7:00 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Explanation of index statistics
Hi Binyamin,
They match because most of the leaf rows (106536) are deleted leaf rows
(106533) meaning you only have *3* non deleted leaf rows.
Basically your table has undergone a somewhat dramatic shrinkage in size.
If you want to learn a thing or two about indexes, take a took at a recent
presentation of mine at
www.actoug.org.au/Downloads/oracle_index_internals.pdf
Regards
Richard
----- Original Message -----
From: "Binyamin Dissen" <bdissen@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, May 14, 2004 8:50 PM
Subject: Explanation of index statistics
I did an analyze against the index that is using so many buffers (when there
was less than 10 rows in the table).
analyze index &indexname validate structure
The INDEX_STATS table shows
HEIGHT BLOCKS NAME PARTITION_NAME
3
16384 &indexname
LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN
106536 6465 2450320 3540 6464 157 122626
BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY
3932 106533 2450251 30847 18
BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS
PRE_ROWS
23503424 2572946 11 3.4536908 5.2268454
0
PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
0 2 28
How do these numbers match to such a small table (at the time of the
analyze)?
--
Binyamin Dissen <bdissen@xxxxxxxxxxxxxxxxxx>
http://www.dissensoftware.com
Director, Dissen Software, Bar & Grill - Israel
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
