The Tom Kyte Blog

Collaborate09 thoughts...

2009-05-28T08:40:00.001-04:00

I've been meaning to write up my thoughts from this years IOUG Collaborate event. Ian Abramson, the president of the IOUG, has a nice series of write ups on the event itself - starting with day 1 through day 3 and then a wrap-up post. He describes it as a three day event - but to me it is more of a four day thing. For the last couple of years, I've done a university day at Collaborate - so once again I spent all day Sunday May 3rd in a conference center talking about Oracle for eight hours. This year I talked about Encryption, all of the Flashback technologies, Read & Write Consistency (a really nice way to introduce the flashback stuff actually, sort of a pre-requisite) and Database Reorganizations (when to, when not to and when you have to - how to).

I also had a one hour "regular" session on Monday where I presented on "What's new in Oracle Application Development" where I touched on general database enhancements, ODP.Net, PHP/Ruby/Python, Java/JDBC, SQL Developer and APEX advances. This is the session where I got most of the questions/comments this year.

I think I might have scared away some of the DBA crowd with my session title, they see "Application Development" and stay away - but I know for sure at least one DBA was in the audience. He came up to me afterwards and commented on how glad he was to have attended - for a pretty simple reason. In each of the tools I discussed (mostly .NET, SQL Developer and APEX) - I pointed out how they easily tied the application into AWR (automatic workload repository) and ASH (active session history) facilities and how they could be used as an interface to the tuning/diagnostic packs. He is sure the developers he supports are unaware of this tie in and he himself certainly was - but his first course of action upon getting back to the office would be to expose them to it. The problem he has had in the past is that many people consider tuning "a DBA task" - when in fact - it is really a developer's thing to do. Since most developers don't have access to, or don't want access to Enterprise Manager - they assumed the advanced tuning features were not available to them. Not any more - all of the development tools now have tie in's to AWR/ASH and various features of the tuning/diagnostic packs.

One of the areas of large interest is the new (in beta) SQL Developer data modeling features. A lot of people (self included) still find the good old ERD (entity relationship diagram) a good way to envision/model your schema. SQL Developer now has an engine to do just that - with the ability to spit out various different physical models from the same logical model - either in support of different databases (yes, it does not just do Oracle), or in support of different environments (simple storage characteristic changes).

One thing I find interesting/good about an event like Collaborate is the ability to learn something new. Even after using Oracle for going on 22 years, I still learn something new often (almost every day). Regardless of your skill level, there is the opportunity to expand your knowledge of the stuff you use. For example - Jonathan Lewis (a pretty smart guy when it comes to Oracle knowledge) posted about some things he discovered for the first time. I strongly encourage everyone to attend at least one event if possible every year - to network, to learn and..... to present. IOUG Collaborate is a good venue for doing that - UKOUG is another - and the call for papers for the UKOUG event in November is currently happening.... Take a chance - go for it. It'll probably go much better than my first presentation :)

An interview question...

2009-05-13T21:16:00.001-04:00

A couple of days ago I read an article "One of the toughest job-interview questions ever". I was reminded of it by this posting...

I found the original post interesting - mostly because I liked the answer the technical writer gave. A bit of background - someone interviewing for a technical writing position is asked what is clearly a "hard core, heads down, write code programmer question". The question seemed entirely inappropriate for the position - but - the answer given was great (I thought)

The answer consisted of lots of questions - in effect - a lot of push back. Define this, specify that, clarify this - need more information.

I can relate.

What surprised me was that a lot of the feedback was negative. A lot of people said "would never hire you", "you missed the point".

All of the time I was reading though, I was nodding my head saying "yeah, what about that". I would have hired him on the spot. Critical thinking, push back, give me the details, tell me what you are really trying to do.

The programmer that rolls over and just answers the question - without enough information to actually answer the question - should send the interviewer running away. But that is apparently what a lot of interviewers are looking for.

I've been known to have three to four very simple interview questions for "Oracle people". They are designed to test the simple to the sublime. They are:

I have a table:

create table t ( ....., month number, ..... );

Month is always a number between 1 and 12.

I ask three questions about this table:

1) how many rows are in the table

2) how many rows BY MONTH are in the table (i want to know how many rows for month one, month two
and so on)

3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)

The fourth question is more of a "do something for me" - and that is "go to the white board, draw a picture of Oracle and tell me how it works".

As the link says - a surprising number of people *struggle* (seriously) with the first question. The second - gets *most* (seriously) of the rest. The third question freaks them out mostly. Especially the parenthetical part. The fourth question - sends people running out of the room.

That is why I liked the article I originally read - the author was poking around, developing derived requirements, fleshing it out, figuring out what really needed to be done, not rolling over and saying "you got it, I'll be right on it, we'll do that straight away". Developers (DBA's, whatever) that don't push back, that don't dig into the question, that don't try to convey "this is more complex than you think, we need to go a bit into this to figure out what you really need" - well, I don't have any patience for them. They do not belong (in our profession).

Will that person (the interview-e) annoy you? Sure, from time to time (I'm sure that every now and then - someone is annoyed by me, probably).

Will you ultimately be really happy they were there? Absolutely.

Will the person that rolls over annoy you? Absolutely - every time - most of the time probably. Especially after they really mess you up the first time they are so "flexible". Will you ultimately be even a little happy they were there? I doubt it.

I've said many times - there are only TWO answers to all technical questions. They are:

WHY (why do you want to do that)
IT DEPENDS (it really does, and it requires digging around, poking, probing to figure out what it depends on...)

poke, probe, ask, discuss, dive deep, play stupid (it works, really) - but get the information...

A unique opportunity...

2009-04-01T19:54:00.001-04:00

Learn SQL from one of the original SQL'ers. Chris Date is doing some a seminar in conjunction with Cary Millsap's company Method-R. Unfortunately I cannot make it that week (I'd be there if I could) - but it is a rather unique and uncommon offering. I've often said that if you want to be able to "tune" SQL, you need to understand what is happening underneath the covers, what is available. You don't want a 10 step checklist, you want knowledge. That is what this is all about.

This article by Cary sums up the seminar as well as why it will be good (in his humble opinion)... Give it a look see...

Happy Birthday...

2009-03-13T10:30:00.001-04:00

To both the World Wide Web, age 20 years this day and Linux v1.0, age 15 years this day. Two creations that have changed a lot of things. Sort of neat that they happened on the same day, albeit five years apart...

Very good advice...

2009-03-09T12:27:00.001-04:00

I've been reading Seth Godin's blog for many years. He is a 'marketing' person, with a lot of good old fashioned common sense. I agree with most of what he writes - and he just did a longish post (for Seth Godin it was long). It was on Slack.

His two points in these unique times - if you find yourself unexpectedly with more free time than you had anticipated you should consider:

a) Continuing your education, learn something new. As you go to interview and look around, people will ask you what you've been doing with your time. If you can arrive at an interview with "I've been learning X in my free time" and be really excited about it - be able to converse about it, that'll be a really positive thing.

b) Participate - join the forums - become known. I've said that myself many times in the past.

So think about that if you find you have more time on your hands than you anticipated having... Not bad ideas. Even if you don't have a sudden abundance of free time - maybe find the time to do these two things..

Missing. The. Point....

2009-02-09T20:26:00.001-05:00

I probably buy 80 to 90% of my non-grocery items online. Furniture, pictures, gifts, TV's, books, kitchen stuff - whatever I can - all online. I hate the "store" experience. Before I go into an actual physical store I usually know exactly what I want - buy it and leave. It took me about 5 minutes to buy shoes this weekend :)

I buy online for the convenience - and the experience is fairly similar regardless where you shop. You typically have to create "that account" (even if you never intend to shop there again..) and you get that form to opt in or out of mailing. They almost always default to "opt in" and I invariably set it to "opt out"

I just bought some shelves while sitting here in King of Prussia, PA (I live in VA, another benefit of shopping online, just do it when/where-ever you want)... I received two emails. Email 1 - my receipt (great). Email 2, well, it was in response to me opting out:

While registering as a shopper with xxxxxx.com, you chose not to receive our promotional Email. This is being sent to confirm that yyyy@yahoo.com will not receive Email from xxxxxx.com.
The decision to receive Email is personal and can be influenced for a variety of reasons. In an attempt to better understand and respond to our customers, we would appreciate it if you would answer a short survey on this topic.

That just strikes me as "missing the point" :)

Can you imagine what my survey comment field might have contained.... The survey did contain

We value your feedback and encourage you to give us candid answers. Are there any comments you would like to make to xxxx? (Note: Response is limited to 250 characters)

250 characters. I shall have to choose my words carefully... I should have it written in Kanji to see if they support multi-byte and truly support 250 characters. Or if it is really 250 bytes.

A SQL Joke

2009-02-05T21:59:00.001-05:00

I don't think I've seen one before. An actual "joke" about SQL

A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

It is about as funny as most of my jokes. All the more funny to me since I just (as in a couple of minutes ago) wrote:

Joins are what RDBMS's do for a living.

Anyway - you can hit this link to see if it gets any comments...

A couple of links and an advert...

2009-02-05T14:03:00.001-05:00

Instrumentation

Cary Millsap has recently pointed to a couple of postings and quoted some people on the fine art of instrumentation. He quoted me (and got it pretty much 100% - dead on) a couple of times as well. I'll add a quote/story to his list of quotes...

When Oracle 10g first was released - I was out and about talking about new features. One of the big ones was the ASH/AWR/ADDM set of functionality. One time, while presenting it an audience member raised their hand and innocently asked:

What is the overhead of this, what performance impact will this have on my system

I paused for a second, thought about it, and said:

Probably at least negative 10 percent or less (meaning more - like negative 1000%, more negative)

The audience now paused and we sort of looked at each other and then I explained. The addition of this instrumentation/repository will allow your database to perform better than it currently is - any 'overhead' of the additional instrumentation is more than offset by the gain in performance.

Instrumentation, using bind variables correctly, not swallowing exceptions (when others, not followed by RAISE), avoiding triggers (and side effects) and designing your system to perform in the first place - it is a really short list I have but everything on it has profound impact.

It's a small world

As it turns out - both Cary and I were in Utrecht, the Netherlands recently. He was there one week, I was there the next (just missed each other). We were both there to deliver seminars and he pointed out that someone who had been to both posted a critique. It was neat to see how the two sessions seemed to have complimented each other.

And since that is sort of a review of my seminar - that brings me to the

Advert

You can see my scheduled on http://asktom.oracle.com/ - there is a calendar there showing my public events. I would like to point out that I'll be doing two day seminars in:

Prague, Czech Republic - March 10th-11th
Athens, Greece - April 7th-8th
Rome, Italy - May 18th-19th

Links to information regarding pricing, location and so on are on the the site. Also, any and all user groups I'm attending for the next 2-3 months is posted there as well. Hope to see you at one (or more) of the events.

Ok, I feel old...

2009-01-16T23:54:00.001-05:00

My kids couldn't/can't do it to me (make me feel old).

Physical exertion doesn't do it to me.

Nothing really did that - until I read this. Not the blog entry - but the comments that said things like:

That being said, that thing is NEAT. I have no idea what it is though. Something to do with old punch card computers?

Nice piece of computer history, though I have no clue what it is. =P

I had an IBM flowcharting template once (more than once), and not as a hand me down. I had the little paper pouch it went into as well. Ouch.

If this Oracle gig doesn't work out...

2009-01-09T11:09:00.001-05:00

Maybe I can become a photographer :) One of my pictures from a recent trip to Sydney Australia was chosen for inclusion in a travel guide.

I don't think that picture is one of my "best", but it was of historic building they wanted to include in the guide. I wish they would have picked a better shot - I like this one myself:

And this one would have been pretty cool - if I had centered it just a little more to the left:

but it was nice to be chosen...

Happy New Year...

2009-01-08T08:51:00.001-05:00

Happy New Year to everyone...

Dr. Dobb's Journal...

2009-01-07T18:15:00.001-05:00

Wow, a bit of my past is going away... Funny enough - I referred to that magazine just today in a talk.

Without Dr. Dobb's Journal - there might not be an asktom.oracle.com site.

Why not? What is the link? Dr. Dobb's is just a programmers journal after all (it and the C Users Journal were two subscriptions that changed my life, I learned a lot from them both). Well, the link is that without Dr. Dobb's I might not have learned Oracle - or learned it much later than I did - or learned it differently, less completely.

I wrote about Dr. Dobb's in the foreword to a book once and I'll reproduce it here:

In 1987 I was just graduating from college and starting my career as a software developer. I started as a PL/I programmer on IBM mainframes using two databases – SQL/DS on VM/CMS and DB2 on MVS. I became familiar with SQL, but was limited as to what I could do on these production environments.

One day while reading a magazine, Dr. Dobbs Journal, I noticed an advertisement for a relational database that ran on DOS – simple PC’s. It was a product named “Oracle”. I clipped out the coupon – filled it in and ordered this relational database for $99. About 2 weeks later – a dozen or so 5-1/4” floppy disks showed up in my mailbox and I had Oracle version 5.1.5c and all of the development tools I needed to start playing, learning and exploring with. I was hooked.

That was then, this is now – and now, you have the ability to do in 10 minutes, for free what took me weeks and $99 ($166 in 2006 dollars!) in 1987 accomplish. With the introduction of Oracle Database Express Edition – you can download, develop, deploy and distribute your applications for free.

Dr. Dobb's Journal
C Users Journal
Borland's Turbo Pascal and Turbo C
Oracle version 5.1.5c

Things that changed everything for me...

Slowly disappearing - well, except for Oracle of course - and C. I haven't read Dr. Dobb's in a while, I still write a bit of C here and there, I definitely use Oracle everyday.

This should be fun to watch...

2009-01-07T11:06:00.001-05:00

On reddit - you can post a 'question' to the community. Someone just posted "What is the worse design decision that you have made ?" My favorite comment on that thread so far is this one:

It started with an thought not unlike the following:
"Nah, flat-file should be fine."
It ended in tears. And a double digit load average.

Been there, seen that happen. My worst design decision I personally made was when I "invented" (see below for why I say "invented") the "funky data model" - better known as an EAV (entity-attribute-value). You know, the extensible model where all you need is four tables:

objects
attributes
object_attributes (objects is 1:M with object_attributes)
links (links objects to objects, an association table)

Man, I could store *anything* in there. And it was very secure - because trying to retrieve anything was really hard. Not only really hard, but really slow.

But the prototype/demo was awesome. It never worked in real life though.

I used the term "invent" above. Of course I didn't "invent" the EAV - it has existed as a concept for a long long time. But, I see it get re-invented in relational database applications over and over and over again. And every developer thinks for a short period of time "hey, this is so cool - I wonder why no one else has thought of this - they must not have thought of it or everyone would be doing it - I'll be famous".

And then they learn why not everyone is doing it :)

All about joins...

2009-01-02T10:19:00.001-05:00

I've pointed to an excellent youtube database posting by Stephane Faroult in the past - about worst practices in the entry "The word pathetic never sounded so good..."

He has published another short video (six minutes) where he stuck me between Descartes and Kipling during the story telling. As he wrote me, I could have been in worse company!

As before, I enjoy his style of delivery. And to see my dance floor analogy animated was sort of amusing for me.

Happy New Year all!

Hey, they stole my line!

2008-12-11T19:44:00.001-05:00

I've been known to say from time to time "my car won't start, why not?" (followed many times by a "I've given you as much information as you gave me regarding your problem so now we are even")

Well, these guys stole my story!!! http://notalwaysright.com/diagnostics-through-osmosis/1379

Very funny blog most of the time - I can feel their pain.

Doing it wrong...

2008-12-11T19:06:00.001-05:00

I hate queries of the following form:

select count(*) from <anything else here>

The reason? The code typically looks something like this around the count(*):

select count(*) into l_cnt from .....;
if ( l_cnt > 0 )
then
    process_the_data;
end if;

I've always wondered why that code isn't just:

process_the_data;

Why bother counting - and then processing if that count was greater than zero. Why not just process_the_data - that routine already knows how to stop when it runs out of data - just let it run out of data naturally on row zero if there is no data.

Many people don't stop to consider that

The count can change between the select count(*) and the process_the_data call - there might be nothing by the time you get into the process_some_data

The count can change while you are running the process_some_data call itself - you cannot use the count as "this is how many times to iterate" (I've seen it done - it fails spectacularly when there are less rows than you counted, it fails silently when there are suddenly more and you never get to them, it also sometimes works by accident).

I've seen code like:

select count(*) into :cnt from t;
allocate array of :cnt elements
open C for select * from t;
for i in 1 .. :cnt
loop
   fetch c into array(i);
end loop;
close c;

You can just imagine the damage that could do in a language like C for example - interesting results when there are less than :cnt rows to get, segmentation fault - core dumped (we HOPE - we hope it crashes) if there are more than :cnt rows to get.

Anyway, this isn't a post about "don't count and then process" (well, ok, it is in part) - this is a post about an interesting snippet of code a friend sent me. They are on site doing some "tuning". I've modified the variables and such to disguise it - but the "logic" is intact:

FUNCTION count_em_up
( p_input1 in number, 
  p_input2 in varchar2 
)
return number
IS
   CURSOR C 
   IS
   SELECT actual_columns
     FROM some_table
    WHERE a_column = p_input1 
      AND another_column = p_input2;

   l_the_cnt    number default 0;
BEGIN
   FOR rec IN C 
   LOOP
      l_the_cnt := l_the_cnt+1;
   END LOOP;
   RETURN l_the_cnt;
EXCEPTION
   WHEN OTHERS THEN
        RETURN NULL;
END;

That hurts me in so many ways.

The dreaded "when others <no error raised here>"

A loop to COUNT THE ROWS RETRIEVED BY A QUERY!!!

Because I did not believe it: A loop to COUNT!!! (had to be said twice)

A function to count rows - probably used in higher level code like this "if count_em_up(x,y) > 0 then process_some_data; end if;"

Well, at least there is the very real probability of tuning this particular application - there is probably lots and lots of low hanging fruit out there like this!

It has been a while...

2008-12-09T20:34:00.001-05:00

It has been a while since I've posted - so lots of things to talk about coming up.

For a long time, I've had two monitors at home. I got them back in 2001 right after finishing the first book Expert One on One Oracle - it was my gift to myself for finishing... They cost a lot back then, it was a huge investment - but worth it. They are both still going today (you can see one of them down below on the left - the other is with a friend). Not only do they still work - but the entire dual monitor concept just changes the way you work (more screen, more stuff).

Two is no longer enough however - now I must have three. But to get three video outputs was hard in my desktop - and I wanted to be able to plug into my TV to show pictures whenever I wanted as well (easily, not by ripping out the VGA cables and such).

I had been looking at wireless USB connections - but they all seemed slow and low resolution. Till I hit this one:

IOGear GUW2015VKIT Wireless USB to VGA Kit

It supports up to 1600x1200 and everything lower. Pop it off the back of the monitor and put it on the TV and voila', I'm using the TV as a monitor. And - it is not slow, and it was really easy to install - no worries about "did I have a free PCI slot, or was it APG, did I need a half height card or a full size - etc". Seconds after plugging it in - I had three monitors going:

Linux in the middle :) That is a full screen VNC window to my server - for answering questions and testing on. I keep my email/status windows on the right. To the left is the 'windows' screen.

I'm really liking it. Until I can figure out how to get more screens of course...

It is amazing how the amount of screen real estate changes the way you work. I cannot do real work on my single screen laptop anymore - web browser, email - sure. But trying to write an article/paper and use the database and have the browser and email going - impossible.

Apparently, I need a bigger desk at home... For the fourth monitor.

Podcast from OOW 2008

2008-09-23T10:02:00.001-04:00

Stirring Up Controversy

...Tom Kyte sits down with Oracle Magazine editor Tom Haunert at Oracle OpenWorld 2008 and stirs things up in this conversation about Oracle OpenWorld happenings, a new approach to publishing, and the trouble with triggers. ...

A new book on APEX coming out...

2008-09-05T08:51:00.001-04:00

There is a new APEX book coming out soon - I wrote a foreword for it. There has been some discussion of it on asktom - and I thought I'd publish the foreword here:

I consider myself a pragmatic person – one that uses the right tools for a job, one that employs the most straightforward and easy way to accomplish a task. To that end, I’ve been a great supporter and fan of Oracle’s Application Express (APEX) from before the day it was introduced. I say “before the day” because I’ve had the honor and pleasure of using APEX long before it was released to the public at large – my website, http://asktom.oracle.com/, is one of the first websites ever built with the software that was to become known as APEX.

APEX is one of the most pragmatic database development tools I know of. It does one thing and one thing well – it rapidly implements fully functional database applications – applications that are used to predominantly access, display and modify information stored in the database (you know, the important applications out there). It facilitates using the database and it’s feature set to the fullest – allowing you to implement some rather complex applications, with as little work (code) as possible. It is possible to build extremely scalable applications with a huge user base (http://metalink.oracle.com/ for example is built with APEX). It is possible to build extremely functional applications, with seriously powerful user interfaces (APEX itself is written in APEX, proof of this). It is easy to build applications rapidly, the current version of http://asktom.oracle.com was developed in a matter of days by two developers – in their spare time, it was not a full time job.

While it all sounds wonderful and easy so far, APEX is a rather sophisticated tool with many bits of functionality and a large degree of control over how the generated application will look and feel. To fully utilize the power of APEX – one needs to have a guide, a mentor show them how to do so; very much akin to what I do with people regarding the Oracle database.

This book – Oracle Application Express – is that guide, the authors – Scott Spendolini and John Scott – are those mentors. The book walks you through the steps you need to understand after you’ve installed and started using APEX, to go beyond the sample applications. Covering diverse topics such as “Using the database features to full advantage” (one of my favorite topics) to “SQL Injection Attacks” – what they are and how to avoid them in APEX – to “Printing”; you’ll find many real world issues you will be faced with explained, demystified and solved in this book.

For example, Chapter 5 “Data Security” covers a wide breadth of topics regarding securing your database application. There is a section on URL injection issues – what they are, how they are exploited, why you care about them and finally how to protect yourself from them. There is a section on Session State Protection – following the same format –what it is, how it is exploited, why you care and finally how to protect yourself. The same mentoring occurs with data level access where the authors introduce how to use Virtual Private Database, a core database feature – not really an APEX feature, to protect your data from unauthorized access. Lastly, a critical application feature – Auditing – is discussed in depth using the “what it is, why it is, why you care and then how to do it” approach. Whilst some of the content in this chapter is not specific to APEX, it is needed to give you a holistic view to building database applications – which is what this book is about.

This book covers not just the nitty gritty details of building a secure application, it covers all you need to build database applications with APEX. When they are done with security, the authors move onto other necessary topics such as how to perform screen layout and application screen navigation, how to integrate reports and charts, how to integrate web services – enabling you to perform application integration – in an APEX environment, and much more.

If you are an APEX developer just starting out, or an APEX developer with experience under their belt but want to learn more about the environment you are using – this book is for you. It describes from start to finish how to build a secure, functional, scalable application using the APEX application development environment.

Thomas Kyte
http://asktom.oracle.com/

Interesting thought...

2008-08-08T09:16:00.001-04:00

I read this yesterday...

Got me thinking about Q&A sites...

I believe the author there has something - if you introduce a barrier to entry, it will have it so that only those that really want in - are in. I don't know that metafilter is better/worse/same as reddit and digg - I do know that I used to follow digg, then it became a "not as good place" for me. I follow reddit - but it is becoming very much the same. I've never used metafilter (on my todo list now) so cannot compare it. But the *idea* proposed, the concept - it does seem to have merit.

It would be neat to know if this has ever been studied before - does anyone know? Thoughts?

ps: I'm not thinking about doing this in asktom. I was asking in general for "social sites" like digg, reddit and the like. Sites that start off nicely with a small focused community but then turn into a bit mess when they become 'cool'.

Read this...

2008-07-10T21:50:00.001-04:00

I read a lot of "stuff". Yeah, I know, I'm writing less here - but that leaves more time to read over there...

I read lots of blogs, lots of forums. I lurk. I observe. I see what people are saying.

I liked what Cary Millsap just said:

I don’t mean “show and tell,” where someone claims he has improved performance at hundreds of customer sites by hundreds of percentage points [sic], so therefore he’s an expert. I mean show your work, which means documenting a relevant baseline measurement, conducting a controlled experiment, documenting a second relevant measurement, and then showing your results openly and transparently so that your reader can follow along and even reproduce your test if he wants to.

Check out the entire posting - and the book he is talking about. Also, check out his new venture. A pretty good read...

I don't know...

2008-06-10T15:44:00.001-04:00

I don't know if

a) I'm getting grumpier as I get older

b) people are losing the ability to phrase a question.

It just seems to me that day by day, the quality of the questions goes down. I don't remember people asking questions like this just 8 years ago. I've been participating in forums since 1994. "Back in the day", the questions were mostly thought out, with examples - with some background, with some thought. In the last couple of years - this seems to be changing - universally.

Oh well, it is probably related to both bullet points... Anyway here is the QOD - question of the day.

Subject: query is not using particular partition and index despite use full table scan

Entire question:

SELECT count(*)
FROM
DIM_BANK RIGHT OUTER JOIN FACT_JRNL_ACTG ON (DIM_BANK.BANK_ID=FACT_JRNL_ACTG.BANK_ID)
LEFT OUTER JOIN DIM_BSA ON (FACT_JRNL_ACTG.BSA_ID=DIM_BSA.BSA_ID)
LEFT OUTER JOIN DIM_FY ON (FACT_JRNL_ACTG.FY_ID=DIM_FY.FY_ID)
LEFT OUTER JOIN DIM_APD ON (FACT_JRNL_ACTG.APD_ID=DIM_APD.APD_ID)
LEFT OUTER JOIN DIM_PSCD ON (FACT_JRNL_ACTG.PSCD_ID = DIM_PSCD.PSCD_ID)
WHERE
(
(DIM_BANK.BANK_ACCT_CD IN ('33')
OR '*' IN ('33'))
AND DIM_PSCD.PSCD_CLOS_CL_CD IN ('1', '4', '5')
AND CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'
AND DIM_APD.PER != 0
AND DIM_APD.PER != 99
AND DIM_APD.FY < 2008
OR DIM_FY.FY = 2008
AND DIM_APD.PER <= 6
AND DIM_APD.PER != 0
AND CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'
AND DIM_PSCD.PSCD_CLOS_CL_CD IN ('1', '4', '5')
AND (DIM_BANK.BANK_ACCT_CD IN ('33')
OR '*' IN ('33'))
);

That's it folks. "My query isn't doing partition elimination (probably, I'm sort of GUESSING) and isn't using some index (on some column of some table)"

Before anyone says anything - this comes from the US.

Some wows from reading the query:

"(DIM_BANK.BANK_ACCT_CD IN ('33') OR '*' IN ('33')) - why would you do that?
"CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'" - why would you do THAT?
"CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'" - just in case you didn't believe us the first time
wow, more duplicated predicates...
outer joins to DIM_BSA, but we have that neat predicate using CASE - if we actually needed to outer join to DIM_BSA then all of the attributes would be NULL - the predicate would never be true - hence, we do not need or want to outer join to DIM_BSA
Same comment about DIM_FY, we outer join to DIM_FY, but if we make up a row - then DIM_FY's attributes will all be null and DIM_FY.FY = 2008 cannot be true (or false, it is unknown)
Ditto for DIM_APD
and of course DIM_PSCD
and just to make it 100% complete, the outer join to DIM_BANK - ditto. every single outer join in this query, should not be there.

I've responded with:

My car won't start. Now we are even, we have shared the same level of detail regarding our respective problems.

when others then null, redux

2008-06-05T19:43:00.001-04:00

It keeps happening.

Over and over it keeps happening.

Here is case 124,215,412,523 for your review.

The user writes me:

... I have a situation where I have started receiving the 'Cursor is Closed' SQLException from a stored procedure, say, SP_A, suddenly for last 5-6 weeks, since 2nd week of March. This happens only intermittently. If I take the same parameters and execute the sp again after a few minutes after getting the error, it does not generate the error and returns expected results back.

...

Additional details as requested:

what ora-xxxx error are you getting

Unfortunately, it does not generate an ORA-???? error. I know that many a times, the 'Cursor is Closed' error is a coding issue, but in this case, that is not the case. I cannot create this error at will, I have not been able to create the error in our development or user acceptance environment. Even in production, I cannot recreate it with same parameters that have generated the error.

...

The reason I think this could be oracle memory issue is that we very recently moved from 9i to 10g RAC. This application - java code and stored procedures - have been working for about 5 to 6 years now. Even the errors of Cursor is closed we receive are not consistent i.e. The parameters that generate the error now will work with exact same code a few minutes later.

...

Ok, here they are - they say "there is no ora-xxxx error, Oracle is just closing cursors on us - no fair. We know this cannot be a bug in our code, this is a bug in Oracle.

The story ALWAYS goes that way. I just finally gave up... closed the question. They followed up:

What was found that SP_A had exception clause of WHEN OTHERS that was masking the actual error.
When we removed that error, this is the error we see

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SP_A"
ORA-06508: PL/SQL: could not find program unit being called: "SP_A"
ORA-06512: at "SP_B", line 317
ORA-06512: at line 1

This is what has been happening intermittently ( we just did not know, because the way the sp was
coded and the java code interacted with returned parameters, it manifested into Cursor Closed Error
).

and now they want "insight" into why this is happening....

Remember everyone, everyone remember, keep in mind:

When others not followed by RAISE or RAISE_APPLICATION_ERROR is almost certainly, with 99.999999999% degree of accuracy, a bug in your developed code. Just say "no" to when others not followed by raise or raise_application_error!

http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html

Seems like a good idea...

2008-06-02T14:47:00.001-04:00

Check it out - and comment away... Tyler has a good idea/thought going on over there.

Also (total context switch here) this is the coolest new bit of SQL I've had the pleasure of writing for a while (11g and above only...)

ops$tkyte%ORA11GR1> with data
  2  as
  3  ( select when, thing, val
  4      from t2
  5    unpivot ( val for thing in
  6              ( LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN,
  7                BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
  8                DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS,
  9                MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE,
 10                PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS,
 11                PRE_ROWS, PRE_ROWS_LEN, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE )
 12            )
 13  )
 14  select THING, before, middle, coalesce, rebuild, rebuild-coalesce diff
 15    from data
 16   pivot( max(val) for when in ( 'before' as before, 'middle' as middle,
 17                                 'coales' as coalesce, 'rebuil' as rebuild )
 18        )
 19   order by thing
 20  /

THING                    BEFORE     MIDDLE   COALESCE    REBUILD       DIFF
-------------------- ---------- ---------- ---------- ---------- ----------
BLKS_GETS_PER_ACCESS          4          4          4          4          0
BR_BLKS                       6          6          6          4         -2
BR_BLK_LEN                 8028       8028       8028       8028          0
BR_ROWS                    2810       2810       1409       1405         -4
BR_ROWS_LEN               33639      33639      16856      16809        -47
BTREE_SPACE            22524924   22524924   11322528   11274488     -48040
DEL_LF_ROWS                   0     238415          0          0          0
DEL_LF_ROWS_LEN               0    9601917          0          0          0
DISTINCT_KEYS            499999     488415     250000     250000          0
LF_BLKS                    2811       2811       1410       1406         -4
LF_BLK_LEN                 7996       7996       7996       7996          0
LF_ROWS                  499999     488415     250000     250000          0
LF_ROWS_LEN            20121735   19665121   10063204   10063204          0
MOST_REPEATED_KEY             1          1          1          1          0
OPT_CMPR_COUNT                0          0          0          0          0
OPT_CMPR_PCTSAVE              0          0          0          0          0
PCT_USED                     90         88         90         90          0
PRE_ROWS                      0          0          0          0          0
PRE_ROWS_LEN                  0          0          0          0          0
ROWS_PER_KEY                  1          1          1          1          0
USED_SPACE             20155374   19698760   10080060   10080013        -47

21 rows selected.

I used pivot to turn my rows into columns and then unpivot to turn some of my columns into rows (I flipped this result side on its side!)

A bit of background, I wanted to compare the effects of:

freshly built index (before)

to an index that had lots of deletes done to it (middle)

to an index that was just coalesced after those deletes (coalesce)

to a rebuilt index (rebuild)

So, in order to capture the information I just did this:

ops$tkyte%ORA11GR1> create index t_idx on t(x,object_name);
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;

ops$tkyte%ORA11GR1> create table t2
  2  as
  3  select 'before' when, index_stats.* from index_stats;

delete data here....

ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
  2  select 'middle' when, index_stats.* from index_stats;

ops$tkyte%ORA11GR1> alter index t_idx coalesce;
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
  2  select 'coales' when, index_stats.* from index_stats;

ops$tkyte%ORA11GR1> alter index t_idx rebuild;
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
  2  select 'rebuil' when, index_stats.* from index_stats;

Now, index_stats has lots of columns... and my table T2 has very few rows - so, if I turn my rows into columns and columns into rows, I'd be able to better visualize this particular set of data on screen (in sqlplus of course...)

That is what that pivot/unpivot query did for me - I like it...

Looks better than this did :)

ops$tkyte%ORA11GR1> select * from t2;

WHEN       HEIGHT     BLOCKS NAME
------ ---------- ---------- ------------------------------
PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------------- ---------- ------------ -------------- ----------------
before          3       2944 T_IDX
                                   499999       2811    20121735       7996
      2810          6       33639       8028           0               0
       499999                 1    22524924   20155374         90            1
                   4          0            0              0                0

middle          3       2944 T_IDX
                                   488415       2811    19665121       7996
      2810          6       33639       8028      238415         9601917
       488415                 1    22524924   19698760         88            1
                   4          0            0              0                0

coales          3       2944 T_IDX
                                   250000       1410    10063204       7996
      1409          6       16856       8028           0               0
       250000                 1    11322528   10080060         90            1
                   4          0            0              0                0

rebuil          3       1536 T_IDX
                                   250000       1406    10063204       7996
      1405          4       16809       8028           0               0
       250000                 1    11274488   10080013         90            1
                   4          0            0              0                0

Hear, Hear

2008-05-23T09:43:00.001-04:00

Double, double...

Been a while since I've pointed to his blog - but this one is one that resonated with me.

I related to it for this reason: when people describe what problems they are facing in technology - they very often assume the person they are describing it to have the same background, nomenclature, experiences. What they forget is we don't work with them, we don't share the same jargon and most importantly - we haven't been staring at the problem like they have for the last 5 days - it is all new to us. And therefore, we need lots of detail.

For me, this manifests itself in a question like this: I have this/these table(s). I need a report that looks like this: .... This query is failing. Please correct it.

It won't matter how loud or long they say "Please correct it", it won't happen - we don't know what the question is yet!

Double, double - indeed.