| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: TEMP tablespace size in a Data Warehouse
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFD553.B7698330
Content-Type: text/plain;
charset="iso-8859-1"
Isn't it true that in 8i it if you use estimate Oracle get specified percentage of data randomly and before 8i it took specified percentage of data from the beginning of the table and therefore in 8i we can use smoller percentage with more reliable results?
Alex Hillman
-----Original Message-----
From: Gaja Krishna Vaidyanatha [mailto:gajav_at_yahoo.com]
Sent: Tuesday, June 13, 2000 12:38 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: TEMP tablespace size in a Data Warehouse
Charlie,
On a 1 TB implementation, we used the "rule of thumb" of (1.5 * size of the largest table) as required temporary tablespace. The database release was in 7.3.4 and 8.0.5. This sizing was done keeping in mind, not only the need for temp segments during application sorts and index creations, but also for the potential need to perform a "full analyze" on the table with the "compute statistics" option. You are aware that a table analyze automatically induces analyze of all the relevant indexes on the said table.
Having said that, it needs to be mentioned here that after a table reaches a certain size threshold (environment-specific but usually in 10s of Gb), it is almost processor/computation prohibitive to do "computes". While 8.0 and above does allow "parallel analyzes" by the use of the dbms_utility. analyze_part_object procedure, the sheer cost of performing a compute is sometimes infeasible.
For most environments "estimates with sample sizes of 16% or above have been known to be statistically adequate". The statistical confidence interval for a 16% sample-size analyze is between 83-91%. I have used a sample size of 20% across the board for the past 5 years and it worked for me. Depending on the degree of skewness in your data, your mileage may vary. But at least you have a number to start with.
If the usual sort-related parameters have been tuned, it is relevant to mention here that the number of tables that are analyzed at a given time, is going to have a direct impact on the size of the temporary tablespace.
Best Regards,
Gaja.
Gaja Krishna Vaidyanatha | 3460 West Bayshore Road, Manager - Integration | Palo Alto, CA 94303 & Consulting Services | gaja_at_brio.com Global Alliances | (650)-565-4442 Brio Technology | www.brio.com
"Opinions and views expressed are my own and not of Brio Technology"
-- Author: Gaja Krishna Vaidyanatha INET: gajav_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01BFD553.B7698330 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: TEMP tablespace size in a Data Warehouse</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>Isn't it true that in 8i it if you use estimate = Oracle get specified percentage of data randomly and before 8i it took = specified percentage of data from the beginning of the table and = therefore in 8i we can use smoller percentage with more reliable = results?</FONT></P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Gaja Krishna Vaidyanatha [<A = HREF=3D"mailto:gajav_at_yahoo.com">mailto:gajav_at_yahoo.com</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Tuesday, June 13, 2000 12:38 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: Re: TEMP tablespace size in a Data = Warehouse</FONT> </P> <BR> <P><FONT SIZE=3D2>Charlie,</FONT> </P> <P><FONT SIZE=3D2>On a 1 TB implementation, we used the "rule of = thumb" of (1.5 *</FONT> <BR><FONT SIZE=3D2>size of the largest table) as required temporary = tablespace. </FONT> <BR><FONT SIZE=3D2>The database release was in 7.3.4 and 8.0.5. = This sizing was</FONT> <BR><FONT SIZE=3D2>done keeping in mind, not only the need for temp = segments during</FONT> <BR><FONT SIZE=3D2>application sorts and index creations, but also for = the</FONT> <BR><FONT SIZE=3D2>potential need to perform a "full analyze" = on the table with the</FONT> <BR><FONT SIZE=3D2>"compute statistics" option. You are = aware that a table analyze</FONT> <BR><FONT SIZE=3D2>automatically induces analyze of all the relevant = indexes on the</FONT> <BR><FONT SIZE=3D2>said table.</FONT> </P> <P><FONT SIZE=3D2>Having said that, it needs to be mentioned here that = after a</FONT> <BR><FONT SIZE=3D2>table reaches a certain size threshold = (environment-specific but</FONT> <BR><FONT SIZE=3D2>usually in 10s of Gb), it is almost = processor/computation</FONT> <BR><FONT SIZE=3D2>prohibitive to do "computes". While = 8.0 and above does allow</FONT> <BR><FONT SIZE=3D2>"parallel analyzes" by the use of the = dbms_utility.</FONT> <BR><FONT SIZE=3D2>analyze_part_object procedure, the sheer cost of = performing a</FONT> <BR><FONT SIZE=3D2>compute is sometimes infeasible.</FONT> </P> <P><FONT SIZE=3D2>For most environments "estimates with sample = sizes of 16% or</FONT> <BR><FONT SIZE=3D2>above have been known to be statistically = adequate". The</FONT> <BR><FONT SIZE=3D2>statistical confidence interval for a 16% = sample-size analyze is</FONT> <BR><FONT SIZE=3D2>between 83-91%. I have used a sample size of = 20% across the</FONT> <BR><FONT SIZE=3D2>board for the past 5 years and it worked for = me. Depending on</FONT> <BR><FONT SIZE=3D2>the degree of skewness in your data, your mileage = may vary. But</FONT> <BR><FONT SIZE=3D2>at least you have a number to start with.</FONT> </P> <P><FONT SIZE=3D2>If the usual sort-related parameters have been tuned, = it is</FONT> <BR><FONT SIZE=3D2>relevant to mention here that the number of tables = that are</FONT> <BR><FONT SIZE=3D2>analyzed at a given time, is going to have a direct = impact on</FONT> <BR><FONT SIZE=3D2>the size of the temporary tablespace.</FONT> </P> <P><FONT SIZE=3D2>Best Regards,</FONT> </P> <P><FONT SIZE=3D2>Gaja.</FONT> </P> <P><FONT SIZE=3D2>--- Charlie Mengler <charliem_at_mwh.com> = wrote:</FONT> <BR><FONT SIZE=3D2>> Is there any "rule of thumb" that can = be used to guesstimate</FONT> <BR><FONT SIZE=3D2>> the appropriate size</FONT> <BR><FONT SIZE=3D2>> of the TEMP tablespace in a data warehouse = instance? For</FONT> <BR><FONT SIZE=3D2>> example </FONT> <BR><FONT SIZE=3D2>> XXX% of total size or </FONT> <BR><FONT SIZE=3D2>> TEMP should be 1.YY * the size of the largest = table or </FONT> <BR><FONT SIZE=3D2>> TEMP should be 1.ZZ * the size of the largest = index or </FONT> <BR><FONT SIZE=3D2>> ??????</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Along these lines what can be done to minimize = or reduce the</FONT> <BR><FONT SIZE=3D2>> amount of TEMP </FONT> <BR><FONT SIZE=3D2>> that is needed & what are the trade-offs = involved. (This is</FONT> <BR><FONT SIZE=3D2>> beside the "obvious"</FONT> <BR><FONT SIZE=3D2>> response WRT to sorts & Sort Area = Size.)</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -- </FONT> <BR><FONT SIZE=3D2>> Charlie = Mengler  = ;  = ; Maintenance</FONT> <BR><FONT SIZE=3D2>> Warehouse </FONT> <BR><FONT SIZE=3D2>> = charliem_at_mwh.com &n= bsp; &n= bsp; 5505 Morehouse</FONT> <BR><FONT SIZE=3D2>> Drive </FONT> <BR><FONT SIZE=3D2>> = 858-552-6229 = = San = Diego, CA</FONT> <BR><FONT SIZE=3D2>> 92121 </FONT> <BR><FONT SIZE=3D2>> Always be sincere about your enthusiasm, = whether you mean it</FONT> <BR><FONT SIZE=3D2>> or not.</FONT> <BR><FONT SIZE=3D2>> -- </FONT> <BR><FONT SIZE=3D2>> Author: Charlie Mengler</FONT> <BR><FONT SIZE=3D2>> INET: charliem_at_mwh.com</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Fat City Network Services -- = (858) 538-5051 FAX: (858)</FONT> <BR><FONT SIZE=3D2>> 538-5051</FONT> <BR><FONT SIZE=3D2>> San Diego, = California -- Public Internet = access /</FONT> <BR><FONT SIZE=3D2>> Mailing Lists</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>> To REMOVE yourself from this mailing list, send = an E-Mail</FONT> <BR><FONT SIZE=3D2>> message</FONT> <BR><FONT SIZE=3D2>> to: ListGuru_at_fatcity.com (note EXACT spelling = of 'ListGuru')</FONT> <BR><FONT SIZE=3D2>> and in</FONT> <BR><FONT SIZE=3D2>> the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>> (or the name of mailing list you want to be = removed from). </FONT> <BR><FONT SIZE=3D2>> You may</FONT> <BR><FONT SIZE=3D2>> also send the HELP command for other = information (like</FONT> <BR><FONT SIZE=3D2>subscribing).</FONT> </P> <BR> <P><FONT SIZE=3D2>=3D=3D=3D=3D=3D</FONT> <BR><FONT SIZE=3D2>Gaja Krishna Vaidyanatha | 3460 West = Bayshore Road,</FONT> <BR><FONT SIZE=3D2>Manager - Integration = | Palo Alto, CA 94303</FONT> <BR><FONT SIZE=3D2>& Consulting = Services | gaja_at_brio.com</FONT> <BR><FONT SIZE=3D2>Global = Alliances | = (650)-565-4442</FONT> <BR><FONT SIZE=3D2>Brio = Technology &n= bsp; | www.brio.com </FONT> </P> <P><FONT SIZE=3D2>"Opinions and views expressed are my own and not = of Brio Technology"</FONT> </P> <P><FONT = SIZE=3D2>__________________________________________________</FONT> <BR><FONT SIZE=3D2>Do You Yahoo!?</FONT> <BR><FONT SIZE=3D2>Yahoo! Photos -- now, 100 FREE prints!</FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://photos.yahoo.com" = TARGET=3D"_blank">http://photos.yahoo.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Gaja Krishna Vaidyanatha</FONT> <BR><FONT SIZE=3D2> INET: gajav_at_yahoo.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =Received on Tue Jun 13 2000 - 11:23:32 CDT
 |
 |