Oracle database 10g sql tuning case study

Oracle database 10g sql tuning case study

We found that on our system optimizer statistics collection in Oracle 10g is a much more resource-intensive operation than it was in Oracle 8i.

The foremost tenet of Oracle SQL tuning is avoiding the dreaded full-table scan. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.

How to tune the sql queries in oracle

This allowed us to switch all tablespaces to locally managed tablespaces, freshly install Oracle components, and compact all of our table and index segments. If our experience can help you plan for your own upgrade in any way, then this paper will have achieved its goal. Execute utlxplan. Then we will share our overall impressions of the upgrade process, compatibility, and Oracle 10g itself. When it comes to sizing the SGA, we have always followed a methodology of making things only as large as they need to be. Once the environment, instance, and objects have been tuned, the Oracle administrator can focus on what is probably the single most important aspect of tuning an Oracle database: tuning the individual SQL statements. For several years, we chose to stand pat with Oracle 8i—that release met all of our needs, and we found it to be smooth and stable. It is important to recognize that in many cases, the cost-based optimizer may not make the proper decision in terms of the speed of the query. Rationale The optimizer is unable to use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.

The only way for you to know how your specific system will fare on Oracle 10g is to try it—in a test environment—and see. Of course, there are times when a full-table scan is appropriate for a query, such as when you are doing aggregate operations such as a sum or an average, and the majority of the rows within the Oracle table must be read to get the query results.

Even so, it's sensible to update profiles periodically. In some cases, an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.

oracle sql tuning pdf

This allowed us to run all sorts of tests to compare how the two versions of Oracle handled identical workloads and challenges. Entire books have been written about the nuances of Oracle SQL tuning; however, there are some general guidelines that every Oracle DBA follows in order to improve the performance of their systems.

Rated 7/10 based on 7 review
Oracle SQL tuning steps