What is AI?

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 spaces. So once you have generated the vector embeddings and stored those vector embeddings and possibly created the vector indexes, you can then query your data with similarity searches. This allows for Native SQL operations and allows you to combine similarity searches with relational searches in order to retrieve relevant data. 11:15 Lois: Ok. I think I've got it. So, Step 1, generate the vector embeddings from your unstructured data. Step 2, store the vector embeddings. Step 3, create vector indices. And Step 4, combine similarity and keyword search. Brent: Now there is another optional step. You could generate a prompt and send it to a large language model for a full RAG inference. You can use the similarity search results to generate a prompt and send it to your generative large language model in order to complete your RAG pipeline. 11:59 Lois: Thank you for sharing such valuable insights about Oracle AI Vector Search, Brent. We can't wait to have you back next week to talk about vector indices and memory. Nikita: And if you want to know more about Oracle AI Vector Search, visit mylearn.oracle.com and check out the Oracle Database 23ai: Oracle AI Vector Search Fundamentals course. Lois: Yes, and if you're serious about advancing in your development journey, we recommend taking the Oracle Database 23ai SQL workshop. It's designed for those who might be familiar with SQL from other database platforms or even those completely new to SQL. Nikita: Yeah, we'll add the link to the workshop in the show notes so you can find it easily. Until next week, this is Nikita Abraham… Lois: And Lois Houston signing off! 12:45 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.

22 Loka 202413min

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-time recovery at the database level. So we wouldn't want to do that. That would something really drastic would have to happen to force us to do the entire container. But we want to do that at the PDB level. 10:54 Lois: Ok. So the issue is resolved. What happens next? Bill: So once we have the issue resolved that caused that, SMON is still going to try to do transaction recovery because why? That quarantined transaction says, hey, I've still got this bad transaction there. So once that transaction has been fixed, we need to drop that quarantined transaction. So that way, SMON says, hey, I have this transaction. I need to recover. SMON will keep from trying to do that. So there is a DDL command to drop that quarantined transaction. So remember, from the views, the quarantined transaction views, that's where we saw the undo segment. We saw the slot number. We saw the quarantined transaction slot number. So that way, we can drop that transaction by using that. 11:51 Nikita: How does the escalation process work for quarantined transactions? And why is it important to protect the PDB and the container database? Bill: So quarantined transaction escalation-- we might have multiple transactions fail, depending on the corruption level. It might have multiple blocks for that that have failed. So just to quarantine a bad transaction may not help whatsoever. It depends on what the root cause is for the failures and how many are happening at that time. So the database with these bad transactions will continuously run in an inconsistent state. So it could be dangerous if we have multiples of the same issue and that. So with that system running in an inconsistent state, things will continue to spread. Things will continue to get worse. That's why, once that level of 3 is reached, we go ahead, and we do a shut down abort on that PDB. Because if a transaction can't be recovered, there's no need in trying to do any other type of shutdown. So with this escalation process, it does benefit us because, again, SMON is going to continuously try to recover that bad transaction for that. OK, SMON's going to keep trying. It's not going to work. And at some point, it might cause it to crash. So by stopping it before it continues getting worse, damaging more, we're going to go ahead and say we're escalating this issue to where we're shutting down the PDB. Fault tolerance, so meaning that we have higher availability of the rest of the container. So it's not going to crash the entire container. So the PDB can continue to operate when we are trying to resolve transactions except in the case where it exceeds the amount, and it does a shutdown abort on that PDB. So with that escalation, we reach that limit of 3 for that. We do a Shutdown Abort on that PDB. That transaction recovery is disabled. OK. Don't try to recover any transactions. Why? Because we know we have a few of them. So it's shut down, so we're going to go out and look at our quarantine transactions views, what's the reason for that, how many do we have? And then, once we resolve the issue, we are going to enable recovery again because it turns off the recovery option before it allows us to open that PDB. It's not going to be in a consistent state, though. So now we can go ahead and alter the system and, OK, go ahead and allow recovery of transactions again. 14:42 Lois: Thank you, Bill, for walking us through the details of automatic transaction quarantine and telling us how to manage and resolve these complex scenarios. Nikita: Yeah, thanks Bill! 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 Nikita Abraham… Lois: And Lois Houston signing off! 15:13 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.

15 Loka 202415min

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 idle session can block another session. Again, it's going to check OK, is the session actually idle? But these don't really address the issue of, hey, I have a higher priority transaction waiting for a lower transaction that's blocking it. 10:27 Lois: Thank you, Bill, for that breakdown. This feature is such a time saver. Nikita: Yeah, and such good way to reduce the manual workload for DBAs. Thanks Bill! Lois: To learn more about what we discussed today and view some of the demonstrations of this feature, 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! 11:02 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.

8 Loka 202411min

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 different sessions on the same table? Bill: Sessions can query that table, can perform conventional DML on that, able to also concurrently perform a direct load, and I can roll back to a save point. So you can see those added features can be very beneficial. But there's still restrictions that apply. It still applies to heap tables only, and it still applies to only tablespaces that are using the automatic segment space management for that. Of course, that includes the temporary tablespace and it doesn't work with tablespaces that have uniform extents. Your application DML might need to query the data after that direct load without committing, applications that might need to modify data within that same transaction as that direct load. You can enable multiple append hint. So you can specify the hint in addition to pending hint to disable. You can specify the no multi-append hint to disable it. 12:27 Lois: Bill, what's the broader impact of these changes. How do these improvements make things more development-friendly? Bill: So changes to the direct load make things a little bit more development friendly by removing those directions after that direct load itself. So previous restrictions when loading-- querying the data kept us from doing multiple things at the same time. So now I can query on that table direct load from the same session, from a different session. I can do conventional DMLs on the table within the same session. It allows me to do a rollback on it. I can do direct loads on the same table within the same session. Again, I can also allow rollback to a save point. As long as my compatibility is set to 21.0.0.0, I will be able to go ahead and benefit from this feature. And there is no increase with it as far as the space usage or causing any fragmentation to the table. So that will not be an issue. 13:35 Nikita: Well, that's the end of our time together, but I want to thank you, Bill, for sharing your expertise with us. Lois: 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:03 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.

1 Loka 202414min

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 corresponding True Cache application service. To help simplify things a little bit, in the naming convention, you'll notice in our examples-- for example, if we have SALES as the primary database service, then we have the True Cache, we have SALES_TC, standing for True Cache, so it's easily identified. You don't have to do that, but it's kind of recommended to do that, some way that you're going to identify it. So we're going to start our True Cache services. And you only start the True Cache services on the True Cache instances. Because it's the database services on the database that you need to make sure are started. And they are read-only. 10:46 Lois: Are there some best practices for maximum availability architecture? Bill: Uniform configuration seems to be a popular one. Why? Because I am going to have the both True Caches can be shared. That way, hopefully, I'm getting full usage out of both. And maybe if I have one service going to one, it might be minimally used. Whereas, the other one might be over. Hey, I could use more memory over here. We'll also recommend use the JDBC 23ai UCP, Universal Connection Pool, for the application. So that can lessen the impact. If one True Cache becomes unavailable, as far as, OK, I need to reroute over here-- benefit of uniform configuration also. Prepopulate the cache. You want to go ahead and run the critical workload for that. If you have a planned outage, and you need to shut down the True Cache, you want to make sure you stop the database application service on that True Cache. And then, how are you going to design your True Cache? Are you going to partition it? Are you going to have uniform? Which partition option are you going to use? So you can try to design that to help minimize the number of fetches it has to do from the primary database. And the more you can keep in the True Cache, the better the performance is going to be. 12:09 Nikita: What do I need to keep in mind when it comes to managing True Cache? Bill: One thing you might need to do for managing the True Cache is to monitor the True Cache. There's a couple different ways that we can do it. One, you can use the V$ view, the V$TRUE_CACHE view. And, of course, you can always use the Automatic Workload Repository. 12:30 Lois: Bill, we already spoke about this a bit, but can you tell us more about using True Cache in an application? Bill: There's two ways of using True Cache, as we've seen, physical and logical. Physical, it's going to maintain two connections, front one to the primary database and one to the True Cache. The application can decide which connection to use, based off of what it is trying to do. If it's just reading, long as it's for a service that's configured with True Cache, it can read the True Cache. If it's going to write something, it's going to update, insert, whatever the case might be, it's for the primary database. And you can use any existing client driver as long as you're using the physical connection method. Any programming language will also work. With the one logical connection method, it uses the application service for the primary database. You're going to use the JDBC Thin driver, starting with 23ai. You can use it and it maintains the connection to the primary database and True Cache. This model only works with Java applications, though. It maintains the physical connections. We're going to enable the driver connection. And then, we're going to set the read only. We're going to set it to read only, true. Read only, false, whatever the case might be. And the read only mode is false for a connection by default. False is the default. Java applications only. 14:14 Nikita: What are some best practices for load balancing in a uniform configuration? Bill: You have multiple--multiple True Caches. They're going to service the same database application. They're going to cache the same data. It's the listener that's going to distribute the load balances. So the listener will automatically distribute and load each session to each cache. It will do it randomly and it will do it based off a load. Where can it configure? Where can it send for the best performance. To route the request to the best performing True Cache, you want to make sure that you are using the same listener. So that remote listener parameter should point to the same listener, which is also the primary database listener. Single instance primary database local listener or scan listener, whichever one you're using, points to the primary. For the application for the JDBC URL, should point to the primary database. You'll remember that Thin driver is going to create that logical connection, and it's going to create the physical connection to the primary database into each True Cache. To simplify things and possibly avoid connection issues, you might consider using the LISTENER_NETWORK, so the initialization parameter instead of specifying the remote and local listener separately. Because with the local--with the listener networks, all listeners within the same network name will cross register. 15:44 Lois: Before we wrap up, are there any complementary features that you would recommend using alongside True Cache to further enhance performance or simplify management? Bill: There are features that can complement True Cache-- the server-side result set cache. So you can create--you can go ahead and create the result set that's part of the library cache set aside, a portion of that. You're going to go in, you're going to configure what objects will use that. You can still use that even with True Cache. There's also the KEEP Buffer Pool that can be used. It's a separate pool that you set aside as part of the buffer cache. And you want to make sure you size it so the object that you want to keep in memory in the buffer cache that you size it appropriately. But again, some configuration, you configure the key pool, plus also you go in and alter the objects to use it. And then lastly, there's the database smart flash cache. So again, if your data doesn't fit into memory, you can expand the capacity of by adding flash devices. When you configure the flash cache, if you are using transparent data encryption data, the local flash devices is not supported. So if it's TD encrypted on the primary database, it's going to stay in the buffer cache of the primary database. 17:11 Nikita: Ok! I think we can close the episode with that. Thank you, once again, for joining us, Bill. Lois: Yes thanks! We're learning so much from you. To learn more about what we discussed today, including the various configuration options that are available, 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! 17:46 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.

24 Syys 202418min

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 type. It is an optimized native binary storage format for JSON data. 11:15 Lois: Ok. So, going forward, there will be better read and write performance for LOBs. Bill: Multiple LOBs in a single transaction can be buffered simultaneously, improving mixed workloads. We just talked about the PGA. Automatically, the buffer is automatically resized. And the improved JSON support. The reason it will recognize, hey, this is a JSON data type. But traditionally, JSON data types were small. So they were small to medium size. So the range from 32k to 32 meg was considered small to medium whereas LOBs were designed for data types larger than 100 meg. So by recognizing this a JSON data type, it can take advantage of the LOB architecture. Other enhancements will also include the acceleration of compressed LOBs, the pen and compression caching, and improves the poor performance of your reads and writes to compressed LOBs. It's faster than previously. 12:24 Nikita: Bill, what do you think about the recent increase in the column limit? Previously, the limit was 1000 columns per table, which sometimes posed issues when migrating from other systems that allowed more than 1,000 columns, right? Bill: Maybe because of workload requirements, the whole machine learning, the internet of things workloads, IOTs can have hundreds of thousands of attributes, dimensional attribute columns for that. And even our very own blockchain tables reserves up to 40 hidden virtual columns, so that takes away from the total amount. Virtual columns count towards the column limits and some applications as they drop columns, what it does, it just converts them to unused, and it still applies towards the limit the number of columns that you can have to that limit. There were workarounds. However, they were most likely not the best way to do it, like column switching, table splitting for that. But big data really use cases, really saw where files have or required more than 1,000 columns. 13:42 Lois: So, now that we can have 4,096 columns in a table, I'm sure it's made handling complex data a lot easier. Bill: So by increasing this, since other systems do support higher column limits, it can-- the increase can make migration from other systems easier and possibly even a little bit more attractive while it can make applications a little bit simpler because the 1,000 column limit was not always optimal for analytics. Where 1,000 might have been plenty for OLTP type environments, but not for the analytics, especially when it comes to machine learning and those internet of things that we talked about, where the previous workarounds, like splitting the tables, really caused more performance issue than anything else. So we want to avoid those suboptimal workarounds. And the nice thing is there's no change to the SQL. So once you have that-- well, if we were doing SQL, if we had tables that were split and we're trying to do things that is actually going to help improve that SQL, now, we don't have multiple objects that we're dealing with. 14:57 Nikita: How do we actually go about increasing the column limit to 4,096? Bill: You do have to have the compatibility set to 23c. Why? Because it's a new feature. There is a new initialization parameter called Max columns, and you do set that. There's two different ways, two different values. We can set it to standard or we can set it to extended. It is dynamic. When it's set to standard, it's only 1,000. When we set it to extended, it's going to allow the 4,096. It is modifiable at the PDB level. However, it will inherit what's at the root level, if it's not explicitly set at a PDB. It can't alter it in a session for that. And multiple instances of the RAC environment must use the same value. Now one thing, notice that it cannot be set to standard if I created a table that had more than 1,000 columns. One thing that might get you, when you drop a table that has more 1,000 columns and you try to set it back to standard, it might tell you, hey, you have tables that have more than 1,000 columns. Don't forget your recycle bin unless you did a drop table purge. 16:09 Lois: Are there any performance considerations to keep in mind, Bill? Bill: There's really no DML or query performance degradation for the tables. However, it might require, as you would expect, the increase in memory when we have the new column limits. It might require additional shared pool, additional SGA with the additional columns, more buffer cache as we're bringing blocks in. So that's shared pool along with the PGA. And also we can add in buffer cache in there, because that increased column count is going to be increase in the total PGA memory usage. And those are kind of expected for that. But the big advantage is it gives us the ability to eliminate some of these suboptimal workarounds that we had in the past. 17:02 Nikita: Ok! We covered a lot today so thank you Bill and Ron. Lois: 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! 17:27 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.

17 Syys 202417min

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 ingest is loading into the table, it doesn't go and see what's sitting in the buffer in the large pool like it does with the buffer cache. Index and constraints are checked but only at flush time. And the memoptimize pool size is a fixed amount of space that we're going to allocate-- of memory that we're going to allocate to use for the memoptimize write. We can enable a table for the fast ingest, enable with the memoptimize for write. We can create a table and do it. We can also alter a table. We already have a table existing. All we have to do is alter it. And we want to use that, the fast ingest, for these tables. 11:21 Lois: Do we have options for the writing operation, Bill? Bill: You do have options for the writing operation. We have the parameters, the memoptimize write where we can turn that on. We can also use it in a hint. It is set at the root level, it. Is not modifiable at the PDB level. It's set at the root level, It is a static parameter. We can also do things in our session. We want to verify, OK, is the memoptimize write on? We can verify a table is enabled. So with the fast ingest, the data inserts, you can also use a hint. You can also set this at a session level. If you decide there's something that you don't want to use the memoptimize write for, then you can disable it for a table. 12:11 Nikita: Bill, what are some of the benefits of the enhancements made in 23ai? Bill: With some of the enhancements-- so now, some table attributes are now supported-- we can now have common default values for a column. We can use transparent data encryption. We can also use the fast inserts, any inline LOBs, along with virtual columns. We've also added partitioning support. We can do subpartitioning and we can also do interval partitioning, along with auto list. So we've added some items that previously prevented us from doing the fast inserts. It does provide additional flexibility, especially with the enhancements and the restrictions that we have removed. It allows to use that fast insert, especially in a data warehouse-type environment. It can also use-- in the Cloud, it can use encrypted tablespaces, because remember, in the Cloud, we always encrypt, by default, users' data. So now, it also gives us the ability to use it in that Cloud environment because of that change. We have faster background flushing for the loads. 13:36 Lois: And how is it faster now? Bill: Because we bypassed the traditional buffer cache. Faster ingest for those direct ingest. So again, bypassing the traditional inserts and using the buffer cache gives the ability to bulk load into large pool, then flush to the database so that way, we have access to that data for possible faster analytics of those internet of things, especially when it comes to the temperature of the temperature sensors. We need to know when a temperature of something is out of bounds very quickly. Or maybe it's sensors for security. We need to know when there's a problem with the security. 14:20 Nikita: How difficult is it to manage this? Bill: Management is fairly simple. We have the MEMOPTIMIZE_WRITE_AREA_SIZE parameter that we're going to say-- it is dynamic. It does not require a restart. However, all instances in a RAC environment must have the same value. So we have the write area. What are we going to set? And then the MEMOPTIMIZE_WRITE, by default, it uses a hint. Or we can go ahead and we can just set that to all. It is allocated from the large pool. You manually set it. And we can see how much is actually being allocated to the pool. We can go out and look at our alert log for that information. There's also a view. The MEMOPTIMIZE_WRITE_AREA has some columns. What is the total memory allocated for the large pool? How much is currently used by the fast ingest? How much free space? As you're using it, you might want to go out and do a little checking, or do you have enough space? Are you not allocating enough space? Or have you allocated too much? It'll also show the total number of writes, and also, the number-- the writers is currently the users that are using it. And the container ID, what is the container within that container database? What's the pluggable or pluggables that's using the fast ingest? There is a subprogram, the DBMS_MEMOPTIMIZE that we have access to that we possibly can use. So there are some procedures. Here, we can return the rows of the low and high water mark of the sequence numbers. And the key here is across all the sessions. We can see the high water mark, sequence number of the rows written to the large pool for the current session. And we can also flush all the ingest data from the large pool to disk for the current session. 16:26 Lois: What if I want to flush them all for all sessions? Bill: Well, that's where we have the WRITE_FLUSH procedure. So it's going to flush the fast ingest data of the Memoptimize Rowstore from the large pool for all the sessions. As a DBA, that's one that you most likely will want to be using, especially if it's going to be before I do a shutdown or something along that line. 16:49 Nikita: Ok! On that note, I think we can end this episode. Thank you so much for taking us through all that, Bill. Lois: Yes, thanks Bill. If you want to learn more about what we discussed today, visit mylearn.oracle.com and search for Oracle Database 23ai New Features for Administrators. 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! 17:21 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.

10 Syys 202417min

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.

3 Syys 202415min