Re: TEMP tablespace: a big one or 10 small ones?

schonlinner_at_yahoo.com schrieb:
> 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_at_ORA102> create temporary tablespace temp_1 tempfile '?/oradata/@/temp_1.dbf' size 5M tablespace group group1;

Tablespace created.

scott_at_ORA102> create temporary tablespace temp_2 tempfile '?/oradata/@/temp_2.dbf' size 5M tablespace group group1;

Tablespace created.

scott_at_ORA102> create temporary tablespace temp_3 tempfile '?/oradata/@/temp_3.dbf' size 5M tablespace group group2;

Tablespace created.

scott_at_ORA102> create temporary tablespace temp_4 tempfile '?/oradata/@/temp_4.dbf' size 5M tablespace group group2;

Tablespace created.

scott_at_ORA102> alter user scott temporary tablespace group1;

User altered.

scott_at_ORA102> select tablespace_name,max_used_size from v$sort_segment;

TABLESPACE_NAME MAX_USED_SIZE
--------------- -------------

TEMP                        1

scott_at_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_at_ORA102> select tablespace_name,max_used_size from v$sort_segment;

TABLESPACE_NAME MAX_USED_SIZE
--------------- -------------

TEMP                        1
TEMP_2                      4

scott_at_ORA102> alter user scott temporary tablespace group2;

User altered.

scott_at_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_at_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_at_ORA102>

Best regards

Maxim Received on Wed Sep 14 2005 - 07:32:59 CDT