Free Exadata Tutorials and Demonstrations

Let Oracle Certified Master DBA John Watson teach you what Exadata is and demonstrate how it works!

Oracle Exadata Database Machine

A Database Machine is a massively scalable and fault tolerant piece of hardware (CPUs, RAM, discs, and networking: all perfectly balanced) combined with the Exadata smart storage software. A really powerful database server, with a disc subsystem that understands the database and take over a huge proportion of the data processing workload. This is the “offload processing” feature: no other platform can do this.

Experience shows that porting a database to a DB Machine is easy – but actually getting the benefits is harder. Before making the investment (a huge investment) in a DB Machine you will do a POC that shows that the applications work. That doesn’t mean they work as well as they could. Offload processing is more elusive than one might think, and to achieve it you may need to reconfigure your data structures and adjust your code. This is a non-trivial task that goes far beyond the usual SQL and segment tuning activities: it is a whole new layer of optimization.

We believe that the real ROI will come only from extensive work after the migration, as the application and the database are tuned to the capabilities of the Exadata platform. Yes, you will get an immediate performance boost – but you should be getting much more.

Let us help you optimize your applications for Exadata!

Contact us at 1-401-783-6172 or email us to see how we can help you.


Oracle Exadata Database Machine Tutorials
(Login Required for sessions 2 and above)

  1. About the Exadata Hardware
  2. What Makes the DB Machine Special
  3. Smart Scan in Theory
  4. How Smart Scan Functions – Demo
  5. Smart Scan in Practice
  6. Making Smart Scan Work – Demo
  7. HCC in Theory
  8. HCC performance and Compression Ratio – Demo
  9. HCC Limitations and Best Practices
  10. HCC Compression Degradation Issue – Demo
  11. Exadata is Good But Not Easy

Let us help you optimize your applications for Exadata! Contact us at 1-401-783-6172 or email us.

 

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


Transcript

How Smart Scan Functions – Demo

I want to show you very briefly how offload Smart Scan or flow processing can actually function.

I’m working here just in the little demonstration Scott schema. I’ll enable auto tracing so that we can see the effect on execution plans, which will also show me whether offload processing is indeed possible.

So it’s a simple query. I’ll use a function here to check that to get offloaded as well as the column selection and the row selection and column projection. We’ll have a predicate.

Right. So there is the statement. It returned these three scalar values, as you would expect. How was this actually executed? There was a select statement, then it was executed, table access full. That’s the only way to run that statement given the index on that table because in the middle of this, we see the key word, “storage.” That key word tells me that this statement could indeed be offloaded to the storage tier. Furthermore, we see down here in the predicate information, “storage.” That key word again tells me that the row filtering could have been done by the storage tier.

So what happened is that the storage tier did not return every block of the empty* table to the database buffer cache, where my session would then have had to extract these values. All it returned to the instance was these three scalar values directly into the PGA of my server process.

Now, there’s also the initcap function there. Was that offloadable or not? There is a view “Read our SQL fm letter data,” and that will tell us for each function whether or not it can be offloaded.

So, just select its name, whether it’s offloadable. Declare offloadable as yes and put in a sort order as well, and we see that in the current release, there are 393 functions that can be offloaded. Which ones? All the typecasting functions, truncations, trims. These are functions that get executed millions of times an hour in many, many environments. Note here two particularly important ones, balloon filters. The offload capability for balloon filters allows us to offload joins or facts to dimension tables. Those two in star schemas can be offloaded.

The function I used was, in fact, initcap. Just for completeness, we’ll scroll through all of these and we’ll see that initcap itself is an offloadable function, and there it is along with many others.

There are two classes of function that cannot be offloaded, and those are the aggregation functions and the analytic functions. I mention them just for completeness. There’s an architectural issue there because aggregations and analytics potentially require access to all rows in the table. The row might be distributed across a cell.

So it’s a technical issue why they can’t be offloaded, but virtually, all other functions can be offloaded with terrific drops in CPU usage, and combining that with the reduction of data traffic into the instances will give us vast performance improvements.

×
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!

 

×