Top 20 Features in Oracle Database 23c(23ai) for DBAs – Part-3

In part-1 and part-2 of this series, we talked about the following features:

• Part-1
  • JSON Relational Duality
  • Automatic Indexing Enhancements
  • Blockchain Tables
  • Native JSON Data Type
  • SQL Macro
• Part-2
  • Oracle Machine Learning Enhancements
  • In-Memory Column Store Enhancements
  • Automatic SQL Plan Management (SPM)
  • High-Frequency Statistics Collection
  • Automatic Index Optimization

In this article, let us have a look at the next set of features:

• Multilingual Engine (MLE)
• Enhanced Data Masking and Redaction
• Automatic Data Optimization (ADO) Enhancements
• Graph Studio Enhancements
• Partitioned Hybrid Tables

Multilingual Engine

MLE leverages the GraalVM, a high-performance runtime that provides support for multiple languages, enabling developers to write and execute code in languages like JavaScript, Python, R, and others within stored procedures, functions, and triggers. The execution of multilingual code within the database environment is secured by leveraging sandboxing and resource management. This isolates and controls the execution of foreign language code, preventing potential security risks and resource contention. Below is an example using python:

-- Enable MLE:
ALTER SYSTEM SET "_multilingual_engine" = TRUE;

-- Create a Python function for data analysis:
CREATE OR REPLACE FUNCTION py_analyze_data(data VARCHAR2) RETURN VARCHAR2
LANGUAGE PYTHON AS
$$
import pandas as pd

def analyze_data(data):
    df = pd.read_json(data)
    summary = df.describe().to_json()
    return summary
$$;

-- Call the Python function from PL/SQL:
DECLARE
  data VARCHAR2(4000) := '{"column1": [1, 2, 3, 4, 5], "column2": [5, 4, 3, 2, 1]}';
  result VARCHAR2(4000);
BEGIN
  result := py_analyze_data(data);
  dbms_output.put_line('Analysis Result: ' || result);
END;
/

Automatic Data Optimization (ADO) Enhancements

Oracle Database has upgraded its Automatic Data Optimization (ADO) feature in version 23c. This builds on ADO’s introduction in previous releases. ADO plays a key role in Oracle’s Information Lifecycle Management (ILM) approach. This approach aims to automate data management throughout its lifecycle. ADO helps manage data storage and performance more . It does this by automating tasks based on specific policies. These policies kick in under different conditions. These include how old the data is how often it’s accessed, and what storage tier it needs.

Key enhancements in Oracle 23c

1. Advanced Automation:
   • 23c has an influence on ADO by making storage tiering and compression automation better. You can now fine-tune policies more precisely based on how data is accessed and what storage costs. This means data moves between tiers—like from hot to warm or cold storage—without anyone having to step in.
2. Better Compression Methods:
   • 23c brings in compression algorithms that take up less space while keeping access speed the same. These upgrades include new low-overhead compression tricks to analyze transactions and better hybrid columnar compression to handle large amounts of data.
3. Enhanced Policy Management:

• Oracle has improved the ADO management and monitoring interfaces making it easier for DBAs to set up, tweak, and keep an eye on the rules for database segments. This includes smoother teamwork with Oracle Enterprise Manager giving a complete picture of ILM policies across the database.

4. Smart Segmentation:
   • Smart Segmentation splits data based on how it’s used and what type it is. This new trick in 23c helps to use different ILM rules on different parts of the same table, depending on how often they’re accessed and changed.
5. Working well with Multitenant Environments:

• ADO now supports Oracle’s Multitenant setup, which allows it to apply the same data optimization tactics across several pluggable databases (PDBs). This means that data lifecycle rules are used throughout the database system, which boosts resource use and performance in combined settings.

Examples of ADO Policy Implementation

Here’s how you can set up ADO policies in Oracle Database 23c:

1. Data Tiering Policy:
   • Move data from expensive high-speed storage to cheap slower storage based on how often it’s used.

ALTER TABLE orders ILM ADD POLICY TIER TO ON (access_time > 12 MONTHS);

This policy sends data in the orders table to a read-storage tier if no one has touched it for over a year.

2. Compression Policy:
   • Use advanced compression for data that doesn’t see much action.

ALTER TABLE orders ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH ON (modification_time > 6 MONTHS);

This command applies high-level compression to rows in the orders table that haven’t changed in the last six months.

3. Smart Segmentation and Tiering in a Multitenant Environment:
   • Set up a policy to segment and tier data based on how often it’s accessed across all PDBs.

ALTER PLUGGABLE DATABASE ILM ADD POLICY TIER TO low_cost STORAGE ON (access_time > 3 MONTHS);

This policy covers all pluggable databases. It moves data to a cheaper storage tier if no one has used it for more than three months.

Keeping an Eye on and Tweaking ADO Policies

Oracle Database 23c’s new features let DBAs use Oracle Enterprise Manager to check how well ADO policies work. They can make changes based on current data use and how things are running. This includes:

• Looking at heat maps that show how data is being used.
• Studying how different storage tiers affect performance.
• Changing policies to balance costs and performance as needed.

Graph Studio Enhancements:

Oracle Graph Studio is part of Oracle’s graph database tech. It offers tools to create, manage, and analyze graphs in an Oracle database setting. Oracle Database 23c has brought new upgrades to Oracle Graph Studio. These changes aim to make it easier to use, boost its performance, and improve how it works with other systems.

Key Enhancements in Oracle Database 23c

1. Better User Interface:
   • 23c has a new look for Graph Studio making it easier to use. The updated design helps users create and manage graph models with less hassle. It also makes visual analysis simpler and speeds up work for beginners and experts alike.
2. Stronger Analytics Tools:
   • 23c focuses on boosting analytics power. Graph Studio now offers more built-in graph algorithms and makes existing ones run faster. This lets users tackle complex network analysis jobs more , like finding communities, measuring importance, and mapping routes.
3. Closer Connection to Oracle Database:

• Graph Studio in 23c has a deeper connection with Oracle Database features, like in-memory tech and partitioning. This link boosts graph query speed and lets Graph Studio tap into Oracle Database’s high availability and security.

4. Support for Bigger Graphs:
   • The upgrades include better handling of larger graphs, thanks to smarter memory use and the ability to spread processing across multiple nodes in Oracle Real Application Clusters (RAC). This makes it possible to work with huge datasets in Graph Studio.
5. Better Visualization Tools:

• Graph Studio’s visualization tools have gotten a big upgrade, with more ways to customize and interact with them. Now, users can create more detailed visual representations of their graphs, which helps them understand data better and make smarter choices.

6. ML Model Integration:
   • Graph Studio now works with Oracle Machine Learning (OML) letting users apply machine learning models right in the studio. This new feature allows for predictive analytics on graph data helping users gain deeper insights into relationships and patterns.
7. Automated Graph Creation and Updates:
   • Graph Studio has added support for automated graph creation and updates from relational data. Users can set rules for how changes in the underlying database show up in their graph models. This ensures that analyses always use the most current data.
8. Better Collaboration Features:
   • Graph Studio’s improved teamwork tools help groups work better on graph projects. These include shared workspaces, version control for graph models, and options to add notes and share findings right in the tool. Teams can now collaborate more productively on their graph work.

Example of Graph Studio Usage

Let’s say you’re looking at how people interact on social media to find groups and key players. Here’s how you might use the new features of Graph Studio in Oracle Database 23c:

1. Data Import and Graph Creation:
   • Use graph creation tools to bring in social interaction data from relational tables and set up the graph structure based on how users interact.
2. Apply Graph Algorithms:
   • Run algorithms to spot communities or groups within the network.
   • Use centrality algorithms to find influential users.
3. Visualization:
   • Use top-notch visualization tools to show the graph data . Adjust the graph display to highlight key communities and nodes based on centrality scores.
4. Machine Learning Integration:
   • Use Oracle Machine Learning models to forecast future trends in social interactions or possible influence patterns.
5. Collaboration and Sharing:

• Show the graph breakdown to your coworkers using Graph Studio’s display. Talk about what you’ve learned in group work areas. Use version tracking to keep tabs on changes in the graph model.

Partitioned Hybrid Tables:

Oracle Database 23c brings in Partitioned Hybrid Tables, a game-changing feature that boosts the database’s ability to handle and get to data across different storage levels and formats well. This feature aims to make things run better and save money by putting together in-memory, disk-based, and outside data storage in one table setup. Partitioned Hybrid Tables come in handy in places with big and mixed data sets where how people use the data can be very different across the set.

Key Features of Partitioned Hybrid Tables

1. Hybrid Storage Management:
   • Partitioned Hybrid Tables let you split data across different storage types, including regular disk storage, in-memory formats, and outside storage like Hadoop Distributed File System (HDFS) or cloud storage. This means companies can keep often-used data in faster pricier storage (such as in-memory), while storing less-used data in cheaper slower options.
2. Automatic Data Tiering:

• This feature has an influence on the automatic shifting of data between different storage levels based on how it’s used and set rules. For instance, data that gets used less often as time goes by can move on its own from memory storage to disk storage or even outside storage cutting costs and making better use of resources.

3. Easy Data Access:
   • No matter where the data sits, people can get to it without having to know the ins and outs of the storage underneath. Searches are fine-tuned on their own to grab data from the right sections and storage types making sure things work well without anyone stepping in.
4. Better Data Life Cycle Management:

• Policies set at the partition level help manage the data lifecycle better. Each partition can follow its own rules to compress, move, and archive data giving fine-grained control over how data is handled throughout its life.

5. Support for Various Data Formats:
   • Partitioned Hybrid Tables let you store data in different formats in each partition. For instance, one partition might keep data in a columnar format to query it , while another might use a more compact format to store it long-term.

Practical Example of Partitioned Hybrid Tables

Let’s look at a real-world case where a retail business needs to handle huge amounts of transaction data collected over many years:

1. Table Creation:
   • The company sets up a Partitioned Hybrid Table that splits data by month. It keeps the most recent months’ information in-memory to speed up access for regular analysis, while it stores older data on disk.

CREATE TABLE sales_data (
    sale_id INT,
    product_id INT,
    quantity_sold INT,
    sale_date DATE,
    revenue DECIMAL
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_current MONTHS BETWEEN (CURRENT DATE - INTERVAL '1' MONTH) AND CURRENT DATE STORE IN (inmemory),
    PARTITION p_previous MONTHS BETWEEN (CURRENT DATE - INTERVAL '6' MONTH) AND (CURRENT DATE - INTERVAL '1' MONTH) STORE IN (ondisk),
    PARTITION p_archived STORE IN (external_storage)
);

This SQL code creates a table named ‘sales_data’ with five columns: sale_id, product_id, quantity_sold, sale_date, and revenue. The table uses range partitioning based on the sale_date column. It divides the data into three partitions:

1. p_current: Stores data from the last month to the current date in memory.
2. p_previous: Keeps data from six months ago to one month ago on disk.
3. p_archived: Holds older data in external storage.

This setup helps to manage and access data based on its age and relevance.

2. Data Access:
   • When you run queries on this table, you don’t need to point out the partition or storage media. The Oracle Database figures out on its own which partitions to grab data from, whether they’re in-memory, on the disk, or in outside storage.

SELECT * FROM sales_data WHERE sale_date BETWEEN '2023-01-01' AND '2023-03-31';

3. Automatic Data Movement:
   • Over time, data moves on its own from the inmemory partition to the ondisk and to the external_storage partition. This happens based on how old the data is and how often it’s used. Oracle’s Automatic Data Optimization (ADO) policies handle this movement.

Benefits and Implications

Cost Efficiency:

• Cuts down expenses by making the best use of pricey in-memory storage. It keeps the most crucial accessed data there.

Performance Optimization:

• Boosts query speed by making sure hot data is ready to go in faster storage. At the same time, it stores cold data in cheaper storage options.

Flexibility and Scalability:

• Gives you lots of options to handle data in various storage systems and grows with different ways people use and amounts of data.

That is all for this post, I will have a separate post altogether, possibly a live demo on enhanced data masking and redaction. Hope you enjoyed this post. Stay tuned for the last part where I will talk a bit on the remaining features.