Each of those queries is a separate trip to the
database. As with the example in the previous section, the database is forced to execute large
numbers of related queries. Even if each of those queries is tuned, the burden from the number
of commands??”multiplied by the number of users??”will consume the CPU resources available
on the server. Such a design may also impact the network usage, but the network is seldom the
problem??”the issue is the number of times the database is accessed.
Within your packages and procedures, you should strive to eliminate unnecessary database
accesses. Store commonly needed values in local variables instead of repeatedly querying the
database. If you don??™t need to make a trip to the database for information, don??™t make it. That sounds
simple, but you would be amazed at how often application developers fail to consider this advice.
There is no initialization parameter that can make this change take effect. It is a design issue
and requires the active involvement of developers, designers, DBAs, and application users in the
application performance planning and tuning process.
Chapter 5: Developing and Implementing Applications 127
For Reporting Systems, Store the Data the Way the Users Will Query It
If you know the queries that will be executed??”such as via parameterized reports??”you should
strive to store the data so that Oracle will do as little work as possible to transform the format of
the data in your tables into the format presented to the user.
Pages:
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231