Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: LMT advice
> I agree that uniform sizing is a lovely idea. And I've been very reluctant
> to endorse autoallocate much before now, because the intracacies of the
> algorithm are nothing if not opaque, and I don't like recommending things I
> can't explain or predict very easily. But my conclusion is simply that
> autoallocate does a very efficient job, and that there is nothing to fear
> from using it.
I too prefer uniform sizing, but in my case, AUTOALLOCATE was the way to go. The database that I posted my results from is running ESRI's ArcSDE product to store spatial data. Spatial data gets to be very large. ArcSDE uses many tables behind the scenes to hold this spatial data. For instance, to see all the tables that comprise this one spatial layer that the one table comes from, look at the following:
GASP SQL> set serveroutput on GASP SQL> set lin 130 GASP SQL> exec layer_space('WEBMAP','UA_ORTHO_336_01','RASTER'); ======================================================================================Space Usage for: WEBMAP.UA_ORTHO_336_01(1071)
Object Name Object Type Total Bytes Unused Bytes Pct Used ------------------------------ ------------ ---------------- ---------------- -------- UA_ORTHO_336_01 TABLE 65,536 49,152 25.00% SDE_AUX_1071 TABLE 2,097,152 843,776 59.77% SDE_BLK_1071 TABLE 175,754,969,088 63,750,144 99.96% SDE_BND_1071 TABLE 65,536 49,152 25.00% SDE_RAS_1071 TABLE 65,536 49,152 25.00% SDE_AUX_1071_UK INDEX 65,536 49,152 25.00% SDE_BLK_1071_UK INDEX 494,927,872 1,753,088 99.65% SDE_BND_1071_UK1 INDEX 65,536 49,152 25.00% SDE_BND_1071_UK2 INDEX 65,536 49,152 25.00% SDE_RAS_1071_UK INDEX 65,536 49,152 25.00% Grand Totals: 176,252,452,86466,691,072 99.96%
PL/SQL procedure successfully completed.
All of these tables and indexes comprise that one spatial layer. Notice that one table, SDE_BLK_1071 accounts for approximately 99% of the total layer size! And some of the underlying tables and indexes are using less than one 64K extent of space. But one index is semi-large too. So do I put all of these into different tablespaces according to size and use UNIFORM? Too much work and I'd have too many tablespaces once all of my spatial data was loaded. Instead, lump them into one tablespace and use AUTOALLOCATE to sort it out. In the end, I agree that mine is a special case. For my other databases, I use UNIFORM extent sizes. But for spatial data, I use AUTOALLOCATE. And it performs very well, even though I haven't been able to sort out everything on how it all works yet.
Cheers,
Brian
-- =================================================================== Brian Peasland dba_at_remove_spam.peasland.com Remove the "remove_spam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three"Received on Mon Sep 29 2003 - 09:37:15 CDT