Identify repeated high-load SQL from AWR statistics. Recently tuned SQL and recursive
SQL are ignored.
Chapter 8: Database Tuning 273
2. Tune high-load SQL using calls to the SQL Tuning Advisor.
3. Create SQL Profiles for the high-load SQL; performance is tested both with and without
the profile.
4. If the performance is better by at least a factor of three, automatically keep the profile;
otherwise, note the improvement in the tuning report.
Figure 8-2 shows a summary of the Advisor tasks from Advisor Central; in this example, you
can see a summary of the results for the Automatic Database Diagnostic Monitor (ADDM), the
Segment Advisor, and the SQL Tuning Advisor.
FIGURE 8-1 Sample AWR report via OEM
274 Oracle Database 11g DBA Handbook
Clicking the SQL Tuning Advisor result link, you can see the SQL Tuning Result summary in
Figure 8-3. On this low-usage database, the SQL Tuning Advisor found 14 repeating SQL statements
that were classified as high-load, but it did not find a way to improve the performance of these
SQL statements.
FIGURE 8-2 OEM Advisor Central summary
Chapter 8: Database Tuning 275
Tuning Solutions
This chapter does not cover every potential tuning solution. However, there is an underlying
approach to the techniques and tools presented throughout this chapter. Before spending your
time and resources on the implementation of a new feature, you should first stabilize your
environment and architecture??”the server, the database, and the application.
Pages:
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467