Hi Experts,
I am working on a new procedure which does everything with single-SQLs, and no PL/SQL loops.
It is a big query and works on 3 major tables - X, X1, X2 - where, X1 is a child of X and X2 is a child of X1.
Rowcount: X = 200K, X1 = 600K, X2 = 1800K
Table X grows at 70K records per day. and X1/X2 accordingly. If required, I can give a blueprint of that query. Let me know.
Now, what i'm concerned is how are the DB parameters sized? Coz. the query is complex and lot of memory might be required to sort/aggregate etc.
Could you please advise, on what basis the below parameters should be sized/tuned ?
| sga_target | 0 | 0 | TRUE | Target size of SGA |
| memory_target | 17179869184 | 16G | FALSE | Target size of Oracle SGA and PGA memory |
| memory_max_target | 34359738368 | 32G | FALSE | Max size for Memory Target |
| bitmap_merge_area_size | 1048576 | 1048576 | TRUE | maximum memory allow for BITMAP MERGE |
| hash_area_size | 131072 | 131072 | TRUE | size of in-memory hash work area |
| sort_area_size | 65536 | 65536 | TRUE | size of in-memory sort work area |
| pga_aggregate_target | 0 | 0 | TRUE | Target size for the aggregate PGA memory consumed by the instance |
| workarea_size_policy | AUTO | AUTO | TRUE | policy used to size SQL working areas (MANUAL/AUTO) |
Thanks and Regards,
-Ranit
( on Oracle 11.2.0.4.0 )