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

Smart Scan in Practice

Smart Scan is a wonderful capability, but you don’t always get it. It’s impossible for many execution plans, and this is a major restriction.

If you think about what a Smart Scan actually does, it delivers individual columns, individual rows back to the instance. Now, a buffer cache can accept only blocks. Therefore, Smart Scan cannot possibly put those columns of rows into the buffer cache. It’s simply not formatted appropriately. So, a Smart Scan has to return values directly into the session’s PGA or, to put it another way, the only access method that can use Smart Scan is direct read.

Well, what access methods can use direct read? There are only two, which are table full scan and index fast full scan. Any other access method, typically index range scan, table access by row ID, cannot use a Smart Scan.

The second major issue, there are strict limitations of the type of objects that can be accessed through Smart Scan. It really is only heap tables. You can’t use indexes. You can’t use clusters. You can’t use IOTs. Heap tables only.

Perhaps hardest to track down and giving sometimes very erratic results is that Smart Scan can be interrupted by various conditions. You’ve met all the requirements for Smart Scan, directory and so on, got the right execution plan. The Smart Scan starts and then hits something that causes a problem. Issues that we know cause problems are, for instance, read consistency, also delayed block cleanout, change rows. Any of those issues and a few others mean that the storage tier will have to interrupt its Smart Scan, deliver complete blocks into that buffer cache, let your session then do what is necessary to the block, and only then can the Smart Scan proceed.

Now, in order to maximize the use of Smart Scan, there may be quite a lot of work. Very often, you’ll have to adjust your index structures. Making them invisible is a nice technique there. There are many, many, many parameters that can influence the likelihood of achieving a Smart Scan, and almost inevitably you’re going to be rewriting a lot of hint SQL and putting hints in it to get the correct execution plans that can enable a Smart Scan to occur.

This is all because of one fundamental problem; the optimizer is not in any way aware of the Exadata. The optimizer develops an execution plan in exactly the way it would without the Exadata storage. The use of Smart Scan, the awareness of Exadata comes at the next level down. The optimizer develops the plan through a normal pass and then passes it through to the SQL execution engine, and it’s the SQL execution engine that determines, on a case-by-case basis, whether to use the Smart Scan.

This means that you might develop a plan and execute the statement 50 times. Forty-nine times, you get a Smart Scan. The 50th time, for whatever reason, the SQL execution engine decides not to. This can result in somewhat erratic performance.

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

 

×