Hi I am using 11.2.0.4.0 - Version of oracle. i am having below paremters on GV$parameter
pga_aggregate_target- 8GB
hash_area_size- 128KB
Sort_area_size- 64KB
Now below query plan is running for ~1hr and resulting into tempspace issue. unable to extend temp segment by 128 in tablespace TEMP .
We have currently allotted ~200GB to the tempspace. This query is running good for daily run using Nested loop and required indexes , but for monthly run it changes the plan due to the volume i believe and went for HASH join, which believe is good decision by the optimizer.
as i know, hash join spilling to temp is going to slow down the query responce time, so i need expert advice on, if we should increase the pga_aggregate_target so that HASH_AREA_SIZE will be increased to adequate to accommadate the driving table into this? and howmuch size should we allocate, should it be same as size of driving table? or is there any other work around for same? Note- Size of the driving table B is '~400GB'.
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 10M(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | FILTER | | | | | | | | |
| 3 | HASH JOIN | | 8223K| 1811M| | 10M (1)| 35:30:55 | | |
| 4 | TABLE ACCESS STORAGE FULL | A_GT | 82 | 492 | | 2 (0)| 00:00:01 | | |
| 5 | HASH JOIN | | 8223K| 1764M| 737M| 10M (1)| 35:30:55 | | |
| 6 | PARTITION RANGE ITERATOR | | 8223K| 643M| | 10M (1)| 34:18:55 | KEY | KEY |
| 7 | TABLE ACCESS STORAGE FULL| B | 8223K| 643M| | 10M (1)| 34:18:55 | KEY | KEY |
| 8 | TABLE ACCESS STORAGE FULL | C_GT | 27M| 3801M| | 118K (1)| 00:23:48 | | |
-----------------------------------------------------------------------------------------------------------------------------------