OraFAQ Forum: Performance Tuning » Calculate leaf_blocks, avg_leaf_blocks_per

Home » RDBMS Server » Performance Tuning » Calculate leaf_blocks, avg_leaf_blocks_per_key etc. (Oracle 9.2.0.8 on Sun OS 5.8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340397]

Tue, 12 August 2008 10:54

john100
Messages: 8
Registered: August 2008

Junior Member

I want to get the information gathering statistics and validating indexes would give me without locking the tables/indexes involved.

I'm getting stuck with the index information leaf_blocks, avg_leaf_blocks_per_key, height, and avg_data_blocks_per_key.

I'm sure it's fairly straight forward but every time I write a query using, for example, user_segments, my results do not agree with those dbms_stats gives me for my test case.

The simple answer is probably "don't do it" but I can't see a way round the requirement so can any one give me any pointers?

Many thanks,

John

Report message to a moderator

Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340408 is a reply to message #340397]

Tue, 12 August 2008 11:44

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I write a query using, for example, user_segments, my results do not agree with those dbms_stats gives me for my test case.

Maybe your query is wrong.
By the way, how could you know leaf_blocks, avg_leaf_blocks_per_key, height, and avg_data_blocks_per_key querying use_segments?

Regards
Michel

Report message to a moderator

Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340566 is a reply to message #340397]

Wed, 13 August 2008 05:20

john100
Messages: 8
Registered: August 2008

Junior Member

"Maybe your query is wrong"

No, Really? There I was asking for help because I thought I had all the answers Confused

I don't think you can know those values by querying user_segments. In fact, I think I've proved fairly successfully that you can't. It should still be possible to work them out without locking the objects - No?

Has any one done this? Any suggestions? I'd prefer to avoid stepping through trace etc. if I'm going to be reinventing the wheel.

Thanks,

John

Report message to a moderator

Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340584 is a reply to message #340566]

Wed, 13 August 2008 06:16

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So the question is: "is it possible to get the same values without using locking the index/table"?

Yes, use dbms_stats.

SQL> create index t_i on t(col);

Index created.

SQL> select * from t;

no rows selected

SQL> insert into t select level from dual connect by level < 1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_index_stats (user,'T_I')

PL/SQL procedure successfully completed.

SQL> select leaf_blocks, avg_leaf_blocks_per_key,blevel, avg_data_blocks_per_key
  2  from user_indexes where index_name='T_I';
LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY     BLEVEL AVG_DATA_BLOCKS_PER_KEY
----------- ----------------------- ---------- -----------------------
          2                       1          1                       1

1 row selected.

dbms_stats does not lock the object.

Regards
Michel

[Updated on: Wed, 13 August 2008 06:16]

Report message to a moderator