Search

Top 60 Oracle Blogs

Recent comments

March 2009

Oracle, Timesten and PL/SQL support

I thought to post about another new interest of mine, TimestTen, as I’ve worked with it in past and I have become a fan of it, especially after Oracle bought the company.
Oracle has announced that TimesTen in-memory database will support PL/SQL in the upcoming release. That’s in 11gR2, where TimesTen is named the “in-memory database cache”.
I’m happy to see the deep level of integration Oracle is doing with it.

Install to go-live, 3 days

This has been an interesting week, but not really that surprising.

I was called back to a previous client site where I had previously helped with some Oracle Application Server (10.1.2.2) post-install configuration. In that previous visit, I got oriented to the environment they use and the packaged application they were deploying. The packaged application uses JSP, Oracle Forms, and Oracle Reports (possibly also Discoverer). The deployment environment is all Microsoft Windows servers with two Oracle Application Server homes per application server since the vendor’s deployment requires that JSPs be deployed in a separate O_H from the Oracle Forms and Oracle Reports environment (that’s the first eyebrow-raise I did, but whatever).

This customer had an environment that was configured by the vendor for testing purposes and it works fine. However, it uses HTTP and they want to use HTTPS for all client-server traffic. They also wanted to be able to manage the environment and be better equipped to support it, so they left the vendor-installed environment as is and built a new environment on new servers so they’d get first-hand views of the install and configuration procedures. Since all the application servers are virtual machines, they could easily create additional machines.

The Evils of Encoding Meaning Into Data

About a year ago I worked on collating and transforming data from an application so that it could be imported into another app. I've performed this exercise a number of times in the past 20 or so years, and while it is never boring, it is sometimes quite challenging.

Oft times when trying to make the data suitable for inclusion in the new applications, I ask my self "What were they thinking?"

I will leave the answer to that up to your imagination, as my answers to that particular question are not always complimentary.

One of the problems run into is when the data modeler and database designer chose to allow data with encoded special meanings.

In other words, there is key data in the database, the meaning of which can only be ascertained by asking someone that knows, or finding it in the documentation (Is there documentation?)

The example I will use is a real one I ran into.

PROTOCOL VIOLATION DETECTED

This error showed in a trace file (udump) and caused all kind of strange errors in the user application at a customer where I was working. A quick search on metalink pointed me to Doc ID 601709.1 titled “What does the message PROTCOL VIOLATION DETECTED mean in the trace files”.  The message seems to be related to bug 4669305 (which is not published by Oracle Support), but it moved me into the direction of ora-600[12333] and ora-3106. There I found as simple test case where some one in SQL*Plus had a reproducable case: select 1 from dual where rownum < ###. This caused the message to show in the trace file and complain about the bind variable value ‘###’. That looked funny as this was not a bind variable, but ofcourse the database had CURSOR_SHARING set to similar or to force. My customer had CURSOR_SHARING set to similar.

The full power of Oracle’s diagnostic events, part 1: Syntax for KSD debug event handling

There’s a recent thread in Oracle-L about deadlocks and a recommendation to dump various instance information when the deadlock happens. A deadlock trace dumps some useful things automatically, but sometimes you want more, especially in RAC environment.

So is it possible to make Oracle dump additional things when the deadlock event happens? Yes it is and it’s doable with Oracle diagnostic event handling infrastructure.

First I’ll take a step back and explain, what this command below means:

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Of course you know what it does, it enables extended SQL trace. But why such cumbersome syntax?

This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):

10046

The full power of Oracle’s diagnostic events, part 1: Syntax for KSD debug event handling

There’s a recent thread in Oracle-L about deadlocks and a recommendation to dump various instance information when the deadlock happens. A deadlock trace dumps some useful things automatically, but sometimes you want more, especially in RAC environment.

So is it possible to make Oracle dump additional things when the deadlock event happens? Yes it is and it’s doable with Oracle diagnostic event handling infrastructure.

First I’ll take a step back and explain, what this command below means:

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Of course you know what it does, it enables extended SQL trace. But why such cumbersome syntax?

This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):

10046

The full power of Oracle’s diagnostic events, part 1: Syntax for KSD debug event handling

There’s a recent thread in Oracle-L about deadlocks and a recommendation to dump various instance information when the deadlock happens. A deadlock trace dumps some useful things automatically, but sometimes you want more, especially in RAC environment.

So is it possible to make Oracle dump additional things when the deadlock event happens? Yes it is and it’s doable with Oracle diagnostic event handling infrastructure.

First I’ll take a step back and explain, what this command below means:

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Of course you know what it does, it enables extended SQL trace. But why such cumbersome syntax?

This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):

10046

The full power of Oracle’s diagnostic events, part 1: Syntax for KSD debug event handling

There’s a recent thread in Oracle-L about deadlocks and a recommendation to dump various instance information when the deadlock happens. A deadlock trace dumps some useful things automatically, but sometimes you want more, especially in RAC environment.

So is it possible to make Oracle dump additional things when the deadlock event happens? Yes it is and it’s doable with Oracle diagnostic event handling infrastructure.

First I’ll take a step back and explain, what this command below means:

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Of course you know what it does, it enables extended SQL trace. But why such cumbersome syntax?

This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):

10046

Performance Benefits of ReUse Statement Flag in Application Engine

I have achieved some significant performance improvements in some Application Engine programs by just enabling the ReUse Statement flag on certain steps. I thought I would share a recent example of how effective this can be.

I don't think I can improve on the description of this feature in PeopleBooks:
"One of the key performance features of PeopleSoft Application Engine is the ability to reuse SQL statements by dedicating a persistent cursor to that statement.

Unless you select the ReUse property for a SQL action, %BIND fields are substituted with literal values in the SQL statement. The database has to recompile the statement every time it is executed.

However, selecting ReUse converts any %BIND fields into real bind variables (:1, :2, and so on), enabling PeopleSoft Application Engine to compile the statement once, dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

In addition, some databases have SQL statement caching. Every time they receive SQL, they compare it against their cache of previously executed statements to see if they have seen it before. If so, they can reuse the old query plan. This works only if the SQL text matches exactly. This is unlikely with literals instead of bind variables."

In fact most databases do this, and Oracle certainly does.

Advanced Query Optimization Question

Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.  I’m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.  I was also able to get some […]