Home » RDBMS Server » Performance Tuning » Calculate leaf_blocks, avg_leaf_blocks_per_key etc. (Oracle 9.2.0.8 on Sun OS 5.8)
Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340397] Tue, 12 August 2008 10:54 Go to next message
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
Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340408 is a reply to message #340397] Tue, 12 August 2008 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
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
Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340566 is a reply to message #340397] Wed, 13 August 2008 05:20 Go to previous messageGo to next message
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 Razz

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
Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340584 is a reply to message #340566] Wed, 13 August 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
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

Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340615 is a reply to message #340397] Wed, 13 August 2008 07:42 Go to previous messageGo to next message
john100
Messages: 8
Registered: August 2008
Junior Member
doesn't give me height though? For that I need to analyze index validate structure?
Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340622 is a reply to message #340615] Wed, 13 August 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
blevel is height-1

Regards
Michel
Re: Calculate leaf_blocks, avg_leaf_blocks_per_key etc. [message #340636 is a reply to message #340622] Wed, 13 August 2008 09:26 Go to previous message
john100
Messages: 8
Registered: August 2008
Junior Member
Thanks,

That's what I was after.

Previous Topic: Disk I/O started behaving differently when loading data
Next Topic: Update million rows
Goto Forum:
  


Current Time: Thu Dec 08 04:03:07 CST 2016

Total time taken to generate the page: 0.15150 seconds