How to use DBMS_STATS.DIFF_TABLE_STATS functions

In 11g, Oracle introduced the DBMS_STAT.DIFF_TABLE_STATS functions to help you compare two sets of statistics for a table along with all its dependent objects (indexes, columns, partitions).

There are three versions of this function depending on where the statistics being compared are located:

  • DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY (compares statistics for a table from two timestamps in the past)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING (compares pending statistics and statistics as of a timestamp or statistics from the data dictionary)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB (compares statistics from a user statistics table and the data dictionary, from two different user statistics tables, or a single user statistics table using two different STATSIDs)

The functions return a report that has three sections:

  1. Basic table statistics
    The report compares the basic table statistics (number of rows, blocks, etc.).
  2. Column statistics
    The second section of the report examines column statistics, including histograms.
  3. Index Statistics
    The final section of the report covers differences in index statistics.

Statistics will only be displayed in the report if the difference in the statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the functions (PCTTHRESHOLD); the default value is 10%. The statistics corresponding to the first source, typically the current table stats in the data dictionary, will be used to compute the differential percentage.

The functions also return the MAXDIFFPCT (a number) along with the report. This is the maximum percentage difference between the statistics. These differences can come from the table, column, or index statistics.

Let’s look at an example.
Continue reading “How to use DBMS_STATS.DIFF_TABLE_STATS functions”

Five things you might not know about Oracle Database

Today I had an opportunity to catch up with old friends and colleagues when my local Oracle User Group –  Northern California Oracle User Group (NoCOUG), held its first in-person event in three years.

In my session, we looked at five things you might not know about the Oracle Database or might have forgotten. For each topic, I explained the functionality and demonstrated the benefits using real-world examples. The topics covered apply to anyone running Oracle Database 11g and up, including Standard Edition, with only a few minor exceptions.

Many folks asked for the slides after the session, so I thought it would be helpful to post them here.

 

 

How to watch Oracle CloudWorld 2022 Technical Sessions

Just over a month ago, we said goodbye to Las Vegas and Oracle CloudWorld 2022.

If you couldn’t make it in person or couldn’t get to all the sessions you wanted, don’t worry, as Oracle has made over 50 critical database technical sessions available on YouTube.

Here are some of the sessions I think you might find worth a watch:

Andy Mendelsohn’s Solution Keynote on Oracle Database 23c. In his keynote, Andy shared details on Oracle Database 23c, the next long-term support release for the database. 23c was code-named App Simple because the primary goal of this release was to assist developers in building next-generation mission-critical, high-performance database apps.

Juan Loaiza’s Solution Keynote on JSON Relational Duality. Juan’s session focuses on one of the marque features of Oracle Database 23c, JSON Duality Views, which promises to provide use-case simplicity of JSON with multi-use case power of relational. With the help of Tirthankar Lahiri and Beda Hammerschmidt, Juan explains how this new approach solves the age-old problem of object-relational mismatch.

Jeff Smith’s technical session Tips and Tricks for the Oracle Database Developer[LRN1523], where Jeff explores Which features of Oracle SQL Developer, Oracle SQL Developer Web, Oracle SQLcl, and Oracle REST Data Services will save database developers the most time?

Vlad Haprian’s session explains Operational Property Graphs [LRN3789] and how they are part of the standard SQL in Oracle Database 23c. He includes some great, simple-to-follow examples too.

If you are looking for more information on our in-database microservices support, you will want to check out Deepak Goel’s session on Distributed Transactions for Kubernetes Microservices [LRN3484].

As always, there were several great sessions by Conor McDonald, including one where he shared his Five-Step Guide to SQL Tuning [LRN3790].

If performance-related sessions are what you are after, then you might want to check out my session on Harnessing the Power of Oracle Database Optimizer Hints[LRN1479] and Nigel Bayliss’s session on Automatic Indexing.

If you are interested in knowing, How to Write Great SQL That’s Easy to Maintain, check out Chris Saxon and Alex Nuijten’s session.

Finally, if you want more details on what to expect in 23c and the features that should make your life a lot easier, you should check out Gerald Venzl’s session on Oracle Database application development: what’s new, what’s next?

And don’t forget to save the date for CloudWorld 2023, which is happening September 18–21 in Las Vegas.

How to do a Fuzzy Text Search on JSON #JoelKallmanDay

More and more apps I deal with store data as JSON documents in the Oracle Database. It is exceptionally convenient for the developers but doesn’t always make it easy to know exactly what data we have stored.

The good news is that Oracle offers multiple ways to help you understand precisely what data you have stored in those JSON documents. For example, you can use the built-in JSON Data Guide, which will trawl through all your documents and return a list of all the attributes you have stored.

But suppose you are interested in searching through your documents and only returning those that contain a particular word, value, or a variation thereof. In that case, you will want to take advantage of Oracle’s fuzzy text search or approximate string matching.

Imagine we have a table that stores movie reviews as JSON documents. I’m doing this demo in 19c, so I’m using a VARCHAR2 column, but from 21c onwards, you can use a JSON column.

CREATE TABLE movie_reviews( 
                     title varchar2(200),
                     cust_id NUMBER(26), 
                     cust_reviews varchar2(32000)
                     CONSTRAINT cr_is_json CHECK (cust_reviews IS JSON));

If you really want a JSON column in 19c, you can take advantage of this trick I learned from @Connor_mc_d.

 CREATE TABLE movie_rewiews( title varchar2(200), 
                             cust_id NUMBER(26), 
                             cust_reviews BLOB,
                             CHECK (cust_reviews IS json format oson));

Each review document contains details on the movie id, the star rating, and the review.

{“movie_id”: 5641,
“star_rating”: 1,
"Feedback":" Loved the tv show, but hated the movie. I am so disappointed."
}

We have been asked to find all the reviews that contain the word disappoint or variations of it. To facilitate a fuzzy text search, we need to create a text-based index on the feedback column inside the review documents.

CREATE SEARCH INDEX review_search_ind ON movie_reviews(cust_reviews) FOR JSON;

Once the index is created, we can run a fuzzy text search using the following query:

SELECT m.title, m.cust_reviews.feedback AS customer_review
FROM   movie_reviews m
WHERE  JSON_TEXTCONTAINS(m.cust_reviews, '$.feedback', 'fuzzy((disappoint))');

This results in the following entries being returned.

TITLE                    COMMENTS
-------------------- ----------------------------------------------------------------
Can You Ever Forgive Me? This movie was so disappointing
Top Gun                  Tom Cruise never disappoints. Definitely worth a watch.
Vice                     Perry’s performance in this movie is just so disappointing
Baywatch                 Loved the tv show but hated the movie. I’m so disappointed.
La La Land               Rent this movie you won’t be disappointed!
Batman                   Complete Disappointment

Alternatively, you can use the abbreviated syntax, which will return the same results as above:

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '?disapoint');

You can also use the stem search operator $. That will match verb forms sharing the same stem, so $disappoint will match “disappointing,” “disappointed,” and “disappoints,” but not “disappointment.”

SELECT m.cust_reviews.feedback AS comments
FROM movie_reviews m
WHERE JSON_TEXTCONTAINS(cust_reviews, '$.feedback', '$disapoint');

This blog was made possible by the lovely Roger Ford, the product manager for Oracle Text and JSON, who has taught me everything I know about text searches in the Oracle Database.

Looking forward to Oracle CloudWorld

There are only 2 days to go until Oracle CloudWorld in Las Vegas, the largest gathering of Oracle customers, partners, developers, and technology enthusiasts of the year! That’s right, folks; we are finally back in person.

Of course, the database team will be there. You will have multiple opportunities to meet with us in one of the informative executive keynotes, technical sessions, customer sessions, or hands-on labs.

But what can you expect from this year’s event?

To help whet your appetite, here are some of the things I’m looking forward to at this year’s CloudWorld.

Reconnecting with the community

If you have ever been to an Oracle OpenWorld conference, you will know there are always plenty of chances to network throughout the day and into the evening. But this year, after two years of pandemic living, I reckon it’s more important than ever to spend time face to face with friends and colleagues to catch up and to learn from one another. That’s why I’m looking forward to networking on the expo floor, meeting customers in the Executive Briefing Center (formerly CVC), hanging out in our Expert Lounges, and kicking back with friends and customers at the legendary Oracle Party featuring the Steve Millar Band.

Getting Inspired

There is nothing quite like attending an Oracle Keynote by Larry Ellison to reignite my love of Oracle technologies. Larry never fails to deliver insights on our end-to-end data strategy and the latest on databases, apps, analytics, and our cloud services.
There will be three other Keynotes along with Larry’s this year featuring Oracle customers and leaders offering additional insights and inspiration on tech and apps.

I’m particularly keen to see Andy Mendelsohn and Juan Loaiza’s Solution Keynotes. Andy will share details on Oracle Database Directions on Tuesday, October 18th, at 12:15 pm. While Juan will be introducing new capabilities that are Revolutionizing Objects, Documents, and Relational Development on Wednesday, October 19th at 11:15 am.

Boosting my knowledge

This year there are even more ways to boost your knowledge and build your skills at CloudWorld via technical deep dives, panel discussions, hands-on labs, and more.

With hundreds of in-depth learning sessions across nine different tracks, no matter your role or where you plan to run your Oracle systems (in the public cloud, on-premises, hybrid cloud, etc.), we will have all the product updates, technology deep-dives, and best practices sessions you need. Choose a track based on your focus areas to get information, advice, and best practices tailored to your specific needs and goals.

I plan to spend most of my time in the database and developer tracks. You’ll hear about new product enhancements and what’s next for the Oracle Database and be able to attend deep-dive sessions on Machine Learning, Graph, Spatial, JSON, REST, APEX, MAA, Exadata, and App Dev. These sessions will be jam-packed with technical details, tips, and tricks you can take home and apply immediately, not marketing hype. You can check out the entire searchable catalog online.

I’m particularly looking forward to sessions on my favorite topic, the Oracle Optimizer.  There will be sessions by Nigel Bayliss, the Optimizer product manager, covering the Oracle Database 19c Optimizer (Thursday at 9 am) and the new Automatic Indexing capabilities (Tuesday at 4:40 pm). There will even be a session on Optimizer Hints given by little ole me (Wednesday at 3:45 pm)!

As well as in-depth technical sessions delivered by Oracle experts and Aces, you will also get an opportunity to try out all the technological innovations you hear about in our Hands-on-Labs.

Putting my knowledge to the test

One of the other things I’m looking forward to participating in at Cloud world is the OCI certifications.  You can earn a recognized OCI certification credential with on-site preparation classes and exams. I’ll definitely try my hand at the updated Oracle Autonomous Database 2022 Certified Professional exam.

Visiting Las Vegas again

Las Vegas is the ultimate home to world-class entertainment, cuisine, and nightlife. If you are a foodie like me, a trip to Las Vegas would not be complete without checking out some of the incredible restaurants they have on offer. Whether you want a great steak, sushi, or a vegetarian feast, Las Vegas has you covered. After all, you will need some fortification after spending all day learning and trying your hand at the latest Oracle technology.

Regardless of what you look forward to at Oracle CloudWorld, I hope to see you there!

If you can’t make it in person, be sure to sign up for a digital pass, so you don’t miss any of the action. I have a code you can use to get a free Cloud World Digital Pass! Simple use Comp Code: OCWDIGITAL.

Register for Oracle Cloud World today!

SQL Tuning: How to fix a SQL statement that isn’t getting partition pruning

Partitioning is one of the most powerful tools at your disposal when managing large volumes of data and improving the performance of queries that would otherwise scan and filter lots and lots of data.

However, it can be tricky to determine why it isn’t helping when you thought it should.

Typically partitioning improves query performance by ensuring only the partition(s) needed to answer the business user’s query will be scanned rather than the entire table.

But how can you tell if you got partition pruning in an execution plan? Or worse yet, how do you determine why you didn’t get partitioning pruning when you were expecting it.

In the video below, I share the steps I use to determine if partition pruning has occurred and what to look at and correct if you don’t automatically get the partition pruning you were expecting.

This blog post is part of a series on SQL Tuning. I shared some simple steps to help you tune a SQL Statement using the wrong Join Type in part one. While part two deals with how to tackle a problem where the optimizer picks the wrong index, and part three shares tips on how to fix statements where the Optimizer chooses a nested loop join instead of a hash join.

Happy Tuning!

 

SQL Tuning: How to fix a SQL statement that picks a Nested Loop instead of a Hash Join

At one point or another during your career, you will face a situation where the optimizer picks the wrong join method. As tempting as it is to brute-force the plan you want via a hint, it’s always better to understand why the Optimizer made a decision and provide the necessary information to allow the Optimizer to select the plan you want by default.

In this short video below, I explain how the Optimizer determines the join method based on the cardinality of the table on the left-hand side of the join. I also provide a simple set of steps to help you identify the common problems that can cause an incorrect join method to be used and guidance on how to supply the necessary information, so the Optimizer will automatically select the appropriate join method.

Adaptive joins, introduced in 12c, can help address some cases where the Optimizer selects the wrong join method but not in all cases. It’s always better to know how to save yourself rather than relying on the built-in safety net.

Happy Tuning!

Five simple ingredients for deploying and running mission-critical databases

At the recent Oracle Database World event, I had the pleasure of presenting the general session for the mission-critical track, along with my colleague Ashish Ray. In our session, I outline the five essential ingredients you need to deploy and operate a mission-critical database, regardless of what workload you plan to run.

Each of the subsequent technical sessions provides a deeper dive into each ingredient.  These sessions include:

  1. Transparent Performance with Exadata presented by Kodi Umamgeswaran
  2. Oracle Maximum Availablity Architecture delivered by Markus Michalewicz
  3. Simplifying App Dev with RAC given by Anil Nair
  4. Complete Database security in 4 simple steps presented by Russ Lowenthal
  5. How to evolve Database Apps entirely online presented by Ludovico Caldara

If you didn’t have an opportunity to join the live event, you can now access all the sessions on YouTube. Below is a link to the entire mission-critical playlist.

Hopefully, watching this playlist will give you valuable tips and technologies to deploy a mission-critical database successfully.

SQL Tuning: How to determine why the Optimizer is picking the wrong index

One of the most common SQL Tuning challenges you will encounter with enterprise applications is a SQL statement where the Optimizer picks the wrong index. As tempting as it is to brute-force the plan you want via an index hint, it’s always better to understand why the Optimizer made the decision and provide the necessary information and access structures to allow the Optimizer to select the plan you want by default.

In the short video below, I explain how the Optimizer costs each of the index accesses available to it and provide you with a simple set of steps to help you identify these types of problems and guidance on how to create indexes so the Optimizer will automatically select them.

This post is part two of a series of blog posts on SQL Tuning. In part one, I shared some simple steps to help you tune a SQL Statement using the wrong Join Type.

SQL Tuning: How to tune a SQL Statement with the wrong Join Type

When it comes to SQL Tuning, I don’t typically recommend folks add one-off hints or look for magical underscore parameters to help improve their query performance.

Instead, I like to share some simple steps to

  1. Accurately characterise the problem
  2. Determine and apply a solution
  3. Measure the effectiveness of that solution

But instead of talking about my approach, I thought it would be more beneficial to show you how I do it. So in the video below, you will see the exact steps I used to determine what was causing a Sales Report to run slowly and the two alternative approaches you can take to resolve similar problems.

%d bloggers like this: