USL in excel - without the need to read

Neil Gunther created a nice small excel spreadsheet to calculate the key values sigma and kappa for his Universal Scalability Law - primarily for the use in his GCaP class. Beside some other problems with excels numerical precision and Negative Scalability Coefficients in Excel I dislike the ugly 'create a graphic, let it show a trendline and it's coefficients, add these into some cells and continue' step. Fortunately, Scott Roberts has created some google spreadsheets where he implemented a real formula (as google spreadsheets does not support trendlines and their coefficients). These are mentioned in Neils blog entry Scalability in a Spreadsheet - google style.

As this method is also available in excel, I decided to extend Neils excel by some functions to avoud the read and insert part.

It is not just easier to apply, it's also more accurate as the coefficients shown in the graph are shown with small rounding.

With the formula shown here:

Don't be confused by the semicolons in the formula, depending on your language settings, excel sometimes use colon or semicolon to separate fields of the formula.

Here the detailed fields I've added:

Field H11:	R²
Field I8:	=INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE); 1)
Field I9:	=INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 1);2)
Field I10:	=INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 1);3)
Field I11:	=INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 3);1)

I hope i used the LINEST function correct, and this might help others to reduce one step while playing with USL.

MOS can't count

MOS (the flash version) can not even count up to 3:

Can someone show me the hidden element?

Guerrilla Capacity Planning arrived - let's prepare the underground resistance!

Some weeks ago one of my X-mas gifts to myself arrived. (One of those I'm always allowed to buy as none of my relatives would ever imagine to buy me such books for Christmas): Guerrilla Capacity Planning by Neil J. Gunther. In fact, I was only interested in his Universal Scalability Law, which did me a good service in a performance review some weeks ago.

I really like this book and I'm sure it will go with me for some more time; at least every day I go to work by public transport, but also in many other situations.

The idea which gave the book it's name is the Guerrilla Capacity Planning. This is described in the first 2 chapters.

I read it as another iteration of KISS, applied to Capacity Planning. The main goal is to achieve fast and just good enough predictions by the usage of minimal resources and time. It just leads one step further and advises to be well prepared in lean tools and flexible methods to reach the goal when needed. In my current situation, I also see it as a hint to do Capacity Planning in every project, even if it's not calculated within the project plan. But keep it small enough to hide it within the jitter every project contains. The following chapters try to provide some simple weapons to be prepared for all tactical situations which can occur.

Chapter 3 is about significant digits, rounding rules and errors.

These 13 pages where worth reading for me, because I never had a full qualified mathematical or statistical education. It might be just enough to estimate the errors I carry through all my work and provide them (and their meanings to the results) in any discussion, presentation and so on.

Chapters 4 to 6 are the real reason why I ordered this book: the Universal Scalability Model (or Law?). Neil leads in a very consistent way, why scalability is not only limited by contention, as Amdahls law implies, but also by coherency, which leads to a retrograde in scalability beyond p*.

Not only the Model itself is described, but - important for all Guerrilleros - an easy method to gain the parameters for σ and κ out of some measured data with excel is provided. Not mentioned in the book directly but easily to find is the spreadsheet which contains exactly the method Neil provides.

I would like to have also other methods provided within the book to circumvent the problem with excels numerical precision, but Neil provided (and discussed it's difficulties) a glimpse of a implementation in R somewhere else.

In chapter 7 the main focus is on virtualization across all scales, from in-CPU (Hyperthreading) up to Grids and P2P on the other end of the scale. Here it's more about queues, schedules and polling cycles. I had the feeling I should have read Analyzing Computer System Performance with Perl::PDQ before. But I have not. (I will, after the 2nd ed. is published). Also there is not too much ammunition for my underground resistance. More a rough description of that area and some major snares.

Currently I cannot say much about chapters 8 to 11 as I'm in the middle of 8 at the moment. they will also be worth to write about them - in the future.

There is also some criticism outside there which laments, USL does only provide limited practical use for the forecast, until a set of data is measured and σ and κ are derived from these. In comparison, Amdahls s can be measured so much easier as the single threaded phase of a program. This criticism provides its own error inside: If s can be measured, this mean the setup is well instrumented and known and the target hardware is chosen (otherwise, the measurement must be somehow translated to the target hardware) at least for a single process. But, with a well instrumented and know setup, also all code parts which will account to coherency can be spotted and therefore not only Amdahls s leads to σ, but the coherency part leads to κ.

Unfortunately, even in a well instrumented Software like Oracle, nobody sorted all the wait events where they account to.

PRKO-2007 is not always correct

This week I had to stop and start an instance in a CRS-cluster. Nothing special so far, I just hit a PRKO-2007 error. For those which does not know it by heart, here the short description:

PRKO-2007: "Invalid instance name: "
Cause: An invalid instance name was entered.
Action: Use the correct instance name for the database. Run 'srvctl config database
-d ' command to find out all instances of a database in the
Cluster Database Configuration.

and here the copy of my commands (solution included):

oracle@aves742p:~/ [EDWP031] srvctl stop instance -d EDWP03 -i EDWP031
PRKO-2007 : Invalid instance name: EDWP031
oracle@aves742p:~/ [EDWP031] srvctl stop instance -d EDWP03_SITE1 -i EDWP031

I'm sure, you see the difference, and it's not the instance!
Just once again: don't trust the error message 100%, sometimes it's still worth to use the brain ;-)

hardware lasts longer than software

A week ago, Oracle has replaced its good old support platform MetaLink with MyOracleSupport (MOS).
The thing I like most about MOS is the non Flash-based HTML-Version.
Many discussions about the need, target and failures during the last week of transition are ongoing in blogs, mailing lists, forums; I also had to suffer and had to communicate it. But that's not what's this blog is about.

I got into touch with MetaLink in 2000, as Oracle tried to move the customers from calling the Support to a self-service platform. They promoted MetaLink on many events, with everything you can imagine. The transition was not forced, but within a short period of time most customers liked the possibility to get many answers faster than on the phone. A real win-win situation.
Also I learned how to use MetaLink effective and therefore like it.



On one of these events, I got the promo-mug. I like it, because I like MetaLink.
Nowadays, everytime I feel without any Support from Oracle, I cling to the mug, hopefully filled with good, strong, hot coffee. This makes me feel a little bit better.
And sometimes, hardware really lasts longer than software, especially if you have the hardware in your hands and take care of.

rollback in PL/SQL

I recently came across a slightly, but possible dangerous mismatch between transactions and the PL/SQL code based on it.
Here a cut down example (not very realistic, but easy to follow):

create table mytab (myval number);

create or replace procedure tuwas (
    varA in number,
    varB in number,
    varC OUT number)
is
begin
 varC := varA;
 insert into mytab values (varA);
 if varB = 0 then
   commit;
 else
   rollback;
 end if;
end;
/

The Idea behind the code is, to get varA and varB as input parameters, do some calculations (missing here), insert the result into a table and return the calculated value for next steps.
There might be reasons to rollback the DML within the procedure, which is not seen as an error.

And here the result of the procedure:

set serverout on

declare
 vA number;
 vB number;
 vC number;
begin
 vA := 1;
 vB := 0; -- commit;
 tuwas(vA, vB, vC);
 dbms_output.put_line('vC: ' ||vC);
 vA := 2;
 vB := 1; -- rollback;
 tuwas(vA, vB, vC);
 dbms_output.put_line('vC: ' ||vC);
END; 
/

vC: 1
vC: 2

select * from mytab;

    MYVAL
----------
        1

What's dangerous here? Even with

 vA := 2;
 vB := 1;

the return value

vC: 2

is given. But t's not visible in the table, as the procedure did a rollback.
I learned from this example never to populate return-values until you are sure it's really stored (if this is a requirement, of course).

Oracle Security Options

Last week I attended a 1 day Seminar about Oracle Security by Dominique Jeunot.
Dominique did a great job to squeeze everything she knows into one day, and it was allways obvious there is a lot more she could tell us in the script and even more in her brain. Nevertheless she created a very good overview with the right level on all topics. It was obvious she could only make a kind of teaser, but it was a very tasty one!
Maybe the most important picture she draw was the first overview about all available features, options and products. I did the effort to draw it for my own joy and will share it here, as it might be helpful for others as well. All options are colored red, so you can see where you have to take extra money.

In general you can say: for nearly every issue there are possibilities to do it without extra money (but extra effort) or you can buy a solution.

If someone wants the visio behind this jpeg, just contact me :)