Cursor sharing enhancements in the optimizer increase the likelihood of statement reuse
within the shared pool??”but the application needs to be designed with statement reuse in mind.
Third, you should restrict the use of dynamic SQL. Dynamic SQL, by definition, is undefined
until run time; an application??™s dynamic SQL may select a couple of rows the first time, perform
several full table scans of the order table the second time, and inadvertently perform a Cartesian
join the third time (or consciously perform a Cartesian join using the cross join keyword in a
select statement!). In addition, there is no way to guarantee that a dynamically generated SQL
statement is syntactically correct until run time. Dynamically generated SQL is a double-edged
sword: you have the flexibility to create your SQL on the fly based on user input, but you open
yourself up to SQL injection attacks for both your in-house applications and your external website
applications.
Fourth, you should minimize the number of times you open and close sessions in the
database. If the application repeatedly opens a session, executes a small number of commands,
and then closes the session, the performance of the SQL may be a minor factor in the overall
performance. The session management may cost more than any other step in the application.
When stored procedures are used, the same code may be executed multiple times, taking
advantage of the shared pool.
Pages:
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413