Oracle

December 27, 2011

Poor design hurts!

Filed under: Uncategorized — srivenu @ 10:55 am

I was in given a task of analysing and triaging performance issues in a system which exhibits performance problems periodically. Every 3-4 months the system is scrubbed, cleaned and optimized. Performance starts degrading gradually from then as new code moves in quickly and regularly.
At the start of my analyis, the project lead calls me up and asks me what needs to be done to avoid the recurrence of the performance problem? I joked if he could stop new code movement. Ofcourse he could not. The business domain is very dynamic and it wouldn’t be feasible/practical to halt or slow down the code movement. We exist for the business and not the other way round! Coming to practical aspects, this particular project, like the many i saw, has an outdated and skinny dev & test environments and so no real testing is possible. The developers come from different vendors/backgrounds and their oracle skills are not great. (As TomKyte says in his first book, any project where the developer’s treat the database as a black box is doomed to failure). There is almost no code review. So I did not have any practical answer for his question.

A few minutes after i started looking at the systems i saw the following and felt really pity for the project lead!

select 	count(*) 
from 	X 
where 	X.SR_NO in (
	select	SR_NO 
	from	(
		select	to_char((to_number(:1) + rownum - 1)) SR_NO 
		from	dual
		connect	by level <= to_number(:2) - to_number(:3) + 1
		)
	) 
and 	X.A = :4 
and 	X.B = :5
and 	X.C = :6

This was one of the sql’s that i observed to bottleneck the system. There were several other SELECT’s and UPDATE’s on the table X which all seemed to have this clause in common

where 	X.SR_NO in (
	select	SR_NO 
	from	(
		select	to_char((to_number(:1) + rownum - 1)) SR_NO 
		from	dual
		connect	by level <= to_number(:2) - to_number(:3) + 1
		)
	) 

At first i did not understand why the developer did not use a BETWEEN clause

select 	count(*) 
from 	X 
where 	X.SR_NO between to_number(:1) and to_number(:2)
and 	X.A = :4 
and 	X.B = :5
and 	X.C = :6

I thought the developer tried to implement a newly learnt trick of serial number generation using DUAL. But then it struck me and i immediately checked the table definition of X.
Ofcourse! SR_NO is defined as VARCHAR2(30).
That’s the reason why he could not use BETWEEN.
Well, he could have used it but the index on SR_NO would not be used if it was written that way.

I asked him the reason for declaring SR_NO as VARCHAR2(30) instead of NUMBER. I was dumbfound when he replied that oracle NUMBER types are only storing 10 digits and anything greater than that are stored as exponential! I explained to him that Oracle NUMBER type infact stores a precision of upto 39 digits and if he wanted to see more digits in SQL*PLUS, he just has to set a higher NUMWIDTH.

So he has chosen the wrong data type for a critical column and wrote convulted sql based on that.
Well in support of the developer, he is a java developer, who has been working on this project for the past 2 years in a stressful situation trying to fix a pile of problems which never seem to reduce.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: