Although similar to a larger shared pool, a larger PGA avoids memory
churn within the private SQL areas. Reduced private SQL area reuse, in turn, reduces
the associated CPU consumption, increasing performance. There is, of course, a
trade-off between allocating memory in the PGA for SQL and overall performance.
OLTP systems typically have a ???working set??? of SQL statements that each user submits.
For example, a user who enters car rental reservations uses the same forms in
the application repeatedly. Performance will be improved if the user??™s server process
has enough memory in the PGA to cache the SQL those forms issue. Application
developers should also take care to write their SQL statements so that they can be
easily reused, by specifying bind variables instead of different hardcoded values in
their SQL statements.
Oracle and Memory Resources | 181
Memory for sorting within the PGA
Each server process uses memory in its PGA for sorting rows before returning them
to the user. If the memory allocated for sorting is insufficient to hold all the rows that
need to be sorted, the server process sorts the rows in multiple passes called runs.
Pages:
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404