Oracle University Podcast

In this episode, Lois Houston and Nikita Abraham discuss the basics of MySQL installation with MySQL expert Perside Foster. Perside covers every key step, from preparing your environment and selecting the right software, to installing MySQL, setting up secure initial user accounts, configuring the system, and managing updates efficiently.   MySQL 8.4 Essentials: https://mylearn.oracle.com/ou/course/mysql-84-essentials/141332/226362 Oracle University Learning Community: https://education.oracle.com/ou-community LinkedIn: https://www.linkedin.com/showcase/oracle-university/ X: https://x.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Kris-Ann Nansen, Radhika Banka, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative  podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Welcome back to another episode of the Oracle University Podcast. I’m Nikita Abraham, Team Lead of Editorial Services with Oracle University, and I’m joined by Lois Houston, Director of Innovation Programs. Lois: Hi everyone! In our last episode, we spoke about Oracle MySQL ecosystem and its various components. We also discussed licensing, security, and some key tools. What's on the agenda for today, Niki? 00:52 Nikita: Well Lois, today, we’re going beyond tools and features to talk about installing MySQL. Whether you're setting up MySQL for the first time or looking to understand its internal structure a little better, this episode will be a valuable guide.  Lois: And we’re lucky to have Perside Foster back with us. Perside is a MySQL Principal Solution Engineer at Oracle. Hi Perside! Say I wanted to get started and install MySQL. What factors should I keep in mind before I do that?  01:23 Perside: The first thing to consider is the environment for the database server. MySQL is supported on many different Linux distributions. You can also run it on Windows or Apple macOS.  You can run MySQL on a variety of host platforms. You can use dedicated servers in a server room or virtual machines in a data center. Developers might prefer to deploy on Docker or Kubernetes containers. And don't forget, you can deploy HeatWave, the MySQL cloud version, in many different clouds. MySQL has great multithreading capability. It also has support for Non-Uniform Memory Access or NUMA. This is particularly important if you run large systems with hundreds of concurrent connections. MySQL storage engine, InnoDB, makes effective use of your available memory. It stores your active data in a buffer pool. This greatly improves access time compared to reading straight from disk. Of course, SSDs and other solid state media are much faster than hard disks. But don't forget, MySQL can make full use of that performance benefit too. Redundancy is very important for the MySQL server. Hardware with redundant power supply, storage media, and network connections can make all the difference to your uptime. Without redundancy, a single point of failure will bring down the server if it fails. 03:26 Nikita: Got it. Perside, from where can I download the different editions of MySQL? Perside: Our most popular software is the MySQL Community Edition. It is available at no cost for mysql.com for many platforms. This version is why MySQL is the most popular database for web application. And it is also open source. MySQL Enterprise Edition is the commercial edition. It is fully supported by Oracle. You can get it from support.oracle.com as an Oracle customer. If you want to try out the enterprise features but are not yet a customer, you can get the latest version of MySQL as a trial edition from edelivery.oracle.com. Because MySQL is open source, you can get the source code from either mysql.com or GitHub. Most people don't need the source. But any developer who wants to modify the code or even contribute back to the project are welcome to do so.  04:43 Lois: Perside, can you walk us through MySQL’s release model? Perside: This is divided into LTS and Innovation releases, each with a different target audience. LTS stands for long-term support. MySQL 8.4 is an LTS release and will be supported for several years. LTS releases are feature-stable. When you install an LTS release, you can apply future bug fixes and security patches without changing any behavior in the product. The bug fixes and security patches are designed to be backward compatible. This means you can upgrade easily from previous releases. LTS releases come every two years. This allows you to maintain a stable system without having to change your underlying application too frequently. You will not be forced to upgrade after two years. You can continue to enjoy support for an LTS release for up to eight years. Along with LTS releases, we also have Innovation releases. These contain the latest leading-edge features that are developed even in the middle of an LTS cycle. You can upgrade from LTS to Innovation and back again, depending on which features you require in your application. Innovation releases have a much more rapid cadence. You can get the latest features every quarter. This means Innovation releases are supported only for their specific release. So, if you're on the Innovation track, you must upgrade more frequently. All editions of MySQL are shipped as both LTS and Innovation releases. This includes the self-managed editions and also HeatWave in the cloud. You can treat both LTS and Innovation releases as production-ready. This means they are generally available releases. Innovation does not mean beta quality software. You get the same quality support from Oracle whether you're using LTS or Innovative software. The MySQL client software and other tools will operate with both LTS and innovation releases.  07:43 Nikita: What are connectors in the context of MySQL? Perside: Connectors are the language-specific software component that connects your application to MySQL. You should use the latest version of connectors. Connectors are also production-ready, generally available software. They will work with any version of MySQL that is supported at the time of the connector's release. 08:12 Nikita: How does MySQL integrate with Docker and other container platforms? Perside: You might already be familiar with the Docker store. It is used for getting containerized images of software. As an Oracle customer, you might be familiar with My Oracle Support. It provides support and updates for all supported Oracle software in patches.  MySQL works well with virtualization and container platform, including Docker. You can get images from the Community Edition on Docker Hub. If you are an Enterprise Edition customer, you can get images from the Docker store for MySQL Oracle Support or from Oracle container's registry.  09:04 Lois: What resources are available for someone who wants to know more about MySQL? Perside: MySQL has detailed documentation. You should familiarize yourself with the documentation as you prepare to install MySQL. The reference manual for both Community and Enterprise editions are available at the Developer Zone at dev.mysql.com. Oracle customers also have access to the knowledge base at support.oracle.com. It contains support information on use cases and reference architectures. The product team regularly posts announcements and technical articles to several blogs. These blogs often contain pre-release announcements of upcoming features to help you prepare for your next project. Also, you'll find deep dives into technical topics and complex problems that MySQL solves. This includes some problems specific to highly available architecture. We also feature individual blogs from high profile members of our team. These include the MySQL Community evangelist lefred. He posts about upcoming events and interesting features. Also, Dimitri Kravchuk offers blogs that provide deep dives into performance. 10:53 Nikita: Ok, now that I have all this information and am prepped and ready, how do I actually install MySQL on my operating system? What’s the process like? Perside: You can install MySQL on various operating system, depending on your needs. These might include several distributions of Linux or UNIX, Windows, Mac OS, Oracle Linux based on the Unbreakable Enterprise Kernel, Solaris, and freeBSD. As always, the MySQL documentation provides full details on supported operating system. It also provides the specific installation steps for each of the operating system. Plus, it tells you how to perform the initial configuration and further administrative steps.  If you're installing on Windows, you have a couple of options. First, the MySQL Installer utility is the easiest way to install MySQL. It installs MySQL and performs the initial configuration based on options that you choose at installation time. It includes not only the MySQL server, but also the most important connectors, the MySQL Shell Client, MySQL Workbench Client with user interface and common utilities for troubleshooting and administration. It also installs several sample databases and models and documentation. It's the easiest way to install MySQL because it uses an installation wizard. It lets you select your installation target location, what components to install, and other options. 12:47 Lois: But what if I want to have more control?  Perside: For more control over your installation, you can install MySQL from the binary zip archive. This does not include sample or supporting tools and connectors, but only contains the application’s binaries, which you can install anywhere you want. This means that the initial configuration is not performed by selecting an option through a wizard. Instead, you must configure the Windows service and MySQL configuration file yourself.

Join hosts Lois Houston and Nikita Abraham as they kick off a new season exploring the world of MySQL 8.4. Together with Perside Foster, a MySQL Principal Solution Engineer, they break down the fundamentals of MySQL, its wide range of applications, and why it’s so popular among developers and database administrators. This episode also covers key topics like licensing options, support services, and the various tools, features, and plugins available in MySQL Enterprise Edition.   ------------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative  podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Lois: Hello and welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Team Lead: Editorial Services. Nikita: Happy New Year, everyone! Thank you for joining us as we begin a new season of the podcast, this time focused on the basics of MySQL 8.4. If you’re a database administrator or want to become one, this is definitely for you. It’s also great for developers working with data-driven apps or IT professionals handling MySQL installs, configurations, and support. 01:03 Lois: That’s right, Niki. Throughout the season, we'll be delving into MySQL Enterprise Edition and covering a range of topics, including installation, security, backups, and even MySQL HeatWave on Oracle Cloud.  Nikita: Today, we're going to discuss the Oracle MySQL ecosystem and its various components. We’ll start by covering the fundamentals of MySQL and the different licenses that are available. Then, we’ll explore the key tools and features to boost data security and performance. Plus, we’ll talk a little bit about MySQL HeatWave, which is the cloud version of MySQL.  01:39 Lois: To take us through all of this, we’ve got Perside Foster with us today. Perside is a MySQL Principal Solution Engineer at Oracle. Hi Perside! For anyone new to MySQL, can you explain what it is and why it's so widely used? Perside: MySQL is a relational database management system that organizes data into structured tables, rows, and columns for efficient programming and data management. MySQL is transactional by nature. When storing and managing data, actions such as selecting, inserting, updating, or deleting are required. MySQL groups these actions into a transaction. The transaction is saved only if every part completes successfully. 02:29 Lois: Now, how does MySQL work under the hood? Perside: MySQL is a high-performance database that uses its default storage engine, known as InnoDB. InnoDB helps MySQL handle complex operations and large data volumes smoothly. 02:49 Nikita: For the unversed, what are some day-to-day applications of MySQL? How is it used in the real world? Perside: MySQL works well with online transaction processing workloads. It handles transactions quickly and manages large volumes of transaction at once. OLTP, with low latency and high throughput, makes MySQL ideal for high-speed environments like banking or online shopping. MySQL not only stores data but also replicates it from a main server to several replicas. 03:31 Nikita: That's impressive! And what are the benefits of using MySQL?  Perside: It improves data availability and load balancing, which is crucial for businesses that need up-to-date information. MySQL replication supports read scale-out by distributing queries across servers, which increases high availability. MySQL is the most popular database on the web. 04:00 Lois: And why is that? What makes it so popular? What sets it apart from the other database management systems? Perside: First, it is a relational database management system that supports SQL. It also works as a document store, enabling the creation of both SQL and NoSQL applications without the need for separate NoSQL databases. Additionally, MySQL offers advanced security features to protect data integrity and privacy. It also uses tablespaces for better disk space management. This gives database administrators total control over their data storage. MySQL is simple, solid in its reliability, and secure by design. It is easy to use and ideal for both beginners and professionals. MySQL is proven at scale by efficiently handling large data volumes and high transaction rates. MySQL is also open source. This means anyone can download and use it for free. Users can modify the MySQL software to meet their needs. However, it is governed by the GNU General Public License, or GPL. GPL outlines specific rules for its use. MySQL offers two major editions. For developers and small teams, the Community Edition is available for free and includes all of the core features needed. For large enterprises, the Commercial Edition provides advanced features, management tools, and dedicated technical support. 05:58 Nikita: Ok. Let’s shift focus to licensing. Who is it useful for?  Perside: MySQL licensing is essential for independent software vendors. They're called ISVs. And original manufacturers, they're called OEMs. This is because these companies often incorporate MySQL code into their software products or hardware system to boost the functionality and performance of their product. MySQL licensing is equally important for value-added resellers. We call those VARs. And also, it's important for other distributors. These groups bundle MySQL with other commercially licensed software to sell as part of their product offering. The GPL v.2 license might suit Open Source projects that distribute their products under that license.   07:02 Lois: But what if some independent software vendors, original manufacturers, or value-add resellers don’t want to create Open Source products. They don’t want their source to be publicly available and they want to keep it private? What happens then? Perside: This is why Oracle provides a commercial licensing option. This license allows businesses to use MySQL in their products without having to disclose their source code as required by GPL v2. 07:33 Nikita: I want to bring up the robust support services that are available for MySQL Enterprise. What can we expect in terms of support, Perside?  Perside: MySQL Enterprise Support provides direct access to the MySQL Support team. This team consists of experienced MySQL developers, who are experts in databases. They understand the issues and challenges their customers face because they, too, have personally tackled these issues and challenges. This support service operates globally and is available in 29 languages. So no matter where customers are located, Oracle Support provides assistance, most likely in their preferred language. MySQL Enterprise Support offers regular updates and hot fixes to ensure that the MySQL customer systems stays current with the latest improvements and security patches. MySQL Support is available 24 hours a day, 7 days a week. This ensures that whenever there is an issue, Oracle Support can provide the needed help without any delay. There are no restrictions on how many times customers can receive help from the team because MySQL Enterprise Support allows for unlimited incidents. MySQL Enterprise Support goes beyond simply fixing issues. It also offers guidance and advice. Whether customers require assistance with performance tuning or troubleshooting, the team is there to support them every step of the way.  09:27 Lois: Perside, can you walk us through the various tools and advanced features that are available within MySQL? Maybe we could start with MySQL Shell. Perside: MySQL Shell is an integrated client tool used for all MySQL database operations and administrative functions. It's a top choice among MySQL users for its versatility and powerful features. MySQL Shell offers multi-language support for JavaScript, Python, and SQL. These naturally scriptable languages make coding flexible and efficient. They also allow developers to use their preferred programming language for everything, from automating database tasks to writing complex queries. MySQL Shell supports both document and relational models. Whether your project needs the flexibility of NoSQL’s document-oriented structures or the structured relationships of traditional SQL tables, MySQL Shell manages these different data types without any problems. Another key feature of MySQL Shell is its full access to both development and administrative APIs. This ability makes it easy to automate complex database operations and do custom development directly from MySQL Shell. MySQL Shell excels at DBA operations. It has extensive tools for database configuration, maintenance, and monitoring. These tools not only improve the efficiency of managing databases, but they also reduce the possibility for human error, making MySQL databases more reliable and easier to manage.  11:37 Nikita: What about the MySQL Server tool? I know that it is the core of the MySQL ecosystem and is available in both the community and commercial editions. But how does it enhance the MySQL experience? Perside: It connects with various devices, applications, and third-party tools to enhance its functionality. The server manages both SQL for structured data and NoSQL for schemaless applications. It has many key components. The parser, which interprets SQL commands. Optimizer, which ensures efficient query execution. And then the queue cache and buffer pools. They reduce disk usage and speed up access. InnoDB, the default storage engine, maintains data integrity and supports robust transaction and recovery mechanism. MySQL is designed for scalability and reliability. With features like replication and clustering, it distributes data, manage more users, and ensure consistent uptime. 13:00 Nikita: What role does MySQL Enterprise Edition play in MySQL server’s capabilities? Perside: MySQL Enter

Want to quickly provision your autonomous database? Then look no further than Oracle Autonomous Database Serverless, one of the two deployment choices offered by Oracle Autonomous Database.   Autonomous Database Serverless delegates all operational decisions to Oracle, providing you with a completely autonomous experience.   Join hosts Lois Houston and Nikita Abraham, along with Oracle Database experts, as they discuss how serverless infrastructure eliminates the need to configure any hardware or install any software because Autonomous Database handles provisioning the database, backing it up, patching and upgrading it, and growing or shrinking it for you.   Survey: https://customersurveys.oracle.com/ords/surveys/t/oracle-university-gtm/survey?k=focus-group-2-link-share-5   Oracle MyLearn: https://mylearn.oracle.com/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X (formerly Twitter): https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Rajeev Grover, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started. 00:26 Lois: Hello and welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Team Lead: Editorial Services. Nikita: Hi everyone! We hope you’ve been enjoying these last few weeks as we’ve been revisiting our most popular episodes of the year.  Lois: Today’s episode is the last one in this series and is a throwback to a conversation on Autonomous Databases on Serverless Infrastructure with three experts in the field: Hannah Nguyen, Sean Stacey, and Kay Malcolm. Hannah is a Staff Cloud Engineer, Sean is the Director of Platform Technology Solutions, and Kay is Vice President of Database Product Management. For this episode, we’ll be sharing portions of our conversations with them.  01:14 Nikita: We began by asking Hannah how Oracle Cloud handles the process of provisioning an  Autonomous Database. So, let’s jump right in! Hannah: The Oracle Cloud automates the process of provisioning an Autonomous Database, and it automatically provisions for you a highly scalable, highly secure, and a highly available database very simply out of the box. 01:35 Lois: Hannah, what are the components and architecture involved when provisioning an Autonomous Database in Oracle Cloud? Hannah: Provisioning the database involves very few steps. But it's important to understand the components that are part of the provisioned environment. When provisioning a database, the number of CPUs in increments of 1 for serverless, storage in increments of 1 terabyte, and backup are automatically provisioned and enabled in the database. In the background, an Oracle 19c pluggable database is being added to the container database that manages all the user's Autonomous Databases. Because this Autonomous Database runs on Exadata systems, Real Application Clusters is also provisioned in the background to support the on-demand CPU scalability of the service. This is transparent to the user and administrator of the service. But be aware it is there. 02:28 Nikita: Ok…So, what sort of flexibility does the Autonomous Database provide when it comes to managing resource usage and costs, you know… especially in terms of starting, stopping, and scaling instances? Hannah: The Autonomous Database allows you to start your instance very rapidly on demand. It also allows you to stop your instance on demand as well to conserve resources and to pause billing. Do be aware that when you do pause billing, you will not be charged for any CPU cycles because your instance will be stopped. However, you'll still be incurring charges for your monthly billing for your storage. In addition to allowing you to start and stop your instance on demand, it's also possible to scale your database instance on demand as well. All of this can be done very easily using the Database Cloud Console. 03:15 Lois: What about scaling in the Autonomous Database? Hannah: So you can scale up your OCPUs without touching your storage and scale it back down, and you can do the same with your storage. In addition to that, you can also set up autoscaling. So the database, whenever it detects the need, will automatically scale up to three times the base level number of OCPUs that you have allocated or provisioned for the Autonomous Database. 03:38 Nikita: Is autoscaling available for all tiers?  Hannah: Autoscaling is not available for an always free database, but it is enabled by default for other tiered environments. Changing the setting does not require downtime. So this can also be set dynamically. One of the advantages of autoscaling is cost because you're billed based on the average number of OCPUs consumed during an hour. 04:01 Lois: Thanks, Hannah! Now, let’s bring Sean into the conversation. Hey Sean, I want to talk about moving an autonomous database resource. When or why would I need to move an autonomous database resource from one compartment to another? Sean: There may be a business requirement where you need to move an autonomous database resource, serverless resource, from one compartment to another. Perhaps, there's a different subnet that you would like to move that autonomous database to, or perhaps there's some business applications that are within or accessible or available in that other compartment that you wish to move your autonomous database to take advantage of. 04:36 Nikita: And how simple is this process of moving an autonomous database from one compartment to another? What happens to the backups during this transition? Sean: The way you can do this is simply to take an autonomous database and move it from compartment A to compartment B. And when you do so, the backups, or the automatic backups that are associated with that autonomous database, will be moved with that autonomous database as well. 05:00 Lois: Is there anything that I need to keep in mind when I’m moving an autonomous database between compartments?  Sean: A couple of things to be aware of when doing this is, first of all, you must have the appropriate privileges in that compartment in order to move that autonomous database both from the source compartment to the target compartment. In addition to that, once the autonomous database is moved to this new compartment, any policies or anything that's defined in that compartment to govern the authorization and privileges of that said user in that compartment will be applied immediately to that new autonomous database that has been moved into that new compartment. 05:38 Nikita: Sean, I want to ask you about cloning in Autonomous Database. What are the different types of clones that can be created?  Sean: It's possible to create a new Autonomous Database as a clone of an existing Autonomous Database. This can be done as a full copy of that existing Autonomous Database, or it can be done as a metadata copy, where the objects and tables are cloned, but they are empty. So there's no rows in the tables. And this clone can be taken from a live running Autonomous Database or even from a backup. So you can take a backup and clone that to a completely new database. 06:13 Lois: But why would you clone in the first place? What are the benefits of this?  Sean: When cloning or when creating this clone, it can be created in a completely new compartment from where the source Autonomous Database was originally located. So it's a nice way of moving one database to another compartment to allow developers or another community of users to have access to that environment. 06:36 Nikita: I know that along with having a full clone, you can also have a refreshable clone. Can you tell us more about that? Who is responsible for this? Sean: It's possible to create a refreshable clone from an Autonomous Database. And this is one that would be synced with that source database up to so many days. The task of keeping that refreshable clone in sync with that source database rests upon the shoulders of the administrator. The administrator is the person who is responsible for performing that sync operation. Now, actually performing the operation is very simple, it's point and click. And it's an automated process from the database console. And also be aware that refreshable clones can trail the source database or source Autonomous Database up to seven days. After that period of time, the refreshable clone, if it has not been refreshed or kept in sync with that source database, it will become a standalone, read-only copy of that original source database. 07:38 Nikita: Ok Sean, so if you had to give us the key takeaways on cloning an Autonomous Database, what would they be?  Sean: It's very easy and a lot of flexibility when it comes to cloning an Autonomous Database. We have different models that you can take from a live running database instance with zero impact on your workload or from a backup. It can be a full copy, or it can be a metadata copy, as well as a refreshable, read-only clone of a source database. 08:12 Did you know that Oracle University offers free courses on Oracle Cloud Infrastructure? You’ll find training on everything from cloud computing, database, and security to artificial intelligence and machine learning, all of which is available free to subscribers. So, get going! Pick a course of your choice, get certified, join the Oracle University Learning Community, and network with your peers. If you’re already an Oracle MyLearn user, go to MyLearn to begin your journey. If you have not yet accessed Oracle MyLearn, visit mylearn.oracle.com and create an account to

Redwood is a state-of-the-art graphical interface that defines the look and feel of the new Oracle Cloud Redwood Applications.   In this episode, hosts Lois Houston and Nikita Abraham, along with Senior Principal OCI Instructor Joe Greenwald, take a closer look at the intent behind the design and development aspects of the new Redwood experience. They also explore Redwood page templates and components.   Survey: https://customersurveys.oracle.com/ords/surveys/t/oracle-university-gtm/survey?k=focus-group-2-link-share-5   Developing Redwood Applications with Visual Builder: https://mylearn.oracle.com/ou/learning-path/developing-redwood-applications-with-visual-builder/112791   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X (formerly Twitter): https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   ---------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started. 00:26 Nikita: Hello and welcome to the Oracle University Podcast! I’m Nikita Abraham, Team Lead: Editorial Services with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi everyone! Thanks for joining us for this Best of 2024 series, where we’re playing for you our four most popular episodes of the year.    Nikita: Today’s episode is #3 of 4 and is a throwback to another conversation with Joe Greenwald, our Senior Principal OCI Instructor. We asked Joe about Oracle’s Redwood design system and how it helps us create stunning, world-class enterprise applications and user experiences.  01:04 Lois: Yeah, Redwood is the basis for all the new Oracle Cloud Applications being re-designed, developed, and delivered. Joe is the best person to ask about all of this because he’s been working with our Oracle software development tools since the early 90s and is responsible for OU’s Visual Builder Studio and Redwood course content. So, let’s dive right in! Joe: Hi Lois. Hi Niki. I am excited to join you on this episode because with the release of 24A Fusion applications, we are encouraging all our customers to adopt the new Redwood design system and components, and take advantage of the world-class look and feel of the new Redwood user experience. Redwood represents a new approach and direction for us at Oracle, and we’re excited to have our customers benefit from it. 01:49 Nikita: Joe, you’ve been working with Oracle user interface development tools and frameworks for a long time. How and why is Redwood different? Joe: I joined Oracle in 1992, and the first Oracle user interface I experienced was Oracle Forms. And that was the character mode. I came from a background of Smalltalk and its amazing, pioneering graphical user interface (GUI) design capabilities. I worked at Apple and I developed my own GUIs for a few years on PCs and Macs. So, Character Mode Forms, what we used to call DMV (Department of Motor Vehicles) screens, was a shock, to say the least. Since then, I’ve worked with almost every user interface and development platform Oracle has created: Character Mode Forms, GUI Forms, Power Objects, HyperCard on the Macintosh, that was pre-OS X by the way, Sedona, written in native C++ and ActiveX and OLE, which didn’t make it to a product but appeared in other things later, ADF Faces, which uses Java to generate HTML pages, and APEX, which uses PL/SQL to generate HTML pages. And I’ve worked with and wrote training classes for Java Swing, an excellent GUI framework for event-driven desktop and enterprise applications, but it wasn’t designed for the web. So, it’s with pleasure that I introduce you to the Redwood design system, easily the best effort I’ve ever seen, from the look and feel of holistic user-goal-centered design philosophy and approach to the cutting-edge WYSIWYG design tools.  03:16 Lois: Joe, is Redwood just another set of styles, colors, and fonts, albeit very nice-looking ones? Joe: The Redwood platform is new for Oracle, and it represents a significant change, not just in the look and feel, colors, fonts, and styles, I mean that too, but it’s also a fundamental change in how Oracle is creating, designing, and imagining user interfaces. As you may be aware, all Oracle Cloud Applications are being re-designed, re-engineered, and re-rebuilt from the ground up, with significant changes to both back-end and front-end architectures. The front end is being redesigned, re-developed, and re-created in pure HTML5, CSS3, and JavaScript using Visual Builder Studio and its design-time browser-based Integrated Development Environment. The back end is being re-architected, re-designed, and implemented in a modern microservice architecture for Oracle Cloud using Kubernetes and other modern technologies that improve performance and work better in the cloud than our current legacy architecture. The new Oracle Cloud Applications platform uses Redwood for its design system—its tools, its patterns, its components, and page templates. Redwood is a richer and more productive platform to create solutions while still being cost-effective for Oracle. It encourages a transformation of the fundamental user experience, emphasizing identifying, meeting, and understanding end users’ goals and how the applications are used.  04:39 Nikita: Joe, do you think Oracle's user interface has been improved with Redwood? In what ways has the UI changed? Joe: Yes, absolutely. Redwood has changed a lot of things. When I joined Oracle back in the '90s, there was effectively no user interface division or UI team. Everybody just did their own thing. There was no user interface lab—and that was started in the mid-‘90s—and I was asked to give product usability feedback and participate in UI tests and experiments in those labs. I also helped test the products I was teaching at the time. I actually distinctly remember having to take a week to train users on Oracle’s Designer CASE tool product just to prep the participants enough to perform usability testing. I can still hear the UI lab manager shaking her head and saying any product that requires a week of training to do usability testing has usability issues! And if you’re like me and you’ve been around Oracle long enough, you know that Oracle’s not always been known for its user interfaces and been known to release products that look like they were designed by two or more different companies. All that has changed with Redwood. With Redwood, there’s a new internal design group that oversees the design choices of all development teams that develop products. This includes a design system review and an ongoing audit process to ensure that all the products being released, whether Fusion apps or something else, all look and feel similar so it looks like it’s designed by a single company with a single thought in mind. Which it is. There’s a deeper, consistent commitment in identifying user needs, understanding how the applications are being used, and how they meet those user needs through things like telemetry: gathering metrics from the actual components and the Redwood system itself to see how the applications are being used, what’s working well, and what isn’t.  This telemetry is available to us here at Oracle, and we use it to fine tune the applications’ usability and purpose.  06:29 Lois: That’s really interesting, Joe. So, it’s a fundamental change in the way we’re doing things. What about the GUI components themselves? Are these more sophisticated than simple GUI components like buttons and text fields? Joe: The graphical components themselves are at a much higher level, more comprehensive, and work better together. And in Redwood, everything is a component. And I’m not just talking about things like input text fields and buttons, though it applies to these more fine-grained components as well. Leveraging Oracle’s deep experience in building enterprise applications, we’ve incorporated that knowledge into creating page templates so that the structure and look and feel of the page is fixed based on our internal design standards. The developer has control over certain portions of it, but the overall look and feel of the page is controlled by Oracle. So there is consistency of look and feel within and across applications. These page templates come with predefined functionalities: headers, titles, properties, and variables to manipulate content and settings, slots for other components to hold like search fields, collections, contextual information, badges, and images, as well as primary and secondary actions, and variables for events and event handling through Visual Builder action chains, which handle the various actions and processing of the request on the page. And all these page templates and components are responsive, meaning they respond to the change in the size of the page and the orientation. So, when you move from a desktop to a handheld mobile device or a tablet, they respond appropriately and consistently to deliver a clean, easy-to-use interface and experience. 08:03 Nikita: You mentioned WYSIWYG design tools and their integration with Visual Builder Studio’s integrated development environment. How does Redwood work with Visual Builder Studio? Joe: This is easily one of my most favorite aspects about Redwood and the integration with Visual Builder Studio Designer. The components and page templates are responsive at runtime as well as responsive at design time! In over 30 years of working with Oracle software development products, this is the first development system and integrated development environment I’ve seen Oracle produce where what you see is what you get at design ti

What do you need to start customizing the next generation of Oracle Fusion Apps? How do you create new pages for business processes? What level of expertise do you require for this?   Join Lois Houston and Nikita Abraham as they get answers to all these questions and more from Senior Principal OCI Instructor Joe Greenwald.   Survey: https://customersurveys.oracle.com/ords/surveys/t/oracle-university-gtm/survey?k=focus-group-2-link-share-5   Develop Fusion Applications Using Visual Builder Studio: https://mylearn.oracle.com/ou/course/develop-fusion-applications-using-visual-builder-studio/138392/   Build Visual Applications Using Oracle Visual Builder Studio: https://mylearn.oracle.com/ou/course/build-visual-applications-using-oracle-visual-builder-studio/137749/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X (formerly Twitter): https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started. 00:26 Lois: Welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Team Lead of Editorial Services. Nikita: Hi there! You’re listening to our Best of 2024 series, where over the next few weeks, we’ll be revisiting four of our most popular episodes of the year. Lois: Today’s episode is #2 of 4, and we’re throwing it back to another episode with our friend and Senior Principal OCI Instructor Joe Greenwald. This episode is all about extending Oracle Cloud Applications that are being built using Visual Builder for the front-end. 01:04 Nikita: Right, Lois. We began by asking Joe to explain what’s happening with the redesign and re-architecture of Oracle Cloud Applications using Visual Builder Studio, or VBS.  Joe: That’s right, Niki. Oracle is redesigning and rebuilding its entire suite of Fusion Cloud Applications, over 330 different products, utilizing over 60,000 engineers — that is “60,” not “16”—at Oracle to develop the next generation of Oracle Fusion Applications. What’s most exciting is that the same tools the engineers are using to accomplish this are available to our partners and our customers to use to extend the functionality and capabilities of Fusion Applications to meet their custom needs and processes.  01:45 Lois: That’s pretty awesome! We want to use this time today to ask you about extensions, the types of extensions you can create, and how to use Visual Builder Studio to create those extensions. Nikita: Yeah, can we start with you telling us what an extension is? I’ve gotten the sense that Oracle uses the term extension as both a noun and a verb and that’s a bit confusing to me. Joe: Yeah, good catch, Niki. Yes, Oracle does use the term extension in two ways: both as a noun and a verb. As a noun, an extension is a container for the code changes that you make to your applications. Basically, it’s a Git repository that Oracle creates and manages for you. So, the extension container holds the code changes you make to your page layouts: the fields, their positioning, showing and hiding fields, that sort of thing, as well as page functionality. These code changes you make are stored in the extension and it is this extension with your code changes that is merged with the main Git branch eventually and then deployed using continuous integration/continuous deployment jobs defined in Visual Builder Studio, which manages the project and its assets. Your extension is a Git branch that is an asset of the project. Once your extension code is merged with the main branch and deployed, then the next time someone brings up the application, they’ll see the changes you’ve made in the app. 02:59 Lois: And as a verb? Joe: As a verb, extension means to extend the functionality and the look and feel of the application, though I prefer the term customization or configuration to describe this aspect, as the documentation does, and to avoid confusion, though I’ll admit I’m not always consistent about the terms I use. 03:16 Lois: What types of customizations, or extensions, and I’m using the verb now, are available for Fusion Apps in Visual Builder Studio? Joe: There are three different ways Fusion Apps can be customized effectively, configured, or extended. The first way is what we call a basic extension, where you’re rearranging hiding, or showing, or moving around fields and sections on the page that have been set up to be extendable by the Fusion Application development teams. Things like hiding fields, showing fields, hiding sections, showing sections…  03:45 Nikita: So fairly basic actions… Joe: Yeah exactly and they can be done in Visual Builder Studio Designer by people with minimal VB training, Visual Builder training. And, most recently, if you have access to it, you can do it in the new Express mode, where the page shows you just those things you can work with and just the tools you need to work with the page. This is new and makes it much easier for folks who are not highly technical to make basic changes to the page layout. 04:09 Lois: People like me! That sounds easy enough. Joe: And the next type of extension is more of an intermediate change and requires some training with Visual Builder Studio because you’re creating rules that govern the display of layouts based on certain conditions on the page. These are highly flexible, powerful, and useful for creating customized page layouts based on a variety of factors from page size and orientation to the role of the person using it to values in the actual fields on the page itself. These rules can be combined to create complex rule-based conditions that display exactly what the user should see, given the conditions of the page and their role. I would also include making changes to action chains, which execute sequences of behaviors and navigation, and the actual structure of the application, but this is more advanced.  Lastly, is creating mashup applications, which are stand-alone Visual Builder visual applications, which use data from Fusion apps, and customer data sources, like their own database tables, and potentially third-party APIs to create brand new pages and applications with new functionality, new processes, new procedures, new displays, all of which look just like Fusion Applications and use the same data as Fusion applications. 05:18 Lois: Joe, how do I get started if I want to extend a page?  Joe: The easiest way to do it is to open a page in Fusion Applications and then select Edit Page in Visual Builder Studio from the Profile menu. You’re then prompted for a project to hold the Git repository for the extension container. And since there’s probably already one that exists, after you select the project, an extension Git container is assigned to you. Unless this is the very first time the application has been extended in which case it creates an extension for you. When creating customizations or configurations, we recommend that each application be done in its own separate project. So, for example, if you’re working on Customer Experience Sales, you might do it in Project A and if you’re working on extensions with HCM, you might do it in Project B. And if you decide to create your own pages and flows in your own app, you might do that in Project C.  06:04 Nikita: But why do you need to do this? Joe: That’s just to keep things nice and separate and organized. The tool, Visual Builder Studio, doesn’t really care, but it makes for cleaner development and can help with the management of the development teams. 06:14 Nikita: Ok, Joe, I have a question. How do I know if the page I’m on in Fusion Apps can be edited in Visual Builder? I know there are a lot of legacy pages still out there and they can co-exist with the new VB-based pages. Joe: If the URL of the page you’re on has the word /Redwood in it instead of /faces, then you know this is a page that was created using Visual Builder Studio and you’ll be able to extend it and make changes to it using the Edit in Visual Builder Studio option. So, if you select Edit in Visual Builder Studio, then the page you are on opens inside Visual Builder Studio Designer and you can make changes to any part of the page that has been explicitly enabled for extension by the development team. 06:53 Lois: That’s an important part, right? The application is not extendable by default.  Joe: That’s right, Lois. It is all locked down and you can’t make any changes to it by default. The development team must specifically enable certain parts of the page: sections, fields, layouts, variables, types, action chains, etc. as extendable for you to be able to make changes to it. This ensures the changes the development team makes to the application in the future won’t break your extensions. And conversely, the development team can choose to not extend portions that they do not want you to touch or mess with. Then if they do change that bit of the app in the future, it won’t break the application and you won’t get a big surprise. So, using the Edit page in Visual Builder Studio, you can make both basic changes, like moving, showing, and hiding fields and sections, as well as the more intermediate types of configurations, like using dynamic components to create rule-based layouts that change dynamically based on several conditions such as page size, roles of the user, and field values on the page itself. 07:51 Nikita: What happens if two developers make changes and essentially overwrite each other’s customizations — say one hides a field and another later exposes it? Joe: Wel

The next generation of front-end user interfaces for Oracle Fusion Applications is being built using Visual Builder Studio and Oracle JavaScript Extension Toolkit. However, many of the terms associated with these tools can be confusing.   In this episode, Lois Houston and Nikita Abraham are joined by Senior Principal OCI Instructor Joe Greenwald. Together, they take you through the different terminologies, how they relate to each other, and how they can be used to deliver the new Oracle Fusion Applications as well as stand-alone, bespoke visual web applications.   Survey: https://customersurveys.oracle.com/ords/surveys/t/oracle-university-gtm/survey?k=focus-group-2-link-share-5   Develop Fusion Applications Using Visual Builder Studio: https://mylearn.oracle.com/ou/course/develop-fusion-applications-using-visual-builder-studio/138392/   Build Visual Applications Using Oracle Visual Builder Studio: https://mylearn.oracle.com/ou/course/build-visual-applications-using-oracle-visual-builder-studio/137749/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X (formerly Twitter): https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started. 00:26 Nikita: Hello and welcome to the Oracle University Podcast! I’m Nikita Abraham, Team Lead of Editorial Services with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi there! If you’ve been following along with us, you’ll know that we’ve had some really interesting seasons this year. We covered Autonomous Database, Artificial Intelligence, Visual Builder Studio and Redwood, OCI Container Engine for Kubernetes, and Oracle Database 23ai New Features. Nikita: And we’ve had some pretty awesome special guests. Do go back and check out those episodes if any of those topics interest you. 01:04 Lois: As we close out the year, we thought this would be a good time to revisit some of our best episodes. Over the next few weeks, you’ll be able to listen to four of our most popular episodes of the year.  Nikita: Right, this is the best of the best–according to you–our listeners.   Lois: Today’s episode is #1 of 4 and is a throwback to a discussion with Senior Principal OCI Instructor Joe Greenwald on Visual Builder Studio. Nikita: We asked Joe about Visual Builder Studio and Oracle JavaScript Extension Toolkit, also known as JET. Together, they form the basis of the technology for the next generation of front-end user interfaces for Oracle Fusion Applications, as well as many other Oracle applications, including most Oracle Cloud Infrastructure (OCI) interfaces. 01:48 Lois: We looked at the different terminologies and technologies, how they relate to each other, and how they deliver the new Oracle Fusion Applications and stand-alone, bespoke visual web applications.  So, let’s dive right in. Nikita: Joe, I’m somewhat thrown by the terminology around Visual Builder, Visual Studio, and JET. Can you help streamline that for us? Lois: Yeah, things that are named the same sometimes refer to different things, and sometimes things with a different name refer to the same thing. 02:18 Joe: Yeah, I know where you’re coming from. So, let’s start with Visual Builder Studio. It’s abbreviated as VBS and can go by a number of different names. Some of the most well-known ones are Visual Builder Studio, VBS, Visual Builder, Visual Builder Stand-Alone, and Visual Builder Cloud Service. Clearly, this can be very confusing. For the purposes of these episodes as well as the training courses I create, I use certain definitions.  02:42 Lois: Can you take us through those? Joe: Absolutely, Lois. Visual Builder Studio refers to a product that comes free with an OCI account and allows you to manage your project-related assets. This includes the project itself, which is a container for all of its assets. You can assign teams to your projects, as well as secure the project and declare roles for the different team members. You manage GIT repositories with full graphical and command-line GIT support, define package, build, and deploy jobs, and create and run continuous integration/continuous deployment graphical and code-managed pipelines for your applications. These can be visual applications, created using the Visual Builder Integrated Development Environment, the IDE, or non-visual apps, such as Java microservices, docker builds, NPM apps, and things like that. And you can define environments, which determine where your build jobs can be deployed. You can also define issues, which allow you to identify, track, and manage things like bugs, defects, and enhancements. And these can be tracked in code review merge requests and build jobs, and be mapped to agile sprints and scrum boards. There’s also support for wikis for team collaboration, code snippets, and the management of the repository and the project itself. So, VBS supports code reviews before code is merged into GIT branches for package, build, and deploy jobs using merge requests. 04:00 Nikita: OK, what exactly do you mean by that? Joe: Great. So, for example, you could have developers working in one GIT branch and when they’re done, they would push their private code changes into that remote branch. Then, they’d submit a merge request and their changes would be reviewed. Once the changes are approved, their code branch is merged into the main branch and then automatically runs a CI/CD package (continuous integration/continuous deployment) package, build, and deploy job on the code. Also, the CI/CD package, build, and deploy jobs can run against any branches, not just the main branch. So Visual Builder Studio is intended for managing the project and all of its assets. 04:37 Lois: So Joe, what are the different tools used in developing web applications? Joe: Well, Visual Builder, Visual Builder Studio Designer, Visual Builder Designer, Visual Builder Design-Time, Visual Builder Cloud Service, Visual Builder Stand-Alone all kind of get lumped together. You can kinda see why. What I’m referring to here are the tools that we use to build a visual web application composed of HTML5, CSS3, JavaScript, and JSON (JavaScript Object Notation) for metadata. I call this Visual Builder Designer. This is an Integrated Development Environment, it’s the “IDE” which runs in your browser. You use a combination of drag and drop, setting properties, and writing and modifying custom and generated code to develop your web applications. You work within a workspace, which is your own private copy of a remote Git branch. When you’re ready to start development work, you open an existing workspace or create a new one based on a clone of the remote branch you want to work on. Typically, a new branch would be created for the development work or you would join an existing branch. 05:38 Nikita: What’s a workspace, Joe? Is it like my personal laptop and drive? Joe: A workspace is your own private code area that stores any changes you make on the Oracle servers, so your code changes are never lost—even when working in a browser-based, network-based tool. A good analogy is, say I was working at home on my own machine. And I would make a copy of a remote GIT branch and then copy that code down to my local machine, make my code changes, do my testing, etc. and then commit my work—create a logical save point periodically—and then when I’m ready, I’d push that code up into the remote branch so it can be reviewed and merged with the main branch. My local machine is my workspace. However, since this code is hosted up by Oracle on our servers, and the code and the IDE are all running in your browser, the workspace is a simulation of a local work area on your own computer. So, the workspace is a hosted allocation of resources for you that’s private. Other people can’t see what’s going on in your workspace. Your workspace has a clone of the remote branch that you’re working with and the changes you make are isolated to your cloned code in your workspace. 06:41 Lois: Ok… the code is actually hosted on the server, so each time you make a change in the browser, the change is written back to the server? Is it possible that you might lose your edits if there’s a networking interruption? Joe: I want to emphasize that while I started out not personally being a fan of web-based integrated development environments, I have been using these tools for over three years and in all that time, while I have lost a connection at times—networks are still subject to interruptions—I’ve never lost any changes that I’ve made. Ever. 07:11 Nikita: Is there a way to save where you are in your work so that you could go back to it later if you need to? Joe: Yes, Niki, you’re asking about commits and savepoints, like in a Git repository or a Git branch. When you reach a logical stopping or development point in your work, you would create a commit or a savepoint. And when you’re ready, you would push that committed code in your workspace up to the remote branch where it can be reviewed and then eventually merged, usually with the main Git branch, and then continuous integration/continuous package and deployment build jobs are run. Now, I’m only giving you a high-level overview, but we cover all this and much more in detail with hands-on practices in our Visual Builder developer courses. Right now, I’m just trying to give you a sense of how these different tools are used. 07:52 Lois: Yeah, that makes sense, Joe. It’s a lot to cover in a short amount of time. Now, we’ve discussed the Visual Builder Designer IDE and

In this special episode of the Oracle University Podcast, Lois Houston and Nikita Abraham, along with Principal HCM Instructor Jeff Schuster, delve into the intersection of HCM and AI, exploring the practical applications and implications of this technology in human resources. Jeff shares his insights on bias and fairness, the importance of human involvement, and the need for explainability and transparency in AI systems. The discussion also covers the various AI features embedded in HCM and their impact on talent acquisition, performance management, and succession planning.  Oracle AI in Fusion Cloud Human Capital Management: https://mylearn.oracle.com/ou/learning-path/oracle-ai-in-fusion-cloud-human-capital-management-hcm/136722 Oracle Fusion Cloud HCM: Dynamic Skills: https://mylearn.oracle.com/ou/course/oracle-fusion-cloud-hcm-dynamic-skills/116654/ Oracle University Learning Community: https://education.oracle.com/ou-community LinkedIn: https://www.linkedin.com/showcase/oracle-university/ Twitter: https://twitter.com/Oracle_Edu Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode. -------------------------------------------------------- 00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started!  00:26 Lois: Hello and welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs here at Oracle University, and with me, is Nikita Abraham, Team Lead of Editorial Services. Nikita: Hi everyone! Last week’s conversation was all about Oracle Database 23ai backup and recovery, where we dove into instance recovery and effective recovery strategies. Today’s episode is a really special one, isn’t it, Lois? 00:53 Lois: It is, indeed, Niki. Of course, all of our AI episodes are special. But today, we have our friend and colleague Jeff Schuster with us. I think our listeners are really going to enjoy what Jeff has to share with us. Nikita: Yeah definitely! Jeff is a Principal HCM Instructor at Oracle University. He recently put together this really fantastic course on MyLearn, all about the intersection of HCM and AI, and that’s what we want to pick his brain about today. Hi Jeff! We’re so excited to have you here.  01:22 Jeff: Hey Niki! Hi Lois! I feel special already. Thanks you guys so much for having me. Nikita: You’ve had a couple of busy months, haven’t you?  01:29 Jeff: I have! It’s been a busy couple of months with live classes. I try and do one on AI in HCM at least once a month or so so that we can keep up with the latest/greatest stuff in that area. And I also got to spend a few days at Cloud World teaching a few live classes (about artificial intelligence in HCM, as a matter of fact) and meeting our customers and partners. So yeah, absolutely great week. A good time was had by me.  01:55 Lois: I’m sure. Cloud World is such a great experience. And just to clarify, do you think our customers and partners also had a good time, Jeff? It wasn’t just you, right? Jeff: Haha! I don’t think it was just me, Lois. But, you know, HCM is always a big deal, and now with all the embedded AI functionality, it really wasn’t hard to find people who wanted to spend a little extra time talking about AI in the context of our HCM apps. So, there are more than 30 separate AI-powered features in HCM. AI features for candidates to find the right jobs; for hiring managers to find the right candidates; skills, talent, performance management, succession planning— all of it is there and it really covers everything across the Attract/Grow/Keep buckets of the things that HR professionals do for a living. So, anyway, yeah, lots to talk about with a lot of people! There’s the functional part that people want to know about—what are these features and how do they work? But obviously, AI carries with it all this cultural significance these days. There’s so much uncertainty that comes from this pace of development in that area. So in fact, my Cloud World talk always starts with this really silly intro that we put in place just to knock down that anxiety and get to the more practical, functional stuff. 03:11 Nikita: Ok, we’re going to need to discuss the functional stuff, but I feel like we’re getting a raw deal if we don’t also get that silly intro. Lois: She makes a really good point.  Jeff: Hahaha! Alright, fair enough. Ok, but you guys are gonna have to imagine I’ve got a microphone and a big room and a lot of echo. AI is everywhere. In your home. In your office. In your homie’s home office. 03:39 Lois: I feel like I just watched the intro of a sci-fi movie. Jeff: Yeah. I’m not sure it’s one I’d watch, but I think more importantly it’s a good way to get into discussing some of the overarching things we need to know about AI and Oracle’s approach before we dive into the specific features, so you know, those features will make more sense when we get there?  03:59  Nikita: What are these “overarching” things?  Jeff: Well, the things we work on anytime we’re touching AI at Oracle. So, you know, it starts with things like Bias and Fairness. We usually end up in a pretty great conversation about things like how we avoid bias on the front end by making sure we don’t ingest things like bias-generating content, which is to say data that doesn’t necessarily represent bias by itself, but could be misused. And that pretty naturally leads us into a talk about guardrails. Nikita: Guardrails? Jeff:  Yeah, you can think of those as checkpoints. So, we’ve got rules about ingestion and bias. And if we check the output coming out of the LLM to ensure it complied with the bias and fairness rules, that’s a guardrail. So, we do that. And we do it again on the apps side. And so that’s to say, even though it’s already been checked on the AI side, before we bring the output into the HCM app, it’s checked again. So another guardrail.  04:58 Lois: How effective is that? The guardrails, and not taking in data that’s flagged as bias-generating? Jeff: Well, I’ll say this: It’s both surprisingly good, and also nowhere near good enough.  Lois: Ok, that’s as clear as mud. You want to elaborate on that?  Jeff: Haha! I think all it means is that approach does a great job, but our second point in the whole “standards” discussion is about the significance of having a human in the loop. Sometimes more than one, but the point here is that, particularly in HCM, where we’re handling some really important and sensitive data, and we’re introducing really powerful technology, the H in HCM gets even more important. So, throughout the HCM AI course, we talk about opportunities to have a human in the loop. And it’s not just for reviewing things. It’s about having the AI make suggestions, and not decisions, for example. And that’s something we always have a human in the loop for all the time. In fact, when I started teaching AI for HCM, I always said that I like to think of it is as a great big brain, without any hands.  06:00 Nikita: So, we’re not talking about replacing humans in HCM with AI.                                                                         Jeff: No, but we’re definitely talking about changing what the humans do and why it’s more important than ever what the humans do. So, think of it this way, we can have our embedded AI generate this amazing content, or create really useful predictions, whatever it is that we need. We can use whatever tools we want to get there, but we can still expect people to ask us, “Where did that come from?” or “Does this account for [whatever]?”. So we still have to be able to answer that. So that’s another thing we talk about as kind of an overarching important concept: Explainability and Transparency. 06:41 Nikita: I’m assuming that’s the part about showing our work, right? Explaining what's being considered, how it's being processed, and what it is that you're getting back. Jeff: That’s exactly it. So we like to have that discussion up front, even before we get to things like Gen and Non-Gen AI, because it’s great context to have in mind when you start thinking about the technology. Whenever we’re looking at the tech or the features, we’re always thinking about whether people are appropriately involved, and whether people can understand the AI product as well as they need to.  07:11 Lois: You mentioned Gen and Non-Gen AI. I’ve also heard people use the term “Classic AI.” And lately, a lot more about RAG and Agents. When you're teaching the course, does everybody manage to keep all the terminology straight? Jeff: Yeah, people usually do a great job with this. I think the trick is, you have to know that you need to know it, if that makes sense.  Lois: I think so, but why don’t you spell it out for us. Jeff: Well, the temptation is sometimes to leave that stuff to the implementers or product developers, who we know need to have a deep understanding of all of that. But I think what we’ve learned is, especially because of all the functional implications, practitioners, product owners, everybody needs to know it too. If for no other reason so they can have more productive conversations with their implementers. You need to know that Classic or Non-Generative AI leverages machine learning, and that that’s all you need in order to do some incredibly powerful things like predictions and matching. So in HCM, we’re talking about things like predicting time to hire, identifying suggested candidates for job openings, finding candidates similar to ones you already like, suggesting career paths for employees, and finding recommended successors. All really powerful matching stuff. And all of that stuff uses machine learning and it’s certainly AI, but none of that uses Generative AI to do that because it doesn’t need to. 08:38 Nikita: So how does that fit in with all the hype we’ve been hearing for a long

Lois Houston and Nikita Abraham continue their deep dive into Oracle Database 23ai backup and recovery strategies with Senior Principal Database & MySQL Instructor Bill Millar.   Picking up from Part 1, they explore critical concepts such as instance recovery, checkpoint processes, and the role of redo log files. Bill shares insights into complete and incomplete recovery, flashback technologies, and lots more.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-backup-and-recovery/141127/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   Twitter: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Radhika Banka, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 
00:26 Nikita: Welcome back to the Oracle University Podcast! I’m Nikita Abraham, Team Lead of Editorial Services with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi everyone! Last week, we had a fantastic chat with Bill Millar, our Senior Principal Database & MySQL Instructor. We dug into the basics of backup and recovery. We touched on everything from a DBA’s role in preventing data loss to handling different types of failures, and even some common mistakes that tend to pop up when managing a database. Nikita: Yeah, if you missed that episode, definitely go back and check it out. It’s packed with useful info, especially if you’re in charge of keeping databases safe. 01:10 Lois: Today, we’re picking up where we left off. We’re going to ask Bill about instance recovery and recovery strategies. Bill, can you kick things off by explaining what instance recovery is? Bill: You can understand instant recovery by becoming familiar with the checkpoint process, the redo log files, and the role of the log writer with the redo log files. Automatically instance or crash recovery. What is it doing? What are the phases of instance recovery? How we possibly can tune that instance recovery. We can use the mean time to recovery advisor that can help us determine how we might tune the instance recovery. 01:51 Nikita: OK, so let’s go through some of these concepts and procedures you mentioned. What is the checkpoint process responsible for exactly? Bill: The checkpoint process itself, it's responsible for updating the data file headers with checkpoint information. When a checkpoint is taken, it is going to write into the controlfiles. It tells the DB writer to write. DB writer writes to the data files, and the checkpoint is also annotated in the data files. So updating controlfiles with that checkpoint information also, controlfiles and database files. It signals that DB writer at full check points again, hey, it's time to write. So that way, it has the latest data written to the data files. The controlfile and datafiles, those are in sync with that. 02:40 Lois: Bill, what about the log writer process and the redo log files? Bill: With the log writer process and the redo log files, the redo log files record the changes to the database himself. It should be multiplexed. 02:53 Nikita: What do you mean by that? Bill: More than one redo log group. Now, the redo log groups, it is recommended that they should be multiplexed. Each group member should be on a different disk or in a different disk group if you're using ASM. 03:10 Nikita: And why is that, Bill? Bill: Because if I lose one, if I lose one redo log group, one member, I can continue to operate with just the one. If I only have one redo log group member and the system comes around and tries to write to it, then my system is going to come to a halt. So the log writer is going to write to those redo logs whenever somebody does a commit. When that redo log buffer is 1/3 full or every three seconds and before DB writer writes. So those are the four mechanism that tells log writer to write from that log buffer to the redo log files. And it'll also write, when we do a shut down, all the buffers will be flushed. And so that way, everything will be in sync when the system is shut down. 04:01 Lois: What are the different modes of operation for a database, Bill? And how do these modes impact the recovery capabilities of the database? Bill: So we have two different modes we can operate in. One is called NOARCHIVELOG mode. It is the default. ARCHIVELOG mode, highly encouraged. But not every environment has to be in ARCHIVELOG mode. 04:21 Nikita: So with ARCHIVELOG mode… Bill: Closed database. You have to close it, recover to the last backup. That's as far as I can go. Actually, I could, depending on what happens, I might be able to apply some redo. Suitable for training and test environments or for data warehouses, we don't have a lot of frequent changes. It's mainly bulk loading data at night and querying during the day. So it might be appropriate for that. Because ARCHIVELOG mode, it is a little overhead. Yes. So with that database, it goes down while it's open. The system, when it comes up, it can recover to the last committed transaction. And this is usually the mode we want to operate in for production environments. So we have that data in the buffer cache. We have that redo being buffered. We have the undo tablespace, keeping track of what the data was before a change. The redo keeps track of what was the change. And if we're in ARCHIVELOG mode, as we switch from one redo log to another, we will generate what's referred to as archived log files, and that's what allows us to do a complete recovery. 05:33 Lois: What happens in the case of automatic instance recovery? Bill: For an automatic instance recovery or crash recovery, our system went down unexpectedly. Because it did not do a clean shutdown, the buffers were not flushed. Everything was not synchronized. So the datafile, controlfile, everything is out of sync. 05:53 Nikita: So, how do the files get synchronized then? Bill: It uses the redo log groups to synchronize the files. It's going to roll forward. It rolls forward the changes that were made. So due to different distinct operations. Roll forward applies committed and uncommitted data. And the redo does not keep track of what was committed and uncommitted. It'll keep track of, hey, I had this transaction, hey, here's a commit for that transaction. But hey, I have a transaction. That was never uncommitted. That's the job of the undo. But rolls forward all those changes. And then anything that did not actually receive a commit, it will roll back the uncommitted data, return to the original state. And that is the job of the undo tablespace. 06:37 Lois: Bill, is it possible to tune instance recovery for better performance? Bill: You can try to tune this instance recovery. Tuning it is touchy. Be careful because you can cause more harm than what you think you might be doing good. The instance recovery, what we're doing, we're trying to-- the transactions between checkpoints. When was the last checkpoint? Because the items between the checkpoints, that's what has to be reapplied. So the last checkpoint to the last redo log, what is that time frame there between those? Well, what we're going to do, we're going to try to control that. We're going to try to control the difference between the checkpoint and the end of the redo log. There is a mean-time recovery advisor. You specify the desired times in seconds or minutes that how often you want that checkpoint to occur. There is a parameter, FAST_START_MTTR parameter that you can set. The default value is zero saying, hey, I'm going to let the system take care of it. And the maximum you can set it is to one hour. 07:46 Nikita: And why 1 hour?
 Bill: The reason being, if I set that to one hour and I have a lot of activity, how long is it going to take? How many transactions can happen within that hour? Yeah, I'm not doing a checkpoint as often, so I'm eliminating that workload. But if it has to recover, how long is it going to take? If I set it too small, the system says, hey, right now, it's going to take me 19 seconds based off statistics. If I said, OK, I want it in five seconds. So what does that mean? Every five seconds, I'm saying do a checkpoint. So what is it doing? OK, time to do a checkpoint. OK, time to go ahead and OK, DB writer write. OK, log writer write. OK, let me update the datafiles and the controlfiles. So you're just thrashing your system. So be careful if you decide to try to manually tune it. And when you go out and look at this mean time to recover, and even if you do it through the command line, you'll see that, that value is most likely going to change throughout the day, depending on the workload that you have. 08:46 Lois: How does the process of restoring and recovering data typically work? Bill: So when we restore, we're restoring our datafiles. All the datafiles, tablespace, controlfiles, archived redo log, server parameter file. Then when we recover, it involves depending on the backup that we use and other factors in there, it is going to apply the redo. So automatically done by RMAN. So I tell it, this is what I want to do. Hey, I want to restore a database. OK, RMAN says, all right, what backup are you going to use? What is it I need to restore? And then we tell it to recover. OK, I know what I need to use to recover. So RMAN can do the work for you. So when we restore and recover due to a manual process and there's different methods that we can use, and depending on the failure, we'll drive what type of restore and recovery we might perform. 09:40 Are you looking for practical use cases to help you plan and apply configurations that solv

In this two-part special, Lois Houston and Nikita Abraham delve into the critical topic of backup and recovery in Oracle Database 23ai.   Together with Bill Millar, Senior Principal Database & MySQL Instructor, they discuss the role of database administrators, strategies for protecting data, and dealing with various types of data failure.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-backup-and-recovery/141127/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   Twitter: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Radhika Banka, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------- Episode Transcript: 00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Team Lead of Editorial Services with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi there! For the last two weeks, we’ve been having really exciting discussions on Oracle AI Vector Search. We covered the fundamentals, benefits, the vector workflow, and lots more. Today, we’re going to talk about backup and recovery in Oracle Database 23ai with Bill Millar. If you’ve been listening this season, you’ll know that Bill is a Senior Principal Database & MySQL Instructor with Oracle University. Nikita: In this two-part special, we’ll dive into some of the things you need to know about backup and recovery, especially if you’re a database and backup admin. So, if you're the person in charge of keeping data safe and handling disaster recovery, this is definitely worth your time. 01:20 Lois: That’s right, Niki. Hi Bill, thanks for joining us again. What’s the role of a Database Administrator, or DBA, when it comes to backup and recovery? Bill: The DBA is typically responsible for ensuring the database is open and available when needed and at times you need to work with system administrators and other people within your organization to achieve that. But we want to try to protect the database from failure wherever possible. We want to increase the mean time between failures. Hopefully, we don't have failures, and we have to increase that time. But it might mean that we need to ensure we have redundant hardware and that in place, again, maybe out of the realm of the DBA, but people within your organization can help with that. We want to protect those critical components by using the redundancy. And we want to decrease the mean time to recover. Failures happen, but how fast can we get access back to that data after that failure. The faster we can do it, the happier customers are. Minimize the loss of data. It's never good to lose data, especially in a critical environment, but maybe in test and development, maybe not so bad.  02:39 Nikita: How do we ensure a separation of duties for backup and recovery processes? Bill: For a separation of duties, we do have a user called SYSBACKUP. It has the privileges that's required to perform backup and recoveries, the privilege to connect and execute the commands in what we refer to as RMAN, our Recovery Manager. As I said, it has permissions for backup and recovery because you do need to shut down the database, start up the database, those type of things. We're able to connect to that closed database to try to troubleshoot it, to get it to the open state again. It does not include any privileges to access data. The SYSBACKUP user is created when we install the database, when we create the database. We can use it explicitly for privileged user connection. It allows us to connect to the database. So RMAN connects as SYSBACKUP. 03:37 Lois: Bill, what should people keep in mind when figuring out what’s considered critical data? Bill: You want to try to identify your critical data. Some data might be highly required to access and make sure we don't lose don't lose data, but then you might have some environments. OK, I don't need to have them up and running as fast. If we lose a little data, it may not hurt, but we want to identify the difference in the different data that we have on different environments. So we want to also prioritize that critical data, which data do we need access to first because how much will the company lose per hour of downtime because we can't do business. We want to make sure the access data protection requirements. Not everybody has access to everything. And there are different types of disaster that can happen that are going to be totally out of your control. There's the physical disaster, a hurricane or tornado, outages, power outages, component failures, failures within the building itself, corruption of data because of some of these failures. And then, the most dreaded one, the one that happens most often, usually those human errors, the logical errors, where the data is just bad, we are able to access and everything. It's just that something has changed that shouldn't have been changed. We want to make sure we access our recovery requirements.  05:04 Lois: So, what are they? What are those requirements? Bill: We want to base that requirement based on how critical is that data, how soon do we need to have access to that? What is our recovery point objective? Do we have a tolerance for any type of data loss? How frequently should we backups? How often they should be taken? What type of backups will be another thing we'll want to figure out? Is point-in-time recovery required? Are we able to or do we ever need to go back to a previous point in time to do something? It's not always just recovery for a database failure. We might need to do a recovery point in time to a different system so we can investigate something. What is my recovery time objective? Again, what is the tolerance for the downtime? How long can I be down? The downtime, the biggest part of when a system goes down is trying to identify what is the problem, then next is what is going to be my plan to recover, and then perform in the recovery. We might have a tiered required time objective based off of critical data, and then depending on the failure. Is that failure at the entire database? Is it just a tablespace? Is it just a table? Is it just a row? That also determines how long it takes to recover and what type of recovery we might try to perform. What is my backup retention policy? Do I have a requirement to where I have to have my backups off site? And it doesn't mean like back in the old days of mainframe computers, you'd back up to tape and you'd take those tapes off site. You might still do that today. Or, am I backing up to a cloud environment? So what do I need to have for that? What about long-term backups? We work with our day-to-day backups, but there's those backups that require for longer, archives like end of year backups. Some places require to keep their end of year backup for like 10 years. How are we going to handle that? So these are some of the things that we have to think about when we start talking about backup and recovery. 07:23 Did you know that the Oracle University Learning Community regularly holds live events hosted by Oracle expert instructors. Find out how to prepare for your certification exams. Learn about the latest technology advances and features. Ask questions in real time and learn from an Oracle subject matter expert. From Ask Me Anything about certification to Ask the Instructor coaching sessions, you’ll be able to achieve your learning goals for 2024 in no time. Join a live event today and witness firsthand the transformative power of the Oracle University Learning Community. Visit mylearn.oracle.com to get started. 08:04 Nikita: Welcome back! Bill, I want to talk about the different failures that can occur in an Oracle database. How would you categorize them? Bill: There are different category of failure. This is not an all-inclusive list by any means. It's just something that possibly can happen. So they can usually be divided into different categories like statement failure. All right. When doing a select and insert, update, delete, the statement itself fails. A user process fails. Single database session fails for some reason. Network failure, connectivity is lost. The user error, probably one of the most common ones we have to deal with. A user successfully completes an operation, but that operation was erroneous. They dropped the wrong table, updated the wrong row. Then there's the instance failure. The database itself shuts down unexpectedly. And then media failure, usually a hard failure of our disk. Something of memory, something failed and caused an error. 09:12 Lois: Ok. I want to dive a little deeper into each of these categories that you mentioned. Let’s start with statement failures. What are typical problems that one might face? Bill: Attempts to enter invalid data into a table. They're trying to put a numeric field in a date field, and usually just working with the user is going to correct that. Is that the DBA responsible? Yes, no, maybe. They attempt to form operations with insufficient privileges. Attempts to allocate space that fails, well, that depends on are they going-- do they have unlimited storage or do they have a limit? Logic errors in the application. Well, that's where we're going to have to work with those developers to try to correct those type of errors. 09:59 Nikita: What about user process failures? Bill: User performs an abnormal disconnect, doesn't close out properly. It can cause something to hang up or even possibly erroneous data to be updated. A user session is abnormally terminated. Well, usually, we don't have to try to resolve those user type errors

This week, Lois Houston and Nikita Abraham continue their exploration of Oracle AI Vector Search with a deep dive into vector indexes and memory considerations.   Senior Principal APEX and Apps Dev Instructor Brent Dayley breaks down what vector indexes are, how they enhance the efficiency of search queries, and the different types supported by Oracle AI Vector Search.   Oracle Database 23ai: Oracle AI Vector Search Fundamentals: https://mylearn.oracle.com/ou/course/oracle-database-23ai-oracle-ai-vector-search-fundamentals/140188/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   Twitter: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Radhika Banka, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started!   00:26 Nikita: Welcome back to the Oracle University Podcast! I’m Nikita Abraham, Team Lead of Editorial Services at Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi everyone! Last week was Part 1 of our discussion on Oracle AI Vector Search. We talked about what it is, its benefits, the new vector data type, vector embedding models, and the overall workflow. In Part 2, we’re going to focus on vector indices and memory. 00:56 Nikita: And to help us break it all down, we’ve got Brent Dayley back with us. Brent is a Senior Principal APEX and Apps Dev Instructor with Oracle University. Hi Brent! Thanks for being with us today. So, let’s jump right in! What are vector indexes and how are they useful? Brent: Now, vector indexes are specialized indexing data structures that can make your queries more efficient against your vectors. They use techniques such as clustering, and partitioning, and neighbor graphs. Now, they greatly reduce the search space, which means that your queries happen quicker. They're also extremely efficient. They do require that you enable the vector pool in the SGA. 01:42 Lois: Brent, walk us through the different types of vector indices that are supported by Oracle AI Vector Search. How do they integrate into the overall process? Brent: So Oracle AI Vector Search supports two types of indexes, in-memory neighbor graph vector index. HNSW is the only type of in-memory neighbor graph vector index that is supported. These are very efficient indexes for vector approximate similarity search. HNSW graphs are structured using principles from small world networks along with layered hierarchical organization. And neighbor partition vector index, inverted file flat index, is the only type of neighbor partition index supported. It is a partition-based index which balances high search quality with reasonable speed. 02:35 Nikita: Brent, you mentioned that enabling the vector pool in the SGA is a requirement when working with vector indexes. Can you explain that process for us? Brent: In order for you to be able to use vector indexes, you do need to enable the vector pool area. And in order to do that, what you need to do is set the vector memory size parameter. You can set it at the container database level. And the PDB inherits it from the CDB. Now bear in mind that the database does have to be balanced when you set the vector pool. 03:12 Lois: Ok. Are there any other considerations to keep in mind when using vector indices? Brent: Vector indexes are stored in this pool, and vector metadata is also stored here. And you do need to restart the database. So large vector indexes do need lots of RAM, and RAM constrains the vector index size. You should use IVF indexes when there is not enough RAM. IVF indexes use both the buffer cache as well as disk. 03:42 Nikita: And what about memory considerations? Brent: So to remind you, a vector is a numerical representation of text, images, audio, or video that encodes the features or semantic meaning of the data, instead of the actual contents, such as the words or pixels of an image. So the vector is a list of numerical values known as dimensions with a specified format. Now, Oracle does support the int8 format, the float32 format, and the float64 format. Depending on the format depends on the number of bytes. For instance, int8 is one byte, float32 is four bytes. Now, Oracle AI Vector Search supports vectors with up to 65,535 dimensions. 04:34 Lois: What should we know about creating a table with a vector column? Brent: Now, Oracle Database 23ai does have a new vector data type. The new data type was created in order to support vector search. The definition can include the number of dimensions and can include the format. Bear in mind that either one of those are optional when you define your column. The possible dimension formats are int, float 32, and float 64. Float 32 and float 64 are IEEE standards, and Oracle Database will automatically cast the value if needed. 05:18 Nikita: Can you give us a few declaration examples? Brent: Now, if we just do a vector type, then the vectors can have any arbitrary number of dimensions and formats. If we describe the vector type as vector * , *, then that means that vectors can have an arbitrary number of dimensions and formats. Vector and vector * , * are equivalent. Vector with the number of dimensions specified, followed by a comma, and then an asterisk, is equivalent to vector number of dimensions. Vectors must all have the specified number of dimensions, or an error will be thrown. Every vector will have its dimension stored without format modification. And if we do vector asterisk common dimension element format, what that means is that vectors can have an arbitrary number of dimensions, but their format will be up-converted or down-converted to the specified dimension element format, either INT8, float 32, or float 64. 06:25 Working towards an Oracle Certification this year? Take advantage of the Certification Prep live events in the Oracle University Learning Community. Get tips from OU experts and hear from others who have already taken their certifications. Once you’re certified, you’ll gain access to an exclusive forum for Oracle-certified users. What are you waiting for? Visit mylearn.oracle.com to get started.   06:52 Nikita: Welcome back! Brent, what is the vector constructor and why is it useful? Brent: Now, the vector constructor is a function that allows us to create vectors without having to store those in a column in a table. These are useful for learning purposes. You use these usually with a smaller number of dimensions. Bear in mind that most embedding models can contain thousands of different dimensions. You get to specify the vector values, and they usually represent two-dimensional like xy coordinates. The dimensions are optional, and the format is optional as well. 07:29 Lois: Right. Before we wrap up, can you tell us how to calculate vector distances? Brent: Now, vector distance uses the function VECTOR_DISTANCE as the main function. This allows you to calculate distances between two vectors and, therefore, takes two vectors as parameters. Optionally, you can specify a metric. If you do not specify a metric, then the default metric, COSINE, would be used. You can optionally use other shorthand functions, too. These include L1 distance, L2 distance, cosine distance, and inner product. All of these functions also take two vectors as input and return the distance between them. Now the VECTOR_DISTANCE function can be used to perform a similarity search. If a similarity search query does not specify a distance metric, then the default cosine metric will be used for both exact and approximate searches. If a similarity search does specify a distance metric in the VECTOR_DISTANCE function, then an exact search with that distance metric is used if it conflicts with the distance metric specified in a vector index. If the two distance metrics are the same, then this will be used for both exact as well as approximate searches. 08:58 Nikita: I was wondering Brent, what vector distance metrics do we have access to? Brent: We have Euclidean and Euclidean squared distances. We have cosine similarity, dot product similarity, Manhattan distance, and Hamming similarity. Let's take a closer look at the first of these metrics, Euclidean and Euclidean squared distances. This gives us the straight-line distance between two vectors. It does use the Pythagorean theorem. It is sensitive to both the vector size as well as the direction. With Euclidean distances, comparing squared distances is equivalent to comparing distances. So when ordering is more important than the distance values themselves, the squared Euclidean distance is very useful as it is faster to calculate than the Euclidean distance, which avoids the square root calculation. 09:58 Lois: And the cosine similarity metrics? Brent: It is one of the most widely used similarity metrics, especially in natural language processing. The smaller the angle means they are more similar. While cosine distance measures how different two vectors are, cosine similarity measures how similar two vectors are. Dot product similarity allows us to multiply the size of each vector by the cosine of their angle. The corresponding geometrical interpretation of this definition is equivalent to multiplying the size of one of the vectors by the size of the projection of the second vector onto the first one or vice versa. Larger means that they are more similar. Smaller means that they are less similar. Manhattan distance is useful for describing uniform grids. You can imagine yourself walking from point A to point B in a city suc

In this episode, Senior Principal APEX and Apps Dev Instructor Brent Dayley joins hosts Lois Houston and Nikita Abraham to discuss Oracle AI Vector Search. Brent provides an in-depth overview, shedding light on the brand-new vector data type, vector embeddings, and the vector workflow.   Oracle Database 23ai: Oracle AI Vector Search Fundamentals: https://mylearn.oracle.com/ou/course/oracle-database-23ai-oracle-ai-vector-search-fundamentals/140188/   Oracle Database 23ai: SQL Workshop: https://mylearn.oracle.com/ou/course/oracle-database-23ai-sql-workshop/137830/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   Twitter: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Radhika Banka, and the OU Studio Team for helping us create this episode.   ---------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started!   00:26 Lois: Hello and welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs here at Oracle University. Joining me as always is our Team Lead of our Editorial Services, Nikita Abraham. Nikita: Hi everyone! Thanks for tuning in over the last few months as we’ve been discussing all the Oracle Database 23ai new features. We’re coming to the end of the season, and to close things off, in this episode and the next one, we’re going to be talking about the fundamentals of Oracle AI Vector Search. In today’s episode, we’ll try to get an overview of what vector search is, why Oracle Vector Search stands out, and dive into the new vector data type. We’ll also get insights into vector embedding models and the vector workflow. 01:11 Lois: To take us through all of this, we’re joined by Brent Dayley, who is a Senior Principal APEX and Apps Development Instructor with Oracle University. Hi Brent! Thanks for joining us today. Can you tell us about the new vector data type? Brent: So this data type was introduced in Oracle Database 23ai. And it allows you to store vector embeddings alongside other business data. Now, the vector data type allows a foundation to store vector embeddings. 01:42 Lois: And what are vector embeddings, Brent? Brent: Vector embeddings are mathematical representations of data points. They assign mathematical representations based on meaning and context of your unstructured data. You have to generate vector embeddings from your unstructured data either outside or within the Oracle Database. In order to get vector embeddings, you can either use ONNX embedding machine learning models or access third-party REST APIs. Embeddings can be used to represent almost any type of data, including text, audio, or visual, such as pictures. And they are used in proximity searches. 02:28 Nikita: Hmmm, proximity search. And similarity search, right? Can you break down what similarity search is and how it functions? Brent: So vector data tends to be unevenly distributed and clustered into groups that are semantically related. Doing a similarity search based on a given query vector is equivalent to retrieving the k nearest vectors to your query vector in your vector space. What this means is that basically you need to find an ordered list of vectors by ranking them, where the first row is the closest or most similar vector to the query vector. The second row in the list would be the second closest vector to the query vector, and so on, depending on your data set. What we need to do is to find the relative order of distances. And that's really what matters rather than the actual distance. Now, similarity searches tend to get data from one or more clusters, depending on the value of the query vector and the fetch size. Approximate searches using vector indexes can limit the searches to specific clusters. Exact searches visit vectors across all clusters. 03:44 Lois: Ok. I want to move on to vector embedding models. What are they and why are they valuable? Brent: Vector embedding models allow you to assign meaning to what a word, or a sentence, or the pixels in an image, or perhaps audio. It allows you to quantify features or dimensions. Most modern vector embeddings use a transformer model. Bear in mind that convolutional neural networks can also be used. Depending on the type of your data, you can use different pretrained open source models to create vector embeddings. As an example, for textual data, sentence transformers can transform words, sentences, or paragraphs into vector embeddings. 04:33 Nikita: And what about visual data? Brent: For visual data, you can use residual network also known as ResNet to generate vector embeddings. You can also use visual spectrogram representation for audio data. And that allows us to use the audio data to fall back into the visual data case. Now, these can also be based on your own data set. Each model also determines the number of dimensions for your vectors. 05:02 Lois: Can you give us some examples of this, Brent? Brent: Cohere's embedding model, embed English version 3.0, has 1,024 dimensions. Open AI's embedding model, text-embedding-3-large, has 3,072 dimensions. 05:24 Want to get the inside scoop on Oracle University? Head over to the Oracle University Learning Community. Attend exclusive events. Read up on the latest news. Get first-hand access to new products. Read the OU Learning Blog. Participate in Challenges. And stay up-to-date with upcoming certification opportunities. Visit mylearn.oracle.com to get started.  05:50 Nikita: Welcome back! Let’s now get into the practical side of things. Brent, how do you import embedding models? Brent: Although you can generate vector embeddings outside the Oracle Database using pre-trained open source embeddings or your own embedding models, you also have the option of doing those within the Oracle Database. In order to use those within the Oracle Database, you need to use models that are compatible with the Open Neural Network Exchange Standard, or ONNX, also known as Onyx. Oracle Database implements an Onyx runtime directly within the database, and this is going to allow you to generate vector embeddings directly inside the Oracle Database using SQL. 06:35 Lois: Brent, why should people choose to use Oracle AI Vector Search? Brent: Now one of the biggest benefits of Oracle AI Vector Search is that semantic search on unstructured data can be combined with relational search on business data, all in one single system. This is very powerful, and also a lot more effective because you don't need to add a specialized vector database. And this eliminates the pain of data fragmentation between multiple systems. It also supports Retrieval Augmented Generation, also known as RAG. Now this is a breakthrough generative AI technique that combines large language models and private business data. And this allows you to deliver responses to natural language questions. RAG provides higher accuracy and avoids having to expose private data by including it in the large language model training data. 07:43 Nikita: In the last part of our conversation today, I want to ask you about the Oracle AI Vector Search workflow, starting with generating vector embeddings. Brent: Generate vector embeddings from your data, either outside the database or within the database. Now, embeddings are a mathematical representation of what your data meaning is. So what does this long sentence mean, for instance? What are the main keywords out of it? You can also generate embeddings not only on your typical string type of data, but you can also generate embeddings on other types of data, such as pictures or perhaps maybe audio wavelengths. 08:28 Lois: Could you give us some examples? Brent: Maybe we want to convert text strings to embeddings or convert files into text. And then from text, maybe we can chunk that up into smaller chunks and then generate embeddings on those chunks. Maybe we want to convert files to embeddings, or maybe we want to use embeddings for end-to-end search. Now you have to generate vector embeddings from your unstructured data, either outside or within the Oracle Database. You can either use the ONNX embedding machine learning models or you can access third-party REST APIs. You can import pre-trained models in ONNX format for vector generation within the database. You can download pre-trained embedding machine learning models, convert them into the ONNX format if they are not already in that format. Then you can import those models into the Oracle Database and generate vector embeddings from your data within the database. Oracle also allows you to convert pre-trained models to the ONNX format using Oracle machine learning for Python. This enables the use of text transformers from different companies. 09:51 Nikita: Ok, so that was about generating vector embeddings. What about the next step in the workflow—storing vector embeddings? Brent: So you can create one or more columns of the vector data type in your standard relational data tables. You can also store those in secondary tables that are related to the primary tables using primary key foreign key relationships. You can store vector embeddings on structured data and relational business data in the Oracle Database. You do store the resulting vector embeddings and associated unstructured data with your relational business data inside the Oracle Database. 10:30 Nikita: And the third step is creating vector indexes? Brent: Now you may want to create vector indexes in the event that you have huge vector spaces. This is an optional step, but this is beneficial for running similarity searches over those huge vector sp

In this episode, Lois Houston and Nikita Abraham explore the Automatic Transaction Quarantine feature with Senior Principal Database & MySQL Instructor, Bill Millar. Bill explains that this feature isolates transactions that could potentially cause system crashes, preventing them from impacting the entire container database. They also discuss the key advantages of automatic transaction quarantine in maintaining database stability and availability.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/140830/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Team Lead: Editorial Services with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi there! In our last episode, we looked at an Oracle Database 23ai new feature called Automatic Transaction Rollback, and we spoke about why it is such an important feature for database administrators. 00:51 Nikita: Today, we’re going to talk about another new feature called Automatic Transaction Quarantine. We’ll discuss what it is, go through the steps to monitor and identify quarantine transactions, explore how an issue is resolved once a quarantined transaction has been identified, and end by looking at quarantined transaction escalation, and how it helps to protect not only your PDB, but also your container database. Lois: Back with us is Bill Millar, our Senior Principal Database & MySQL Instructor with Oracle University. Hi Bill! What is automatic transaction quarantine and why do we need it? 01:27 Bill: The good news is that starting in 23c with the database quarantines, it's going to isolate a transaction or transactions that could possibly cause a system crash, so you can avoid crashes. It's going to isolate those transactions that potentially could cause a problem. However, those transactions must be manually resolved by the DBA so that the row locks are released from those bad transactions. A transaction recovery basically is going to isolate failure and also identify what is the cause of that corruption. So when a system restarts, transaction can fail to recover while the other transactions can be recovered. So with the transaction recovery, basically, we know when the system recovers, the SMON is going to use the redo and the undo. 02:27 Nikita: Can you explain that in a little more detail? How does transaction recovery work and why is it so critical for database stability? Bill: It does the redo to roll forward the database. However, at that point, it'll go ahead and open the database, allow it to start being used while it is applying the undo. And when it cannot apply that undo, that's when the system is going to mark that transaction as bad for that. That is what is transaction recovery. Whereas instance recovery is basically the same thing, except now you're in a RAC environment. And it's unable to be recovered on one of the instances within your RAC environment. Because it can be, it'll have those rows locked, and it can affect the other instances. So SMON might be unable to perform that recovery, so it could cause that PDB or the CDB to crash. OK, now, nobody can access any information. So once if that entire container crashes, recovery is going to stop. If it has a bad transaction, recovery stops. So it might be because of physical data, might be because of the index is corrupt, might be logical corruption. So it stops that interactive transaction recovery process. So not only does it stop the recovery of the transaction that is trying to be recovered by SMON, it's going to stop the rest of the inactive transactions. Those row locks are held. And it can impact critical operations. Yeah, if my system can't do anything, yes, it's going to have an impact. The DBAs must resolve what is that bad transaction, how to get rid of it, how we're going to get around it? 04:12 Lois: Bill, what’s the workflow a DBA would follow when a transaction is quarantined? Bill: So in the system, when that transaction recovery failure is, OK, I've found this dead transaction. I'm going to quarantine. I'm going to say, hey, you have something you need to take care of for that. So it's not recovered by the SMON. So what's going to happen? So there is also is going to be a limit. So if it does reach that limit and the limit is three, then you're going to have to step in and try to take care of that very quickly. The shut down abort will be performed on the PDB. So the good news there is that it's going to keep it from impacting the entire container. If the limit isn't reached, well, then, OK, hey, we have this bad transaction that's going to quarantine, is going to populate. There's a couple of views that you can go out and look at. There's a CDB quarantine transactions or a DBA quarantine transactions. Those views you can look at. And then once we determine that, what are we going to do to try to recover it? If we're going to try to recover it, then we can go ahead and drop that bad transaction. It'll help free up the rows. That way, everything can start working again. That PDB can be opened. 05:30 Nikita: What can you tell us about monitoring quarantined transactions? What specific views or logs should DBAs monitor? Bill: So you can view. You'll see these quarantine transactions in several different places. One is the alert queue. It's going to be sent to the alert queue. That is what is going to notify Enterprise Manager Cloud Control, also populates it within the AWR. Back in 21c, we added the attention log. It shows critical events. Hey, you need to take a look at this. It also can populate it. It will populate it to the alert log. So remember you have the V$DIAG_ALERT that you can look at. Or, if you're familiar with or you use the ADRCI, automatic diagnostic repair recovery advisor, so you can also look at the alert log there. So there are two new views, the CDB_QUARANTINED_TRANSACTION, the DBA_QUARANTINE_TRANSACTIONS working with multi-tenant. The CDB, I can see all the quarantine transactions from the root container, the DBA_QUARANTINE_TRANSACTIONS what I see if I'm in a specific PDB. But it's going to give me the information. 06:52 Lois: What about resolving quarantined transactions? Bill: Monitoring is a must to be able to identify, hey, we have bad transactions that we need to-- quarantine transactions we need to take care of. You can apply the appropriate MOS note if you're not sure what to do. Like anything else, if something happens-- and hopefully, you're not getting quarantined transactions daily or anything like that. But once we start doing a few things, we remember how to do them. 07:21 Lois: And, how do we take care of this? Bill: Well, you always have the ability to go to My Oracle Support. There is a view called-- that CDB quarantine transaction that we talked about that we can look at, hey, here's the reason. And we might use that to go out there and search My Oracle Support and/or contact Oracle Support. 07:49 Do you have an idea for a new course or learning opportunity? We’d love to hear it! Visit the Oracle University Learning Community and share your thoughts with us on the Idea Incubator. Your suggestion could find a place in future development projects! Visit mylearn.oracle.com to get started.  08:09 Nikita: Welcome back! Bill, what are some of the common causes of quarantined transactions? Could you share some examples with us? And how do you resolve them? Bill: One could be physical corruptions. It could either be logical or physical. So maybe because media failed. Hardware bits get flipped. So that might be able to be easily fixed by using the RMAN Block Media Recovery. And that's the lowest level of recovery that we can apply. And then there's logical corruptions. This is the recommended order when trying to resolve logical corruptions. First level is the Block Media Recovery. And then, after that, if the Block Media Recovery fails, then possibly, how about re-creating that data segment? So either truncate or drop it, and then recover it from another source. So once you drop the segment, the transaction then is going to skip trying to recover it. It's no longer there. So it's, OK, hey, I'm successful now. And then, the last resort type method is to drop that undo segment. There's an offline rollback segment that you can use. But it's recommended-- it's best to avoid that-- again, kind of a last-ditch effort to try to fix something. There are other options that you might try. However, these options do end up being a loss of data. Why? Because we're going to do a point-in-time recovery. So we can go back to a table point-in-time recovery. So we start with the Block Media Recovery. OK, we can't. OK, so how about if we go back before that transaction and try to recover the table at that time? So it will be a loss of data. Then, the next level is, we can't do the table. Can we do the entire tablespace? That might be an option. Might flashback the database if we are using-- if we have Flashback Database on. Again, that's just another method of point-in-time recovery. And then also do a database point-in-time recovery. If we can do the database point-in-time recovery flashback at the PDB level, so that way it's not impacting the entire container, hopefully, we don't have to try to do a point-in-ti

Join Lois Houston and Nikita Abraham as they discuss the Automatic Transaction Rollback feature with Senior Principal Database & MySQL Instructor, Bill Millar. Bill explains that in the 23ai release, transactions blocking other transactions can now be automatically rolled back, depending on certain parameters. Bill highlights the advantages of using automatic transaction rollback, which eliminates the time-consuming process of manually terminating blocking transactions. They also cover the workload reduction benefits for database administrators.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/140830/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   -------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Lois: Hello and welcome back to the Oracle University Podcast. I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Team Lead of Editorial Services. Nikita: Hi everyone! Last week, we looked at two Oracle Database 23ai new features related to Data Manipulation Language, or DML. One was Unrestricted Parallel DMLs and the other was Unrestricted Direct Loads. Do check out that episode if you missed it. 00:56 Lois: Today, we have Senior Principal Database & MySQL Instructor, Bill Millar, with us. He’s been on several times this season taking us through all the different 23ai new features. In this episode, we’re going to ask him about the Automatic Transaction Rollback feature. Hi Bill! What is automatic transaction rollback and why is it an important feature for database administrators? 01:22 Bill: We can now have transactions that are blocking other transactions, depending on some settings, to automatically roll back. It does require some parameters to be set. Rows basically get locked in a single row. Each row is locked based off of what type of activity is being performed on that row, such as inserts, updates, deletes, merge, select for updates. 01:52 Nikita: And how were things before this feature? Bill: Traditionally, the database administrator had to research and manually terminate blocking transactions, or there are some things that resource manager might have been able to do. 02:05 Lois: This seems like such a game-changer for DBAs, Bill. So, how does it work? Bill: So there are some parameters that control the automatic rollback. One is the transaction priority. We're going to set that priority for a transaction either to medium, high, or low. We have the high priority wait target and a medium priority wait target that we can set. The high wait target will terminate if a medium transaction is blocking that high target based off of the values that we set, the medium transaction can be terminated. A medium transaction will terminate a low priority. So if a transaction designated as low exceeds the blocking time that we set for the medium priority wait time, then it'll be terminated. Whereas, the high priority will terminate both medium and low transactions. We have the rollback mode. We're either going to roll back or we're going to track, depending on what we're trying to do. 03:10 Nikita: So, if I decide that I want to use automatic transaction rollback… if I decide to implement it…I’ll need to set those parameters, right? Bill: So we can set those at a session level. We also have some system level wait targets. What are the wait times for the medium, high transactions? How long they are going to wait for those lower transactions? And then we also have the rollback mode. Are we actually going to roll back or are we just going to track for right now? We have to determine what is going to be the wait times for those transactions that we want to wait before those lower transactions, priority transactions are rolled back? At that session level, we're going to set the session. High is the default. So if we want transactions to run at a lower, we have to set those. So we can set the medium or low because that's going to determine how they're rolled back. So, what is that rollback order? Again the low, we'll roll back any low that's blocking mediums. High, we'll roll back any mediums or lows that are blocking. So you do need to have the understanding of that application, and how critical are the different transactions, because if you start rolling back transactions, what? It does-- If you roll back the transactions, it does generate a little research, a little bit more work on why did that happen. 04:38 Lois: Yeah… you don't want to set it without really understanding what you’re doing. Ok, so, what else do I need to know? Bill: So we do have the system level wait targets again. How long is the high priority transaction going to wait for a lower transaction before it rolls it back? How long that medium priority is going to wait? We use the ALTER SYSTEM SET command. It does have a range of values from one second to 2,147,483,647 seconds. That's like 68 years. Might not want to wait 68 years for a transaction to be rolled back. We can set it at the PDB level. Each pluggable can have a different value. And it can have a different value in the different RAC instances. We have those system level wait targets that we want to set. Automatic rollback. In order for it to function, all the parameters have to be set properly. What is that transaction priority? We saw the medium, high, low. What is the wait target? How long is the medium is going to wait? How long is the low is going to wait? We set that in seconds. The order of those transactions determine how they are terminated. 05:53 Lois: Earlier on, you mentioned rollback mode. Can you tell us a little more about it? Bill: So with that automatic rollback mode, there's only two valid values. It is considered advanced parameter. We can either set it in rollback, which is the default, or we can put it in track mode. Track mode gives us the ability to try it out. I guess you can say. It will say, hey, if I would have been running, if I would have been used, I would have terminated this transaction. It'll show me the number of times it would have happened for high priority, the number of times it would happen for a medium priority. It is modifiable in the PDB, but however, the track mode must be the same in each instance. So that rollback mode, again, that is the default value for that. So statistics are going to be available. So how many high priority rollbacks occurred? How many medium rollbacks occurred? In that track mode, I have to set that value. I do have to have the time set for how long is it going to wait for those, so the high and medium. And those priorities has to be set in the session. So statistics are available for the high and the medium in the track mode. Not only when we're actually rolling back, but also tracking. Again, this gives us the ability, by having it in the track mode, gives us the ability to do a little testing with it first. 07:27 The Oracle University Learning Community is an excellent place to collaborate and learn with Oracle experts and fellow learners. Grow your skills, inspire innovation, and celebrate your successes. All your activities, from liking a post to answering questions and sharing with others, will help you earn a valuable reputation, badges, and ranks to be recognized in the community. Visit mylearn.oracle.com to get started. 07:55 Nikita: Welcome back! Bill, when it comes to monitoring, how do you keep track of these rollbacks? Bill: For monitoring our rollback transactions, the data dictionary information is available to assist with monitoring our transaction priority. So from the V$TRANSACTIONS, there are columns available allowing us to do that. Based off that transaction priority shows what is the wait target for that. And then also each of the priority of those transactions. We can view this information, it will be populated to the alert log. So we can see that session ID, what was session ID of that? What was the transaction ID? What was the priority? What was the system identifier for that? It tells you-- even tells you the parameter and tells you what that wait time was set at. If it was a medium transaction that was terminated, it shows, OK, it was a medium. So we can view the alert log. And we can look for these terminations. Gives an idea of what's being done. 09:01 Nikita: And finally, what are the key advantages of using automatic transaction rollback? Bill: It eliminates a very manual process. It can be very time-consuming for the DBA to go out there and try to find what's the blocking session. Yep, I'll go ahead and do an ALTER SYSTEM. I'll kill that session trying to track it down, finding the views to look at it to say, OK, Yeah, this is the blocking one. I want to go ahead and take care of it. Resource manager doesn't really fully address blocking transactions. Some things that can do for that. We have the maximum estimate execution time. So that's the number in CPU seconds allowed for that call. It's terminated. It doesn't matter whether it's blocking another session or not in that case or even another transaction. It just says, OK, you exceeded this time. I'm going to terminate you. Then we also have the max idle time again. That's maximum session idle time. All right. You haven't been doing anything to session, we're going to terminate you. And then we have the MAX_IDLE_BLOCKER. That's the time duration of an idl

In this episode, hosts Lois Houston and Nikita Abraham discuss new features in Oracle Database 23ai related to Data Manipulation Language (DML). They are joined by Senior Principal Database & MySQL Instructor, Bill Millar, who explains the concept of unrestricted parallel DMLs and their importance in speeding up large operations and maintaining summary tables. The discussion then turns to unrestricted direct loads, examining the evolution of direct loads with 23ai and the broader impact of these changes.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/140830/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00  Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Principal Technical Editor with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi there! In our last episode, we discussed a ground-breaking caching solution in Oracle Database 23ai, known as True Cache. We spoke about its configuration and deployment, and explored how to apply True Cache to our applications. Nikita: Today, we’re going to talk about two Oracle Database 23ai new features related to Data Manipulation Language, or DML. The first is Unrestricted Parallel DMLs and then we’ll move on to Unrestricted Direct Loads. We’ll talk about the situation prior to 23ai, identify the improvements that have been made, and look at their benefits. 01:15 Lois: And returning for another episode is Bill Millar, our Senior Principal Database & MySQL Instructor with Oracle University. Hi Bill! So, to start, can you explain what unrestricted parallel DMLs are and why they are important, especially in the context of Oracle Database? Bill: The Oracle Database allows DML statements such as inserts, updates, deletes, merge to be executed in parallel by breaking those statements into smaller task. These transactions can contain multiple DML statements. And they can modify multiple different tables. So transactions with the parallel DML is going to use the execution method by breaking up those large operations to execute the transaction in parallel. It helps speed up the large operations. And it's advantageous to data warehouse environments where we're maintaining like summary tables, historical tables. And even in OLTP systems, it can be beneficial for long-running batch jobs. The scale up. Well, it's basically dividing the executing SQL against those large tables and indexes into those smaller units of work. 02:36 Nikita: So, what were the limitations prior to 23ai? Bill: So once that object was modified by APLL statement, the object cannot be read or modified later in the same transaction. After that parallel DML modifies a table, there is no follow-on DML or query on the same table within that same transaction. If any attempt to access a table modified by that parallel statement, the transaction would be rejected. You're only allowed to query on those tables prior to that DML on that object itself. 03:16 Lois: Ok… So with these new improvements, I’m guessing some of these restrictions have been removed? Bill: In this case, in the same session, you can query the table multiple times. You can perform conventional DML on the same table within the same session. And you can also have multiple direct loads in the same session without having to do that commit. But there are still some restrictions with it. Heap tables only. You can't do it with any clustered tables or IOT, Index Organized Tables. Non-ASSM, the Automatic Segment Space Management tables. The temp table is not under ASSM. Why? Because it has to have uniform extents or any other tablespaces that you created with the uniform extents. So those restrictions still apply. So some of the improvements are some of the restrictions can help reduce the overhead. We can enable Parallel DML within that session. Allows the multiple operations on the same object. And it doesn't require that commit for each separate operation. Makes it a little bit easier to use by removing some of these limitations. Now users can run parallel DMLs and any combination of statements within that same transaction. And it can help simplify and speed up data loading analytic processes by making the database, the parallel execution and parallel queries, at the same time within that same session, again, eliminating having to do commits. 04:58 Nikita: Thanks for that summary of all the improvements, Bill. Now, how do you enable this? Is it enabled by default? Bill: To enable the Parallel DML mode, it is required for a session. It is disabled by default. That's because the Parallel DML and Serial DML, they have different locking, different ways to handle the transactions, different disk space requirements. When Parallel DML is enabled in a session, all DML statements are considered for parallel execution. Only a statement is considered for parallel execution when the Enable Parallel DML hint is used if I don't set it for a session. The sessions DML mode does not influence any parallelism of DDL statements. When the Parallel DML is disabled, no DML is executed in parallel, even if the hint is used. 05:59 Lois: Bill, I would like to dig a little deeper into the benefits. How do these lifted restrictions improve the overall performance and reduce overhead? Bill: There's no longer that requirement to commit everything separately. So that's going to reduce the overhead, not having to do the commit all the time. The scalability of accessing those large objects, executing parallel makes the decision support systems, those data warehouses and batch OLTP jobs or any other larger DML operation execute faster. By removing that one touch limitation, it allows the parallel DML statements to be read or modified by later statements of the same transaction in the same session. It's very similar to the non-parallel statements. And even OLTP systems can also benefit, for example, maintaining a larger operation, such as the creation of indexes, refreshing tables, or even creating summary tables. 07:14 Did you know that Oracle University offers free courses on Oracle Cloud Infrastructure? You’ll find training on everything from cloud computing, database, and security to artificial intelligence and machine learning, all free to subscribers. So, what are you waiting for? Pick a topic, leverage the Oracle University Learning Community to ask questions, and then sit for your certification. Visit mylearn.oracle.com to get started. 07:42 Nikita: Welcome back! Let's move on to the next new feature, which is unrestricted direct loads. Bill, what was the situation with direct loads like, prior to 23ai? Bill: After a direct load and prior-- it was always prior to a commit, queries in additional DMLs were not allowed on that same table. You might encounter the ORA error, the 12838, saying, hey, you can't read or modify this in parallel. That's because the DML on that direct load had access to that and that session for that. So you might have received that error. The enq contention, the wait event for the direct load issue in a different session from the other sessions during the direct load is having to wait, because of that queuing that-- because a transaction gets that table, locks that information to keep that table from being modified until that direct load has actually committed. Within the same transaction, within the same session, trying to do multiple DMLs with the-- while it is being modified with the direct loads itself. Unlike conventional loads, the direct loads, as the new blocks and extents are added to the segment, the high water mark does not actually get moved until the actual commit itself. So that's why there is restrictions in the same session or even in other sessions to be able to do anything. So to prevent the errors, the applications had to do a commit immediately after that direct load to prevent those errors from happening. Well now, there are restrictions when that direct load was done prior to that commit for that. The same table in the same session, couldn't query, couldn't do any additional DMLs, couldn't do any additional parallel DMLs. And even in other sessions, queries were not allowed on the same tables that was in use by the other session. So no additional conventional DMLs, no additional parallel DMLs were allowed. 10:09 Lois: Ok.. it was restrictive in what could be done. So, how have direct loads evolved with the 23ai release? Bill: Some of those previous restrictions have been lifted in that same session with that same table. So now you can immediately-- and notice that we're talking here, same session, same table. All right. So you can query multiple times within that same session. You can perform additional DML and you can also do multiple direct loads in the same session without having to do that commit. However, there still are restrictions. It has to be a heap table. It does not work with index organized tables or clustered tables. And the tablespace, if it's not using the automatic segment space management, it cannot-- it does not apply to those either, or if tables with a uniform extents-- tablespace with uniform extents. That's why anything in the temporary table is also included. Why? Because the temporary tablespace has to be uniform extents. 11:17 Nikita: So, what are the restrictions lifted for diffe

Hosts Lois Houston and Nikita Abraham are joined by Senior Principal Database & MySQL Instructor Bill Millar who explains Oracle's newest caching solution called True Cache. Available in Oracle Database 23ai, True Cache is an automatically managed, in-memory, read-only cache that improves application performance dramatically. Bill provides an overview of its features and highlights the benefits of using True Cache.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/140830/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Lois: Hello and welcome to the Oracle University Podcast. I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Principal Technical Editor. Nikita: Hi everyone! Last week, we had quite a power-packed episode. We discussed the 23ai new feature for Automatic SQL Plan Management. We also looked at the 23ai automatic feature that enhances SecureFiles LOB Write Performance as well as the update to Wide Columns. 00:59 Lois: Yeah, and in today’s episode, we will look at True Cache, another 23ai new feature. To tell us all about it, we have Bill Millar back with us. Bill is a Senior Principal Database & MySQL Instructor with Oracle University. We'll ask Bill to give us an overview of True Cache, talk about its configuration and deployment, and discuss how to apply True Cache to our applications.  Nikita: To kick things off, Bill, can you give us a high-level overview of what True Cache is? How does it differ from other caching solutions like Redis or Memcached? 01:35 Bill: True Cache is an in-memory cache. It is read-only. True Cache is deployed in front of a primary database, and it is automatically managed. It keeps the most frequently accessed data in the cache, and it keeps the cache consistent with the primary database. They call it diskless, but it's not. It does require some space for SP file, redo logs, control files, and such. But it's very similar to Active Data Guard.  The queries can be offloaded to the True Cache for faster query response. And the data in the query cache is consistent. Unlike other mid-tier caches like Redis or Memcached, a query to the True Cache returns only committed data, and the data is always consistent. It's secure. Why? Because we implement our Oracle database security policies and you can control access to the cache.  02:33 Lois: So, why should we use True Cache?  Bill: Improve application performance without having to rewrite any applications. That can save considerable amount of time, effort, and expense. Reduces the application response time. So the closer the True Cache is to the application, the faster the response. Now, you do need a large amount of memory. We're talking memory here. It's an in-memory storage area, and depending on how you configure it, you can have it shared, you can have it divided. We mentioned it's automatically maintained. So there's no application changes required, and it is transparent to the application. Again, simplifies that development and maintenance.  03:15 Nikita: How does it impact application performance, and what kind of scenarios would benefit the most from implementing True Cache? Bill: So at a high-level view, True Cache or primary database, the application configuration serves as other things that are going to decide where is it going to query the data from, from the True Cache or from the primary database.  The True Cache satisfies that query. And that's where the data will be fetched from. If not, then from the primary database. On start up, True Cache is empty. So it starts reading large chunks of data to populate the True Cache. So after a block is cached, then again, it can be automatically updated, apply the redo to it-- very similar to the Oracle Active Data Guard. In the data returned, it is always going to be consistent.  04:04 Lois: Is it going to be current data? Bill: Maybe, maybe not. If it's been updated in the primary, if they redo apply hasn't occurred yet, then it's not the most consistent. But as far as the query cache is concerned, it is the most current because we only display consistent. You can have multiple True Caches. You can save the same database application service to the True Cache as you can partition it.  04:28 Nikita: I'm curious about the memory requirements, Bill. How crucial is memory for True Cache's performance? Bill: You need to have significant amount of memory. Memory, memory, memory. So True Cache is completely memory, memory. So I want to have all my data possible in there. The more memory you have, the less likely something is going to age out. And of course, just like with the standard caching, you can also pin objects to stay in the True Cache.  Yeah, like I said, there are some requirements for storage, even though it's called diskless because of, again, redo log files, configuration files like the control files, SP file. And again it is read only.  05:11 Lois: Can you explain the differences between using physical and logical connections with True Cache? How does this impact the way applications interact with the database? Bill: So with using the True Cache, we have two physical connections, and we can have one to the primary database and one to the True Cache. Each connection has a database application service associated with it, and it's going to choose which connection to use based whether it's going to go to the True Cache or to the primary database.  The second way is the application maintains one logical connection that uses the application service for the primary database. It's the JDBC thin driver, starting with Oracle Database 23 is available. It's going to maintain the physical connections to the primary database and the True Cache itself. Now, the logical connection, the one logical and one physical, is for Java applications only.  Applications that work with JSON, we extend the HTTP entity tag support for that. So a database GET request to the True Cache is going to compute the ETag, insert it into the return document.  06:27 Nikita: But what happens if there’s a mismatch when the modified document is put back into the primary database?  Bill: Well, then the database is going to verify. OK, what happens with that?  It's going to verify the document row still matches that ETag for that. If with that put command, let's say, I have new data here, the row is going to match that ETag that was automatically updated. If there's no match, another user has changed the data and the PUT request is rejected. So the PUT request can be retired using the new data.  07:05 Are you planning to become an Oracle Certified Professional this year? Whether you're a seasoned IT pro or just starting your career, getting certified can give you a significant boost. And don't worry, we've got your back! Join us at one of our cert prep live events in the Oracle University Learning Community. You'll get insider tips from seasoned experts and learn from other professionals' experiences. Plus, once you've earned your certification, you'll become part of our exclusive forum for Oracle-certified users. So, what are you waiting for? Head over to mylearn.oracle.com and create an account to jump-start your journey towards certification today! 07:48 Nikita: Welcome back! Now, how do you configure True Cache, Bill? Bill: You can configure True Cache one of two ways. You can either use the Database Configuration Assistant, which actually makes it a little simpler to configure it, and you can also manually create it.  You have some environment options. One is a uniform configuration where you can deploy identical True Cache that use the same database application service. Another way is partition configuration. The data is going to be divided across multiple True Caches, which, each cache is a different subset of the data. You can also deploy True Cache in a RAC environment. As one might expect, there are some additional configuration steps for a RAC environment.  You want to make sure you verify your configuration, that the database application services are working as expected after you configure it. And then, optionally, you can enable DML redirection. What that will do, it writes data to the primary database, and that data is automatically updated in the cache. It's very similar how to the Oracle Active Data Guard works. Because the DML redirection uses more resources, it's not recommended for update-intensive applications.  There is a parameter, a ADG_REDIRECT_DML initialization parameter, that you will set to True in order to do that.  09:18 Lois: Bill, what are the specific challenges or considerations that administrators should be aware of during the configuration process?  Bill: You do need to make sure your network is configured for True Cache in the primary database. So optionally, you can create a remote listener for high availability. But you create your True Cache. You go ahead, and make sure that you have your primary database. You want the network configuration for both of those. And then you create the True Cache. Once the True Cache is created, you're going to create the application services associated with the database. And then, you're going to start the database application services on the True Cache.  When it comes to naming the application service names, each primary database application is going to be associated with a corres

Join Lois Houston and Nikita Abraham, along with Senior Principal Database & Security Instructor Ron Soltani, as they discuss how the new Automatic SQL Plan Management feature in Oracle Database 23ai improves performance consistency and simplifies management. Then, Senior Principal Database & MySQL Instructor Bill Millar shares insights into two new features: one that enhances SecureFiles LOB Write Performance, improving read and write speeds, and another that increases the column limit in a table to 4,096, making it easier to handle complex data.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/137192/207062   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Principal Technical Editor with Oracle University, and joining me is Lois Houston, Director of Innovation Programs. Lois: Hi there! Last week, we looked at the Oracle Database 23ai enhancements that have been made to Hybrid Columnar Compression and Fast Ingest. In today’s episode, we’ll talk about the 23ai new feature for Automatic SQL Plan Management with Ron Soltani, a Senior Principal Database & Security Instructor with Oracle University.  01:01 Nikita: And later on, we’ll be joined by Bill Millar, another Senior Principal Database & MySQL Instructor, who will tell us about the 23ai automatic feature that enhances SecureFiles LOB Write Performance. We’ll also get him to talk about the Wide Columns update. So, let’s get started. Hi Ron! What have been the common challenges with SQL plans and database performance? Ron: One of the problems that we have always had, if you remember, was when data changes, database setting configuration, parameter changes, SQL that were operating very well could now behave badly using the SQL plan that were associated to them. And remember, the same SQL plan generally Oracle likes to continuously reuse.  So the SQL plans were put in the baseline in the past, and we could have those SQL plan baseline, which are a set of approved plans to be used for a SQL from the SQL history stored in AWR, then could be used for the optimizer to choose from. However, which plan to choose and which one would be the best one to use, this is what the problem has been in managing the SQL plan baselines, and a lot of the operation would have been done manually.  02:22 Lois: And what have we done to overcome this?  Ron: So now this new system will going to perform all of those operations automatically for us. Now it can search the Automatic Workload Repository. It can find SQL plans for a particular SQL statement, then look for any alternative plans that may available in alternate sources like SQL tuning sets. And then validate those plans and see if those plans are going to be good and to be used as SQL plan baseline for executing SQL statement by the optimizer. 03:00 Nikita: So we now have the Automatic SQL Plan Management Evolve Advisor to help manage operations automatically, right? Can you tell us a little more about it? How does it ensure optimal performance? Ron: This is an automatic advisor that is created that can go look for different plans and validate the plans by examining them, making sure that they are not causing any regression compared to the previous operation, and then evolve that plan into a good baseline.  This simplifies management of the baseline repository for a SQL statement. So as data changes, as parameters changes, optimizer could come up with different type of plans that are set within this baseline that has been validated to be good baseline for each situational operation. So this way you reduce a lot of hard parsing operations.  04:00 Lois: And how does the SQL Evolve Advisor work, Ron? Ron: First, it will check the AWR to find what are the top SQLs that has been found. Then it will look to see if these top SQLs who did not perform well with the plan that they have, that's why they're top SQL, have other alternative plans that are stored in the SQL plan history, in AWR, or available in any other sources.  Then if it finds any additional plans, it will go ahead and add all of those plans into the plan history. So in the plan history, now you have accumulation of all the plans available in AWR and anything that has been brought from other sources. Then it will test every one of those plans and validate that by use of the plan, the SQL statement will not deprivate and get slower. The performance is either similar or actually better. So normally, there is a percentage that the SQL should improve. So we will then validate these baselines.  And finally, once the baselines or those plans have been validated, they will be accepted, and then they will be added as SQL plan baselines. They will remain in the statement history, in the AWR, and will be available for optimizer for the future use.  05:28 Nikita: What are the benefits of this? Ron: Number one is Autonomous Database. As you know, they want to automate all management, including management of the SQL execution due to changes that are happening for the application, for the data, or the database and its environment.  It totally eliminates any manual intervention for management of the statement, and it can transparently repair any statement that had been affected by a major change.  06:00 Lois: What sort of problems does this feature solve for us? Ron: Of course, this is a performance consistency. We want to make sure that every statement performed to its best performance and any specific changes that may impact those SQL statements would be taken into an account, and a better plan, if available, would then be available for use.  It also improves the application performance level, therefore database service level will get much improvement. And the SQL execution plans will be automatically managed behind the scene by expanding these baselines, by managing all of these baseline history and all of that that is managed by this automatic SQL plan management environment automatically.  06:50 Nikita: And when do we use this?  Ron: If there is a change in a database environment, like you add SGA, the change into the shared pool, change in the size of the buffer cache or any type of storage effects. So all of those can actually affect the SQL execution.  Now all of those changes, including data changes, can cause a SQL plan to not behave very well or behave as well as it was doing before. Therefore, if particular plans do not perform as well as they did before, that affects the performance of the application. This also affects the performance of the database and the instance.  07:35 Lois: So, how do we use this environment?  Ron: Well, best news that I have for you in that is that there is nothing manual needs to be done. All we need to do is, number one, make sure that we enable foreground automatic SQL plan management that we done through the package for the DBMS SPM for SQL plan management.  You will use the package with the configure option, and you enable the auto SPM evolve task, and you set it to auto. Once this is done, now the SQL evolve plan management and advisor are enabled, and they will then monitor your statements, review all of the top SQLs as they are found with all of the ADDM operation, and then do their work in looking for better plans and being able to maintain the SQL plan baselines we talked about.  Now for you to be able to view, monitor, and see how these operations are going, if it is enabled, you can take a look at the DBA SQL plan baseline's view. There are many, many columns in that particular baseline, and there are also columns that has been added that tell you where is the plan generated from, if a plan is approved, and any other user interaction with the plan or settings can then be verified using that DBA SQL plan baseline view.  09:13 Are you looking for practical use cases to help you plan and apply configurations that solve real-world challenges? With the new Applied Learning courses for Cloud Applications, you'll be able to practically apply the concepts learned in our implementation courses and work through case studies featuring key decisions and configurations encountered during a typical Oracle Cloud Applications implementation. Applied learning scenarios are currently available for General Ledger, Payables, Receivables, Accounting Hub, Global Human Resources, Talent Management, Inventory, and Procurement, with many more to come! Visit mylearn.oracle.com to get started. 09:54 Nikita: Welcome back! Let’s bring Bill into the conversation. Hi  Bill! Can you tell us about the 23ai automatic feature that enhances SecureFiles LOB Write Performance?  Bill: The key here is that it is automatic and transparent. There's no parameters set. Nothing to configure in table, no hints, and nothing that you have to do with these improvements. It is tightly integrated with SecureFiles LOB infrastructure.  So now, multiple LOBs can be handled in a single transaction and can be buffered simultaneously. This will help with mixed workloads, switching between the LOBs that are writing in a single transaction. The PGA will adaptively resize based off the size for these large writes for the LOBs if you're using the No Cache option. Remember, no cache is going to bypass the buffer cache and does direct reads and writes from the PGA.  JSON type will be transformed into the OSON Oracle data typ

In this episode, hosts Lois Houston and Nikita Abraham speak with Senior Principal Database & MySQL Instructor Bill Millar about the enhanced performance of Hybrid Columnar Compression, the different compression levels, and how to achieve the best compression for your tables. Then, they delve into Fast Ingest, what’s new in Oracle Database 23ai, and the benefits of these improvements.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/137192/207062   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Lois: Hello and welcome to the Oracle University Podcast. I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Principal Technical Editor. Nikita: Hi everyone! In our last episode, we spoke about the 23ai improvements in time and data handling and data storage with Senior Principal Instructor Serge Moiseev. Today, we’re going to discuss the enhancements that have been made to the performance of Hybrid Columnar Compression. We'll look at how Hybrid Columnar Compression was prior to 23ai, learn about the changes that have been made, talk about how to use this compression in 23ai, and look at some performance factors. After that, we’ll move on to Fast Ingest, the improvements in 23ai, and how it is managed. 01:15 Lois: Yeah, this is a packed episode and to take us through all this, we have Bill Millar back on the podcast. Bill is a Senior Principal Database & MySQL Instructor with Oracle University. Hi Bill! Thanks for joining us. So, let’s start with how Hybrid Columnar Compression was prior to 23ai. What can you tell us about it? Bill: We support all kinds of platforms from the Database Enterprise Edition on up to the high engineered systems for that and even the Exadata Cloud at the Customer. We have four different levels of compression. One is considered the warehouse compression where we do a COLUMN STORE COMPRESS FOR QUERY LOW and COLUMN STORE COMPRESS FOR QUERY HIGH. The COLUMN STORE COMPRESS FOR QUERY HIGH is the default, unless another compression level is specified. With the archive compression, we have the COLUMN STORE COMPRESSED FOR ARCHIVE LOW and also COLUMN STORE COMPRESS FOR ARCHIVE HIGH. With the Hybrid Columnar Compression warehouse and archive, the array inserts are compressed immediately. But, however, some conditions have to be met. It has to be a locally-- to use these, it has to be a locally managed tablespace, the automatic segment space management. And compatibility level, at least 12 too or higher when these values have been introduced. There are different compressors that are used for the compression hidden from the customer. It just depends on what is selected as to what is going to be the compression that's going to be used for--  notice that with the COLUMN STORE FOR QUERY HIGH and for ARCHIVE LOW, the zlib compression method is used, whereas if you select the ARCHIVE HIGH, the Bzip2. And in 19C, we added the Zstandard. And it's available for the MEMORY COMPRESS FOR CAPACITY HIGH.  03:30 Nikita: So, what’s happened in 23ai? Bill: When in 23c, to take advantage of the changes in compression, the compatibility level has to be set at least to 23.0.0 or higher. When a table is created or altered with the hybrid column compression, the Zstandard will automatically be selected. So it doesn't matter which one of the four you select, that will be the one that is selected. It is internally set transparent to the user. There is no new SQL format that has to be used in order for the Zstandard compression to be applied. And the Database Compatibility Mode has to be at least at 23.0.0 or higher. Only then can the format of the Hybrid Column Compression storage use that Zstandard compression. If we already have compressed data blocks in existing tables, they're going to remain in their original format.  04:31 Lois: And are the objects regenerated? Bill: If the objects are-- they might be regenerated if they were deleted in another operation. If you want to completely take advantage of the new compression, all you have to do is alter table move. And that's going to go ahead and trigger the recompression of that, whereas any newly created tables that are created will use the Zstandard by default. 05:00 Nikita: What are the performance factors we need to think about, Bill? Bill: There are some performance factors that we do need to consider, the ratio, the amount of space reduction in storage that we're going to achieve, the time spent compressing the data, the CPU cost to compress that data, and also, is there any decompression rate, time spent decompressing the data when we're doing queries on it? 05:24 Lois: And not all tables are equal, are they? Bill: Not all tables are equal. Some might get better performance by different compression level than others for that. So how we can basically have to test our results, there is a compression advisor that's available, that you can use to give you a recommendation on what compression to use. But only through testing can we really see the availability, the benefits of using that compression for an application. So best compression, just as in previous versions, the higher the compression levels, the more CPU it's going to use. The higher the compression level, the more space savings that we're going to achieve for that as we are doing those direct path inserts. So there's always that cost. 06:20 Did you know that the Oracle University Learning Community regularly holds live events hosted by Oracle expert instructors. Find out how to prepare for your certification exams. Learn about the latest technology advances and features. Ask questions in real time and learn from an Oracle subject matter expert. From Ask Me Anything about certification to Ask the Instructor coaching sessions, you’ll be able to achieve your learning goals for 2024 in no time. Join a live event today and witness firsthand the transformative power of the Oracle University Learning Community. Visit mylearn.oracle.com to get started.  07:01 Nikita: Welcome back! Let’s now move on to the enhancements that have been made to fast ingest. We’ll begin with an overview of fast ingest, how to use it, and the improvements and benefits. And then we’ll look at some features for managing fast ingest. Bill, why don’t you start by defining fast ingest for us?  Bill: Traditionally the fast ingest, also referred to as deferred inserts, is faster than processing a single row at a time. It can support high-volume transactions like from the Internet of Things applications, where you have hundreds of thousands of items coming in trying to write to the database. They are faster, because the inserts don't use the traditional buffer cache. They use a pool that will size out of the large pool. And then they're later written to disk using the SMCO, the space management coordinator. Instead of using the buffer cache, they're going to write into an area of the large pool. The space management coordinator, it has these helper threads, however many-- that's just a number for that-- that will buffer. And as buffer is filled based off size of that algorithm, it will then write those deferred inserts into the database itself. 08:24 Lois: So, do deferred inserts support constraints? Bill: Deferred writes do support constraints in index just as for regular inserts. However, performance benchmarks that have been done recommend that you disable constraints, if you're going to use the fast ingest. 08:41 Lois: Can you tell us a bit about the streaming and ingest mechanism?  Bill: We declare a table with the memoptimize for write. We can do that in the create table statement, or we can alter the table for that. The data is written to the large pool, unlike traditionally writing items to the buffer cache. It's going to write to the ingest buffer, the large pool. And it's going to be drained. It's going to be written from that area by using those background processes to write to the actual database itself. So the very high throughput, since drainers issues deferred writes in large batches. So we're not having to wait especially for the buffer cache. OK, I need space. OK, I need to write. I need to free up blocks. Very ideal for these streaming inserts, sensor readings, alarms, door locks. Those type of things. 09:33 Nikita: How does performance improve with this? Bill: With the benchmarks we have done, we have found that the performance can be up to 75% faster by going ahead and doing the fast ingest versus traditional inserts. The 23 million inserts per second on a single X6-2 server with the benchmarks that we have. 09:58 Nikita: Are there any considerations to keep in mind? Bill: With the fast ingest, some things to consider for that. The written data, you might need to validate to make sure it's there. So you might have input files that are writing to that that are loading it. You might want to hang on to those, before that data destroyed. Have some kind of mechanism to validate, yes, it was written. There is a possible loss of data. Why? Because unlike the buffer cache that has the recovery mechanism with the redo and the undo, there is none with that large pool. So that's why if the system crashes, and the buffers haven't been flushed yet, then it's possible loss of data. There's no queries from the large pool meaning that if I want to query the information that the fast inges

In this episode, hosts Lois Houston and Nikita Abraham discuss improvements in time and data handling and data storage in Oracle Database 23ai. They are joined by Senior Principal Instructor Serge Moiseev, who explains the benefit of allowing databases to have their own time zones, separate from the host operating system. Serge also highlights two data storage improvements: Automatic SecureFiles Shrink, which optimizes disk space usage, and Automatic Storage Compression, which enhances database performance and efficiency. These features aim to reduce the reliance on DBAs and improve overall database management.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/137192/207062   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   ---------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Principal Technical Editor with Oracle University, and joining me is Lois Houston, Director of Innovation Programs. Lois: Hi there! Over the past two weeks, we've delved into database sharding, exploring what it is, Oracle Database Sharding, its benefits, and architecture. We’ve also examined each new feature in Oracle Database 23ai related to sharding. If that sounds intriguing to you, make sure to check out those episodes. And just to remind you, even though most of you already know, 23ai was previously known as 23c. 01:04  Nikita: That’s right, Lois. In today’s episode, we’re going to talk about the 23ai improvements in time and data handling and data storage with one of our Senior Principal Instructors at Oracle University, Serge Moiseev. Hi Serge! Thanks for joining us today. Let’s start with time and data handling. I know there are two new changes here in 23ai: the enhanced time zone data upgrade and the improved system data and system timestamp data handling. What are some challenges associated with time zone data in databases? 01:37 Serge: Time zone definitions change from time to time due to legislative reasons. There are certain considerations. Changes include daylight savings time when we switch, include the activity that affects the Oracle Database time zone files.  Time zone files are modified and used by the administrators. Customers select the time zone file to use whenever it's appropriate. And customers can manage the upgrade whenever it happens.  The upgrades affect columns of type TIMESTAMP with TIME ZONE. Now, the upgrades can be online or offline.  02:24 Lois: And how have we optimized this process now?  Serge: Oracle Database 23c improves the upgrade by reducing the resources used, by selectively using the updates and minimizing the application impact. And only the data that has dependencies on the time zone would be impacted by the upgrade.  The optimization of the time zone file upgrade does not really change the upgrade process, so upgrade can be done offline. Database would be unavailable for a prolonged period of time, which is not optimal for today's database availability requirements.  Online upgrade, in this case, we want to minimize the application impact while the data is being upgraded. With the 23c database enhancement for time zone file change handling, the modified data is minimized, which means that the database updates only impacted rows. And it reduces the impact to the applications and other database operations.  03:40 Nikita: Serge, how does updating only the impacted rows improve the efficiency of the upgrade process? Serge: The benefits of enhanced timezone update include customers who manage large fleet of databases. They will benefit tremendously with a lower downtime. The DBAs will benefit due to the faster updates and less resource consumption needed to apply those updates. And that improves the efficiency of the update process.  Tables with no affected data are simply skipped and not touched. All results in the significant resource savings on the upgrade of the time zone files. It applies to all customers that utilize timestamp with time zone columns for their data storage.  04:32 Lois: Excellent! Now, what can you tell us about the improved system data and system timestamp data handling?  Serge: Date and time in Oracle databases depends on the system time as well as the database settings. System time now can be set as the local time zone for an individual database.  04:53 Nikita: How was it before this update? Serge: Before 23c, the time has always matched the time zone of the database host operating system. Now, imagine that we use either multitenant environments or cloud-based environments when the host OS system time zone is not really the same as the application that runs in a different geographic locality or affects data from other locations.  And system time obviously applies not only to the data stored and updated in the database rows but also to the scheduler, the flashback, to a place to materialized view refresh, Recovery Manager, and other time-sensitive features in the database itself.  Now, with the database time versus operating system time, there is a need to be more selective. It is desired that the applications use the same database time in the same time zone as the applications are actually being used in.  And multitenant and cloud databases will certainly experience a mismatch between the host operating system time zone, which is not local for the applications that run in some other geographical locations or not recognizing some, for example, daylight savings time.  So migration challenge is obviously present. If you want to migrate from a specific on-premises database to either multitenant or cloud, you would experience the host operating system time zone by default.  06:38 Lois: And that’s obviously not convenient for the applications, right? Serge: Well, the database-specific time in Oracle Database 23c, any cloud database can set local time zone to whatever the customer's requirements are explicitly. And any pluggable database can also set its own local time zone to customer's requirements, not inheriting the time zone from the container database it is currently running in.  This simplifies migration to multitenant or cloud for applications that are time-sensitive. And it offers more intuitive, easier database monitoring, and development.  07:23 Working towards an Oracle Certification this year? Take advantage of the Certification Prep live events in the Oracle University Learning Community. Get tips from OU experts and hear from others who have already taken their certifications. Once you’re certified, you’ll gain access to an exclusive forum for Oracle-certified users. What are you waiting for? Visit mylearn.oracle.com to get started.   07:51 Nikita: Welcome back! Let’s move on to the data storage improvements. We have two updates here as well, automatic secure file shrink and automatic storage compression. Let’s start with the first one. But before we get into it, Serge, can you explain what SecureFiles are?  Serge: SecureFiles are the default storage mechanism for large objects in Oracle Database. They are strongly recommended by Oracle to store and manage large object data.  The LOBs are stored in segments. Those segments may incur large amounts of free space over time. Because of the updates to the LOB data, the fragmentation of the space used is growing depending, of course, on the frequency and the scope of the updates.  The storage efficiency could be improved by shrinking segments with the free space removed. And manual secure files shrinking has become available since Oracle Database 21c, requiring administrators to perform these tasks manually.  Traditional SecureFiles required the time-consuming DBA activities. DBAs would need to manually identify eligible LOB segments either using Segment Advisor or PL/SQL or built-in database views.  Once identified, the administrators would manually execute shrink operations on very large LOBs which takes too much time and may result in excessive disk space consumption. For example, code to operate this shrinking would look like ALTER TABLE some table SHRINK SPACE CASCADE.  That would shrink all LOB segments in a particular table. If you want to scope the shrinking to a single column, the code would be required to ALTER TABLE some table MODIFY LOB, followed by the column name SHRINK SPACE.  This affects only a single column in a table with LOBs.  10:01 Lois: So, how has automatic secure shrinking made things better? Serge: Automatic SecureFile shrink removes the emphasis from the DBAs to manually perform these tasks. And it results in the more optimal use of space over time.  It is integrated into the automated database maintenance tasks. The automation once enabled runs every 30 minutes, collects eligible LOB segments, and shrinks them offline. The execution time and freed space would vary depending on the fragmentation and the size of the LOBs. Each shrink execution may reclaim up to 5 gigabytes of unused disk space from each LOB segment that is idle.  On the high level, automatic SecureFile shrink improves the Oracle Database 23c storage usage efficiency. It is part of the ongoing Oracle Database improvement effort and transparently reclaims the free space with negligible to no impact on performance of the database operations.  Again, this is done in the background without affecting the running processes. It makes Oracle database 23c less dependent on the DBA activities while reducing the disk

Join hosts Lois Houston and Nikita Abraham in Part 2 of the discussion on database sharding with Ron Soltani, a Senior Principal Database & Security Instructor. They talk about sharding native replication, directory-based sharding, and coordinated backup and restore for sharded databases, explaining how these features work and their benefits. Additionally, they explore the automatic bulk data move on sharding keys and the ability to split and move partition sets, highlighting the flexibility and efficiency they bring to data management.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/137192/207062   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative  podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Lois: Hello and welcome to the Oracle University Podcast. I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Principal Technical Editor. Nikita: Hi everyone! In our last episode, we dove into database sharding and Oracle Database Sharding in particular. If you haven’t listened to it yet, I’d suggest you go back and do so before you listen to this episode because it will give you a lot of context.  00:53 Lois: Right, Niki. Today, we will discuss all the 23ai new features related to database sharding. We will cover sharding native replication, directory-based sharding, coordinated backup and restore for sharded databases, and a few more.  Nikita: And we’re so happy to have Ron Soltani back on the podcast. If you don’t already know him, Ron is a Senior Principal Database & Security Instructor with Oracle University. Hi Ron! Let’s talk about sharding native replication, which is RAFT-based, meaning that it is reliable and fault tolerant-based, usually providing subzero or subsecond zero data loss replication support. Tell us more about it, please.  01:33 Ron: This is completely transparent replication built in within Oracle sharding that duplicates data across the different shards. So data are generally put into chunks. And then the chunks are replicated either between three or five different shards, depending on how much of the fault tolerance is required.  This is completely provided by the Oracle sharding database, and does not require use of any other component like GoldenGate and Data Guard. So if you remember when we talked about the architecture, we said that each shard, each database can have a Data Guard component, whether through GoldenGate or whether through Data Guard to have a standby.  And that way support high availability with the sharding native replication, you don't rely on the secondary database. You actually-- the shards will back each other up by holding replicas and being able to globally manage the replica, make sure everything is preserved, and manage all of the fault operations.  Now this is a logical replication, generally consensus-based, kind of like different components all aware of each other. They know which component is good, depending on the load, depending on the failure. The sharded databases behind the scene decide who is actually serving the data to the client. That can provide subsecond failovers with zero data loss.  03:15 Lois: And what are the benefits of this? Ron: Major benefits for having sharding native replication is that it is completely transparent to the application or any of the structures. You just identify that you want to go ahead and use this replication and identify the replication factor. The rest is managed by the Oracle sharded database behind the scene.  It supports fast failover with zero data loss, usually subsecond failovers. And depending on the number of replicas, it can even tolerate multiple failures like two server failures.  And when the loads are submitted, the loads are also load-balanced across all of these shards based on where the data is located, based on the replicas. So this way, it can also provide you with a little bit of a better utilization of the hardware and load administration.  So generally, it's designed to help you keep your regular SQL-based databases without having to resolve to FauxSQL or NoSQL environment getting into other databases. 04:33 Nikita: So next is directory-based sharding. Can you tell us what directory-based sharding is, Ron?  Ron: Directory-based sharding basically allows the user to define the values that are used and combined for different partition, so better control, location of the data, in what partition, what shard. So this allows you to set up a good configuration.  Now, many times we may have a key that may not be large enough for hash partitioning to distribute the data enough. Sometimes we may not even know what keys are going to come in the future. And these need to be built in the future. So having to build these, you really don't want to have to go reorganize the whole data based on new hash functions, and so when data cannot be managed and distributed using hash partitioning or when we need full control over combination of where data exists.  05:36 Lois: Can you give us a practical example of how this works? Ron: So let's say our company is very small in three different countries. So I can combine those three countries into one single shard. And then have three other big countries, each one sitting in their own individual shards. So all of this done through this directory-based sharding. However, what is good about this is the directory is created, which is a table, created behind the scene, stored in the catalog, available to the client that is cached with them, used for connection mapping, used for data access. So it can give you a lot of very high-level benefits.  06:24 Nikita: Speaking of benefits, what are the key advantages of using directory-based sharding? Ron: First benefit allow you to group the data together based on the whatever values you want, depending on what location you want to put them as far as across the shards are concerned. So all of that is much better and easier controlled by us or by the designers. Now, this is when there is not enough values available. So when you're going to use hash-based partition, that would result into an uneven distribution of the data.  Therefore, we may be able to use this directory for better distribution of the data since we understand the data structure better than just the hash function. And having a specification where you can go ahead and create future component, future partitions, depending on how large they're going to be. Maybe you're creating them with an existing shard, later put them in another shard. So capability of having all of those controls become essential for management of this specific type of data.  If a shard value, the key value is required, for example, as we said, client getting too big or can use the key value, split it or get multiple key value. Combine them. Move data from one location to another. So all of these components maintain automatically behind the scene by us providing the changes. And then the directory sharding and then the sharded database manages all of the data structure, movement, everything behind the scene using some of the future functionalities. And finally, large chunk of data, all of that can then be moved from one location to another. This is part of the automatic chunk data move and whatnot, but utilized within the directory-based sharding to allow us the control of this data and how we're going to move and manage the data based on the load as the load or the size of the data changes.  08:50 Lois: Ron, what is the purpose of the coordinated backup and restore system in Oracle Database Sharding? Ron: So, basically when we talk about a coordinated backup and restore, remember in a sharded database, I have different databases. Each database is a shard. When you take a backup, each database creates its own backup.  So to have consistent data across all of the shards for the whole schema, it is extremely important for these databases to be coordinated when the backup is taken, when the restore is being done. So you have consistency of the data maintained across all of the shards.  09:28 Nikita: So, how does this coordination actually happen? Ron: You don't submit this through our main. You submit this through the Global Management tool that is used for the sharded database. And it's the Global Management tool that is actually submit your request to each database, but maintains the consistency of when the actual backup is taken, what SCN.  So that SCN coordination across all of the shards is then maintained for the backup so you can create a consistent backup or restore to a consistent point in time across the sharded database. So now this system was enhanced in 23C to support multiple destinations.  So you can now send your backup to an object store. You can send it to ZDLRA. You can send it to Amazon S3. So multiple locations can now be defined where you can send these backups to. You can also use multiple recovery catalogs.  So let's say I have data that is located on different countries and we have requirement that data for each country must stay in that country. So I need to also use a separate catalog to maintain that partition.  So now I can use multiple catalog and define which catalog is maintaining which partition to satisfy those type of requirements or any data administration requirement when it comes to backup recovery. In addition, you can also now specify different type of encrypti

In this two-part episode, hosts Lois Houston and Nikita Abraham are joined by Ron Soltani, a Senior Principal Database & Security Instructor, to discuss the ins and outs of database sharding. In Part 1, they delve into the fundamentals of database sharding, including what it is and how it works, specifically looking at Oracle Database Sharding and its benefits. They also explore the architecture of a sharded database, examining components such as shards, shard catalogs, and shard directors.   Oracle MyLearn: https://mylearn.oracle.com/ou/course/oracle-database-23ai-new-features-for-administrators/137192/207062   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:26 Nikita: Hello and welcome to the Oracle University Podcast. I’m Nikita Abraham, Principal Technical Editor with Oracle University, and with me is Lois Houston, Director of Innovation Programs. Lois: Hi there! The last two weeks of the podcast have been dedicated to all things database security. We discussed why it’s so important and looked at all the new features related to database security that have been released in Oracle Database 23ai, previously known as 23c.  00:55 Nikita: Today’s episode is also going to be the first of two parts, and we’re going to explore database sharding with Ron Soltani. Ron is a Senior Principal Database & Security Instructor with Oracle University. We’ll ask Ron about what database sharding is and then talk specifically about Oracle Database Sharding. We’ll look at the benefits of it and also discuss the architecture.  Lois: All this will help us to prepare for next week’s episode when we dive into each 23ai new feature related to Oracle Database Sharding. So, let’s get to it. Hi Ron! What’s database sharding?  01:32 Ron: This is basically an architecture to allow you to divide data for better computing and scaling across multiple environments instead of having a single system performing the work. So this allows you to do hyperscale computing and other different technologies that are included that will allow you to distribute your queries and all other requests across these multiple components to be able to get a very fast response.  Now many times with this distributed segment across each kind of database that is called a shard allow you to have some geographical location component while you are not really sharing any of the servers or the components. So it allows you separation and data management for each of the shards separately. However, when it comes to the application, the sharded database is totally invisible. So as far as the application is concerned, they connect to a global service, submit their statements. Everything else is managed then by the sharded database underneath.  With sharded tables, basically it gets distributed across each shard. Normally, this is done through horizontal partitioning. And then the data depending on the partitioning scheme will be distributed across like server A, server B, server C, which are independent servers that are running independent databases.  03:18 Nikita: And what about Oracle Database Sharding specifically? Ron: The Oracle Database Sharding allows you to automate how the data is distributed, replicated, and maintain the kind of a directory that defines the complete sharding scheme, while everything is distributed across many servers with no sharing whether the hardware or software. It allows you to have a very good scaling to be able to scale based on this partitioning across all of these independent servers.  And based on the subset and the discrete data configuration, you can go ahead and distribute this data across these components where each shard is an independent data location or data component, a subset of data that can be used, whether individually on its own or globally across all of the shards together. And as we said to the application, the Oracle Database Sharding also looks as a single component.  04:35 Lois: Ron, what are some of the benefits of Oracle Database Sharding? Ron: With Oracle Database, you basically have linear scaling capability across as many shards as you like. And all of the different database configurations are supported with this. So you can have rack databases across the shards, Oracle Data Guard, GoldenGate. So all of the different components are still used to give you all of the high availability and every other kind of functionality that we generally used to having a single database with.  It provides you with fault toleration. So each component could be down. It could have its own replicated data. It doesn't affect other location and availability of the data in those other locations.  And finally, depending on data sovereignty and configuration, you could actually distribute data geographically across the different locations based on requirements and also data access to provide a higher speed for local data management.  05:46 Lois: I’d like to understand more about the architecture of Oracle Database Sharding. Ron, can you first give us a broad overview of how Oracle Database Sharding is structured? Ron: When it comes to dealing with Oracle Database architecture, the components include, first, your shards. The shards-- each one is an independent Oracle Database depending on the partitioning you decide on a partition key and then how the actual data is divided across those shards.  06:18 Nikita: So, these shards are like separate pieces of the database puzzle…Ok. What’s next in the architecture? Ron: Then you have shard catalog. Shard catalog is a catalog of your sharding configuration, is aware of all of the components in the shard, and any kind of replicated object that master object exists in the shard catalog to be maintained from there.  And it also manages the global queries acting as a proxy. So queries can be distributed across multiple shards. The data from the shards returned back to the catalog to group together and then sent back to the client.  Now, this shard catalog is basically another version of an Oracle Database that is created independently of the shards that include the actual data, and its job is to maintain this catalog functionality.  07:19 Nikita: Got it. And what about the shard director? Ron: The shard director is like another form of a global service manager.  So it understands the sharding by being able to access the catalog, knows where everything exists. The client connection pool will hit the shard director. In general, communication and then whether it's being distributed to the shard catalog to be able to proxy it, or, if the key is available, then the director can send the query directly to the shard based on the key where the data exists. So the shard can then respond to the client directly. So all of the connection pool and the components for global administration, generally managed by the shard director.  08:11 Nikita: Can we dive into each of these components in a little more detail? Let’s go backwards and start with the shard director. Ron: The shard director, as we said, this is like a global service manager. It acts as a regional listener where all of the connection requests will be coming to the shard director and then distributed from that depending on the type of connection that is being used.  Now the director understands the topology--maintains the complete understanding of the mapping of the data against the shards. And based on the shard key, if the request are specified on the specific key, it can then route the connection request directly to the shard that is appropriate where the data resides for the direct response.  09:03 Lois: And what can you tell us about the shard catalog? Ron: The shard catalog, this is another Oracle Database that is created for special purpose of holding the topology of the sharded database. And have all of the centralized information metadata about your sharded database. It also act as a proxy.  So, if a client request comes in without providing a shard key, then the request would go to the catalog. It can be distributed to all of the shards. So the shards that you actually have the data can respond, but the data can then be combined and sent back to the client. So, it also creates the master copy of all the duplicate tables that are created in the shard database.  09:56 Lois: Ok. I’ve got it. Now, let’s talk more about the shards themselves. Ron: Each shard is basically a database. And data is horizontally partitioned to be placed on each of these shards. So, this physical database is called the shard. And depending on the topology of your sharding, there could be user sharding, for example, where multiple keys are in a single shard or could be a system sharding that based on the hash value data is distributed whether singly or multiple data components across each shard.  Now, this is completely transparent to the application. So, as far as application is concerned, this is a single database and the response everything that they do is generally just operating as a single database interaction.  However, when it comes to the administrators, each shard is a separate database. Each shard can be managed independently and can have its own standby and other components that is then set up for high availability and management of the data operations.  11:21 Do you have an idea for a new course or learning opportunity? We’d love to hear it! Visit the Oracle University Learning Community and share your thoughts with us on