 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Oracle / Oracle Server / August 2005When should I reorg?
At what point should a reorganization of a table be considered? I have a table that has 48 extents, and two indexes of 77 and 23 extents each. The time required to read this table sequentially has dropped significantly recently after many new rows were added. The table is 334 meg and containst 41,785 blocks. The two indexes are 450 meg and 288 meg in size with 60,000 blocks and 36,000 blocks respectively. Thanks for all replies Wardell Castles Perhaps you would be so kind as to tell us the version of Oracle operating system & version how many disks in the raid array current block size SGA specifics when stats were run last - you are using CBO, aren't you???  SignatureHans Forbrich Canada-wide Oracle training and consulting mailto: Fuzzy.GreyBeard_at_gmail.com *** I no longer assist with top-posted newsgroup queries *** Hans, I am sorry but I don't have that information. I am not the DBA. I am but a lone insignificant developer trying to figure out why access to this table is so poor recently. > Hans, > > I am sorry but I don't have that information. I am not the DBA. I am > but a lone insignificant developer trying to figure out why access to > this table is so poor recently. Then your guess is as good as mine. SignatureHans Forbrich Canada-wide Oracle training and consulting mailto: Fuzzy.GreyBeard_at_gmail.com *** I no longer assist with top-posted newsgroup queries *** Wardell, by the way the number of extents a table or index is in will usually not affect DML performance except in those cases where the table bocks are largely empty and you are full table scanning. Then a rebuild wherre you pack the blocks full would likely run faster than before the reorg. If the blocks are full then reading a 100M table stored in 1 extent or 100 extents is not likely to matter. This fact means the extent information you posted is usually not very helpful in solving performance issues. What is interesting is that you said that "many new rows were added" recently. Where the CBO statistics updated to reflect the new data quantity. Bad statistics often result in bad plans. HTH -- Mark D Powell -- Sorry.. I meant to say... "The time required to read this table sequentially has INCREASED significantly....." > Sorry.. I meant to say... "The time required to read this table > sequentially has INCREASED significantly....." Add to the list: Explain Plan or peferrably a trace and a StatsPack. What has changed. Reorganizing a large table, without benefit of metrics, is just a shot in the dark. SignatureDaniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond) If you add rows, you add data. The more data you have, the more you have to read when querying the table. The more you have to read, the longer it will take... A reorganization of a table should be considered when 1) Empirical testing demonstrates that the performance benefit outwieghs the time required to rebuild the table AND (a very important AND) 2) The underlying issue that caused a reorganization to be required has been identified and fixed. If you had just deleted 90% of the rows of a table AND were never going to insert more rows AND many queries were full table scans, a table rebuild would probably be beneficial. What's significantly less? Why do you require to read the whole table every time? Have you considered using materialized views for summaries (if you scan the whole table for summaries)? Have you considered selecting subsets of data (e.g. utilizing rownum)? (if you require looking through the whole table in parts). ....... We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4 remove NSPAM to email > At what point should a reorganization of a table be considered? > [quoted text clipped - 8 lines] > Thanks for all replies > Wardell Castles See also http://www.jlcomp.demon.co.uk/faq/table_frag.html (although it is a bit out of date [modern Oracle databases should use LMT's] and the SAFE link seems broken, try http://metalink.oracle.com/metalink/plsql/docs/239049.PDF if you have support.) You should find out if you are using Locally Managed Tablespaces, and if not, use them. jg -- @home.com is bogus. http://patft.uspto.gov/netacgi/nph-Parser?Sect1=PTO1&Sect2=HITOFF&d=PALL&p=1&u=/ netahtml/srchnum.htm&r=1&f=G&l=50&s1=6,935,954.WKU.&OS=PN/6,935,954&RS=PN/6,935, 954 Hello, wardellcastles schrieb: > At what point should a reorganization of a table be considered? Reorg of the table is not needed most time as others have pointed at. > I have a table that has 48 extents, and two indexes of 77 and 23 > extents each. The time required to read this table sequentially has > dropped significantly recently after many new rows were added. More important it seems to look at the indexes. 1. Why is Index 1 significant bigger then the table? Maybe there was a lot of delete before inserts. if so the index should be rebuild. 2. Are the indexes used? hth Matthias |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.