| [11g]Direct-path insert / temp usage / PGA [message #569637] |
Tue, 30 October 2012 09:58  |
nikko
Messages: 9
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. 
I m totally confused with this behavior.
I googled and searched on metalink too for 2 days but got no answer. 
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  |
John Watson
Messages: 8960
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 |
nikko
Messages: 9
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
