Home » RDBMS Server » Performance Tuning » [11g]Direct-path insert / temp usage / PGA (Oracle 11gr2 / linux 2.6 x86_64)
[11g]Direct-path insert / temp usage / PGA [message #569637] Tue, 30 October 2012 09:58 Go to next message
nikko
Messages: 8
Registered: September 2010
Junior Member
Hi all.


I encounter weird behavior when I use direct-path insert for inserting data from partitioned table(range/interval) into a new table created as follows :

CREATE TABLE tdest
partition by range(DATE_RECORDED) INTERVAL(NUMTODSINTERVAL(1, 'DAY')) store in (HHH_X1)
(
partition p1 values less than (TO_DATE('20000103', 'YYYYMMDD'))
)
AS SELECT * from tsource  where 3=2;



When i use conventional path, ie :

insert into tdest select * from tsource;


,all is OK, the insert succeeds.

When i use direct path , ie
 insert /*+ APPEND */  into tdest select * from tsource;


,I got an ora-01652 : ORA-01652: unable to extend temp segment by 128 in tablespace TMP

(For information, TMP tablespace is 5G and parallelism is desactivated on database)

When I raise pga_aggregate_target from 128m to 1g, direct path insert does not use TMP tbs and succeeds. Shocked

I m totally confused with this behavior.

I googled and searched on metalink too for 2 days but got no answer. Sad

Can somebody give some clues to investigate or explain this behaviour?

Thanks in advance.

Regards
nikko.

Re: [11g]Direct-path insert / temp usage / PGA [message #569639 is a reply to message #569637] Tue, 30 October 2012 10:23 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
Hi - I can think of a possibility.
You say you are using 11gR2, the exact release might be important, but usually with 11.2.x you will find that by default the _serial_direct_read parameter is set to AUTO. This means that Oracle might decide to make direct reads of tsource into PGA. If it is also having to assemble blocks in the PGA for a direct load into tdest, well, it is going to need a lot of PGA! And I guess it might be spilling to a temp segment, possibly a very big one. When doing a conventional load, it might still decide to do a direct read, but at least the rows are being written to buffers in the SGA, not in the PGA.
A PGA aggregate target of 128M is very small for these sort of operations.
Can you try an experiment?
alter session set "_serial_direct_read"=never;

and try again.
Another possibility,
alter session set workarea_size_policy=manual;
alter session set sort_area_size=100m;
alter session set hash_area_size=100m;

I'm just guessing at figures, but I'm sure you can see what I'm trying to test.
Finally, can you query v$sql_workarea to see what the estimated_optimal_size and estimated_onepass_size is for the two statements?
Re: [11g]Direct-path insert / temp usage / PGA [message #569641 is a reply to message #569639] Tue, 30 October 2012 10:42 Go to previous messageGo to next message
nikko
Messages: 8
Registered: September 2010
Junior Member
hello John

setting "_serial_direct_read"=never did not solve my issue.
I agree that 128M is a small value.
but i want to understand this behavior that appears weird to me.

Here is the result of v$sql_workarea when insert fails:

SQL> /

ADDRESS 	 HASH_VALUE SQL_ID	  CHILD_NUMBER WORKAREA_ADDRESS OPERATION_TYPE	     OPERATION_ID POLICY     ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_DEGREE TOTAL_EXECUTIONS OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS ACTIVE_TIME
---------------- ---------- ------------- ------------ ---------------- -------------------- ------------ ---------- ---------------------- ---------------------- ---------------- ---------- ----------- ---------------- ------------------ ------------------ ---------------------- -----------
MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
---------------- -----------------
00000000765390A8   63728396 1f7adfs1wsusc	     0 0000000071D8AB90 LOAD WRITE BUFFERS		1 AUTO			     262144		    262144	     541696 OPTIMAL		 1	  1		     1			0		       0   112969378




I keep you informed for the "workarea_size_policy=manual" test.

Regards
Re: [11g]Direct-path insert / temp usage / PGA [message #569680 is a reply to message #569641] Wed, 31 October 2012 04:19 Go to previous message
nikko
Messages: 8
Registered: September 2010
Junior Member
hi all.

I try the 2nd workaround proposed by John (workarea_size_policy=manual, ..... ) , but it fails too.

anyone have other suggestions?

Regards

nikko
Previous Topic: How tune the following query?
Next Topic: Merge Join Cartesian in Outline Data
Goto Forum:
  


Current Time: Wed Oct 01 01:52:34 CDT 2014

Total time taken to generate the page: 0.21047 seconds