Oracle Tuning Concepts

Lesson 1

This course introduces you to some advanced Oracle performance tuning tools and concepts associated with Oracle. This course is designed to give you the skills needed to maximize the performance of your Oracle database. In this course, you will have the opportunity by means of hands-on exercises to look into the more subtle elements of the Oracle engine, and the more advanced features of Oracle tuning.

• Advanced Oracle Tuning Concepts Goals After completing the course, you will be able to:
  1. Use data blocks efficiently to improve I/O performance
  2. Tune using the new Oracle Data Structures
  3. Use the Data Dictionary to monitor performance
  4. Define and use Oracle table and index partitions
  5. Tune with Oracle indexes for optimal performance
  6. Tune Oracle for Web Applications
  7. Describe and implement tuning techniques for Distributed Databases
  8. Tune the Oracle Parallel Server (OPS)

In this course, you will learn and practice Oracle tuning concepts with two kinds of presentation methods:
the diagram and image gallery.
The following features are new in Oracle 12 Release 1:

1. In-memory aggregation: This optimization minimizes the join and GROUP BY processing required for each row when joining a single large table to multiple small tables, as in a star schema. VECTOR GROUP BY aggregation uses the infrastructure related to parallel query (PQ) processing, and blends it with CPU-efficient algorithms to maximize the performance and effectiveness of the initial aggregation performed before redistributing fact data.
2. SQL Monitor supports adaptive plans in the following ways:
   1. Indicates whether a plan is adaptive, and show its current status: resolving or resolved.
   2. Provides a list that enables you to select the current, full, or final plans

Performance tuning is an indispensable activity in the domain of Oracle Database management, representing an ever-evolving set of methodologies, tools, and best practices. The tuning process has undergone a monumental transformation from Oracle 9i to Oracle 13c, accompanied by the advent of increasingly complex data workloads, cloud architecture, and machine learning algorithms. The core objective of ensuring optimal performance and resource utilization remains constant, but the strategies and tools to achieve this objective have matured significantly. Below is a detailed examination of how the Oracle performance tuning landscape has evolved over these versions.

1. Query Optimization:
   1. Oracle 9i: In Oracle 9i, query optimization was largely rule-based or utilized the older Cost-Based Optimization (CBO) models. The DBA would often need to manually gather statistics and might even resort to query hints to influence the optimizer's behavior.
   2. Oracle 13c: Fast-forward to Oracle 13c, the optimizer has become more sophisticated, leveraging a feature called Adaptive Query Optimization. This feature allows the optimizer to make real-time decisions on executing SQL queries, and it can even re-optimize a query in the middle of execution if the initial assumptions do not hold true.
2. Indexing:
   1. Oracle 9i: The role of indexes in Oracle 9i was somewhat limited to B-tree and bitmap indexes. The choice was often binary; B-tree for OLTP and Bitmap for OLAP.
   2. Oracle 13c: The concept of indexing has expanded greatly. Advanced indexing options such as function-based indexes, domain indexes, and invisible indexes offer better ways to optimize performance. Partitioned indexes are also now in play, allowing data to be logically segmented for better access speed.
3. Memory Management:
   1. Oracle 9i: Oracle 9i primarily used manual memory management. The initialization parameters for the System Global Area (SGA) and Program Global Area (PGA) had to be set explicitly, requiring frequent tuning exercises.
   2. Oracle 13c: Oracle 13c introduced the Automatic Memory Management (AMM) and Automatic Shared Memory Management (ASMM), significantly reducing the overhead of manual memory allocation. These features adjust memory dynamically, minimizing the need for manual intervention.
4. Diagnostic and Tuning Tools:
   1. Oracle 9i: Oracle 9i relied heavily on manual tools like Explain Plan, Tkprof, and Statspack for diagnostics. Performance tuning was more of an art, requiring a significant understanding of the underlying architecture.
   2. Oracle 13c: With Oracle 13c, tools like Oracle Automatic Workload Repository (AWR), Active Session History (ASH), and Oracle Real-Time SQL Monitoring provide more in-depth diagnostics and recommendations. These are part of the Oracle Diagnostic and Tuning packs, offering automated capture of database performance metrics and enhanced visualization.
5. Concurrency Control:
   1. Oracle 9i: Oracle 9i employed basic concurrency control mechanisms, such as locks and latches. While these were efficient for their time, they required manual tuning for optimal performance.
   2. Oracle 13c: In Oracle 13c, more advanced features like the Result Cache, Materialized Views with Query Rewrite, and Optimistic Concurrency Control significantly improve data access and manipulation, reducing the need for frequent locking and latching.
6. Cloud Integration:
   1. Oracle 9i: Oracle 9i predated the widespread adoption of cloud computing. Thus, performance tuning was strictly an on-premise affair.
   2. Oracle 13c: Oracle 13c allows seamless integration with Oracle Cloud, introducing cloud-specific performance tuning options. Features like Database as a Service (DBaaS) and multi-tenant architecture have added another layer of complexity and options for performance tuning.

The journey from Oracle 9i to Oracle 13c is a testament to Oracle's commitment to evolving its database technologies in tandem with the shifting paradigms of data management and cloud computing. Performance tuning, once a laborious manual effort, has become more intelligent, automated, and integrated, without compromising on the core objective of resource optimization and performance excellence.