Lesson 1

The purpose of this module is to address the issues surrounding the development and tuning of SQL statements in an Oracle database shop. Historically, the development of SQL has been fragmented with the development staff writing SQL and the DBA staff tuning the SQL statements.
Now, with the evolution of Oracle and the use of SQL in stored procedures, the DBA is taking a more active role in the approval and tuning of all SQL statements that run against the database because the tuning of SQL statements can have more positive impact than almost any other tuning activity.
This module will show the various roles of the developer and the DBA as well as discuss methods and standards that the DBA can use to ensure quality and consistency of SQL development. Topics in this module include:

1. Identifying the roles of the DBA in SQL tuning
2. Enforcing SQL standards
3. Working with SQL developers
4. Using SQL in stored procedures
   Let us get started by identifying the roles of the DBA in SQL tuning.

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. Let us elaborate on SQL tuning in Oracle versions 13c to 19c, focusing on the key points of your provided definition:
SQL Tuning in Oracle 13c-19c: Elaboration

• Iterative Process: SQL tuning isn't a one-time fix. It involves these recurring steps:
  1. Identify problematic SQL: Find the statements with poor performance through tools and techniques mentioned below.
  2. Analyze and Diagnose: Understand the root cause of the problem (indexes, execution plans, statistics, resource-heavy operations, etc.)
  3. Implement Changes: Modify SQL, adjust indexes, restructure, use hints, etc.
  4. Evaluate and Verify: Did the changes improve performance measurably? If not, it goes back to step 2 for further analysis.
• Improving SQL Statement Performance: The core focus is enhancing execution speed and resource efficiency. Key techniques Oracle 13c-19c offer:
  • Execution Plans: Analyze how the optimizer will execute your query. Tools like `EXPLAIN PLAN` and graphical interfaces in Enterprise Manager help you read these.
  • Indexing: Strategic indexes can dramatically speed up data retrieval. Oracle supports various index types (B-tree, bitmap, function-based, etc.).
  • Statistics: Optimizer relies on up-to-date statistics about your data. Oracle gathers these automatically but may need manual intervention.
  • SQL Rewrites: Restructuring SQL, using hints, and utilizing Oracle-specific syntax features can lead to better plans.
  • Adaptive Query Optimization (12c onwards): Oracle can dynamically adjust execution plans mid-query based on real-time data statistics.
• Specific, Measurable, Achievable Goals:
  • Specific: Don't just say "make it faster." Specify targets like "reduce query time from 30 seconds to under 5 seconds".
  • Measurable: Use Oracle's monitoring tools (AWR, ASH, Real-Time SQL Monitoring) to collect baseline and post-change metrics.
  • Achievable: Be realistic. Huge improvements may require a combination of techniques and possibly application code changes.

Oracle 13c-19c Specific Features

• Automatic SQL Tuning (12c onwards): Oracle can run SQL tuning jobs to analyze and recommend improvements.
• SQL Plan Management (11g onwards): Stabilize performance by saving and reusing known good execution plans.
• In-Memory Columnar Store (12c onwards): Dramatically speeds up analytic queries in certain scenarios.
• Enhanced Diagnostics and Monitoring: Real-Time SQL Monitoring, detailed AWR reports give deep insights into query execution patterns.

Important Considerations

• SQL Tuning is often a trade-off between various queries and workloads.
• Database design heavily influences how tunable your SQL is.
• Consider hardware resources (memory, CPU, fast storage) alongside pure SQL optimization.

The key to obtaining good SQL performance is to design your application with performance in mind. This section contains the following topics:

Data modeling is important to successful application design. You must perform data modeling in a way that represents the business practices. Heated debates may occur about the correct data model. The important thing is to apply greatest modeling efforts to those entities affected by the most frequent business transactions. In the modeling phase, there is a great temptation to spend too much time modeling the non-core data elements, which results in increased development lead times. Use of modeling tools can then rapidly generate schema definitions and can be useful when a fast prototype is required.

During the design and architecture phase of system development, ensure that the application developers understand SQL execution efficiency. To achieve this goal, the development environment must support the following characteristics:

1. Good database connection management: Connecting to the database is an expensive operation that is not scalable. Therefore, a best practice is to minimize the number of concurrent connections to the database. A simple system, where a user connects at application initialization, is ideal. However, in a web-based or multitiered application in which application servers multiplex database connections to users, this approach can be difficult. With these types of applications, design them to pool database connections, and not reestablish connections for each user request.
2. Good cursor usage and management: Maintaining user connections is equally important to minimizing the parsing activity on the system. Parsing is the process of interpreting a SQL statement and creating an execution plan for it. This process has many phases, including a) syntax checking, b) security checking, c) execution plan generation, and d) loading shared structures into the shared pool.

There are two types of parse operations:

1. Hard parsing: A SQL statement is submitted for the first time, and no match is found in the shared pool. Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a parse.
2. Soft parsing: A SQL statement is submitted for the first time, and a match is found in the shared pool. The match can be the result of previous execution by another user. The SQL statement is shared, which is optimal for performance. However, soft parses are not ideal, because they still require syntax and security checking, which consume system resources.

Effective use of bind variables: Application developers must also ensure that SQL statements are shared within the shared pool. To achieve this goal, use bind variables to represent the parts of the query that change from execution to execution. If this is not done, then the SQL statement is likely to be parsed once and never re-used by other users. To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements.

Because parsing should be minimized as much as possible, application developers should design their applications to parse SQL statements once and execute them many times. This is done through cursors. Experienced SQL programmers should be familiar with the concept of opening and re-executing cursors. For example:

SELECT *
FROM employees
WHERE last_name LIKE 'KING';
Statement with bind variables:
SELECT *
FROM employees
WHERE last_name LIKE :1; 

Test #Users Supported
No Parsing all statements 270
Soft Parsing all statements 150
Hard Parsing all statements 60
Re-Connecting for each Transaction 30
These tests were performed on a four-CPU computer. The differences increase as the number of CPUs on the system increase.