Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘new feature’

Lets do our hands dirty with the MySQL 9.7 Hypergraph optimizer

Posted by FatDBA on November 11, 2026

Hello Everyone..

Every DBA I know has a complicated relationship with the MySQL optimizer. You feed it a five-table join, it picks an order that made sense to somebody’s heuristics fifteen years ago, and you spend the next hour sprinkling STRAIGHT_JOIN and index hints around like seasoning until the plan stops embarrassing you. We’ve all done it. Some of us have done it this week.

So when MySQL 9.7 landed and I read that the hypergraph optimizer is now available in the server I can just download and run, I didn’t rush to write about it. I’ve been burned by optimizer release notes before. Instead I pulled 9.7.4 into a lab box, built a dataset, and spent an evening actually flipping the switch on and off to see what changes. This post is that evening, written down.

Short version: this isn’t a patch on the old optimizer. It’s a second, completely separate optimizer living inside the same server, with a different approach to join planning, a different cost model, and even a different look to its EXPLAIN output. It’s off by default, you can turn it on per session or even per query, and in the right workload the plans it finds are genuinely better. In the wrong one, you’ll want to know how to back out. We’ll cover both.

Everything below was run on a stock MySQL 9.7.4 server. No tuning, no tricks, default innodb_buffer_pool_size aside from what my little lab VM allows. Your numbers will differ; the plan shapes won’t.

So what is it, actually

The optimizer MySQL has carried around for decades builds left-deep plans: it lines tables up in a chain, mostly explored greedily, and once an index exists on a join column it leans hard toward nested-loop index lookups. That design was a great fit for OLTP point queries, and it’s exactly why your five-table reporting query sometimes ends up doing two hundred thousand single-row index lookups when a couple of scans and a hash table would have been kinder to the storage layer.

The hypergraph optimizer is not an upgrade to that code path. It’s a second planner sitting beside it. The name comes from how it models your query: tables become nodes, join predicates become edges in a hypergraph (an edge there can connect groups of tables, not just pairs, which is what lets it reason about tricky outer-join and multi-way conditions correctly). On top of that structure it runs a dynamic-programming search over join orders, and crucially it considers bushy plans, where two independently-built join results get joined to each other. The old planner structurally can’t produce most of those shapes.

It also carries its own cost model. You’ll spot it instantly in EXPLAIN, because every operator now shows a cost range:

-> Inner hash join (c.id = o.customer_id) (cost=188516..239268 rows=29980)

The first number is roughly the cost to produce the first row, the second the cost to produce them all. That distinction matters for things like LIMIT, where a plan that’s slow to finish but quick to start is the right answer. The old single-number costs couldn’t express that.

Why a whole second optimizer instead of more patches? Because join ordering is the one thing you can’t retrofit. Twenty years of heuristics are load-bearing in the old code; every fix risks someone’s production plan. A parallel planner lets the new approach mature while the old one keeps doing its job, and lets you, the DBA, pick per session or per query. Starting with MySQL 9.7, that switch is in the server you’re already running.

Turning it on (and off, and on for just one query)

Out of the box it’s dormant. Check your server:

mysql> SELECT @@optimizer_switch LIKE '%hypergraph_optimizer=on%' AS hypergraph_on;
+---------------+
| hypergraph_on |
+---------------+
| 0 |
+---------------+

Flip it for your session only, which is where all experimentation should start:

SET SESSION optimizer_switch = 'hypergraph_optimizer=on';

Same syntax with GLOBAL applies it to new connections, and SET PERSIST survives a restart. Hold off on those until you’ve read the rollout section.

The option I’ve ended up using most is neither: the SET_VAR hint scopes it to a single statement. This is gold for testing, and later for pinning the new planner onto the one reporting query that benefits, without touching anything else:

EXPLAIN FORMAT=TREE
SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */
COUNT(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Pune';
-> Aggregate: count(0) (cost=72208..72208 rows=1)
-> Nested loop inner join (cost=2.28..68363 rows=29980)
-> Filter: (c.city = 'Pune') (cost=10.1..49255 rows=4892)
-> Index scan on c using PRIMARY (cost=0.995..46437 rows=48917)
-> Covering index lookup on o using idx_cust (customer_id = c.id) (cost=0.637..3.91 rows=6.13)

Which brings up the obvious question: how do you know which optimizer produced a plan? The tell is right there in the output. Cost ranges (cost=2.28..68363) mean hypergraph; a single cost number means the classic planner. Notice the example above still chose a nested-loop join. The new optimizer isn’t allergic to nested loops; it just doesn’t default to them. Here the loop genuinely is the cheapest plan, and it said so.

EXPLAIN FORMAT=JSON changes shape too. Instead of the familiar query_block document you get a leaner tree of operators under query_plan, with inputs, access_type and estimated_rows fields. If you have monitoring that parses explain JSON, that’s your first migration task right there.

A playground with enough rows to matter

You can’t judge a join planner on four-row demo tables, so I built a small sales schema and loaded it with enough data that plan choices show up in wall-clock time: 50,000 customers, 1,000 products, 300,000 orders and 900,000 order items.

CREATE TABLE customers (
id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, city VARCHAR(24) NOT NULL,
segment ENUM('consumer','smb','enterprise') NOT NULL, created DATE NOT NULL
);
CREATE TABLE products (
id INT PRIMARY KEY, sku VARCHAR(20) NOT NULL, category VARCHAR(16) NOT NULL,
price DECIMAL(8,2) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL,
status ENUM('open','shipped','cancelled') NOT NULL,
KEY idx_cust (customer_id), KEY idx_date (order_date)
);
CREATE TABLE order_items (
id INT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL,
qty INT NOT NULL, unit_price DECIMAL(8,2) NOT NULL,
KEY idx_order (order_id), KEY idx_prod (product_id)
);

For loading I used the old cross-join trick: a ten-row digits table multiplied against itself into a million-row seq table, then INSERT ... SELECT with a bit of modular arithmetic to spread cities, dates and statuses around. One table as a sample, the rest follow the same pattern:

CREATE TABLE digits (d INT PRIMARY KEY);
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE seq (n INT PRIMARY KEY) AS
SELECT a.d + b.d*10 + c.d*100 + e.d*1000 + f.d*10000 + g.d*100000 AS n
FROM digits a, digits b, digits c, digits e, digits f, digits g;
INSERT INTO orders
SELECT n+1, 1 + (n * 7919) MOD 50000,
DATE_ADD('2024-01-01', INTERVAL n MOD 680 DAY),
ELT(1 + n MOD 10, 'open','shipped','shipped','shipped','shipped',
'shipped','shipped','shipped','shipped','cancelled')
FROM seq WHERE n < 300000;

The one step people skip and then blame the optimizer for: statistics.

ANALYZE TABLE customers, products, orders, order_items;

Both planners read the same InnoDB statistics. Feed them stale numbers and they’ll both lie to you, just in different accents. Every run below was done warm (query executed a couple of times first), so we’re comparing plans, not buffer pool luck.

Reading the new plans without squinting

Let’s take a bread-and-butter reporting query: revenue by customer segment and product category, for shipped orders from one city since July.

SELECT c.segment, p.category, ROUND(SUM(oi.qty * oi.unit_price),2) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.order_date >= '2025-07-01'
AND o.status = 'shipped'
AND c.city = 'Montreal'
GROUP BY c.segment, p.category;

The classic planner, EXPLAIN FORMAT=TREE:

-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=22819 rows=11232)
-> Nested loop inner join (cost=18887 rows=11232)
-> Nested loop inner join (cost=14956 rows=3757)
-> Filter: (c.city = 'Montreal') (cost=4932 rows=4892)
-> Table scan on c (cost=4932 rows=48917)
-> Filter: ((o.order_date >= DATE'2025-07-01') and (o.`status` = 'shipped')) (cost=1.46 rows=0.768)
-> Index lookup on o using idx_cust (customer_id = c.id) (cost=1.46 rows=5.85)
-> Index lookup on oi using idx_order (order_id = o.id) (cost=0.747 rows=2.99)
-> Single-row index lookup on p using PRIMARY (id = oi.product_id) (cost=0.25 rows=1)

Four tables, one chain of nested loops, every join via index lookup. The default planner’s comfort food.

Same query, same session, after SET SESSION optimizer_switch='hypergraph_optimizer=on':

-> Table scan on <temporary> (cost=216016..216388 rows=2237)
-> Aggregate using temporary table (cost=216015..216015 rows=2237)
-> Inner hash join (p.id = oi.product_id) (cost=1057..205543 rows=11779)
-> Nested loop inner join (cost=17.2..202633 rows=11779)
-> Nested loop inner join (cost=38.8..152718 rows=3933)
-> Filter: (c.city = 'Montreal') (cost=10.5..51509 rows=4892)
-> Index scan on c using PRIMARY (cost=0.995..48692 rows=48917)
-> Filter: ((o.order_date >= DATE'2025-07-01') and (o.`status` = 'shipped')) (cost=20.7..20.7 rows=0.804)
-> Index lookup on o using idx_cust (customer_id = c.id) (cost=3.3..20.2 rows=6.13)
-> Index lookup on oi using idx_order (order_id = o.id) (cost=4.24..12.7 rows=3)
-> Hash
-> Index scan on p using PRIMARY (cost=0.691..691 rows=1000)

Three things worth your attention here.

It kept most of the old plan. Customers filtered first, then orders, then items … the new planner independently arrived at the same join order for the lower three tables. When the old plan was fine, the new one tends to agree. That alone should lower your blood pressure about enabling it.

It changed the one piece that deserved changing. The classic plan probes the products primary key once per item row … 17,199 single-row lookups in this run. The hypergraph plan reads all 1,000 products once, builds a hash table, and streams item rows past it. Same answer, far fewer storage engine round trips for that join.

The estimates are still estimates. It predicted 2,237 groups out of this aggregation; the real number is 18. Costing has improved, clairvoyance hasn’t. Keep reading plans with the same skepticism you always have.

On the clock, this one’s nearly a wash on my warm little box: 172 ms classic, 162 ms hypergraph, end to end in EXPLAIN ANALYZE. The section was about reading the output. The next one is about winning.

Where it wins, with a stopwatch

Here’s the query shape that made me sit up. A fat date range instead of a selective city filter … the kind of thing month-end reports are made of:

SELECT c.segment, COUNT(DISTINCT o.id) AS orders,
ROUND(SUM(oi.qty * oi.unit_price),2) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_date >= '2025-06-01'
GROUP BY c.segment;

That date predicate matches 71,883 orders … about a quarter of the table. Watch what the classic planner does with it, EXPLAIN ANALYZE, warm cache:

-> Group aggregate: sum(tmp_field), count(distinct orders.id) (actual time=1111..1155 rows=3 loops=1)
-> Sort: c.segment (actual time=1088..1099 rows=215649 loops=1)
-> Stream results (cost=224955 rows=417116) (actual time=2.14..1024 rows=215649 loops=1)
-> Nested loop inner join (cost=224955 rows=417116) (actual time=2.13..899 rows=215649 loops=1)
-> Nested loop inner join (cost=78965 rows=139522) (actual time=2.1..288 rows=71883 loops=1)
-> Filter: (o.order_date >= DATE'2025-06-01') (cost=30132 rows=139522) (actual time=2.08..104 rows=71883 loops=1)
-> Table scan on o (cost=30132 rows=299796) (actual time=1.96..81.2 rows=300000 loops=1)
-> Single-row index lookup on c using PRIMARY (id = o.customer_id) (cost=0.25 rows=1) (actual time=0.0023..0.00234 rows=1 loops=71883)
-> Index lookup on oi using idx_order (order_id = o.id) (cost=0.747 rows=2.99) (actual time=0.00727..0.00806 rows=3 loops=71883)

Read those loops numbers. It probed the customers primary key 71,883 times, then probed idx_order on the items table another 71,883 times. Each probe is cheap; seventy-two thousand of anything is not. Total: 1.155 seconds, and on a cold cache with real random I/O this pattern gets much uglier than my warm numbers show.

Same query under the hypergraph optimizer:

-> Group aggregate: sum((oi.qty * oi.unit_price)), count(distinct o.id) (cost=1.18e+6..1.27e+6 rows=221) (actual time=516..584 rows=3 loops=1)
-> Sort: c.segment (cost=1.18e+6..1.18e+6 rows=417886) (actual time=472..493 rows=215649 loops=1)
-> Inner hash join (oi.order_id = o.id) (cost=271333..784737 rows=417886) (actual time=104..422 rows=215649 loops=1)
-> Index scan on oi using PRIMARY (cost=0.424..380688 rows=897926) (actual time=0.68..138 rows=900000 loops=1)
-> Hash
-> Inner hash join (c.id = o.customer_id) (cost=62789..223884 rows=139522) (actual time=12.8..95.9 rows=71883 loops=1)
-> Filter: (o.order_date >= DATE'2025-06-01') (cost=0.995..138846 rows=139522) (actual time=0.983..61.3 rows=71883 loops=1)
-> Index scan on o using PRIMARY (cost=0.406..121576 rows=299796) (actual time=0.925..44.4 rows=300000 loops=1)
-> Hash
-> Index scan on c using PRIMARY (cost=0.949..46437 rows=48917) (actual time=0.0858..7.72 rows=50000 loops=1)

584 ms. Half the time, same rows out.

And look at the shape. This is a bushy plan: it built orders ⋈ customers as one hash join (filtered orders on the build side, one clean scan of customers), kept that result in a hash table, and streamed all 900,000 item rows past it in a single pass. Three sequential scans, two hash tables, zero per-row index probes. loops=1 on every line.

This is the philosophical difference in one picture. The classic planner saw an index on customer_id and an index on order_id and couldn’t resist using them row by row. The hypergraph planner priced the alternative … scan everything once, join in memory … and noticed it was cheaper at this selectivity. Nobody hinted anything. It just did the arithmetic.

A quarter of the orders table was the tipping point here. Drop the date filter to a few days and the nested loops win again, and the hypergraph planner happily picks them, as we saw in the Pune example earlier. The point isn’t “hash joins good, loops bad.” The point is the planner now genuinely considers both, on shapes the old one couldn’t even represent.

The honest section: what to watch out for

I went into this expecting a list of “not supported yet” errors to warn you about. I came back nearly empty-handed. On 9.7.4 I deliberately threw the awkward stuff at it with the switch on … recursive CTEs, window functions, GROUP BY ... WITH ROLLUP, JSON_TABLE, LATERAL derived tables, multi-table UPDATE, SELECT ... FOR UPDATE, even full-text MATCH ... AGAINST … and every single one ran and returned correct results. Whatever list of unsupported corners existed in earlier builds, I couldn’t trip over it in an evening of trying. Test your own workload, obviously, but don’t let “it’s probably half-finished” be the excuse.

The real catches are quieter:

Your hint toolbox loses its grip. This is the one that will bite working DBAs. STRAIGHT_JOIN and JOIN_ORDER hints execute without errors or warnings under the hypergraph planner, but in my tests they didn’t change the plan. I asked for orders-before-customers with both; the plan that came back was identical to the unhinted one each time. Years of carefully placed join-order hints in your application? Under the new planner, assume they’re decorative until you verify otherwise. SET_VAR hints work fine … that’s how you control the optimizer choice itself.

Estimates can still be far off. You saw the aggregation estimate of 2,237 groups against an actual 18. A better search over a wrong cardinality model can still pick a wrong plan. The usual medicine applies: fresh statistics, and histograms on skewed columns.

Some queries get slower, and nobody can tell you which in advance. My Montreal query was a coin flip (162 vs 172 ms). Others in my scratchpad came out a few percent behind the classic planner. Different cost model, different tie-breaks; a workload that spent years being unconsciously tuned around the old planner’s habits has landmines in it. That, more than missing features, is why the switch ships in the off position.

Plan-dependent tooling needs a look. Anything parsing EXPLAIN FORMAT=JSON output will meet the new query_plan structure. Anything diffing plan fingerprints will see cost ranges where it expected single numbers.

Rolling it out like an adult

The temptation after seeing a 2x win is SET PERSIST optimizer_switch='hypergraph_optimizer=on' on a Friday. Resist. Here’s the sequence I’d actually run in a real shop.

1. Replay your top queries in a lab or on a replica, session-scoped. Pull the heavy hitters from performance_schema.events_statements_summary_by_digest (sort by SUM_TIMER_WAIT, take the top 30 or so). Run each with EXPLAIN ANALYZE twice per optimizer, warm, and write the numbers down. You’re building a simple three-column table: digest, classic time, hypergraph time.

SELECT DIGEST_TEXT, COUNT_STAR,
ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 30;

2. Pin the winners with SET_VAR, leave everything else alone. The per-statement hint means you don’t need a server-wide decision to start collecting the benefit. The month-end report that went from 1.2 s to 0.6 s gets its hint; the OLTP path stays on the planner it’s been happy with for years:

SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */
c.segment, COUNT(DISTINCT o.id), ...

3. Graduate to session-level for whole workloads that profit. Reporting users, ETL accounts and read-only analytics connections are natural candidates … set it in the connection setup of those jobs, not globally. Your application’s point lookups gain nothing from a smarter join search; they don’t need to be part of the experiment.

4. Only then consider GLOBAL/PERSIST, once a full business cycle (including that one monster quarter-close query someone wrote in 2019) has run clean. And keep the escape hatch written down where 3 AM you can find it:

SET GLOBAL optimizer_switch = 'hypergraph_optimizer=off';

5. Re-run the comparison after every upgrade. Both optimizers keep evolving, and a plan that was a wash last release may be a win or a regression in the next. The ten-minute digest replay is cheap insurance.

One process note: because the switch lives in optimizer_switch, it plays nicely with everything you already use for staged changes … per-user init_connect tricks, proxy-level session settings, connection pool configs. You almost certainly already have the machinery to roll this out gradually. Use it.

I’ve spent a good part of my career negotiating with query optimizers, and the negotiation usually goes one way: the planner has a blind spot, and I paper over it with hints, rewrites, or the occasional covering index built purely to bribe it. What struck me most in this evening of testing wasn’t the 2x number. It was the Pune query … the one where the hypergraph planner looked at a nested loop, agreed with the old optimizer completely, and moved on. A planner that knows when not to be clever is the one I’ll eventually trust with the global switch.

We’re not at “flip it everywhere” yet. The hint behaviour alone means any shop with a history of join-order surgery needs to retest before leaning on it. But the direction is unmistakable: MySQL now has a planner that can represent the plans analytic queries actually need, and it’s one SET statement away on a stock server.

If you want to try this on your own workload, the whole experiment fits in a lunch break:

  • Build or borrow a dataset with real row counts; ANALYZE TABLE everything.
  • Pull your top 30 digests from performance_schema and race them: classic vs SET_VAR hypergraph, EXPLAIN ANALYZE, warm runs.
  • Pin wins per query with SET_VAR, note the losses, and check whether any of your join hints still do anything.
  • Re-race after your next upgrade.

All plans and timings in this post came from a stock MySQL 9.7.4 on a modest lab VM … expect your absolute numbers to differ, and the I/O-bound gaps to be wider than mine. If you run the comparison on your own workload, I’d genuinely like to hear which way it went: the comments on fatdba.com are open, and the counterexamples are usually where the interesting posts come from.

Happy flipping. Carefully.


Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

Oracle Support New AI-Powered Search

Posted by FatDBA on April 8, 2025

In a major upgrade to the user experience, Oracle Support has introduced a new AI-powered search feature on its support websites … This enhancement aims to make information discovery faster, smarter, and more relevant, solving one of the most common frustrations: finding the right document among millions.

Oracle Support’s new search interface is now powered by AI, providing:

  • Faster Search Results: As you start typing your query, the AI intelligently predicts what you’re looking for.
  • Relevant Suggestions: It offers instant access to related Knowledge Base articles, Community discussions, and Official Documentation.
  • Context-Aware Results: Instead of just matching keywords, the search engine understands the intent behind your query.
  • Cleaner Interface: A streamlined design shows results by category: Knowledge Base | Community | Documentation.

In the screenshot example (see above), when you search for “How to Find the Oracle BI Publisher version,” the AI doesn’t just return a random list … it shows an exact match article explaining where to find version info, depending on whether you use a standalone install or an embedded one.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Oracle 23ai Tip: Use SESSION_EXIT_ON_PACKAGE_STATE_ERROR to Prevent Silent Data Corruption

Posted by FatDBA on December 28, 2024

Oracle Database 23ai introduces a new parameter, SESSION_EXIT_ON_PACKAGE_STATE_ERROR, designed to enhance session management and prevent potential data corruption by enforcing a hard session exit when the session state becomes invalidated.

Why SESSION_EXIT_ON_PACKAGE_STATE_ERROR Matters ?

In typical Oracle database environments, stateful PL/SQL packages, MLE modules, or environments may be modified while sessions actively use them. This can lead to errors such as:

  • ORA-04068: Can occur when a PL/SQL package body is recompiled, invalidating the session state.
  • ORA-4106 / ORA-4107: Can be raisrd when an MLE module or environment is altered via DDL, invalidating the session.

By default, the session remains active and throws an error when the invalid package or module is called. However, many applications may not properly handle these errors, leading to silent data corruption or unexpected behavior.

The SESSION_EXIT_ON_PACKAGE_STATE_ERROR parameter mitigates this risk by forcing an immediate session exit instead of raising an error.

Some of the benefits of using the parameter.

  • Prevents Data Corruption: By terminating sessions with invalid state, the risk of silent data corruption is reduced.
  • Simplifies Error Handling: Many applications are better at handling session disconnects than catching specific errors like ORA-04068.
  • Consistency Across Sessions: Ensures that all sessions dealing with modified packages or MLE modules are treated consistently, minimizing inconsistencies.

How SESSION_EXIT_ON_PACKAGE_STATE_ERROR Works

When SESSION_EXIT_ON_PACKAGE_STATE_ERROR is set to TRUE, the following behavior is enforced:

  1. PL/SQL Package Modification:
    • If a stateful PL/SQL package is modified, any active session that tries to invoke the package receives ORA-04068. With this parameter set to TRUE, the session exits immediately instead of raising the error.
  2. MLE Module or Environment Modification:
    • If an MLE module or environment is modified via DDL, active sessions receive ORA-4106 or ORA-4107. With SESSION_EXIT_ON_PACKAGE_STATE_ERROR = TRUE, these sessions are forcibly disconnected.
  3. Application Handling:
    • Most applications are designed to capture session disconnects and reestablish connections, streamlining recovery from session invalidation.

Use Cases

  • High-Availability Environments: In systems where continuous uptime is critical, preventing data corruption is paramount.
  • Distributed Applications: Applications spread across multiple environments that frequently modify PL/SQL packages or MLE modules benefit from session termination to maintain data integrity.
  • Oracle RAC Deployments: Different instances in an Oracle RAC environment can independently configure this parameter, allowing fine-grained control based on workload requirements.

Configuring SESSION_EXIT_ON_PACKAGE_STATE_ERROR:

Examples:
ALTER SYSTEM SET SESSION_EXIT_ON_PACKAGE_STATE_ERROR = TRUE;
ALTER SESSION SET SESSION_EXIT_ON_PACKAGE_STATE_ERROR = TRUE;
ALTER SYSTEM SET SESSION_EXIT_ON_PACKAGE_STATE_ERROR = TRUE SCOPE = SPFILE;

Considerations

  • Default Behavior: By default, this parameter is set to FALSE, meaning sessions will raise errors rather than exit.
  • Testing and Validation: Test this configuration in lower environments to ensure application compatibility.
  • Session Management: Monitor session disconnects to ensure that forced exits do not disrupt critical workflows.

Conclusion

SESSION_EXIT_ON_PACKAGE_STATE_ERROR is a powerful new feature in Oracle Database 23ai that enhances session management by enforcing session termination on package or module state invalidation. By using this parameter, Oracle environments can significantly reduce the risk of data corruption and streamline error handling processes across diverse applications. Whether managing PL/SQL packages or MLE modules, this parameter offers greater control and reliability for database administrators and developers both.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , , , | Leave a Comment »

How I see vector indexing and search in Oracle 23ai

Posted by FatDBA on November 1, 2024

Hi everyone!

It’s been a while since Oracle 23c (or 23c AI) was released, and there’s already a ton of great content out there on one of its coolest new features: Vector Indexes and Searches. This feature has opened up exciting possibilities for AI-driven applications, from advanced recommendations to similarity searches within databases. Now, it’s finally my turn to dive in and put it to the test!

Vector search in Oracle 23c AI is a feature designed for searching, comparing, and finding similarities between data represented as vectors (numerical arrays). This capability is particularly useful for AI and machine learning applications, where data like images, text, and products are often encoded as vector embeddings. Oracle’s vector search enables the database to efficiently process these embeddings, allowing users to perform similarity-based queries within the database. Oracle’s vector search in 23c AI makes it possible to bring advanced similarity-based search and recommendation capabilities directly into the database, facilitating powerful new applications that leverage the latest in AI and machine learning.

The base or the foundation of this feature is VECTOR data type, a powerful tool for storing and managing vector embeddings directly within the database. This capability allows Oracle to support advanced AI and machine learning applications, such as similarity searches and recommendation engines, by efficiently handling multi-dimensional arrays that represent data features, semantics, or other complex characteristics.

Key Advantages of the VECTOR Data Type

  1. Compact Multi-Dimensional Array Storage:
  • VECTOR allows fixed-length arrays of numbers (often floating-point) to be stored compactly in a single column. A 100-dimensional vector, for instance, might represent an embedding for text, an image, or a user profile, capturing nuanced data in a manageable form.
  1. Efficient Similarity Calculations:
  • With Oracle 23c AI, VECTOR data can be processed for similarity searches directly in the database. Functions like VECTOR_DISTANCE make it easy to calculate similarity scores or distances between vectors, enabling Oracle to quickly identify similar items without needing external tools or complex data transformations.
  1. Tailored for AI and Machine Learning:
  • VECTOR is ideal for applications that require semantic searches, such as finding similar products, media, or documents, and for AI-driven recommendations. This makes it well-suited for Natural Language Processing (NLP), recommendation engines, and image recognition tasks.
  1. Support for Various Distance Metrics:
  • Oracle’s VECTOR data type is compatible with common distance metrics, such as Euclidean and cosine similarity, which measure how close or far two vectors are from each other. This enables efficient and flexible similarity calculations, essential for applications relying on vector comparisons.
  1. Scalability with Vector Indexing:
  • Oracle 23c AI allows for VECTOR data indexing through VECTOR_INDEX, optimizing performance for vector-based queries. This makes real-time similarity searches on large datasets feasible, supporting applications that need quick response times, such as real-time AI insights and personalized recommendations.

The VECTOR data type in Oracle 23c AI is a robust addition for data scientists and developers, enabling high-performance similarity searches, efficient data storage, and a foundation for complex AI and machine learning workloads directly within the Oracle database.

Alright, so now when the stage is set, lets stay tuned as I explore how Vector Indexes and Searches can bring a whole new dimension to Oracle 23c. Here’s a detailed, step-by-step demo to help you showcase vector search and indexes in Oracle Database 23c AI, which will be ideal for your blog post. This example will simulate a product recommendation system using vector embeddings to represent product features.

1. Set Up the Environment

Ensure you’re using Oracle 23c AI, as vector indexes and the VECTOR data type are new features available in this release.

2. Create a Table with Vector Embeddings

In this example, each product is represented by a 10-dimensional vector embedding. This vector could represent a combination of product attributes, like color, category, brand, and other numerical encodings.

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    product_description VARCHAR2(255),
    product_embedding VECTOR(10) -- Vector with 10 dimensions
);

3. Insert Sample Data

Populate the table with sample products and their vector embeddings. Here’s an example where each vector represents the product’s characteristics:

INSERT INTO products (product_id, product_name, product_description, product_embedding) VALUES 
    (1, 'Laptop A', 'High-performance laptop for professionals', VECTOR(0.5, 0.2, 0.7, 0.1, 0.4, 0.3, 0.8, 0.6, 0.9, 0.3)),
    (2, 'Smartphone B', 'Latest smartphone with advanced features', VECTOR(0.6, 0.1, 0.8, 0.5, 0.2, 0.7, 0.4, 0.3, 0.6, 0.1)),
    (3, 'Tablet C', 'Compact and powerful tablet for multimedia', VECTOR(0.4, 0.9, 0.5, 0.7, 0.3, 0.6, 0.2, 0.8, 0.1, 0.9)),
    (4, 'Laptop D', 'Affordable laptop with reliable performance', VECTOR(0.7, 0.6, 0.4, 0.9, 0.8, 0.2, 0.5, 0.3, 0.1, 0.4)),
    (5, 'Headphones E', 'Noise-canceling headphones with high fidelity', VECTOR(0.3, 0.8, 0.1, 0.9, 0.6, 0.4, 0.7, 0.2, 0.5, 0.6));

4. Create a Vector Index for Efficient Searches

Creating a vector index allows Oracle to search these embeddings efficiently based on similarity, which is ideal for recommendation systems, image retrieval, and text similarity searches.

CREATE INDEX product_vector_idx ON products (product_embedding)
    INDEXTYPE IS VECTOR_INDEX;

5. Perform a Vector Similarity Search

With the vector index in place, you can now perform searches to find products that are similar to a given vector. For example, suppose a user has shown interest in products that correspond to a vector representation of [0.6, 0.2, 0.5, 0.4, 0.3, 0.5, 0.7, 0.8, 0.2, 0.6]. You can use this vector to find products with similar embeddings.

SELECT product_name, product_description, product_embedding
FROM products
ORDER BY VECTOR_DISTANCE(product_embedding, VECTOR(0.6, 0.2, 0.5, 0.4, 0.3, 0.5, 0.7, 0.8, 0.2, 0.6)) ASC
FETCH FIRST 5 ROWS ONLY;

In this query:

  • The VECTOR_DISTANCE function calculates the similarity (distance) between the target vector and each product’s embedding. Sorting by VECTOR_DISTANCE allows you to retrieve the closest matches.
  • This example fetches the top 5 most similar products.

6. Use Case: Personalized Product Recommendation

This setup is ideal for applications such as a product recommendation engine. Suppose you have a user profile represented as a vector, you could use a vector search to find products similar to that profile, offering highly relevant recommendations.

7. Handling Updates to Vector Embeddings

If a product’s features change, you may need to update its embedding. Here’s how you could update the vector for a specific product:

UPDATE products
SET product_embedding = VECTOR(0.7, 0.3, 0.9, 0.5, 0.6, 0.4, 0.8, 0.2, 0.3, 0.5)
WHERE product_id = 2;

8. Advanced Use Case: Nearest Neighbor Search with Filtering

Let’s extend the search by filtering for specific criteria. For example, if you only want to recommend products that contain “Laptop” in the name, you can add a WHERE clause to the query.

SELECT product_name, product_description, product_embedding
FROM products
WHERE product_name LIKE '%Laptop%'
ORDER BY VECTOR_DISTANCE(product_embedding, VECTOR(0.6, 0.2, 0.5, 0.4, 0.3, 0.5, 0.7, 0.8, 0.2, 0.6)) ASC
FETCH FIRST 5 ROWS ONLY;

9. Explain the Vector Distance Metric

For your blog, it’s useful to explain the concept of vector similarity:

  • Vector Distance (Similarity): This metric measures how similar or dissimilar two vectors are. A smaller distance implies a higher similarity. Oracle 23c AI’s VECTOR_DISTANCE function calculates this distance to identify closely related items, making it ideal for applications in recommendations, content search, and clustering.

10. Additional Notes for Optimization and Use Cases

  • Index Maintenance: Like other indexes, a vector index should be maintained for performance. Ensure to monitor the index usage and rebuild if necessary for large datasets.
  • Scalability: Oracle 23c AI’s vector search is optimized for large-scale deployments, making it suitable for extensive recommendation systems, content-based searches, and personalized user experiences.

This detailed walkthrough should provide a comprehensive demo for your blog post, showcasing Oracle 23c AI’s new vector search and indexing features. Let me know if you need any additional details or further customization!

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , , , , , , | Leave a Comment »

Adapting Data Pump for the Cloud: Trailer Blocks in 23ai

Posted by FatDBA on November 1, 2024

Oracle Database 23c AI introduces a new trailer block format for Data Pump dump files, moving away from the traditional header block approach used in earlier versions like 21c. This new structure is designed specifically to support cloud storage environments, particularly within Oracle Cloud Infrastructure (OCI), and makes it easier to manage and transfer Data Pump files to object stores.

What’s Different with Trailer Blocks?

Trailer blocks are written only once the export process completes, contrasting with header blocks, which required upfront metadata and continuous updates throughout the job. This deferred approach means trailer blocks accumulate necessary metadata during the export process, allowing it to be stored in a more cloud-compatible format at the end. The switch to trailer blocks by default enables seamless storage of dump files in cloud object stores and reduces the limitations associated with local-only file systems.

Note : Trailer block files are compatible only with Oracle Database 23c AI and later. To ensure backward compatibility for previous Oracle versions, you can use the VERSION parameter to revert to the legacy header block format.

How to Use Trailer Blocks with Cloud Object Storage

When running Data Pump in Oracle 23c AI, trailer blocks allow straightforward use with object storage by configuring these essential parameters:

  1. Authenticate with Cloud Credentials: Ensure you have a credential object in place to establish the required connection with your object store.
  2. Configure DUMPFILE and CREDENTIAL Parameters: List export files using DUMPFILE and specify the credential to direct these files to the cloud.
  3. Set Log File Storage: Use the DEFAULT_DIRECTORY parameter to control log file storage. Failing to set up a valid cloud credential for object store URIs will lead to errors in dump file writing.

Steps for Cloud Storage Access

To export files directly to a cloud object store, ensure you have cloud credentials stored in an Oracle Wallet, with the location specified in your sqlnet.ora configuration. You will also need to add a certificate for authentication using orapki:

orapki wallet add -wallet <path_to_wallet> -trusted_cert -cert <certificate_file> -pwd <wallet_password>

This setup allows the Data Pump to use the CREDENTIAL parameter with the export command, establishing secure access to cloud storage.

Header Blocks vs. Trailer Blocks

In Oracle Database versions prior to 23c, Data Pump files used header blocks, where metadata was embedded at the start of each dump file. This format was limited to local storage and required metadata updates during export, which could be resource-intensive.

With Oracle Database 23c AI, trailer blocks are the default. This approach shifts metadata to the end of the file, streamlining the process for cloud storage. By using the VERSION parameter, you can control whether to retain the newer trailer block format or revert to header blocks based on compatibility needs.

Types of Trailer Blocks in Oracle 23c AI

Oracle 23c AI introduces two types of trailer blocks for handling Data Pump files:

  • Disk-Based Trailer Blocks: Directly written at the end of each primary table’s data in the dump file, providing a compact storage format.
  • Table-Based Trailer Blocks: Stored externally within the export’s primary table, conserving space by omitting individual trailer blocks for each data object. This approach efficiently manages storage while maintaining essential metadata for cloud-based and local export jobs.

Trailer blocks make Data Pump in Oracle Database 23c AI a flexible and cloud-ready solution, enabling efficient data movement across hybrid and cloud infrastructures. This updated approach supports larger cloud-based workflows by improving performance and storage efficiency for Data Pump exports.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , , , , | Leave a Comment »