OraFAQ Forum: Server Administration » change in TEMP tablespace management on 10g

Home » RDBMS Server » Server Administration » change in TEMP tablespace management on 10g

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

change in TEMP tablespace management on 10g [message #140439]

Tue, 04 October 2005 04:29

navaHo
Messages: 6
Registered: October 2005
Location: Moscow, Russia

Junior Member

I've installed Oracle 10g Enterprise Edition and transferred my data from previous installation of Oracle 9. Everything works almost fine except TEMP segment. It grows greatly and now it is bigger then the combined size of DATA tablespace and INDEX tablespace together. I know that it is normal for TEMPORARY tablespace to grow and not to free occupied space. But now I wonder - is it normal for TEMP tablespace to be so big? Under the same circumstances (with the same data) 9i generated much smaller TEMP segment. So my questions are:

This is normal behavior for Oracle 10g?
May it be somehow connected with changes of optimizer in 10g?

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140466 is a reply to message #140439]

Tue, 04 October 2005 06:58

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Perhaps you had autoextend on and one lone operation caused the temp to grow more than normal? You can create a new smaller one and leave autoextend off to keep it smaller, but of course that might cause problems. Perhaps your new server has less RAM allocated to oracle and it causes more temp file space to be used?

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140683 is a reply to message #140439]

Wed, 05 October 2005 03:51

navaHo
Messages: 6
Registered: October 2005
Location: Moscow, Russia

Junior Member

We adjusted initialization parameters so that
pga size=100М for 10 users
sga_max_size=1.5G
, decreased again TEMP segment size back to 1G, autoextend is on

After a little workload on server - again TEMP tablespace became 7G

some stats are:

select * from v$system_event where event in ('direct path write temp',
'direct path read temp' )

gives

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
1 direct path read temp 116605 0 28 0 278048 861319509 1740759767 8 User I/O
2 direct path write temp 243368 0 289 0 2889618 38438084 1740759767 8 User I/O

It doesn't seems to be a problem of RAM amount. The question again: what could lead to such an intense TEMP usage?

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140696 is a reply to message #140683]

Wed, 05 October 2005 04:54

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Do you have pga_aggregate_target set? It could be (partially) caused by sorting.
Check out v$sysstat:

select * from v$sysstat where name like '%sort%';

To see if you are doing a large amount of disk sorts.

HTH
Jim

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140721 is a reply to message #140439]

Wed, 05 October 2005 06:25

navaHo
Messages: 6
Registered: October 2005
Location: Moscow, Russia

Junior Member

pga_aggregate_target=104857600 (from v$parameter) - so it is 100M

select * from v$sysstat where name like '%sort%';

returns

STATISTIC# NAME CLASS VALUE STAT_ID
1 341 sorts (memory) 64 44099 2091983730
2 342 sorts (disk) 64 11 2533123502
3 343 sorts (rows) 64 70801930 3757672740

11 disk sorts - is not a large amount comparing to 44099 of memory sorts.

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140728 is a reply to message #140721]

Wed, 05 October 2005 07:22

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Yeah, but those 11 sorts (or even just one of them) might be massive Smile

Have a look at the v$sort_segment and see how much space is being used for sorts.

Jim

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140735 is a reply to message #140439]

Wed, 05 October 2005 07:32

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Out of curiosity, how much RAM on your old server did you have allocated to sort area size?

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140760 is a reply to message #140439]

Wed, 05 October 2005 09:09

navaHo
Messages: 6
Registered: October 2005
Location: Moscow, Russia

Junior Member

select * from v$sort_segment

shows 1 row

TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO
1 NC_TEMP 0 0 128 0 9999 1279872 0 0 9999 1279872 9999 50928 9999 1 9999 1279872 9999 1279872 9999 1279872 0
i.e. all 10G of TEMP tablespace are full of one huge sort segment

on my old 9.1 server sort_area_size=2097152 but it doesn't matter because both old 9.1 server and 10g have workarea_size_policy set to AUTO

Here I compare:

10g workarea_size_policy=AUTO
pga_aggregate_target=104857600 (100M)
sort_area_size=65536 (bytes)
9.1 workarea_size_policy=AUTO
pga_aggregate_target=157286400 (150M)
sort_area_size=2097152 (bytes)

but even more - I played with pga_aggregate_target on 9.1 and it never resulted in such a strange TEMP TABLESPACE behavior

This seems like some bug in new Orace - did anyboady ever ancounter something like this?

My Oracle instance version is 10.2.0.1.0

[Updated on: Wed, 05 October 2005 09:24]

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140769 is a reply to message #140760]

Wed, 05 October 2005 09:35

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

From the data that you have posted (max_sort_blocks) is showing that you have a single sort operation using 1279872 blocks (nearly 10G I'm guessing at an 8k blocksize) find that operation, that's where the issue is.
Jim

[Updated on: Wed, 05 October 2005 09:36]

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140974 is a reply to message #140769]

Thu, 06 October 2005 08:02

navaHo
Messages: 6
Registered: October 2005
Location: Moscow, Russia

Junior Member

Finally I solved this problem!

Thanks to everyone for attention.

Of coarse it was a query with a stupid execution plan.

I will not provide the real query and it's plan because they are rather big, but the problem in core is that Oracle 10 optimizer by default generates very bad execution plans for hierarchical queries.
I solved the problem by setting hidden parameter _old_connect_by_enabled = true

For example when _old_connect_by_enabled = false (this is the default in 10i!) the simple hierarchical query like:
select * from htable start with id=101 connect by prior parent_id=id

generates execution plan

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=3	Cardinality=1	Bytes=77
 CONNECT BY WITH FILTERING					
  TABLE ACCESS BY INDEX ROWID	Object owner=VERSION	Object name=NC_OBJECTS			
   INDEX UNIQUE SCAN	Object owner=VERSION	Object name=XPKNC_OBJECTS	Cost=2	Cardinality=1	Bytes=12
  NESTED LOOPS					
   BUFFER SORT					
    CONNECT BY PUMP					
   TABLE ACCESS BY INDEX ROWID	Object owner=VERSION	Object name=NC_OBJECTS	Cost=3	Cardinality=1	Bytes=77
    INDEX UNIQUE SCAN	Object owner=VERSION	Object name=XPKNC_OBJECTS	Cost=2	Cardinality=1	
  TABLE ACCESS FULL	Object owner=VERSION	Object name=NC_OBJECTS	Cost=3	Cardinality=1	Bytes=77

with _old_connect_by_enabled = true for the same query E.P. is:

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=3	Cardinality=1	Bytes=77
 CONNECT BY					
  INDEX UNIQUE SCAN	Object owner=VERSION	Object name=XPKNC_OBJECTS	Cost=2	Cardinality=1	Bytes=12
  TABLE ACCESS BY USER ROWID	Object owner=VERSION	Object name=NC_OBJECTS			
  TABLE ACCESS BY INDEX ROWID	Object owner=VERSION	Object name=NC_OBJECTS	Cost=3	Cardinality=1	Bytes=77
   INDEX UNIQUE SCAN	Object owner=VERSION	Object name=XPKNC_OBJECTS	Cost=2	Cardinality=1

do you feel the difference?! Needless to say that some more complicated query can result in Execution Plan which will require huge TEMP segment for unnecessary sorts!

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140978 is a reply to message #140974]

Thu, 06 October 2005 08:09

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Thanks for posting your solution NavaHo. I'll need to keep an eye out for that on heirarchical queries.
Jim

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140993 is a reply to message #140439]

Thu, 06 October 2005 08:56

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

When you say the plan "by default", what do you mean? Statistics?

Report message to a moderator

Re: change in TEMP tablespace management on 10g [message #140994 is a reply to message #140993]

Thu, 06 October 2005 09:10

navaHo
Messages: 6
Registered: October 2005
Location: Moscow, Russia

Junior Member

By saying "by deafult" I mean cost based optimizer and also default value of _old_connect_by_enabled which is false

Report message to a moderator

Previous Topic:	TEMP Tablespace Space
Next Topic:	make oracle available for other clients

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 08:00:58 CST 2025