Content Inside :
The topics will include the new Oracle parameters that affect SQL performance, the use of hints to change SQL execution plans, re-writing SQL queries in more efficient forms and the use of advanced techniques such as Materialized Views, replacing SQL with PL/SQL, the new automated CBO statistics collection, and using the new Oracle10g CPU costing approach. Understanding Oracle SQL Tuning Before relational databases were introduced, database queries required knowledge of the internal structures and developers needed to build in the tuning as a part of writing the database query. However, the SQL standard imposed a declarative solution to database queries where the database optimizer determines important data access methods such as what indexes to use and the optimal sequence to join multiple tables together. Oracle SQL tuning is a phenomenally complex subject, and entire books have been devoted to the nuances of Oracle SQL tuning, most notably the Kimberly Floss book Oracle SQL & CBO Internals by Rampant TechPress. This chapter provides a review the following areas of SQL tuning: § The goals of SQL tuning § Simplifying complex SQL § SQL Optimization instance parameters. Optimizing Oracle SQL Execution The key to success with the Oracle Cost-based Optimizer (CBO) is stability, and ensuring success with the CBO involves the consideration of several important infrastructure issues. Pre-tune the SQL before deploying: Many Oracle systems developers assume that their sole goal is to write SQL statements that deliver the correct data from Oracle. In reality, writing the SQL is only half their job and successful Oracle sites require all developers to ensure that their SQL accesses Oracle in an optimal fashion. Many DBAs will export their production CBO statistics into their test databases so that their developers can see how their SQL will execute when it is placed into the production system. Goals of SQL Tuning There are many approaches to SQL tuning and this paper describes a fast, holistic method of SQL tuning where we optimize the SGA, the all-important optimizer parameters, and adjust CBO statistics, all based on current system load. Once the “best” overall optimization is achieved, we drill-down into the specific cases of sub-optimal SQL, and change their execution plans with SQL profiles, specialized CBO stats, or hints.
Get free oracle magazine subscription here
Tags : oracle systems, oracle sql, test databases, oracle 10g, infrastructure issues, writing sql queries, optimal sequence, relational databases, sole goal, tuning techniques, sql standard, system goals, internal structures, dbas, database query
If you see unrelated pdf files with the description or copyrighted material published, please report to us, we'll correct/delete it it as soon as possible.NONE OF THOSE MATERIALS ARE HOSTED IN THIS SERVER NOR UPLOADED BY ME IN SOMEONE'S SERVERS. Read our DISCLAIMER for more detail.
We are neither affiliated with authors and brands nor responsible for its content and change of content.
Information contained herein is provided "as is" without warranty of any kind, either expressed or implied, including any warranty of merchantability or fitness for a particular purpose. In no event shall ANYONE be held liable for any loss of profit, special, incidental, consequential, or other similar claims.