Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.vmunix.org!newsfeed01.sul.t-online.de!t-online.de!newsfeed.arcor.de!news.arcor.de!not-for-mail Date: Wed, 14 Sep 2005 14:32:59 +0200 From: Maxim Demenko Reply-To: mdemenko@gmail.com User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: de-DE, de, en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: TEMP tablespace: a big one or 10 small ones? References: <1126686836.529573.152920@z14g2000cwz.googlegroups.com> In-Reply-To: <1126686836.529573.152920@z14g2000cwz.googlegroups.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 102 Message-ID: <43281892$0$27555$9b4e6d93@newsread4.arcor-online.net> Organization: Arcor NNTP-Posting-Date: 14 Sep 2005 14:33:23 MEST NNTP-Posting-Host: ad5162e8.newsread4.arcor-online.net X-Trace: DXC=jnTIld_1Ufda0B5i45NL;d:ejgIfPPlddjW\KbG]kaMhFYk:AnJB[CmD]YCZg;Y>Cfo^QgQFX7Znog;AHh8`5? Hi, > > we have an Oracle 10g database on a HP/UX machine (parallel queries > enabled). Users access the database and perform selects on the tables, > sometimes very large selects. Additionally during monthly production > huge amounts of data need to be put into the database. Currently the > database is not productive, we are only testing... > > I first created a single 30GB temp tablespace for the database and > measured the performance of queries. After that I created 10 > 3-GB-temp-tablespaces and assigned them to a tablespace group, and > assigned the tablespace group as a temporary tablespace to the user > executing the queries. The performance then was slightly better. > > Now my question is: If there is a huge query during monthly production > which - say - would need 20 GB of temp space in order to do some > sorting and is forced to use only a single processor, can Oracle then > use several of the small tablespaces to suffice the space need of 20GB > or does the query break due to non sufficient temp tablespace? > > Best regards, > Alex > In addition to infos posted here, you can always set up small test, to get your answer. scott@ORA102> create temporary tablespace temp_1 tempfile '?/oradata/@/temp_1.dbf' size 5M tablespace group group1; Tablespace created. scott@ORA102> create temporary tablespace temp_2 tempfile '?/oradata/@/temp_2.dbf' size 5M tablespace group group1; Tablespace created. scott@ORA102> create temporary tablespace temp_3 tempfile '?/oradata/@/temp_3.dbf' size 5M tablespace group group2; Tablespace created. scott@ORA102> create temporary tablespace temp_4 tempfile '?/oradata/@/temp_4.dbf' size 5M tablespace group group2; Tablespace created. scott@ORA102> alter user scott temporary tablespace group1; User altered. scott@ORA102> select tablespace_name,max_used_size from v$sort_segment; TABLESPACE_NAME MAX_USED_SIZE --------------- ------------- TEMP 1 scott@ORA102> select count(*) from (select 'x' from all_objects,all_objects,all_objects,all_objects); select count(*) from (select 'x' from all_objects,all_objects,all_objects,all_objects) * ERROR at line 1: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_2 scott@ORA102> select tablespace_name,max_used_size from v$sort_segment; TABLESPACE_NAME MAX_USED_SIZE --------------- ------------- TEMP 1 TEMP_2 4 scott@ORA102> alter user scott temporary tablespace group2; User altered. scott@ORA102> select /*+ parallel(t 4) */ count(*) from (select 'x' from all_objects,all_objects,all_objects,all_objects) t; select /*+ parallel(t 4) */ count(*) from (select 'x' from all_objects,all_objects,all_objects,all_objects) t * ERROR at line 1: ORA-12801: error signaled in parallel query server P000 ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_3 scott@ORA102> select tablespace_name,max_used_size from v$sort_segment; TABLESPACE_NAME MAX_USED_SIZE --------------- ------------- TEMP 1 TEMP_2 4 TEMP_3 4 TEMP_4 4 scott@ORA102> Best regards Maxim