|
-
Hash join builds hash tables in memory (...and disk) which is a collection of hash buckets or partitions. The number of buckets in memory used to be dictated by hash_area_size but with the introduction of pga_aggregate_target, oracle automatically controls how much of hash memory is allocated within a session's work area. In a idle system, it can be generous so that it can accomodate more hash partitions but oracle always chooses the smaller of the tables or resultsets to build the hash partitions in memory and also ensures that only the smaller buckets are kept in memory while the rest are in the temp area on disks.
And so yes, if oracle through its automatic allocation allocated large hash area for the session then your process size can grow since there might potentially be more hash buckets in memory. Based on the limited information from the explain plan output from autotrace it looks like oracle's building hash tables on these:
(1) cameragroup (33 rows)
(2) Result rowset of cameragroup and sys_c004035 (237 rows)
(3) result rowset of join between camera and (2) above (237 rows)
(4) result rowset of join between bcapture and (3) above (365221 rows)
I would be curious to see as to what the behaviour of the query (execution plan) is with workarea_size_policy disabled for your session and hash_area_size set to a very low number (100 or 200K).
Coming to your query below, with the amount of hash joins and full scans involved, what surprises me is the fact that I don't see any parallel query processes being used. Are they not enabled or did the database have the max parallel query processes already in use when you ran the query?
Also at a high level, what are the columns within bcapture and hotlistmatch tables that are defined as not null and have unique values in them? Are there indexes on those columns and are those columns being used in the query?
.....num_rows and blocks values for the tables involved in the query and an output from the explain plan using dbms_xplan.display would also help.
Good luck......
http://www.dbaxchange.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|