dbms_space.free_space[12]|中文方案文档站(提供各类方案下载,WORD文档下载,范文,案例等)

thanks june 13, 2003
reviewer: raghu from milwaukee, wi usa

hi tom,

thanks for your response. i absolutely agree with you that the performance of 
the batch i had given in my example could be improved by changing the algorithm. 
i am sorry that my example diverted you from the "real" reason for my comment.

i was wondering how i can make use of the output of your query in my db 
maintenenace. for instance. is there a co-relation between the number of free 
blocks and performance.

in my original example, can i attribute the difference in batch performance 
between two instance to number of free blocks?(again i accept that the 
performance could be improved further).

another example, i have a table that has around 6 million rows. the average row 
length is 1250 bytes. we do lots of "delete/insert/update" on this table. we 
recently re-partitioned this table (using export, create table and import). we 
noticed a huge performance changes for selects..over the period of time, it 
performance is going back to the "normal". can i attribute this to free blocks 
and do you think that we should constantly look for such blocks and "clean it" 
and how can we do it?. 
note: at this time, i am trying to find the root cause for the performance 
changes. when i read this article of yours i felt that i might have discovered 
"one" of the casues and wanted a confirmation from you.  i also like to point 
out that the db are very similar in terms of size, parameter etc. the code is 
also same.

followup:

there could be -- but in this case, the numbers are far too small. i thought it 
was clear that i do not think that the small number of blocks you have on 
freelists here would have any material affect on anything....

no, you cannot attribute anything to anything here.  there is insufficient data 
to say anything reasonable.

you don't say which way the performance change was, what type of operations you 
do on this table, nothing.  again, totally insufficient data to say anything 
about anything (except hypothesize, which i don't like doing too much).  now, if 
you had some metrics (tkprofs!!!  statspacks!!!) - hard numerical, 
incontravertible data points that would provide information as to the number of 
consistent gets, query plans, inputs, etc over time -- then, well, then we'd be 
cooking with gas...

(but, the performance of queries is unaffected by the number of blocks on the 
freelist -- i could set pctfree/pctused so that every block is on the freelist.  
i could set the pctfree/pctused so that no block is on the freelist.  i could 
have both of these tables -- one with every block on and other with every block 
off -- organized in such a way that block for block they are in effect "the 
same".  they would perform identically.  it is nothing to do with how many 
blocks are on the freelist (although you may be able to use that as an indicator 
if you understand how pctfree/pctused are set and how they are used).  it has 
everything to do with how many blocks oracle must process in order to answer 
your queries.



you need a tkprof.  you need a tkprof of this "batch" on dev, of this "batch" on 
prod.  you need to compare them.  that'll get you 90% of your way along in your 
root cause analysis.

bookmark review | bottom | top

dbms_space.free_space[12]

[入库：2005年8月18日] [更新：2007年3月24日]