Lesson 1

This module is designed to give you an overview of the internal functions and tuning of Oracle SQL statements. Each of the subjects introduced in this module will be examined in much greater detail during the course. Topics in this module include:

1. Oracle extensions to ANSI standard SQL
2. Basic SQL tuning tools
3. Using the EXPLAIN PLAN utility
4. The SQL optimizer modes
5. The rule-based SQL optimizer
6. The cost-based SQL optimizer
7. Tuning SQL with hints

SQL functions are built into the Oracle Database and are available for use in various SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL. If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle attempts to convert the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, REPLACE, and REGEXP_REPLACE.
SQL tuning is the iterative process of improving SQL statement performance to meet specific, measurable, and achievable goals. SQL tuning implies fixing problems in deployed applications. In contrast, application design sets the security and performance goals before deploying an application.

A SQL statement becomes a problem when it fails to perform according to a predetermined and measurable standard.After you have identified the problem, a typical tuning session has one of the following goals:

1. Reduce user response time, which means decreasing the time between when a user issues a statement and receives a response
2. Improve throughput, which means using the least amount of resources necessary to process all rows accessed by a statement

For a response time problem, consider an online book seller application that hangs for three minutes after a customer updates the shopping cart. Contrast with a three minute parallel query in a data warehouse that consumes all of the database host CPU, preventing other queries from running. In each case, the user response time is three minutes, but the cause of the problem is different, and so is the tuning goal. What is the purpose of SQL Tuning in Oracle?

SQL Tuning in Oracle is the process of optimizing SQL statements to improve their performance, efficiency, and scalability. The main purposes of SQL Tuning are:

1. Improve Response Time: Reduce the time it takes for SQL statements to execute, making applications more responsive.
2. Increase Throughput: Allow more transactions to be processed in a given time, increasing system productivity.
3. Reduce Resource Consumption: Optimize resource usage, such as CPU, memory, and I/O, to minimize system overhead.
4. Enhance Scalability: Ensure that SQL statements can handle increasing amounts of data and user activity without performance degradation.
5. Prevent Performance Bottlenecks: Identify and resolve potential performance issues before they become critical.
6. Optimize System Configuration: Adjust database settings, indexing, and other parameters for optimal performance.
7. Improve Query Efficiency: Rewrite or refine SQL statements to use more efficient algorithms, reducing execution time and resource usage.

By achieving these goals, SQL Tuning helps to:

• Improve application performance and user experience
• Increase system availability and reliability
• Reduce costs associated with hardware upgrades and maintenance
• Enhance overall database performance and efficiency

Oracle provides various tools and techniques for SQL Tuning, including the SQL Tuning Advisor, SQL Trace, and EXPLAIN PLAN, among others.