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

This is the last post of the 04-part series on the top 20 features on Oracle Database 23c(23ai). Here are the links to the previous three posts (Part-1, Part-2, Part-3). In this post, let us have a look at the last set of the features.

• Blockchain Ledger Table Enhancements
• Active Data Guard DML Redirection
• Sharding Enhancements
• Automatic Workload Repository (AWR) Enhancements
• SQL Quarantine

Blockchain Ledger Table

Blockchain Ledger Tables are built to give a data structure where you can add new info. Each row links to the one before it using special math. This setup stops anyone from changing data once it’s in the table. It makes sure no one can mess with or erase the data without getting caught. 🙂

Key Features of Blockchain Ledger Tables

1. Immutability:
   • You can’t update or delete data once it’s in a Blockchain Ledger Table. Each row links to previous rows with cryptographic hashing. This makes sure the data stays the same and shows if someone tries to change it.
2. Cryptographic Chaining:
   • Every row in a Blockchain Ledger Table has a hash. This hash combines the previous row’s hash and the current row’s data . This chaining helps to keep transactions in order and intact.
3. Row-Level Security and Non-repudiation:

• Insert operations need digital signatures. This makes sure allowed users can add data. It also means every entry can be traced back to who made it giving proof they can’t deny.

4. Checking and Agreement:
   • Adding data might need one or more parties to check a transaction before it’s added to the database. This way of agreeing is like what you see in spread-out blockchain systems.
5. How Long to Keep Data:
   • People in charge can set a time limit for how long to keep data in Blockchain Ledger Tables. When this time is up, the data can be removed from the table. This helps manage storage well while following rules about how to handle data.
6. Transparent and Secure Auditing:
   • Blockchain Ledger Tables make auditing easy. The data’s unchangeable nature and cryptographic linking let auditors check data integrity. They don’t need access to the app’s inner workings or database details to do this.

Practical Example of Blockchain Ledger Tables

Let’s look at a real-world case where a drug company needs to keep an unchangeable record of how they make their drugs. They need to do this to follow the rules set by regulators:

1. Table Creation:
   • Make a Blockchain Ledger Table to store details about each batch of drugs they produce. This table will include information about the ingredients used, the batch numbers, and when each batch was made.

CREATE BLOCKCHAIN TABLE f00d_manufacturing_log (
    batch_id NUMBER,
    ingredient_batch VARCHAR2(100),
    quantity_used NUMBER,
    production_date DATE,
    digital_signature RAW(2048),
    CONSTRAINT f00d_manufacturing_log_pk PRIMARY KEY (batch_id),
    HASHING USING "SHA-256" VERSION "v1"
) NO DROP UNTIL 365 DAYS IDLE;

2. Data Insertion:
   • Users add data to the table, including a digital signature to guarantee non-repudiation. The system calculates each row’s hash to include the hash of the previous row.

INSERT INTO f00d_manufacturing_log (batch_id, ingredient_batch, quantity_used, production_date, digital_signature)
VALUES (101, 'Batch-1023' 100, '2023-08-01' SYS_GUID());

3. Data Auditing:
   • Auditors can check the data’s accuracy by examining the cryptographic hashes and matching them with known correct values or by recalculating them to spot any changes.

Benefits and Use Cases

Regulatory Compliance:

• Industries like pharmaceuticals, finance, and aerospace can use Blockchain Ledger Tables to keep records that meet strict regulatory requirements.

Supply Chain Transparency:

• Companies can use these tables to keep tabs on products from production to purchase making sure all deals are open and checkable.

Safe Data Sharing:

• Businesses can use Blockchain Ledger Tables to swap info with partners while making sure no one can change the data, which builds trust and teamwork.

Active Data Guard DML Redirection

DML Redirection lets a standby database in an Oracle Data Guard setup handle DML operations (INSERT, UPDATE, DELETE) aimed at it. These operations get sent to and run on the primary database, and the changes then come back to the standby database as part of the usual redo apply process. This means you can use the standby database for both reading and writing turning it into a fully updatable standby.

How DML Redirection Works

1. User Session and Transaction Handling:
   • Active Data Guard catches DML operations that start on the standby database.
   • It wraps the operation in a transaction and sends it to the primary database.
2. Execution on the Primary Database:
   • The primary database runs the DML operation as if it began there.
   • Oracle keeps data consistent and maintains transaction integrity across both databases.
3. Redo Data Propagation and Application:
   • The primary database creates redo data that includes the changes from the DML operation.

• The standby database gets this redo data and applies it. This keeps the standby database in sync with the primary one.

4. Client Response:
   • After the standby finishes applying the redo, you can see the results of the DML operation.
   • The standby database session gets the DML operation’s outcome just as if it ran .

Configuration and Setup

You need to take multiple steps to set up Active Data Guard with DML Redirection. Your main focus should be on making sure you configure redo transport services and role transitions :

1. Start Redo Transport Services:
   • Set up and launch the redo transport service on the main database to make sure redo data keeps moving to the backup.
2. Set Up Standby for Redirection:
   • The backup database needs settings to take in redirected DML transactions. This often means adjusting startup settings to turn on this feature.
3. Handle Roles:
   • Check that the databases have the right roles (main and backup) and that DML redirection works when the backup is active (read-write).

Advantages of DML Redirection

1. Better Availability:
   • The backup database can handle read-write tasks giving non-stop access even when the main database needs fixing or stops working.
2. Better Use of Resources:
   • By letting the backup do some work, the system can spread the load between the main and backup servers making the best use of the hardware.
3. Stronger Backup Plans:
   • With DML redirection, the backup database stays almost in sync with the main one making backup plans more reliable.

Use Cases

• Maintenance and Upgrades:
  • Keep the primary database running while you fix or improve it. Just send all the write operations to the standby instead.
• Load Balancing:
  • When things get busy, you can send some of the heavy write jobs to the standby. This helps manage the load on the primary.
• Reporting and Queries:
  • Run your big complex queries and reports on the standby. Meanwhile, the primary can keep handling the live stuff by sending DML changes to the standby.

Sharding:

Oracle Database sharding splits data across multiple separate databases called shards, which can sit on different hardware. This setup helps applications that need to scale and keep problems isolated. Oracle Database 23c has made big improvements to its sharding features making them easier to manage faster, and more flexible.

Key Improvements in Oracle Database 23c Sharding

1. Automated Shard Deployment and Management:
   • Oracle 23c makes shard database management easier by automating many shard deployment and configuration tasks. This has an impact on automated shard provisioning, rebalancing, and scaling operations, which cuts down on the administrative work needed to manage a sharded database setup.
2. Enhanced Connection Routing:
   • The connection routing system now works better to route client requests to the right shards faster and more . This matters a lot for performance in sharded setups where requests must go to the correct shard based on the sharding key.
3. Support for Multiple Sharding Methods:

• Oracle 23c supports different sharding methods, like consistent hash, range, list, and composite sharding. This gives companies options to pick the sharding strategy that matches their app’s access patterns and data distribution needs best.

4. Better Data Consistency and Recovery Systems:
   • Upgrades in data consistency models and recovery systems make sure the sharded database can keep high levels of data integrity and availability. This includes improved global sequence generation and better cross-shard transaction abilities.
5. Built-in Observability and Diagnostics:

• The addition of built-in tools to watch and diagnose performance issues across shards makes it easier to spot and fix potential problems . This goes hand in hand with advanced analytics tools that offer insights into how sharded databases perform.

6. Cross-Shard Query Optimization:
   • Cross-shard querying has gotten better with smarter query optimization methods. These cut down on the delay and processing power needed to run queries that cover multiple shards.
7. Better Security Features:
   • Security in sharded setups has gotten tougher. There’s better encryption stronger access controls, and improved auditing. All of this helps to protect data and follow rules across all shards.

Practical Example of Sharding Improvements

Let’s look at a worldwide online shopping platform that needs to stay up all the time and grow as more customers join. This platform uses Oracle Database 23c’s sharding to spread customer data across different parts of the world:

1. Shard Setup:
   • The platform sets up shards based on where customers live to make things faster. Each part of the world (like North America, Europe Asia) has its own group of shards.

CREATE SHARDED TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    email VARCHAR2(100),
    region VARCHAR2(50)
)
PARTITION BY LIST (region)
( PARTITION p1 VALUES ('North America'),
  PARTITION p2 VALUES ('Europe'),
  PARTITION p3 VALUES ('Asia') );

2. Automated Management:
   • Oracle’s sharding manager has the ability to provision new shards when someone adds a new region to the platform. It also takes care of rebalancing data as the customer distribution changes.
3. Query Routing:
   • Connection pooling and advanced routing methods make sure that customer queries go to the right shard based on the region sharding key, which helps to improve response times.
4. Cross-Shard Operations:
   • For operations that affect the whole system, like creating global sales reports, Oracle 23c makes cross-shard queries more efficient to combine data from all shards .
5. Monitoring and Diagnostics:

• The platform uses Oracle’s built-in monitoring tools to keep an eye on how each shard performs, spot bottlenecks, and guess future scaling needs based on real-time data usage patterns.

Automatic Workload Repository (AWR) Enhancements

The Automatic Workload Repository (AWR) plays a crucial role in Oracle Database. It gathers, processes, and keeps performance stats to detect problems and tune itself. Oracle updates and improves AWR with each new database release. Oracle Database 23c brings major upgrades to AWR. These changes aim to give deeper insights better performance analysis, and more useful data.

Key Improvements in Oracle Database 23c for AWR

1. Longer Data Keeping and More Detail:
   • Oracle Database 23c lets you keep AWR data for longer, which helps you look at performance over a bigger time frame. It also grabs more detailed info giving you a clearer picture of what’s happening in your database as time goes by.
2. Smart Performance Insights:
   • The AWR in 23c now has better ways to spot performance hiccups and patterns on its own. This helps database managers understand how things are running and catch potential problems before they turn into big headaches.
3. Working with Machine Learning:

• Oracle has integrated machine learning algorithms into AWR. This has an influence on providing predictive analytics about database performance and workload forecasting. It allows DBAs to expect future performance bottlenecks and plan capacity upgrades or tuning interventions.

4. Better Reporting and Visualization:
   • AWR’s reporting features have gotten much better. Oracle Database 23c includes new visualization tools and dashboards making it easier to understand AWR data and get insights. Users can customize these tools to show different levels of detail based on what they need.
5. Real-Time Performance Monitoring:

• AWR now has improved real-time performance tracking giving quick updates on changes to database speed. This helps users watch how new apps or system updates affect things as they start up.

6. Active Session History (ASH) Upgrades:
   • AWR’s ASH now has better sorting and more in-depth tracking of what sessions do. These updates link more clearly to specific SQL queries and database tasks. This makes it easier to find the root causes of speed issues.
7. Reports Across Instances in RAC Setups:

• Oracle Real Application Clusters (RAC) now allows AWR to analyze performance across instances. This update lets DBAs see and check performance metrics on several RAC instances at once, which helps manage clustered database setups better.

8. DBaaS and Cloud Integration:
   • As more people use Database as a Service (DBaaS) and Oracle pushes cloud services, AWR has improved to support cloud-based databases better. These improvements include tweaks to manage multi-tenant setups and better support for cloud-specific performance metrics.

Practical Example of Using AWR Improvements

Think about a big company running a busy Oracle Database system that’s just upgraded to Oracle 23c. The company’s DBAs need to make sure everything runs while also getting ready for an upcoming sales event that’s expected to double the usual database activity.

Step-by-Step Use of AWR Improvements:

1. Set Up Longer AWR Storage:

EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 7776000); -- 90 days in minutes

• Set up AWR to keep detailed performance data for 90 days instead of the usual 30 days, to give a longer baseline to analyze.

2. Look at Past Performance Trends:
   • Use the new AWR reporting tools to look at past performance data finding times of day or specific queries that have caused slowdowns in the past.
3. Use Predictive Analysis:
   • Use AWR’s machine learning features to forecast potential slowdowns during the upcoming sales event based on trends and patterns found in the past data.
4. Watch Real-Time Performance:

• During the sales event, use AWR’s better real-time tracking to watch database performance making sure you spot and fix any unexpected behavior.

5. Create After-Event Report:
   • Once the event ends, make thorough reports to look over performance for the whole period comparing what you thought would happen with what happened to improve future guesses and planning.

SQL Quarantine

SQL Quarantine is a tool in Oracle Database. It aims to shield the system from the harmful effects of resource-hungry SQL queries. Oracle created it to spot and limit troublesome SQL statements on its own. These statements eat up too many system resources, like CPU and I/O, or take too long to run. This can slow down the system for other users and tasks.

How SQL Quarantine Works

1. Detection and Identification:
   • Oracle’s Resource Manager spots SQL statements that go beyond set resource limits or run-time thresholds. Admins can set these thresholds based on typical system performance and operational needs.
2. Quarantine Mechanism:
   • When a SQL statement is flagged as problematic, it gets quarantined. This means the system will block any future tries to run the same SQL statement (with identical text and execution plan), which helps to prevent potential resource drain.
3. Notification and Logging:

• When Oracle quarantines a SQL statement, it logs the event and notifies the DBA. This allows the admin to review and step in. DBAs can find details about quarantined SQL statements in specific data dictionary views.

4. Management and Oversight:
   • DBAs have tools and views from Oracle to manage quarantined SQL statements. They can choose to release (take out of quarantine), keep, or change the quarantine rules for each SQL statement as needed.

SQL Quarantine Management

Oracle offers tools and views to manage quarantined SQL statements:

• Views like DBA_QUARANTINE and DBA_SQL_QUARANTINE: These views let admins see details about quarantined SQL statements. They show why a statement is in quarantine, which resource limits it exceeded, and other key diagnostic info.
• DBMS_SQLQ Package: This PL/SQL package has procedures to manage SQL quarantines. Admins can use it to drop, disable, or turn quarantines back on as needed.

-- Example: Dropping a quarantine
BEGIN
  DBMS_SQLQ.DROP_QUARANTINE('quarantine_name');
END;
/

-- Example: Disabling a quarantine
BEGIN
  DBMS_SQLQ.DISABLE_QUARANTINE('quarantine_name');
END;
/

SQL Quarantine Has Several Advantages

1. Better System Stability:
   • SQL Quarantine keeps the system running and stable by stopping resource-hungry SQL queries from running. This helps the whole database work better.
2. Better Performance:
   • When SQL Quarantine protects the system from costly SQL runs, it helps manage resources better. This means all apps and users can get the resources they need.
3. Solving Problems Before They Happen:
   • SQL Quarantine lets you handle potential database issues . You can fix them before they cause trouble for the whole system.
4. Checking and Following Rules:
   • SQL Quarantine has thorough logging and management features. This helps with following rules and checking processes. It gives clear records of how it deals with tricky SQL queries.

Use Cases for SQL Quarantine

• Highly Transactional Systems: SQL Quarantine can stop optimized or harmful SQL queries from hurting overall system performance in places where speed and uptime matter most (like financial services platforms or online retail systems).
• Multi-Tenant Databases: SQL Quarantine helps make sure no single tenant can hog system resources in a shared database setting where many users and apps work on the same system.
• Real-time Applications: SQL Quarantine can manage apps that need real-time access and updates by making sure long-running queries don’t block key operations.

This concludes this 04-part series where we took a look at the top 20 features of Oracle database 23ai. Hope it was good and helps.