Page 262 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 14: Application Development and Administration

Notes

Oracle tuning involves the following steps, with each step getting more specific and targeted:
1. Server & network tuning: This is always the first step, as not amount of tuning will help a
poor server environment.

2. Instance tuning: Tuning the Oracle SGA is the next step, and all of the Oracle initialization
parameters must be reviewed to ensure that the database has been properly configured
for it’s workload. In some cases, a database may have a bi-modal workload (online vs.
batch) and the instance parms are adjusted as-needed during this step.

3. Object tuning: This step of performance tuning looks at the setting for Oracle tables and
indexes. Table and index settings such as PCTFREE, PCTUSED, and FREELISTS can have a
dramatic impact on Oracle performance.

4. SQL tuning: This is last step in tuning, and the most time-consuming tuning operation
because there can be many thousands of individual SQL statements that access the Oracle
database. If you have carefully optimized the workload as a whole from step 2, there you
will only need to tune “outlier” SQL statements. Within this step, there are sub-steps:

(a) Remove unnecessary large-table full-table scans: In this tuning step you evaluate the SQL
based on the number of rows returned by the query. Standard b-tree indexes can be
added to tables, and bitmapped and function-based indexes can also eliminate
full-table scans.
(b) Cache small-table full-table scans: In this step we ensure that a dedicated data buffer is
available for the rows.
(c) Verify optimal index usage: This step is critical because you may have “missing” indexes
in your database, causing excessive I/O.
(d) Materialize your aggregations and summaries for static tables: One features of the Oracle
SQLAccess advisor is recommendations for new indexes and suggestions for
materialized views.

Task I want to connect databases to my web application? Discuss.

LOVELY PROFESSIONAL UNIVERSITY 255