Transcript

Lesson 1 – Agenda

How Oracle RAC Cache Fusion Works Part 1 of 6

 

>> Brian:  Hello, everyone. My name is Brian Peasland. I am the author of Oracle RAC Performance Tuning on Rampant Techpress. I am very excited to be here today to record this video for SkillBuilders. Today I am going to talk about Oracle RAC Cache Fusion and its global cache transfers and I’ll even provide a little demo for you. 

 

[pause]

 

After viewing this video, you should have a better understanding of how Cache Fusion works, the differences between current and consistent read transfers, the difference between two-way versus three-way transfers and the wait events associated with global cache transfers. I do assume that you have a basic understanding of Oracle’s architecture including the concept of an Oracle instance, the buffer cache, how data gets moved into the buffer cache and wait events. 

 

Let me give you a little history. Back in 1988, Oracle version 6 was released and this version included the very first commercially available clustered database system. Back then it was known as Oracle Parallel Server or OPS. Oracle Parallel Server did provide good scalability but it did have its issues, as well. 

 

Primarily, the biggest issue with OPS was a concept known as disk pinging. To understand disk pinging, I thought I could bring in a little picture here showing a two-node Oracle Parallel Server cluster. I have two users, Jack and Jill. Jack is connected to one node of the cluster and Jill is connected to another node of the cluster. 

 

[pause]

 

Even in the beginning, even in Oracle version 6, its clustered database was a shared disk architecture and you can see both nodes of the cluster can access the same shared disk. Between the nodes is a private network, but when OPS was first released the private network didn’t do too much. It was primarily for things like heartbeats between the nodes. Just checking the status – are you alive? Yes, I’m alive. Good. We’re all together. 

 

If Jack read some data from disk and Jill needed that same data and that data was not in the buffer cache of Jill’s instance, that block needed to be read from disk, as well. This meant two users accessing the same block could incur two reads from disk if they just happen to have the unfortunate circumstances of being on separate instances, separate nodes of the cluster. 

 

Things became more difficult when Jack modified a block of data. Jack updated a row on a table and later on Jill tried to read the committed row. Before that could happen, Jack’s changes must be written to disk and then Jill’s session could read the data block from disk into the buffer cache of her instance. This was disk pinging. Write and subsequent reads could result in disk I/O activity. 

 

In 1999, Oracle released version 8i of their database product and the 8i release was very historic from the purposes of today’s discussion because it introduced a concept called cache fusion. And Oracle marketing decided “Enough with the Oracle Parallel Server name since cache fusion is really a big important thing. Let’s change the name Real Application Clusters.” And that’s what it’s known as today, is Oracle RAC. 

 

So an Oracle RAC, the private network between the nodes and the cluster can now transfer data blocks. This has resolved the disk pinging issue we saw in Oracle Parallel Server. So Jack needs to read a row of data from a table. That block is read from the shared storage into the buffer cache. Later on, Jill wants to access that same row of data. Instead of reading the data from disk, Jill’s session will request that block be transferred across the private network also known as the clustered interconnect. We’ll see examples of this as we go forward. 

 

So that in a nutshell is cache fusion. Oracle RAC Cache Fusion is simply where Oracle RAC transfers data blocks from the buffer cache of one instance to the buffer cache of another instance across the cluster interconnect. Transfers across this private network are in order of magnitude better than disk I/O activity and they help Oracle RAC performance.

Copyright SkillBuilders.com 2017

×
Transcript

Lesson 2 – Demonstration: Cache Fusion in Action

How Oracle RAC Cache Fusion Works Part 2 of 6

 

>> Brian:  Let’s see some of these in action. To do that, I’m going to get rid of my diagram here and then I’m going to explain my environment. 

 

[pause]

 

I am running a two-node Oracle RAC cluster in a virtual environment on my laptop here. And what you can see on the screen is just simply two terminal sessions, one to host01 and the other one to host02. 

 

[pause]

 

That’s it, nothing special so far. Before I can begin with my demonstration, I have three scripts that I’ll use in the demo. They just saved me a lot of typing, make my life easier. 

 

The first one is one that I call just “session stats.” If I look at the contents of that script, it’s just selecting from the stat name and the session stats. There are four particular session stats that I’m interested in and I will get those from my specific user environment for my SID. So basically, I’ll be able to run that and see what my session stats are at that moment in time. 

 

There’s another one that I use which just starts a trace in my session and all that does is go through and select my SID and serial number from v$session and then it uses the DBMS monitor package to start a trace in my session. Notice that I have both wait events and bind variables TRUE, just in case I need this for various reasons. I want to be able to capture both. 

 

[pause]

 

Then the last script that I use is one that just generates the path and name to my trace file. So feel free to use these if you like them. There are other ones out there on the Internet that do something similar. You may already have something in your code library that you use, but nothing special here. Get my session stats, start a trace in my session, and see what my trace file name is. 

 

[pause]

 

Let’s look at an example of Cache Fusion in action. So I’m going to connect my instance here with SQL Plus and we can see that SQL Plus is at the 12.1.0.1 release and it even says I’m connected to Oracle database 12c Enterprise Edition release 12.1.0.1, as well. 

 

In the other tab on the other session, I’ll connect as well with the SQL Plus. This instance was freshly started. There is nothing special about it. I’m going to create a table that I’m going to use for my demonstration. 

 

So I’m going to call this database objects as select everything from the DBA objects view. My user Scott is a little different than most people might have because in my sandbox, in my test bed here, I just granted the DBA role plus Scott just to make my life easier. 

 

I’m going to create an index on that table, on the object ID column which we know from our experience is a unique column, the primary key to database objects, actually to the underlying table sys_stat obj$ but I’m just going to add that index here because I want to be able to get an index lookup in my example here and then I’m going to just gather stats on that table. 

 

[pause]

 

Stats are gathered on there. Now what I want to do is bounce the instances. 

 

[pause]

 

While that’s coming down, I’m going to go over to this SQL Plus session I established on the other host and exit out of there. The instances are down, so I’m going to start them. And now all instances are up, so I will connect to Scott again.

 

Copyright SkillBuilders.com 2017

×
Transcript

Lesson 3 – Demonstration Understanding Global Cache Statistics and Trace Output

How Oracle RAC Cache Fusion Works Part 3 of 6

 

>> Brian:  When this first instance – the first thing I’m going to do is I’m going to start a trace in my session and I’m going to use that script that I already showed you the contents of. Pretty simple. It saved me a lot typing there. And now I’m just going to select one row from that table that I created. 

 

[pause]

 

One simple row. The other thing I’m going to do is I’m going to repeat those same steps over here on my other session on the other instance. 

 

[pause]

 

It seems simple enough. Now let’s go back to host01 over here and let’s look at the session stats that were generated from the activity. Remember, all we did here was we started a trace in the session and we selected the one row from the table. 

 

Here we can see that the session did some logical reads and it did a fair number of physical reads and then we see some global cache transfers. We know they’re global cache transfers because the statistic name starts with GC. They don’t say “cache fusion” anywhere in there. They say “global cache” and I had some blocks that were received. And there’s few small amount in there. 

 

Now let’s look at the session over on host02. 

 

[pause]

 

Notice there that the physical reads were much less but the number of global cache transfers was much higher. 

 

Let’s go back and look at host01. Sixty one physical reads and a total of six global cache transfers. On host02, down to 21 physical reads and 149 total global cache transfers. Not all of these global cache transfers were the blocks involved in the statements, some were due to recursive SQL. 

 

The recursive SQL, if you are not aware, are the SQL statements that Oracle issues on your behalf. For instance, when you go and you try to access a table, Oracle will issue an SQL statement to see if you have privileges on that table. That’s recursive SQL. 

 

The last thing I want to do is I want to get my trace filename. So there on host02 is the trace filename of my session. I’ll go back over here on host01 and do the same. 

 

I’ll exit out of SQL Plus. Now I’m going to take this whole trace filename. I’m just going to use vi to look at the content of my trace file, then I’m going to search for that table. This is my raw trace file and here is my SQL statement that I had executed. 

 

[pause]

 

We can see in here a db file sequential read, a db file scattered read and a db file sequential read on all file 5 with a couple of different block numbers – block 515, block 516, and block 131. We can see the db file sequential read read one block then the db file scattered read read four blocks and then the db file sequential read read one block. This is three different read requests that read into the buffer cache, six different blocks total, all to satisfy this one SQL statement. 

 

Remember, this was host01. The instance suggest come up, these blocks were not in the buffer cache so they had to be read from disk. The wait event showed us here in the trace file that these were in fact physical reads from disk. 

 

Now let’s go over to host02. We’ll exit out of SQL Plus. Again, we’ll use vi to look at the contents of the trace file and I’ll just fast forward to where that statement is. 

 

[pause]

 

Notice this time there are no physical reads being performed. In its place are three global cache wait events – the GC current block two-way, the GC CR multi-block read request, and the GC current block two-way. 

 

If we look at the P1 value of each, we can see that they are all belonging to file 5. For these wait events, P1 is the file number. In the case of the GC CR multi-block read request, the trace file actually shows us the file number instead of saying P1. 

 

The P2 parameter is the block number. Again, these are the same blocks – 515, 519, and 131. 

 

[pause]

 

Which objects do these blocks belong to? I’m going to cut and paste in a simple SQL statement. 

 

[pause]

 

Let me reformat that so it’s a little bit more readable. 

 

[pause]

 

We can see from this query the results that were returned is that file 5 starting at block 128 for a length of eight blocks is the db objects table. This contains block 131. So that final event was for block 131 from the db objects table. File 5, block ID 512 for eight blocks is the index. This shows us that we use the index. 

 

Now let’s go back to that trace file. Here’s that trace file where we left off. The first global cache wait event is saying I need to read some data from the index. The second one is getting more data from the index. The third wait event for the global cache transfer is getting the block of data from the table itself. 

 

Now the astute person may have noticed that there’s a slight difference here. If we go back to host01, notice that the first read event of interest here is block 515 file 5, just like the first global cache transfer. But notice that the second one is block 516 of file 5 but four blocks were read. So this is blocks 516, 517, 518, and 519. Yet, when the data is was transferred across the wire on the second global cache transfer file 5, only block 519 was transferred. Not all four blocks that were read in that physical read. 

 

So this is even more efficient. And then in the end, no matter which session we were looking at, block 131 of file 5 was read to get the data. 

 

So we can see here that global cache transfers via Cache Fusion are working as advertised. The data was read from disk on a session in instance one and then transferred via the Cache Fusion mechanism to instance two.

 

Copyright SkillBuilders.com 2017

×
Transcript

Lesson 4 – demonstration: Global Cache Current Transfer vs Consistent Read Transfer

How Cache Fusion Really Works Part 4 of 6

 

>> Brian:  The next thing I want to talk about is the difference between a current block and consistent read block. 

 

In our example that we’ve seen so far, a user on instance one just read the data. And on instance two, another user just read the data. There was no data being changed. Because data was not modified, the user on instance two could obtain the most recent or, in other words, the most current version of that block. Hence, the global cache current wait event. 

 

What I’m going to do is bounce the instances. 

 

[pause]

 

Now that the instances are bounced, I’ll start them up. The big reason I do this is just to make sure we’re starting with a clean slate, fresh from scratch so that nothing else influences our analysis. Now I’ll go back into SQL Plus and I’m going to update one row in this table. 

 

[pause]

 

We’ll just verify that result. 

 

[pause]

 

And there it is. But I have yet to commit the changes from this session. So only this session can see that change. If I go over to the other instance and this time I’m going to start a trace in my session –

 

[pause]

 

And let’s look at that value. Here it says the owner is system, not Scott. This is Oracle’s consistent read mechanism in play. Any session is not allowed to see uncommitted changes to the data. This is true on single instance as well as Oracle RAC. Oracle RAC has to follow those same rules. What Oracle does under the covers is it uses the information in the UNDO table space to create an image of that block before the change occurred and Oracle then takes that consistent read image and transfers it via Cache Fusion as a global cache transfer. 

 

Now let’s look at my trace file. 

 

[pause]

 

We’ll look at the wait events here and we can see the three wait events of interest are all global cache transfers because the wait event name starts with GC. The first block is still a current transfer because the index block has not changed. But notice that third one, now we see a global cache CR wait event and this is a global cache consistent read wait event. It’s still on the same file and it’s still on the same block of that file. 

 

Hopefully this explains the difference between a global cache current and a global cache consistent read transfer. Remember that a global cache current transfer is Cache Fusion transferring the current image because the block has no uncommitted changes. When there are uncommitted changes, Cache Fusion will ship a global cache consistent read image instead as our demonstration has shown.

 

Copyright SkillBuilders.com 2017

×
Transcript

Lesson 5 – 2-Way vs 3-Way Block Transfers

How Oracle RAC Cache Fusion Really Works Part 5 of 6

 

>> Brian:  Next I want to discuss the difference between the two-way and the three-way cache transfers. In order to understand the difference between the two-way and the three-way, we need to recognize that there are three pieces of information that a block will have in an Oracle RAC database environment. 

 

First, every block is a resource and just like every resource in Oracle RAC, one instance is charged with being the master of that resource. There can also be an instance that holds the most recent block and then there can be an instance that is requesting that block of data. So the three things are the resource master of the block, the holder of the block, and the requestor of the block. 

 

Now, to understand this I’m going to bring back my diagram featuring my two instances here. Let’s assume that Jack is the holder of the block as we’ve seen so far in our demonstration. Jill is then the requestor of the block. While one of these instances must be the master of the block as well, the master of that resource, it doesn’t matter which instance is the master and it doesn’t necessarily have to be the instance that’s holding the block. The instance Jill is connected to could very well be the master of the block or it could be Jack’s instance. We don’t know and it really doesn’t matter. 

 

But in this case, only two instances are involved. Some combination of the resource master, the holder of the block, and the requestor of the block are all involved in these two instances. Therefore, any cache fusion transfer is called a two-way global cache transfer. So what about the three-way global cache transfers? 

 

Remember, there is a resource master, the holder of the block, and the requestor of the block. If there are three or more instances in the Oracle RAC database environment then it is possible for the resource master to be in an instance that is not shared with either the holder or the requestor. In fact, the more instances in the Oracle RAC databases, the more likely it will be that the resource master is not with the holder or the requestor at all. In that case, we will see a three-way global cache transfer. 

 

There will never be more than the three-way global cache transfer. You will not have a four-way or a five-way even if you have four or five instances for your Oracle RAC database. This is because we are only concerned with just the resource master, the holder, and the requestor. 

 

[pause]

 

Look at some of the wait event names. I can look in V$ event name. 

 

[pause]

 

And here I can see global cache transfer wait events. 

Copyright SkillBuilders.com 2017

×
Transcript

http://youtu.be/Krs4qFnbLLc

How Oracle RAC Cache Fusion Really Works Part 6 of 6

 

>> Brian:  The three-way transfers. Now these wait event names which may have seems cryptic at first should make so much more sense. 

 

To recap, we showed current global cache transfer which are nothing more than a session obtaining the block via Cache Fusion from another instance’s buffer cache and that block is getting as the most recent that contains no uncommitted transactions. Then we also saw an example where we had a session in one instance with an uncommitted transaction and a session in another instance that was requesting the block to be transferred via Cache Fusion. So Oracle had to generate a consistent read image of that block. Hence, the CR wait event. 

 

Our examples were only on a two node RAC cluster in a virtual environment on my laptop here, so therefore we could only see some two-way wait events. If there were three or more instances involved, we could’ve seen three-way wait events. Remember that the three instances involved could be some combination of the resource master, the holding instance of the block, and the instance requesting that block. 

 

[pause]

 

I appreciate you watching my video. I hope you found it informational and instructive. Thank you for your time.

 

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×