The Oracle Instructor

This article shows an easy way to determine, whether your Oracle Database Performance Tuning task has been successful – or not. In the end, it boils down to “The objective for tuning an Oracle system could be stated as reducing the time that users spend in performing some action on the database, or simply reducing DB time.” as the Online Documentation says. Best proof would be a confirmation from the end users that run time got reduced; second best is a proof of reduced DB time, which is discussed here.

A tuning task should always end with such a proof; your gut feeling or high confidence is not sufficient – or as I like to say: “Don’t believe it, test it!”

The demo scenario: With an Oracle Enterprise Edition version 11.2.0.3, an application uses these commands to delete rows:

SQL> create table t as select * from dual where 1=2 -- see * below ;
 
Table created.
SQL> begin
 for i in 1..100000 loop
 execute immediate 'delete from t where dummy='||to_char(i);
 end loop;
 end;
 /

I am pretty sure that this code is not optimal, because it uses Literals instead of Bind Variables where it is not appropriate. Before I implement an improved version of the code, I take a Baseline with Automatic Workload Repository (AWR) snapshots. On my demo system, snapshots are taken every ten minutes:

The code with Literals was just called – now about 10 minutes later on the Enterprise Manager (EM) Performance page:

The AWR report that I take (with EM or with awrrpt.sql) as the baseline shows the following:

Notice especially the poor Library Cache hit ration, significant for not using Bind Variables – and the meaningless high Buffer Cache hit ration

Starting after 9:00 am, my improved code that uses Bind Variables runs:

SQL> begin
 for i in 1..100000 loop
 delete from t where dummy=to_char(i);
 end loop;
 end; 
 /

The EM Performance page show no peak during the next 10 minutes which represent my comparison period after the tuning task:

Let’s look at the AWR report of the second snapshot range after the tuning task:

Same wall clock time, same application load, but reduced DB time – I was successful! Could stop here, but some more details:

The important (especially for OLTP systems) Library Cache hit ratio is now very good. A very convenient way to compare the two snapshot ranges is the ‘AWR Compare Periods’ feature in EM (or awrddrpt.sql) , which shows us instructively:

Although in both periods, CPU was the top event (also in % DB time), it took much less time in total for the 2nd period:

The Time Model Statistics confirm a strongly reduced Parse Time for the 2nd period:

Especially, we see a striking improvement for the run time of the code with Bind Variables: From about 82 seconds down to about 3 seconds!

This kind of proof (less DB time) can be used also in cases where the reduction of run time for a single statement is not so obvious as in my example. If 1000 users had done each 100 deletes, they would have seen not much difference in run time each – but the parse time summarizes and impacts overall performance similar as seen here. If you would like to see the three original AWR reports were I took the screen shots above from, they are here as PDFs

AWR_Report_baseline

AWR_report_after_tuning_task

AWR_Compare_Periods

Conclusion: You will – and should – be able to prove the effectiveness of your Oracle Database Tuning task with a reduction of DB time from an AWR report comparison. After all, you don’t want to waste your efforts, do you?

* My apologies to anyone who tried the first code without the addendum where1=2 – it doesn’t work. Apparently, I have deleted the one row in the table during my preparation of the demo.

After years of teaching and studying (holding a Bachelor’s Degree in Educational Science), I think I’m now able to put my experience & conclusions into the this formular:

Explain:

Even a complex technical topic (like an Oracle Database) should be explained to the audience in an easy understandable way. That is of course only possible if the instructor knows his stuff thoroughly but also resists the temptation to blind the audience with his deep knowledge in order to appear clever. Things get complex by themselves soon enough. The explanations must be technically correct, though, but they may be simplified. Teaching will always show a model of the reality – and that model should hold water after the knowledge of the audience expands.

Two extremes that illustrate what should be avoided here: 1) “The Expert” The instructor is deeply involved in some complex technical matter and tells many specific details about that to the audience that they can hardly understand or benefit from, which the instructor does not recognize. 2) “The Ignorant” The instructor knows little about the subject (reading introductions during breaks) and just fantasizes answers if the questions are too difficult.

Exemplify: 

It is not enough to show only slides in order to achieve a good understanding! Demonstrations will not only make the teaching more lively, they will also prove (or sometimes falsify even) the statements of the instructor. They should be reproducible, so that the audience can see for themselves. A claim that essentially only says: “I know that because I’m an expert, trust me!” is not acceptable.

Empower:

The ultimate goal of the instructor should be to empower the recipients to use the explained technology themselves in an effective and efficient way. Accompanying practices during a course help a lot to reach that goal. In absence of practices, demonstrations are the second best way to empower the audience. They should realize that they can do these things also, not only the instructor. Teaching is not supposed to be a sales pitch for products or services!

That’s already it – easy, right? Some hard work involved under the covers, though…

My best days are when I encounter former attendees of my courses, telling me how much they liked it and that they actually could make use of the contents and implemented this and that feature during a certain project. It happens every now and then, when I think to myself: Empowered – Good Job

The impact, Exadata will have on your IT’ s organizational structure can range from ‘None at all’ to ‘Significantly’. I’ll try to explain which kind of impact will likely be seen under which circumstances. The topic seems to be very important, as it is discussed often in my courses and also internally. First, it is probably useful to be clear about the often used term ‘Appliance’ in relation to Exadata: I think that term is misleading in so far as Exadata requires ongoing Maintenance & Administration, similar to an ordinary Real Application Cluster. It is not like you deploy it once and then it takes care of itself.

Due to its nature as an Engineered System, it is partly much easier to deploy and to manage than a self-cooked solution, but there are still administrative tasks to do with Exadata, as the following picture shows:

Without pretending precision (therefore no percentages mentioned), you can see that the major task on Exadata is by far Database Administration, while Storage Administration and System/Network-Administration are the smaller portions. The question is now how this maintenance is done. You could of course decide to let Oracle do it partly or completely – very comfortable, but not the focus of this article. Instead, let’s assume your IT Organization is supposed to manage Exadata.

I think it’s important to emphasize that your internal organization does not have to change because of Exadata. You can stay as you are! Many customers have dedicated and separated teams for Database Administration, Storage Administration and System/Network Administration. It is perfectly valid to decide that the different components of your Exadata are managed by those different teams as on this picture visualized:

The responsiveness and agility towards business requirements will be the same with the siloed approach as it is presently with other Oracle-related deployments at your site. There is obviously some internal overhead involved, because the different teams need to be coordinated for the Exadata maintenance tasks.

This approach is likely to be seen if Exadata is deployed more for tactical reasons (we put this one critical and customer-facing OLTP system on an Exadata eight-rack, e.g.) respectively if your internal organization is very static and difficult to change. I have seen this from time to time (SysAdmins in the course who have never seen Oracle Databases before), but I would say it is more an exception than the rule.

In short: You will get the technical benefits out of Exadata, but you will leave benefits that come from increased administrative efficiency and agility on the table.

Here you give the administrative ownership of Exadata to a team, built largely or exclusively from your DBAs and give them named contacts from the other IT groups, so they can get their expertise on demand, like this picture here shows:

Why is the DBA team supposed to own Exadata? Because as shown on Pic 1 above, they are doing the major task on Exadata anyways. And it is relatively easy to train these DBAs for Oracle Database Administration on Exadata, because they know already most of it:

I simply cannot emphasize enough how important this point is: The know-how of your Oracle DBAs remains completely valid & useful on Exadata! The often huge investment into that know-how keeps paying back! I am still surprised that the true quote ‘Exadata is still Oracle!’ is from a competitor and not from our Marketing

For DBAs, it is similar as moving from a Single-Instance system to RAC. Some additional things to learn, like Smart Scan and Storage Indexes. Over time, the DBAs on that team may incorporate the know-how they gather at first from their named contacts of the other groups. The pragmatic EDBA approach is likely if Exadata is seen as a strategic platform, but the effort to build a DBMA team is regarded to high respectively the internal organizational structure is not flexible enough to start with the third approach. Administrative responsiveness and agility are already higher here as with the first approach, though.

Either the EDBA team evolves over time into a DBMA team or you start straight with this approach, typically by assigning some Admins from the Storage/System/Network groups to the DBMA team and let all team members cross-train in the three areas:

This gives you the optimal administrative responsiveness and agility for your business requirements related to Exadata, which is what the majority of customers will probably want to achieve – at least in the long term. You will see this approach most likely if Exadata is supposed to be a strategic platform at your site. The good news (in my opinion) for the team members here is that they all will enlarge their mental horizon and gather new and exciting skills!

Regardless of your particular approach, the most important single administrative tool will be the Enterprise Manager 12C:

You can see that you can manage all Exadata components with a single, centralized tool. The picture is from our excellent Whitepaper Oracle Enterprise Manager 12c: Oracle Exadata Discovery Cookbook

Much of the above, especially the terms EDBA and DBMA can be found in our Whitepaper Operational Impact of Deploying an Oracle Engineered System (Exadata) – for some reason it is presently only available through our Oracle Partner Network. It’s kind of our official ‘party line’ about the topic, though. I’d also like to recommend Arup Nanda’s posting Who Manages the Exadata Machine? that contains related information as well.

Finally, let me highlight the offerings of Oracle University for Exadata – you will see that there is training available for any of the three approaches