Call-Less Cursor

Many people know that the Tkprof does not report base statistics and times for calls that have recursive calls. This can be handled by other alternative tools. There is one more unfortunate effect to watch out for when doing tuning of a DB that does fair amount of LOB operations. It seems to be more fundamental and much harder to cope with. Read the rest of this entry »

Tuning Collections in Queries [1]

[To Introduction

I will continue series about tuning collections in queries starting from the simplest case – collection of values joined to one table. Our subject SQL statements look similar to the following two forms. Read the rest of this entry »

ORA-01461: can bind a LONG value only for insert into a LONG column

Just one another issue with JDBC I think is worth mentioning. You perhaps wonder why so obvious error message could be troublesome to comprehend. Unfortunately, it speaks of things that a developer never meant to happen. It doesn’t have to be a LONG value. This error could appear when ASCII strings are bound for VARCHAR2 columns. In this case it happens when JDBC v. 10.1/2 is used with a Oracle Server 9i database configured for multi-byte character set. Before going any further, please note that according to support matrix Oracle JDBC 10g and 9i are cross supported with Oracle Server 9i and 10g. Read the rest of this entry »

Tuning Collections in Queries [Introduction]

[To Next Article [1]] [To Performance Study]

If you never heard of using collections instead of regular IN-LIST expressions, you might want to read varying elements in IN list article from AskTom. It unfortunately has little information about the way CBO treats collections. So, if you have problems with tuning queries that employ nested tables or just evaluating what is in the way please read on. Read the rest of this entry »

Blog theme

I know, I know… The theme looks exactly like the one Jonathan Lewis has for his Scratchpad. Sorry, about that. I’ve tried every possibility and this is the only one that is comfortable for posting Oracle trace or code snippets. The closest thing I found that wouldn’t wrap the code is a Sandbox theme. Others would have very small or otherwise irritating font.

“Undocumented?!” – Use it or not

Rainy day… Thoughts arn’t well aligned… At the end of the day I was wondering on the Internet searching for an inspiration. For a moment I thought I found something interesting. It was on the Jonathan Lewis’s blog – something about Oracle traces. But just a second later after finished reading I grasped what the message was about. In few words there was confusion, doubt and something else, or so it seemed. The title was Trace Files. And here you go an inspiration came, and as usual from a direction you’d never expect. Read the rest of this entry »

Statement Cache causes OutOfMemoryError (JDBC)

How efficient parse one – execute many in JDBC can get? So obvious recommendation to keep PreparedStatement open as long as possible is not easy. Testing custom built statement cache solution proved it to be not very well scalable. Increasing number of concurrent connections or the size of the cache of open statements lead to ineviable OutOfMemoryError exceptions. Read the rest of this entry »