Lesson 1

The Oracle database contains a number of features designed to deliver improved performance for types of database access that have become more popular. Improvements in the cost-based optimizer, as described in the previous module, have increased the ability to use the star schemas of the data warehouse ^[1]. Materialized views are another feature designed to improve the performance of the data warehouse.

• Module Objectives
  In this module, you will learn how to:
  1. Describe a materialized view and its uses
  2. Describe the parameters and what they mean in the CREATE MATERIALIZED VIEW command
  3. Write Structured Query Language (SQL) to create a materialized view that stores summary data
  4. Describe the conditions and types of indexing for materialized views
  5. Check the status of a materialized view and fix it
  6. Describe what conditions allow for query rewriting
  7. Describe the function of the DBMS_OLAP and DBMS_MVIEW packages

Since Oracle8i, materialized views have provided another means of achieving a significant speedup of query performance. Summary-level information derived from a fact table and grouped along dimension values is stored as a materialized view. Queries that can use this view are directed to the view, transparently to the user and the SQL they submit. Oracle has continued to improve optimizer usage of materialized views with each new release of the database.
Materialized views were introduced for the creation of summary tables for facts and dimensions that can represent rollup levels in the hierarchies. A materialized view provides precomputed summary data; most importantly, a materialized view is automatically substituted for a larger detailed table when appropriate. The cost-based query optimizer can perform query rewrites to these summary tables and rollup levels in the hierarchy transparently, often resulting in dramatic increases in performance. For instance, if a query can be answered by summary data based on sales by month, the query optimizer will automatically substitute the materialized view for the more granular table when processing the query. A query at the quarter level might use monthly aggregates in the materialized view, selecting the months needed for the quarter(s). Oracle Database 10g added query rewrite capabilities such that the optimizer can make use of multiple appropriate materialized views. Materialized views can be managed through Oracle Enterprise Manager. The SQL Advisor^[2] accessible in Enterprise Manager includes a SQL Access Advisor that can recommend when to create materialized views

The Automatic Tuning Advisor, introduced in Oracle 10g, continues to be available and used in "Oracle 11g R2" as well as in "cloud-enabled databases" from Oracle 12c to Oracle 19c. Here's how it is utilized across these versions:
Oracle 11g R2

• Automatic SQL Tuning Advisor remains an integral feature.
• It analyzes SQL statements during predefined maintenance windows or when invoked manually.
• It provides recommendations for performance tuning, such as:
  • Creating SQL profiles.
  • Index recommendations.
  • Query restructuring.

Oracle 12c to Oracle 19c (Cloud-Enabled Databases)

• SQL Tuning Advisor and Automatic SQL Tuning are enhanced and integrated with newer features:
  • The Automatic Workload Repository (AWR) provides comprehensive historical performance data for better recommendations.
  • SQL Plan Baselines help ensure consistent query performance over time.
  • Adaptive Query Optimization introduced in Oracle 12c complements SQL Tuning Advisor by dynamically adjusting execution plans at runtime.
• Cloud-Specific Enhancements:
  • In cloud-enabled environments, tuning tasks are integrated with Oracle's Autonomous Database capabilities, where some aspects of tuning (e.g., adaptive indexing and plan selection) are automated.
  • Tuning Advisor recommendations can be applied manually or automatically, depending on configuration.

Key Differences in Cloud-Enabled Databases

• Higher Automation: Oracle's Autonomous Database in 18c and 19c leverages Machine Learning to handle more tuning tasks without manual intervention.
• Ease of Use: Cloud consoles often provide a user-friendly interface for running and applying SQL Tuning Advisor recommendations.

In conclusion, the Automatic Tuning Advisor remains a powerful tool for optimizing SQL performance across Oracle 11g R2 and cloud-enabled databases (12c-19c), with increasing levels of automation and integration in cloud-native environments.

Materialized views can also be used for other purposes, such as providing snapshot replication for distributed sites or downloading extracts in a mobile computing environment.
To improve the performance of an application, you can make local copies of remote tables that use distributed data, or create summary tables based on group by operations. Oracle provides materialized views to store copies of data or aggregations. Materialized views can be used to replicate all or part of a single table, or to replicate the result of a query against multiple tables; refreshes of the replicated data can be done automatically by the database at time intervals that you specify. In this module, you will see the general usage of materialized views, including their refresh strategies, followed by a description of the optimization strategies available. In the next lesson, we will discuss materialized views and their uses.

[1]Data Warehouse: A type of database that is used for analysis of data. The data warehouse frequently has a different type of structure a star schema as opposed to a standard normalized schema and is also frequently separate from an organization's transaction database.

[2] SQL Advisor: Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself.