If 30 seconds have passed, exit the loop;
Otherwise repeat the loop, calculating SysDate again.
end loop
Perform Step 2.
126 Oracle Database 11g DBA Handbook
Is this a reasonable approach? Absolutely not! It will do what the developer wanted, but at a
significant cost to the application. What??™s more, there is nothing a database administrator can do
to improve its performance. In this case, the cost will not be due to I/O activity??”the DUAL table
will stay in the instance??™s memory area??”but rather due to CPU activity. Every time this program
is run, by every user, the database will spend 30 seconds consuming as many CPU resources as
the system can support. In this particular case the select SysDate from DUAL query accounts
for over 40 percent of all the CPU time used by the application. All of that CPU time is wasted.
Tuning the database initialization parameters will not solve the problem. Tuning the individual
SQL statement will not help; the application design must be revised to eliminate the needless
execution of commands. For instance, in this case the developer could have used a sleep command
at the operating system level or within a PL/SQL program using the DBMS_LOCK.SLEEP() procedure
to enforce the same behavior without the database accesses.
For those who favor tuning based on the buffer cache hit ratio, this database has a hit ratio of
almost 100 percent due to the high number of completely unnecessary logical reads without related
physical reads.
Pages:
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229