You can also manually compile procedures, functions, and
packages to avoid run-time compilation. When you create a procedure, Oracle automatically
compiles it. If the procedure later becomes invalid, the database must recompile it before
executing it. To avoid incurring this compilation cost at run time, use the alter procedure
command shown here:
alter procedure MY_RAISE compile;
You can view the SQL text for all procedures in a database via the Text column in the DBA_
SOURCE view. The USER_SOURCE view will display the procedures owned by the user performing
the query. Text for packages, functions, and package bodies is also accessible via the DBA_SOURCE
and USER_SOURCE views, which in turn reference a table named SYS.SOURCE$.
The first two design guidelines discussed??”limiting the number of user accesses and coordinating
their requests??”require the application developer to know as much as possible about how the
data is to be used and the access paths involved. For this reason, it is critical that users be as
involved in the application design as they are in the table design. If the users spend long hours
drawing pictures of tables with the data modelers and little time with the application developers
discussing the access paths, the application will most likely not meet the users??™ needs. The access
paths should be discussed as part of the data modeling exercise.
Chapter 8: Database Tuning 247
Tuning SQL
As with application design, the tuning of SQL statements seems far removed from a DBA??™s duties.
Pages:
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414