The optimizer can maintain the history of execution
plans, and when a new plan is detected, the optimizer uses the old plan and evaluates
the performance of the new plan. Once the optimizer verifies that the new
plan can deliver the same performance, the old plan is replaced. This feature
does not directly relate to bad SQL, but rather to the occasional effects of plan
changes, which can result in unplanned performance degradation.
Excessive parsing
As we discussed in the section ???Memory for SQL statements,??? Oracle must parse
every SQL statement before it??™s processed. Parsing is very CPU-intensive, involving
a lot of data dictionary lookups to check that all the tables and columns
referenced are valid. Complex algorithms and calculations estimate the costs of
the various optimizer plans possible for the statement to select the optimal plan.
If your application isn??™t using bind variables (discussed in Chapter 9), the
database will have to parse every statement it receives. This excessive and unnecessary
parsing is one of the leading causes of performance degradation.
Pages:
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410