Identifying DBA Roles in SQL Tuning

The Database Administrator (DBA) plays a vital role in the tuning of Structured Query Language (SQL) in Oracle 13c. Here are the key responsibilities that a DBA undertakes in the context of SQL tuning:

1. Identifying High-Load or Top SQL Statements: A DBA needs to locate SQL statements that are responsible for a high proportion of the total load on the system. These may include those with high CPU consumption, disk I/O, or those that are frequently executed. Tools such as Automatic Workload Repository (AWR) and Oracle Enterprise Manager (OEM) are commonly used for this purpose.
2. Statistical Analysis and Gathering: A DBA is responsible for ensuring that the database's statistical information is current. This information is used by the Oracle Optimizer to select the best execution plan for SQL statements. The DBA should regularly schedule jobs to collect statistics on tables, indexes, and system usage.
3. Optimizing SQL Statements: DBAs review problematic SQL statements and identify opportunities for optimization. This includes reviewing indexes, checking whether the appropriate join methods and types are being used, and rewriting queries for better performance.
4. Using Oracle Tools: A DBA should make effective use of the SQL Tuning Advisor and SQL Access Advisor tools provided by Oracle. These tools offer insights into SQL performance and provide recommendations for improvement.
5. Implementing SQL Profiles: Based on the recommendations from SQL Tuning Advisor, a DBA might create SQL Profiles that help the optimizer create more optimal execution plans.
6. Monitoring and Adjusting Parameters: DBAs also monitor and adjust initialization parameters that influence SQL execution plans and performance, such as optimizer_mode, pga_aggregate_target, and others.
7. Managing Database Resources: A DBA should ensure that database resources are efficiently used. This involves managing schema objects, partitioning tables, implementing parallel execution where necessary, and effectively managing memory and storage.
8. SQL Plan Management: DBAs create, evolve, and manage SQL plan baselines using SQL Plan Management (SPM). SPM ensures SQL execution plan stability, even when there are changes in the database.
9. Index Management: Regularly reviewing the efficiency of indexes, creating new indexes, or rebuilding or dropping unused ones is an important aspect of a DBA's role in SQL tuning.
10. Liaison with Development Team: DBAs collaborate with developers to ensure SQL statements are effectively written. They provide guidance on writing efficient SQL and making appropriate schema changes.

The DBA's role in SQL tuning in Oracle 13c is multifaceted, requiring not only a strong understanding of SQL and database concepts but also a deep familiarity with Oracle's suite of tools for diagnosing and rectifying performance issues. This systematic approach ensures that the Oracle database operates at peak efficiency and reliability.

Historically, the Oracle DBA assumes overall responsibility for the performance and tuning of the Oracle database. With this responsibility comes the challenge of monitoring all of the SQL that is written and executed against the Oracle database. SQL statements can originate from a variety of sources, including PC-based SQL code generators. It can be a formidable challenge for the DBA to keep track of the origin of all of these statements. The following Slide show describes the potential origins of SQL statements.

While the method varies from shop to shop, the goal of the DBA is to manage and control all of the SQL and ensure that all SQL statements are properly tuned. While a formal SQL management plan would be very desirable, any plan can be very difficult to enforce. WithOracle, SQL can be written and executed from any Oracle client without the knowledge or consent of the DBA. The only known way to enforce an SQL management plan is to deny access to the Oracle tables, except through GRANT EXECUTE on stored procedures. However, many shops find this cumbersome because the DBA acquires responsibility for stored procedures and packages for every SQL statement. Now let us look at methods that the DBA can use to enforce SQL standards.