Google Code

I've create a project on Google Code where I plan to start putting everything that I create, whether I blog about it or not.

http://code.google.com/p/oraclenerd/

I'm trying to figure out if I should do it project based or schema based...or application based? Don't really know.

I'll probably start with the Poor Man's Data Vault.

Google has some pretty cool stuff in their apps department there:
http://code.google.com/more/#products-products-android

I'd definitely like to check out the Chart API


http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250x100&chl=Hello|World

How cool is that?

Also want to check out their Visualization API. A nice example can be found here.

So I strayed?

Has anyone else out there used Google Code?

ORA-08177 can't serialize access for this transaction

We've been getting this error recently and we are in the process of researching the problem.

I figured I might as well share some of my reference material in case someone else may find it useful.

First, what is it? From the docs:

ORA-08177: can't serialize access for this transaction
Cause: Encountered data changed by an operation that occurred after the start of this serializable transaction.

Action: In read/write transactions, retry the intended operation or transaction.

There are 4 isolation levels when it comes to transaction processing, all are based on the ANSI/ISO SQL standard SQL92:

• READ UNCOMMITTED
• READ COMMITTED
• SERIALIZABLE
• REPEATABLE READ

Oracle handles these the following ways:

READ UNCOMMITTED:
Oracle Database never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle Database.

READ COMMITTED
Oracle Database meets the READ COMMITTED isolation standard. This is the default mode for all Oracle Database applications. Because an Oracle Database query only sees data that was committed at the beginning of the query (the snapshot time), Oracle Database actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation.

SERIALIZABLE
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.

REPEATABLE READ
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.

I've learned more about transaction isolation levels in the last few days than I ever cared to know.

READ COMMITTED is the default for Oracle. There's a possibility that our calling applications are using SERIALIZABLE via the driver. I've never worked with SERIALIZABLE.

According to the trace files it is happening on both INSERTs and SELECTs and we've seen multiple occurrences on the same 2 statements.

I suspect, but can't prove, it's (evil) trigger related. Any pointers as to how to prove that would be most helpful. Or any pointers in general in dealing with this issue.

Update
I did find this post where the author says:

Once more - in the vaste majority of cases usage of serializable isolation level is a design error that leads to non-scalable applications. Most databases enforce it with very restictive locks that kill concurrency and in case of Oracle and other databases that rely on multiversioning (PosgreSQL, Interbase) you have to be ready to failed ("Can not serialize") transactions. Latter is, usually, tolerable as soon as application is prepared for them (normally, it is enough just to restart transaction). For Oracle quite good discussion of this topic (as almost any other Oracle-related topic) may be found on asktom.oracle.com (http://asktom.oracle.com).

Unfortunately it was just a statement without the proof to back it up. I'm trying to get away from just making blanket statements without providing the evidence.

Coding is Easy - II

Yesterday I said Coding is Easy.

Some of the comments here and on reddit got me thinking a little bit (more).

I'll expand on that as it relates to the database world. First though, how do you measure code complexity? There has to be a scientific way, as compared to just stating, "This code is too complex." right?

Cyclomatic Complexity

...(or conditional complexity) is a software metric (measurement). It was developed by Thomas J. McCabe in 1976 and is used to measure the complexity of a program. It directly measures the number of linearly independent paths through a program's source code.

The concept, although not the method, is somewhat similar to that of general text complexity measured by the Flesch-Kincaid Readability Test.

Cyclomatic complexity is computed using the control flow graph of the program: the nodes of the graph correspond to the commands of a program, and a directed edge connects two nodes if the second command might be executed immediately after the first command.

Later in the Key Concepts section:

The cyclomatic complexity of a section of source code is the count of the number of linearly independent paths through the source code. For instance, if the source code contained no decision points such as IF statements or FOR loops, the complexity would be 1, since there is only a single path through the code. If the code had a single IF statement containing a single condition there would be two paths through the code, one path where the IF statement is evaluated as TRUE and one path where the IF statement is evaluated as FALSE.

In the database world, specifically Oracle (PL/SQL), your Control statements, IF/END IF, CASE, LOOP, etc. would increase the number of paths through the code.

Have you ever seen a CASE statement like this?

CASE
  WHEN some_column = 'VALUE 1' THEN
    do_something;
  WHEN some_column = 'VALUE 2' THEN
    do_something_else;
  WHEN some_column = 'VALUE 3' THEN
    do_something_else_thrice;

and it goes on for another 20 lines or more? Is this a good time to possibly rethink the underlying model? I say it is.

It may lead to the creation of another (lookup) table.

It may mean reorganizing your data in other ways.

It may mean absolutely nothing.

From Code Simplicity:

Often, if something is getting very complex, that means that there is an error somewhere far below the level that things are getting complex on.

For example, it’s very difficult to make a car move if it has square wheels. You’re going to be spending lots and lots of time figuring out how to make the car work, when really it should just have round wheels.

Any time there’s an “unsolvable complexity” in your program, it’s because there’s something fundamentally wrong with it. If the problem is “unsolvable” at one level, maybe you should back up and look at what’s underlying the problem. Maybe you put square wheels on the car, and now you’re trying to figure out how to make it go fast.

Programmers actually do this quite often. For example, “I have this terribly messy code, now it’s really complex to add a new feature!” Well, your fundamental problem there is the that code is messy. Clean it up, make the already-existing code simple, and suddenly adding the new feature will be simple.

At the risk of just quoting the entire Code Simplicity article...ah, who am I kidding? He says it much better than I could.

So when things get complex, back up and you look at the problem that you’re trying to solve. Take a really big step back. You are allowed to question everything. Maybe you thought that adding 2 and 2 was the only way to get 4, and you didn’t think about adding 1 and 3 instead, or just skipping the addition entirely and just putting “4” there. The “problem” is “How do I get 4?” Any method of solving that problem is acceptable, so figure out what the best method would be, for the situation that you’re in.

Discard your assumptions. Really look at the problem that you’re trying to solve, and think about the simplest way to solve that problem. Not “How do I solve this problem using my current code?” Not “How did Professor Bob solve this problem in his program?” No, just how, in general, in a perfect world, should that problem be solved? From there, you might see how your code needs to be re-worked. Then you can re-work your code. Then you can solve the problem.

Back up and take a look at the problem you are trying to solve.

Indeed.

A good design WILL make your coding less complex. In my case, that means a good data model.


For other references see:
Linux Journal, Programming Tools: Code Complexity Metrics
CodeProject, Cyclomatic Code Complexity Analysis for Microsoft .NET Applications
ONJava, Code Improvement Through Cyclomatic Complexity
Google, Let Me Google That For You

Coding is Easy

There, I said it. What a relief too.

Coding is easy. Once you learn the syntax of your language of choice, hammering out code is easy. The hard part is the design onto which you apply your code. The better the design, the easier the code.

With a nod to Kathy Sierra and her wonderful graphs...



I don't mean to infer that there is a sweet spot there where design goodness and complexity of code meet, just bare with me.

My point is simple, the better the underlying design, the easier it is to code against. Naturally I see this as a database thing as that's where my level of "expertise" lies, but it applies to all software development. Java, Ruby, .Net, whatever. In the OO world it would probably be class design. In my world, it's all about the data model.

If it were so easy, why the heck do they pay us so much? I ask myself that every day. Since I wasn't talented enough to play professional baseball and make oodles of money, I consider myself extremely fortunate to make the money I do at something I so thoroughly enjoy. I sometimes feel guilty about it too.

But why is it easy? I don't really know how to answer that. It's just been my experience. I would also imagine there is alway an exception; as I don't write programs for the Space Shuttle, I can't attest to that, but the point is the same. If you have a good model (design), coding is easy.