Tag: vaulted

Implicit Statement Results and Extended Data Types in Oracle 12c…

Over the last couple of days I’ve put another couple of articles live. In this case they are both focused on easing migration from non-Oracle databases to Oracle.

It’s a little hard to get excited about either feature as I would not expect any straight Oracle shop to want to use them.

The implicit statement results will be a big bonus for SQL Server migration projects, and those trying to maintain similar APIs for Transact-SQL and PL/SQL, but I don’t see myself using this feature in preference to an explicit out parameter for my own code, for the reasons explained by Tom Kyte here.

My first impression of the 32K VARCHAR2 is I pretty much hate it. I’m very much of the opinion, if you need a CLOB, use a CLOB. I don’t like the lack of control of the LOB segment and I dislike loads of objects in my schema with system generated names. Of course, migration projects will want as little refactoring as possible, so it could be very useful there, which is after all the reason it was added.

I’m a little confused why the limit is 32K. It matches the PL/SQL VARCHAR2 now. It’s bigger than the maximum size of a VARCHAR2(n) in SQL Server,  but it is a lot smaller than the VARCHAR(MAX) from SQL Server. I’m thinking I may have missed the point? Maybe I’ll warm to them the next time I do a migration…

Of course, your opinion of any new feature depends very much on what you do on a day to day basis. I reserve the right to do a complete U-turn on this stuff if circumstances change. 🙂

Cheers

Tim…

Top-N Queries and the Multithreaded Model in Oracle 12c…

A couple of new 12c articles have rolled off the press. Both of them small, but interesting to me.

These resulted in amendments to a couple of old articles.

I wonder how many people will start using the multithreaded model and still try and kill sessions on the operating system using the UNIX/Linux process ID? 🙂

Cheers

Tim…

WITH Clause Enhancements in Oracle 12c…

After doing a number of 12c installations, I decided it was time to write something new. I figured I’d pick something easy to start off with, so here is the first thing off the press.

Over the last year I’ve heard a few speakers talk about these enhancements, but I got myself into a right pickle while I was working through this. If you caught the first draft of this article you would see I made a complete mess of it and jumped to all the wrong conclusions. Having worked through it again with fresh eyes, it all became clear. Of course, if you didn’t see the first draft, then ignore what I said. The article has always been perfect. 🙂

I think I will carry on doing some of the simple stuff to build my confidence before I can tackle something big, like pluggable databases. 🙂

Cheers

Tim…

Update: Note to self. Searching the 11gR2 docs for “PRAGMA UDF” is unlikely to result in you finding this 12c feature. Who’da thunk it? Thanks to Tom Kyte for pointing me to the correct doc reference. I’ve altered the searches on my homepage to default to the 12c docs, so hopefully I won’t be such a Muppet next time. 🙂

The sort of latency heat map you don’t want to see!

We’ve had a couple of short lived, but very inconvenient I/O latency issues recently. I’ve been using the awesome Latency Heat Map Visualization by Luca Canali as one of the tools to investigate this.

I’m guessing this isn’t the type of I/O latency heat map most people would want to see from a production system. 🙂

OraLatencyMap

 

This is the same system that has been reporting Warning “aiowait timed out x times” in alert.log [ID 222989.1], which only appears if an asynchronous I/O takes longer than 10 minutes…

The pictures look much nicer when things are going wrong! 🙂

Cheers

Tim…

The Law of Logical Argument…

I received a mail today containing a list of laws (in the scientific sense), all of which were silly observations just for amusement, but this one stood out.

“The Law of Logical Argument – Anything is possible if you don’t know what you are talking about.”

Although not directly relevant, that got me thinking… I answer a lot of questions on forums and one of the things that strikes me time and again is people find it really difficult to define and communicate their problem. In some cases there’s a language barrier to contend with, but most of the time it comes down to a basic lack of thought by the poster. How many times have you spent days following a forum thread only to find the question you were answering was not the question the original poster “thought” they had asked? This is not just a feature of forum posts, but also in every day interactions. People will spend hours skating around issues, but never formally outline what the issue is. In many cases, the process of clearly defining the issue actually allows them to answer the question for themselves, or at least highlights a possible path of investigation.

Unless you can clearly define a problem, there is no way you can communicate it to anyone else. What’s more, unless you have an understanding of your problem, you can’t judge the value of answers, making you susceptible to suggested voodoo solutions.

Over the years I’ve had the good fortune of interacting with a lot of people I consider Uber Geeks, but what continually strikes me is the simplicity of their approach to solving problems. They may sometimes look like they are making leaps, but that’s just because experience allows them to do the steps quickly in their heads, rather than have to draft it out on paper, but most of the time they are following a fairly simple recipe to solve the problem, the first stage of which is actually defining the problem itself.

Is it surprising that database performance tuning is still considered by many to be some form of black magic? Not at all, because it is one of those areas that requires thought, reason and a meticulous approach.

Enough ranting for today. I’m off to run a database on a single 1TB disk and wonder why my I/O throughput is so bad. Must need a faster CPU. 🙂

Cheers

Tim…

PS. Someone on Twitter recently linked to an article called The Lost Tools of Learning. It’s quite long an a bit heavy at times, but some of the points it makes tie in with this post and I think it’s worth a read.

Schema Owners and Application Users…

I was trying to explain to a colleague the concept of using application users, rather than logging directly into the schema owner. Although it’s a very basic point, it seemed worthy of a write-up, especially because it’s been a long time since I’ve written anything about Oracle. So here is it:

Schema Owners and Application Users

Cheers

Tim…

My Utopian Development Environment

Some thoughts about my perfect development environment. Of course it will never happen 🙂

Compulsory PL/SQL APIs
Client application developers would have no direct access to tables. Not even for queries. All access would be provided by PL/SQL APIs. I like this because:

  • It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
  • It prevents people who don’t understand SQL writing stupid queries. All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries.
  • The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.
  • The API implementation can be altered and tuned without affecting the client application.
  • The same APIs are available to all applications that access the database. No duplication of effort.

J2EE abolished
I like Java, but I dislike J2EE. When I look at the way J2EE applications interact with the database it worries me. Perhaps I’ve been unlucky, or perhaps I’m missing the point, but I think it’s shocking. Using container managed persistence (CMP) to generate SQL on the fly worries me. I want to know at development time what is going on. I want to be able to cut and paste the SQL, not try and capture or trace it during a run. At least with bean managed persistence (BMP) you get this, but Java developers seem to avoid BMP like the plague.

Even when J2EE developers use BMP with calls to PL/SQL APIs, the containers have some strange ideas about what’s sensible. To update a record the container requests a query, update and requery of the data, rather than just a query and update. What’s this extra work for? I know what the state of the data is. I’ve just updated it. Even if someone does sneak an update in after me, the bean is being destroyed so the extra query was pointless.

I’m not saying that J2EE is pointless, I just think that 90% of the people out there don’t know, or care, how badly their applications interact with the database. Maybe some Java guru will tell me why I’m talking rubbish, but I can only judge this on my experience and that tells me that J2EE applications are not performant.

Miscellaneous
Just a few extra points to throw into the mix.

  • All client application developers would understand the rudiments of SQL, PL/SQL and DBA work. They don’t have to be experts, but they should understand the basic concepts.
  • All DBAs would understand the rudiments of client application develpment work. Once again, they don’t have to be experts.
  • All DBAs would be skilled at SQL and PL/SQL. I feel they should be the best in the company, but I know others would disagree. Oracle has now dropped the requirement for PL/SQL in the OCP DBA syllabus. In my opinion this is a mistake.
  • Managers would understand when a decision should be made by the techies.
  • Techies would understand when a decision should be made by the management.
  • People would share ideas and information, rather than trying to empire build.
  • Companies would encourage reskilling of employees.
  • Techies would want to keep there skills up to date, or make way for someone who does.
  • Applications would be planned, designed and built using more formal methods, not RAD or iterative development.

Perhaps the most important point is that I would be presented with lavish gifts whenever I entered the room.

Cheers

Tim…