Urgent Issue on temporary tablespace

[dba_akram]

Hi All,
One of my developers was trying to run his stored proc from the backend and the stored proc was just taking a lot of time and just gang indefinitely. Using OEM I found that this particular session from the developer was running a hash join and categorised it as Long Operation. The same stored procedure runs excellent in one test environment as well as production. This happens only in the development environment. The error I got is
ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
ORA-06512: at "IDBOWNER.SOPVCCONFIG", line 185
ORA-06512: at line 1

I then thought of increasing hash_multiblock_io_count and increased it with the statement
alter system set hash_multiblock_io_count = 1;

But it again bombed with
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP
ORA-06512: at "IDBOWNER.SOPVCCONFIG", line 185
ORA-06512: at line 1

I do have enough free space on my tablespace 3 named temp.
Can any one of you here please tell me how to go about this situation. Can you also provide me with literature on practical aspects of tuning. I tried to read the documentation but didnot make much use for me. Please advice or comment or suggest.

[sjadhavdba]

trace your procedure, explain plan your queries, see whether proper indexes are created. otherwise increase the size of temp tablespace

[ovidius]

Maybe the question looks stupid, but I'll ask it:
Are you sure tablespace 3 is the temp tablespace, not the UNDO tablespace?


[Edited by ovidius on 04-05-2002 at 07:47 AM]

[dba_akram]

I can bet on it. Below is the output of the query select * from v$tablespace; is

TS# NAME
---------- ------------------------------
0 SYSTEM
6 IDB
2 RBS
3 TEMP
5 AUD
4 INDX

6 rows selected.

Does it make sense atleast by now.

[ovidius]

So, the two errors was on the same tablespace, TEMP.
Next question:
What do you need, to solve the problem, to understand what happens(, or both)?
I think incresing the tablespace will solve the problem punctually. But if this even happens, never knows when it will occur again, neither why.

Hey, guys, a helping hand - brain - is needed here, gurus... I'm not able to think and realise theoretically why is this happening... Sorry!

[thanigai]

Hi guys I too got the similar problem in my tempfile research.
Well I solved it by increasing the space in temp tablespace.
Well,Your tablespace temp is a temporary tablespace or the tablespace used for temporary.Hope you have understood the
diff between temporary tablespace and tablespace temporary.
It was one of my research in tempfile concept.Increase the size it will solve your problem.
Thanigaivasan.

[dba_akram]

I have my temp tablespace sized to 1Gb. The instance is running on solaris 2.6 on ultra sparc, oracle 8.1.6. Iam still getting the same error even after getting increasing the size of the temp tablespace. I think the moderator's hand is needed in this. Can anyone of you throw some light on this!!!!!
ERROR at line 1:
ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
ORA-06512: at "APPOWNER.PROCNAME", line 185
ORA-06512: at line 1

[julian]

Originally posted by dba_akram
I have my temp tablespace sized to 1Gb. The instance is running on solaris 2.6 on ultra sparc, oracle 8.1.6. Iam still getting the same error even after getting increasing the size of the temp tablespace. I think the moderator's hand is needed in this. Can anyone of you throw some light on this!!!!!
ERROR at line 1:
ORA-03232: unable to allocate an extent of 58 blocks from tablespace 3
ORA-06512: at "APPOWNER.PROCNAME", line 185
ORA-06512: at line 1

Do one of the following, your choice:

1. Decrease the value of HASH_MULTIBLOCK_IO_COUNT. What is it set to now?

2. Increase NEXT for the TEMP tablespace. Run:

ALTER TABLESPACE TEMP DEFAULT STORAGE(NEXT ...);

MTS or not?

[dba_akram]

The current setting for hash_multiblock io_count is 0.
SQL> show parameter hash

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
hash_area_size integer 6291456
hash_join_enabled boolean TRUE
hash_multiblock_io_count integer 0

The following query gives you more info:
SQL> select initial_extent, next_extent, min_extents, max_extents, pct_increase,
2 min_extlen, status
3 from dba_tablespaces where tablespace_name='TEMP';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS
-------------- ----------- ----------- ----------- ------------ ---------- ---------
262144 262144 2 5 262144 ONLINE

This satisfies the condition already that NEXT > hash_multiblock_io_count
I think it is MTS. Can you tell me how to confirm it?

[julian]

Oracle computes the value of hash_multiblock io_count for every query. If Oracle does the automatic computation of hash_multiblock io_count , then the value is 0 in V$PARAMETER.

I suggest you recreate the TEMP tablespace with a bit bigger INITIAL and NEXT. Drop TEMP and run:

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/datadg/disk22/temp_2.dbf' 
SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

so that you replace '/datadg/disk22/temp_2.dbf' with your own directory.