Time & Data Handling & Data Storage

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 space required to store SecureFiles, reducing the usage of LOB segments.  Automatic securefile shrink runs incrementally in small steps over time. Some of the features are tunable. And it is supported for all types of large objects, storage, compressed, encrypted, and duplicated the object segments.  11:50 Nikita: Right, and note that this feature is turned on out-of-the-box in the Autonomous Database 23ai in Oracle Cloud. Now, let’s talk about Automatic Storage Compression, Serge.  Serge: With Automatic Storage Compression and Automatic Clustering, the storage compression gives you the background compression functionality. Directly loaded data is first uncompressed to speed up the actual load process. Rows are then moved into hybrid columnar compression format in the background asynchronously.  The automatic clustering applies advanced heuristic algorithms to cluster the stored data depending on the workload and data access patterns and the data access is optimized to more efficiently make use of database table indices, zone maps, and join zone maps.  Automatic Storage Compression advantages include the improvements to Oracle Database 23c storage efficiency as well. It is part of the continuous improvement, part of the ongoing Oracle Database improvement effort. And it brings performance gains, speeds up uncompressed data loads while compressing in the background.  The latencies to load and compress data are because of that also reduced. With the hybrid columnar compression in particular, this works in combination.  And it results in less DBA activities, makes the Database Management less dependent on the DBA time and availability and effort.  Automatic Storage Compression performs operations asynchronously on the data that has already been loaded. To control Automatic Storage Compression on-premises, it must be enabled explicitly. And you have to have heatmap enabled on your Oracle Database objects.  Table must use hybrid columnar compression and be placed on the tablespace with the SEGMENT SPACE MANAGEMENT AUTO and allowing autoallocation. And this feature, again, is transparent for the Autonomous Database 23c in the Oracle Cloud. 14:21 Lois: Thanks for that quick rundown of the new features, Serge. We really appreciate you for taking us through them. To learn more about what we discussed today, visit mylearn.oracle.com and search for the Oracle Database 23ai New Features for Administrators course. Join us next week for a discussion on some more Oracle Database 23ai new features. Until then, this is Lois Houston… Nikita: And Nikita Abraham signing off! 14:50 That’s all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We’d also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.