Optimizers and optimization plans are discussed in Chapter 4.
The private SQL area contains the session-specific information about the execution
of the SQL statement within the session, such as the number of rows retrieved so far.
Once a SQL statement has been processed, its private SQL area can be reused by
another SQL statement. If the application reissues the SQL statement whose private
SQL area was reused, the private SQL area will have to be reinitialized.
Each time a new SQL statement is received, its shared SQL area must be located (or,
if not located, loaded) in the shared pool. Similarly, the SQL statement??™s private SQL
area must be located in the PGA or, if it isn??™t located, reinitialized by the server process.
This reinitialization is relatively expensive in terms of CPU resources.
A server process with a PGA that can contain a higher number of distinct private
SQL areas will spend less time reinitializing private SQL areas for incoming SQL
statements. If the server process doesn??™t have to reuse an existing private SQL area to
accommodate a new statement, the private SQL area for the original statement can
be kept intact.
Pages:
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403