Do you realize that this query is going to run for each row in your main cursor? At best you have a range scan, otherwise you are doing a full table scan with this query for each record returned from your main query, not to mention the other inefficiencies that exist in you query, like doing a select count(*) when you don't need to.
Originally posted by dbasupuser
The point is I am not pushing this table or any other table/data out of the default memory pool. If I keep this table in it's own memory it is not going to interfere with any other process. Other side of it is if I have this huge memory pool will there be any oracle back ground process/resource used up to maintain sync up the table. Also how many latches etc I need to assign for this pool...I guess that's just an trail/error method since there is no thumb rule regarding how many latches to use based on the size of the memory pool.