Compression to the rescue

We've had issues getting enough storage space allocated on the development server our project is intended to use. For this project, our team doesn't have full DBA rights on the box and have very limited privileges. Even before I joined the team, a request had been made for a little over 3.5 TB of space to be used for the development of a proof of concept datamart. The first project deliverable is due this week and we've only been allocated 200 GB so far. As you can imagine, it's difficult to get needed data loaded when there is less than 10% of the space we need available.

The data is supposed to span a 2 year period. A set of base transaction tables have to be extracted from various sources and loaded, then that data will be flattened out into a model that will represent the final design. With only 200 GB currently available, my initial loads for 5 (out of 50) tables took nearly all of what we had available. So, I had to do something to try and fit as much data as possible into the space we had available. Compression to the rescue!

I decided to compress the tables I had extracted using basic compression (just add COMPRESS to the table definition). But first, in order to try and get the most optimal compression possible, I collected statistics on the tables I wanted to compress and reviewed the num_distinct column statistic. Using this statistic, I sorted the data into my new compressed table using a column order from lowest to highest number of distinct column values.

When the new compressed tables were ready, I had achieved 86% compression! Using this method, I was able to load the majority of the key tables I needed to allow progress to continue for our initial project deliverable. I still haven't been able to get everything, but at least I got enough to allow our statisticians to produce some initial findings (the project is intended to create a predictive analytical model).

An additional 2 TB was allocated to us on Friday but of course it was on a different server so the initial data has to be exported and imported into the new location (sigh...). But the lack of available space gave me an opportunity to effectively use a feature I probably wouldn't have used if I had all the space I needed to start with.

Book Adverts

I'm very pleased to announce two Oracle related books I have co-authored are to be published shortly by Apress.


The first book is entitled Expert Oracle Practices: Oracle Database Administration from the Oak Table and it will be officially published in December. There are 9 chapters currently available for eBook download via the Apress Alpha program. The Alpha program allows you to purchase (at a substantial discount from the full publish price) an unedited, unfinished pre-release format. The full book isn't available yet, but when it becomes available, you will be able to download the full eBook at no additional cost. It's a good option if you want to get a jump on what's to come! I wrote the chapter on Managing SQL Performance. I'm really excited about the book and am very proud to be among the group of Oak Table folks who are co-authoring it together.

Not far behind the Oak Table book will be Beginning Oracle SQL. The book is a revision of the classic Mastering Oracle SQL and SQL*Plus by Lex de Haan originally published in 2004. Again, I was fortunate to be able to work with a fantastic group of co-authors to revise Lex's work to include many of Oracle's latest developments to the SQL query language. I knew Lex for only a very short time before his passing in 2006 but I knew him to be a brilliant and wonderful man and I'm very honored to be able to contribute to revising his original work.

These books represent my first time as a published author. I've written many whitepapers and articles for various Oracle user group periodicals and such in the past but never attempted the process of writing a book. After having this experience, I'm grateful that my first time was as a co-author. It's comforting to know there are other folks out there sweating the process with you!

The folks at Apress have been awesome and I hope that my first experience as an author won't be my last!

Things that make you go "hmmmmm"

I was reading a blog post entitled How Good is Good?. It was written 8 years ago by a graphic designer by the name of Stefan Sagmeister. The post was speaking about creating designs that were meaningful and make an impact on the world (or the part of the world each design is directed toward). While the post was interesting, it was one of the comments that caught my eye. Most of the commenters were inspired by the post but one commenter, in reference to the idea of making a match between good causes and good design a priority vs the more mundane every day marketing stuff, said:

Simple, don't go into graphic design, Choose something else. Cause if you don't do "that" work, other designers will. Do people really think that everybody enjoys their job?

It was the commenter's question that made me thoughtful: Do people really think that everybody enjoys their job?

I've always felt very fortunate to be able to do work that I really love doing. There may be times when I get frustrated with some of the particulars, but overall I know I'm doing "the thing" I'm supposed to be doing.

My choice of career path came to me in college. I started out as an accounting major but after my first computer-related course, I switched majors and knew that working in the information technology field was for me. So, I got my degree and have worked in the field ever since.

I honestly can't imagine working in a job that I didn't like. So, it was just a bit of an "ah-ha" moment to consider the idea that not everyone enjoys their job. What would that be like? What would it be like to wake up every morning and go do something that you don't enjoy for 8 or more hours? That would seem like torture to me.

But, I suppose it may be more likely that more people don't enjoy their jobs than do. Have you ever seen the TV show "Dirty Jobs"? Some of those jobs are (in my opinion) just horrid. I can't imagine having to do some of those tasks every day. But, there are a lot of jobs out there that I can't imagine doing that many people love.

However, I think that it may be more about "doing what you gotta do" many times vs actually choosing a job you want/enjoy/love. I just find it sad, and somewhat humbling, to think about all the people who do things they don't enjoy just to make enough money to maintain a life for themselves and their families. To think of spending such a large portion of my life doing something I found no enjoyment in really makes my heart ache.

Are you doing what you really enjoy?

OOW09 and my favorite 11gR2 feature

I decided to wait until the end of Oracle Open World 2009 before I posted anything. So many people have been tweeting and blogging and shooting video of this year's event, I figured I'd put in my two cents worth at the end.

Man...it's been a l-o-n-g week. I don't know if it's my increasing age or what, but being in a herd of this size for an entire week has left me feeling somewhat bruised (both physically and mentally). I started at 9am on Sunday morning by attending Tom Kyte's keynote for Oracle Develop and have made it through Thursday - which seems like a minor miracle somehow. But I have survived!

So much goes on here, it's hard to keep moving through it given the mass of people and multiple session locations (not to mention the after hours opportunities for fun and frolic). Add a bit of rain (some kind of monsoon as it seemed to me) and, at times, it was like being in an undersized kennel filled with wet dogs. :)

The sessions I attended, and there were many, were all quite excellent. I sat in on most all of the 11gR2 sessions and am really amazed at many of the new features and options 11gR2 offers. My favorite is edition-based redefinition (also referred to as "online application upgrades"). This feature isn't getting nearly as much press time as some of the other more sexy things (for instance, you couldn't move 10 feet without seeing or hearing about Exadata) but as far as I'm concerned, editioning is a hugely beneficial new feature.

By the way, it is a feature, not an option. Wow...for once it's something you don't have to pay extra for! I loved that when Tom Kyte did his "10 - no 11 - top things about 11gR2" session, that he gave editioning two spots on the list. So what is editioning?

1. You can create a new edition to contain any new code changes.
2. Data changes can be made (add new columns or new tables) and the new code can safely write to the new stuff without any of it being seen by the old edition.
3. Different projections of tables are exposed via editioning views so that each edition is allowed to see only its own columns.
4. If the old edition makes data changes those changes are propogated using cross-edition triggers into the new edition's columns (or vice-versa).

The bottom-line is that now with 11gR2, if you want to make changes to procedures, packages, views, synonyms, etc, you can do it using editioning and maintain both the old (original) version as well as the new. It also means a lot less headache. Have you ever tried to CREATE OR REPLACE PACKAGE in production while the instance is in use? What happens? Well, if the package is in use, Oracle has to wait until it is no longer in use in order to replace it. So, Oracle waits. But, if new requests to use the package come in during this time, those sessions must wait also. That doesn't even begin to touch what happens if the new package causes other objects to be invalidated and thus they need recompilation also. It can be a pretty long, arduous and resource-intensive process to get something seemingly so simple done.

With editioning, a new edition is created and the modified package is compiled into that edition. No muss, no fuss...and no waiting! This is just a bit about this great feature, but I recommend you look on OTN for more info and several whitepapers Oracle has available on the topic.

There's so much more I could blabber on about, but I'll save other thoughts for other posts. All in all, it's been a good week, my presentation on Tuesday was well attended and I got great feedback, and I've got lots of great material to take back home for further review and sharing with colleagues.

Memorization vs. Understanding

This graphic was recently pointed out to me (thanks Jared!) and I loved it...so I thought I'd share.

The key point (as noted in the upper right) is:

Understanding how something works can drastically reduce the need to memorize a bunch of seemingly arbitrary facts.

I believe this is an accurate statement. I've been around many people who seemed to be able to spout out the most arcane command syntax or provide the "textbook" definition of virtually any topic. But, if there was a problem where something more than knowing the definition of something or being able to quote the documentation on a topic was required, they quickly got lost and didn't know what to do or how to proceed.

Certification exams, for instance, ask questions that can be answered correctly if you've memorized a lot of facts. I've always jokingly said that if I gave my mother (sweet southern homemaker that she is) enough material to memorize, that she could likely pass the Oracle certification exams in 1 or 2 tries. And although she'd never indulge me to see if I could prove that theory, I suspect it's close to the truth. She's a bright woman with a great memory so I think she could memorize and do pretty well on the tests.

I once met a kid that had just graduated college and had started his first real job with a client I was doing some work for several years ago. The company paid for him to take a two week "boot camp" course and take the certification exams. So, with about 2 months of on-the-job experience, he went to the two week class, took all of the exams at the end of it, passed them all and returned back to work a "certified expert". Of course there was a big production flame-out right after he got back and he got razzed pretty good about "not knowing" what to do since he was now an "expert".

The bottom-line, as I see it, is that memorized knowledge can (and likely should) be part of your foundation. But, it will only get you so far. It's the ability to really understand something and how it works that will help you solve issues that just aren't solvable with memorized facts alone.

Cost and value

I just read a post by Seth Godin entitled "If Craigslist cost $1". I've used Craigslist and I agree with him that even a small charge for the service would likely clean it up a whole lot. At $1 per listing, most people with a legitimate need would still choose to use the service. But, for scammers and those with not so virtuous purposes, the small charge and thus the requirement for verifiable identification for the money exchange, would push them out of the game. Plus, the money coming in for using the service could go towards making the service better or making the owners richer or be used for philanthropic purposes or whatever.

The post made me think about cost and value. What if the cost to use the service was higher ($5, $10, $20)? At what point would the cost outweigh the value? Well, certainly the higher the cost to use the service is, the higher the cost of the items sold on the service will be, right? I mean, I don't think I'd want to pay $10 to use the service if I'm going to sell my item for $10. Maybe if I'm selling my item for $20, I'd be willing to pay half of that to get it sold.

My actual, real-life use of Craigslist has typically been for a few basic reasons:

1) I no longer use the item(s) but they are in good enough shape that I think someone else would/could use them.
2) Having someone come pick up the item and take it away is much easier than trying to figure out how to get it to my local Goodwill (stuff like furniture and larger items).
3) I bought something on a whim and either never used it or only used it once or twice before realizing that I really didn't want or need it.

The real bottom-line for me has typically been that I just wanted to get rid of something that I was no longer using but believed the item to be in good enough condition that it "shouldn't" be thrown away. (Waste not, want not!) So, in most every case, even if I only asked a few dollars, the point was that I was able to get the items out of my house and off to someone else who wanted them. I didn't have to do anything except put up an ad, answer a few calls and then exchange the item(s) for cash when a buyer appeared.

It wasn't about the money that I received for the item. Even if I hadn't sold the item, I would have gotten rid of it somehow, so the money was just a "nice to have" benefit. The value of the service was that it was easy for me and I believed it was a win-win for both me and the buyer. I'd have been willing to pay a fee to use the service.

But really, this isn't about Craigslist. It's about the cost (price you're willing to pay) of something versus the value having or using that something has to you. Another example is software. Some of my favorite software tools have cost me little to nothing to obtain. I use them constantly and have been very happy to pay for them (most everything I'm thinking of has cost less than $30 each). But there are other products that cost *a lot* ($1,000 or more) and while I really might like to have them, I'm not willing to pay that cost. I'm particularly not willing to pay when there is a comparable product that is available for free or at a very low cost. The first example that comes to mind is TOAD vs SQL Developer. [sidebar] I am not advocating either product nor am I comparing features or virtues of either. [/sidebar] Since SQL Developer is free, I'd very likely choose it instead of the costed product and use it until I found that it didn't meet my needs to such a degree that the cost of buying TOAD would be outweighed by the benefit of having it. Even then, I'd hesitate unless the feature I needed was so key/critical that I could easily identify how having that feature would save me money in the long haul.

But, if TOAD was priced much lower, say under $300 (instead of the base edition being nearly $900), I don't think I'd hesitate to buy it. This is where I wonder how companies decide to price their products. Is it truly a "what the market will bear" pricing strategy or what? I likely have a poor way of looking at it, but if I had a software product that I think everyone should use, then I'd be willing to sell it at a lower cost in hopes that I'd sell more and thus make up the difference. In other words, I'd rather sell 1000 copies at $300 each than 100 copies at $3000 each. I'd say there is an excellent reason why pricing isn't done this way and there's also a reason why I'm not in sales and marketing. ;)

How about you? How does cost and value fit into your personal buying decisions?

SMART goals

I was just reviewing the written goals for a project I've started working on and after looking at a few of them, I was reminded of how important it is to make sure your goals are "SMART".

For example, one of the project goals is: To create a standard framework that supports high levels of service. Hmmm... When I read that goal, I found myself thinking of the concept of SMART goals that I learned a long time ago. Somehow this goal doesn't seem to fit the SMART paradigm.

So, what is a SMART goal?

SMART is just a mnemonic that can help you remember how to effectively formulate a goal. Here's a brief description:

Specific - The What, Why, and How.
What are you going to do? What do you want to accomplish?
Why is it important (reasons, purpose, benefits)?
How will you accomplish the goal?

Measurable - Concrete criteria for measuring progress.
If you can measure properly, you can see change occur as you progress toward the goal. Ask questions like "how much?" or "how many?".

Attainable - Takes enough effort to be a bit of a stretch, but not so far out of reach that is nearly impossible to complete.

Relevant - "Do-able"...not "easy". The skills are available, the learning curve is not vertical and the goal is within bounds of the overall plan.

Time-Bound - A clear target date or time period to work towards. Without the time element, the commitment to begin action is often too vague.

I think the project goals I've been reviewing have most of the details that would cover the SMART elements elsewhere in the project docs, but I'm glad for the opportunity to review this method for stating goals.

The best flowchart ever

This was forwarded to me and I totally laughed out loud. I've seen a lot of flow charts (mostly of the boring technical sort), but this is my absolute all-time favorite! Zoom in on it or visit the originating site to see it better.

It's a flow chart of the song "Total Eclipse of the Heart".

Resumes, interviews and truth in advertising

OK...what's the deal with resumes that say one thing (so that a candidate looks nearly perfect) and then you interview them and find out they can barely spell Oracle? I'd think that if your resume says you've been working with Oracle since 1988 and have worked extensively with PL/SQL, you'd know what a REF CURSOR is or maybe even know a bit about collections or something, right? I asked one candidate these questions and they said they'd never ran into those 'features'. So finally, just for fun, I asked "On a scale of 1-10, with 10 being expert, where would you rank yourself in terms of your PL/SQL proficiency?" The answer: "Well, I suppose it's a bit conceited to give yourself a 10, so I'll just be humble and say 9."

Are you kidding me? Really?

Personally, I think my resume is lacking overall. I seem to have a hard time distilling over 20 years of experience into a couple of pages and making the "real me" show up on paper. But, if you get me into the ballpark (i.e. an interview), I'll hit most every pitch you throw at me. But, I'd be terrified to try to over-sell myself and get caught unable to deliver the goods. So, it's just a bit scary for me to look at resumes and think "Wow!" and then talk to the person for 3 minutes and think "Yuck!"

At what point did this become the norm and not the exception? Or, am I just in the midst of some weirdly skewed tilt of the interview universe? It almost reminds me of my reaction when reading an ad that claims "World's Best" or "Indescribably Perfect" or some other line and knowing it's just a ploy. I'm not a fan of those that skirt the edges of "truth in advertising". But when it comes to selling yourself, I'd really hope the claims you make could be backed up. Sigh...

Here's hoping that the rest of this week's interviews are with folks who match their advertising.

Brain Rules, multitasking and cubicles

One of my favorite books is Brain Rules by John Medina. The following passage is from the book's introduction:

Most of us have no idea how our brain works.

This has strange consequences. We try to talk on our cell phones and drive at the same time, even though it is literally impossible for our brains to multitask when it comes to paying attention. We have created high-stress office environments, even though a stressed brain is significantly less productive. Our schools are designed so that most real learning has to occur at home. This would be funny if it weren’t so harmful. Blame it on the fact that brain scientists rarely have a conversation with teachers and business professionals, education majors and accountants, superintendents and CEOs. Unless you have the Journal of Neuroscience sitting on your coffee table, you’re out of the loop.

This book is meant to get you into the loop.

I've had this book for a while and recently picked it back up to re-read it. Today, this bit of the intro seemed appropriate to discuss here. Brain researchers have proven that it is (as stated above) nearly impossible for our brains to multitask. However, given the way most of us attempt to operate every day, we don't believe or accept this is true.

I was thinking about how I am constantly doing what I believe to be multitasking. For example, I listen to an audiobook during my drive to and from work. Since the book is on my iPhone, I may be interrupted by a call that comes in, so I often talk on the phone while driving. When I get to work, I switch from my audiobook to a playlist of light jazz or something else with few words (I admit that I can't quite handle music with lyrics while trying to work as I always end up singing along and forget what I am doing). I work in a "cubicle village" with about a dozen of my colleagues where we each occupy about a 5x7 foot area with a half height divider wall in between. Every sound is clearly audible throughout the village so I constantly find myself vicariously sucked into other conversations and phone calls. [That's precisely the reason why I tell myself I wear earbuds so I can drown out some of the noise.] Then, at the end of the day, I switch back to my audiobook and drive home.

What I noticed this morning was that when I got to work, I couldn't really remember the drive to get there. I did remember the predicament my audiobook's main character was in when I stopped listening, but the drive was a blur. :)

I then started thinking about my work day. There have been several times when I'll be working on something and a song or a nearby conversation pulls my attention away from what I'm doing and I end up struggling to get back on track after a few seconds (or minutes) of distraction.

In Medina's book introduction he says that we've created high-stress environments and in so doing have made ourselves less productive. I think that's true. How many of you actually have a real office...you know, one with a door and 4 real walls? How many times a day are you distracted or completely interrupted from what you're working on because of your environment? I understand the economics behind cubicles vs offices, but I think it would be fascinating to know what the real cost is in terms of lost productivity.

I don't know that I'll give up my audiobook during my drive (and I may regret it one day if listening to it results in me being unable to avoid an accident), and I'm confident that I'll not rate an office with a door for a long while. But knowing that my brain can't multitask is apparently not going to stop me from trying to emulate the effect.

A rude morning wake-up

I've been just a bit muddle-headed the past few days. I've been fighting a nasty cold with nasty symptoms. I am not reluctant to medicate to alleviate the worst of my symptoms. My mom was a public health nurse and dosed my siblings and I well whenever we got sick. So, if my nose starts to close and my throat burn, I head straight to the pharmacy and stock up. My current round of over-the-counter goodies includes DayQuil, NyQuil, and Afrin nose spray (extra moisturizing, of course). On top of that, I've already been through about 10 boxes of Puffs (plus Lotion) tissue and think I'll go through another 10 before this is over.

When you wake up in the morning, after having pumped your system full of the aforementioned drugs, it's not so much that you wake up as it is that you move out of a state of total unconsciousness to one of mobile unconsciousness.

It was in that state that I entered the bathroom this morning to take my shower. I think I had one eye half open (the other still felt glued shut) as I stepped into the shower. I reached down, turned on the water and... holy crap! Cold water sprayed me and I jumped back about 3 feet. Both eyes open now, I tried to squeeze between the shower wall and the cold stream to reach down and turn it off. After a few brutal seconds, I finally got the water turned off and laid my head against the wall to try and recover. I got out of the shower, reached back in, turned the water on to let it heat up and stood there on the rug, cold and dripping while I waited. This is where you say "awwwww...poor baby".

I really don't think it matters that this happened while I'm fighting a cold. I'd have hated it and had the same reaction even if I'd been healthy and well. It just seemed worse this morning because I was so fuzzy-headed.

Now that I've bored you with all that, let me get to the thought I had, after the water heated and I climbed back into the shower, that prompted this insipid post...

Are there actually people out there who get in the shower first and THEN turn on the water?

Typos

I've had a problem recently with typographical errors. I've often been guilty of using "its" when I should have used "it's", typing "teh" instead of "the" or making other errors that are spelled wrong or are grammatically incorrect. But with word processing software being what it is these days, I usually am made aware of the error and can fix it before I send an email out and get totally embarrassed.

However, it is not always the case that the spelling or grammar checker catches my mistakes. Here are two recent whoppers that have served to make me a bit more conscientious.

Email 1

Ric: Do you know of any docs that talk to the use of Regular expressions in Oracle SQL other than the docs?

Me: Jonathan Gennick wrote a small O'Reilly book on Regular Expressions. I hate it and it does a good job.

...Click Send and then look at what I just typed and realize "Oops!"...

Me (again about 2 seconds later): That last response should have read "I haVe it and it does a good job", not "I haTe it". :)

Ric: Well that made me laugh this morning!! LOL!!

OK...that wasn't too bad, but still! What if I hadn't caught that and had sent it out to a large distribution?!?!? Yeesh...


Email 2

I had written a fairly lengthy response to explain something to a group of folks that had attended a meeting with me. After several paragraphs of explanation, I ended the email as follows:

I hope this helps clear things up. Let me know if you need any further information.

Retards,
Karen

A few minutes later I received this response:

Your detailed explanation was very helpful. However, I'd suggest you remember that the letters T and G are very close to each other on a keyboard.

It seriously took me a minute to get what he meant. But when I noticed the error, I was horrified! How embarrassing! I've now added an auto-signature option and will never end an email using the phrase "Regards" again. :)

Parallel Query question

I received a question in the comment section on an older post from Arun that I wanted to address directly. Here's his question:

I have one query regarding parallel execution. I have seen one of your post related to parallel execution of queries involving unions.

My question is: If you have query something like this

select appln_jrnl_id, sum(payment_count) 
from fihub.tbl_voucher_acct_line d
where appln_jrnl_id = 'AP_PAYMENT'
and fiscal_year_num = 2008
and acct_period_cd = 5
group by appln_jrnl_id
union
select 'AP_PAYMENT', sum(payment_count) from fihub.tbl_voucher_acct_line d
where appln_jrnl_id = 'AP_PAYMENT'
and fiscal_year_num = 2008
and acct_period_cd = 5;

How you will make this query to work in parallel mode? Do you prefer me to include parallel hint in that Query? If so, union queries may have different tables lists in the from clauses right?

Please throw some light on this!!

-Arun

Well, first let me point you to Doug Burns site for access to great paper he wrote on tuning parallel execution. There's so much excellent info in that paper that I'll defer the in-depth coverage of the topic to him rather than try to review it here.

However, I'll add a couple of things for you to think about.

First, why do you want to force the query to execute in parallel? If the optimizer isn't already choosing parallel operations for the execution plan, it must think it is unnecessary. Have you executed the query in both a serial and parallel manner and compared the results to prove which is better? From your question about using a hint to get parallel behavior, I'm thinking that you likely have not been able to get the query to run in parallel yet. So, if the optimizer doesn't choose to use parallel execution on it's own and you want to test the parallel version, the answer is yes, you can use a hint to attempt to force the optimizer towards parallel execution. At least as of v9.2 (and perhaps prior, but I'm not sure), Oracle supports parallel execution of UNION queries (and bunches of other stuff). So, as long as the proper instance parameters are set to allow parallel execution to occur, the optimizer should either choose a parallel plan (if it's "the best") or you can use the PARALLEL hint to force it.

Second, I'm having a hard time understanding your query. The way it reads to me is that both sides of the UNION are giving you the same answer. Therefore, the final answer to the query would be 1 row like: AP_PAYMENT 12345. The answer to each query will be the same. Since the query is using UNION, the duplicate will be removed and you'll end up with just the one row. So, you could remove the UNION and just execute

select 'AP_PAYMENT', sum(payment_count) 
from fihub.tbl_voucher_acct_line d
where appln_jrnl_id = 'AP_PAYMENT'
and fiscal_year_num = 2008
and acct_period_cd = 5; 

Unless you've got a boatload of data that matches your conditions and due to that the query runs for several seconds or minutes or more, it looks to me like the query should not need parallel execution. If you have a decent index in place, say on appln_jrnl_id, fiscal_year_num, acct_period_cd (and you could even throw in payment_count to keep you from having to do any table accesses at all), your query should execute quite well.

But, as I see it, if you want to test this particular query for how it would perform using parallel execution, rewrite it as I discussed above and then, if needed, add a PARALLEL hint. And do make sure to read through Doug's paper and also check out the links he provides at the end to other great resources on the topic.

Hope that helps!

10 seconds - use 'em or lose 'em (lose your audience, I mean)

I just read a post by Geoffrey James entitled "Presentation? You've got 10 seconds!" and this statement caught my attention:

"When you’re presenting, you’ve got 10 seconds (more or less) to capture the attention of the decision-makers. If you don’t… out come the Blackberries and the iPhones, and you can basically kiss the meeting goodbye. They won’t be listening."

10 seconds...really? I've heard before that you've got between 3-7 minutes to grab your audience, but has it really gotten so bad as to drop to 10 seconds? He says that the best way to grab your audience's attention in that short time span is to quote a statistic or something "credible and factual that surprises and (probably) frightens them a little bit."

I agree that you have to grab your audience from the outset. But, if we're now down to a 10 second time window, I fear we're all doomed. So, I'm not willing to buy in totally to that time estimate. What I'm willing to agree to is that you need to give your audience something worth listening to and deliver it in a way that enables them to hear the message clearly. It's about presentation skills as well as content. No matter how good your content is, it's just as much about the delivery.

I also agree that today's technology allows us to exhibit ADHD behavior in a multitude of ways. From my iPhone, I can check email, send text messages, surf the web, tweet, play games, read books, and so much more. If I'm sitting in your audience and you don't keep my attention, I can easily (and, for the most part, unobtrusively) find a way to entertain myself and ignore you. I don't have to get up and leave. I simply tune you out and give my attention to my friendly, and oh so fun, iPhone.

As a presenter, I hate looking out and seeing the tops of people's heads as they stare down at their mobile devices (or worse yet, their laptops). Even if 99% of the audience is staring at me in rapt attention, the other 1% disturb me beyond measure. I do understand that when you are away from the office, you aren't free from responsibility. You may still have to respond to problems if/when they arise. But, if you've booked time in your schedule to attend a presentation or conference, would it be fair to ask the folks back at the office to expect some delay in your responses? How about establishing a "check in" schedule? Maybe every 1-2 hours or whatever time frame is reasonable for your situation. Then, instead of feeling like you have to look at and respond to every call, text or email, you can just silence your little electronic friend and pick it up after the presentation is over. If you end up getting bored with the presentation and don't feel like it's worth your time, get up and leave. Hmmm...now wouldn't that be a novel approach?

In the end, I do think that a 10 second "grab 'em or lose 'em" time frame is too short. But, I think the time to capture and hold your audience's attention is short in proportion to the amount of time a person is willing to limit their access to every imaginable distraction that is available at their finger tips.

Maybe in the end, it's not just the presenter's job to capture the audience's attention. Maybe it's also the job of the audience to limit the distractions they will allow themselves and to give the speaker their full attention. Like the old song says "it takes two baby, it takes two".

11gR2 - LISTAGG

I was looking at the Oracle 11gR2 New Features guide and came across the new LISTAGG analytic function. Oh boy, oh boy! I've used Tom Kyte's stragg function for ages to do string concatenation of multiple row values. I'm way happy that that function is now "built in".

The syntax is:

LISTAGG (measure_expression [, 'delimiter_expression'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

Here are two examples from the docs:

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
    MIN(hire_date) "Earliest"
    FROM employees
    WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02



SELECT department_id "Dept.",
  LISTAGG(last_name, '; ')
   WITHIN GROUP (ORDER BY hire_date) "Employees"
   FROM employees
   GROUP BY department_id;

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
    60 Austin; Pataballa; Lorentz; Ernst; Hunold
    70 Baer
... 

After I read it in the NF guide, I Googled and found lots more folks talking about it.

Cool stuff! No telling what else I'll find...better keep reading!

The Power of the Pause

I was just reading an article in O Magazine entitled "The O Power List" and I read something that I thought was excellent. In this article, 20 women from many different disciplines are recognized for their contributions in everything from business to the arts. One of these women is 73 year old Pema Chödrön, a former elementary school teacher who was the first American woman to be ordained in the Tibetan tradition. The portion of the article about her is entitled "The Power of the Pause".

You can read the full text if you follow the link to the article, but this portion really touched something in me:

"In the next moment, in the next hour, we could choose to stop, to slow down, to be still for a few seconds. We could experiment with interrupting the usual chain reaction, and not spin off in the usual way. We don't need to blame someone else, and we don't need to blame ourselves.

Pausing is very helpful in this process. It creates a momentary contrast between being completely self-absorbed and being awake and present. You just stop for a few seconds, breathe deeply, and move on."

That truly is a powerful touchstone for me. It's so easy to react and lash out in the old, usual ways when I feel wronged or misunderstood or under-appreciated or whatever. I think it's true that it's easy to seek to place blame somewhere, whether it's on another individual or on myself.

But, what would happen if we all just paused? What if we did short circuit our ingrained behavior to react? I think to do so would mean a truly powerful transformation would occur in the relationships we have (both personally and professionally).

I will strive to integrate the power of the pause in my daily life. A few deep breaths each day are bound to be good for me (and for those around me too)!

A long road to hoe...

That's what it's been...a long road. While mid-April isn't really that far in the past as far as calendar time, it seems like an eternity. I've once again been remiss in my blogging...oh well. Perhaps if I give you the "short version" of my long road, you'll forgive me for my neglect.

April 22 - I was officially laid off from my position at Method R. It sucked, but it was a necessity for the company. I get it, but it sucked.

Panic stricken and thinking I would soon be bankrupt and on the streets, I began to look for new work. The good folks at my old employer, Hotsos, were able to provide me with 5 weeks of sub-contract work that got me through till almost the end of June. Whew!

I applied for a gazillion jobs between April and July but only one opportunity really looked promising. Around the first of July, I got a formal offer from Agilex Technologies based in Chantilly, Virginia. I accepted. But, the offer was contingent on me passing a government security clearance and also on the client I would be working for approving the funding. So, I had to decide if I should take the risk that everything would work out and go ahead and move (all the way from Oregon to Virginia) in anticipation of everything falling into place, or if I should just stay put and wait and see.

Well, I'm not very good at wait and see. And, since I don't have a checkered past (polka-dot maybe, but not checkered) and figured I'd pass the clearance, I decided to assume the risk and move. Now moving 3000 miles across country is not fun. The last week of July, everything got packed up and big burly boys were hired to load our U-Haul. My brother flew out from TN and he and I drove across country (in under 5 days!) with a 27 foot truck and my family mini-van. At the end of that road were more burly boys waiting to unload the U-Haul. It was a whirlwind and quite tiring, but it's over and Virginia is now home sweet home.

One of the 2 best pieces of news I needed to have came while I was somewhere in Nebraska. My job offer was finalized (as funding and limited security clearance was approved) and a start date of August 24 was set. The other piece of good news happened 4 days after arriving in Virginia. That news was that my house in Oregon had sold!

R-E-L-I-E-F!

So, here I am just a few short weeks later and I've been on the job at Agilex for 3 whole days. So far I'm just getting my feet wet and doing a lot of reading about the project I'll be working on. This new job means that my travel days are over. I will get to do what I love (Oracle performance optimization, particularly on the application side) and pile on a broader range of other stuff as well. But, I will be home every night and will get up every morning and go to work at the same place. After all these years of being on the road, that sounds pretty darn great to me! My 2-year old daughter confirmed that the job decision was the right thing when I walked in the door after my first day. She said "You came back Momma! You came back! I love you Momma!" and "Did you have a good day at your new office?" 'Nuff said!

I'm looking forward to new challenges and a new day-to-day routine. I'm also looking forward to having many new adventures to blog about. As soon as I catch my breath and settle in, I'll be back in the saddle. Until then, thanks for hanging with me and wish me luck!

Quoted

Iggy Fernandez, editor of the NoCOUG Journal, blogs at http://iggyfernandez.wordpress.com and has republished an interview with me from several months ago that was originally published in the Journal. If you're interested, you can read the full interview or a small excerpt of the interview with a question on out-sourcing.

See you at Oracle OpenWorld

I'm looking forward to attending Oracle OpenWorld in October. Here's my session info:

ID#: S307360
Title: Managing Statistics for Optimal Query Performance
Track: Database
Date: 13-OCT-09
Time: 13:00 - 14:00
Venue: Moscone South
Room: Room 236

I hope to see you there!

What you say isn't "exactly" what you mean

This is real. This cake was picked up after a call in order was made to a bakery to order a cake for a going away party (changing jobs). But, what the heck is "Under Neat That"?

OK, so this is how I imagine this conversation went:

Bakery Employee: 'Hello, how can I help you?'

Customer: ' I would like to order a cake for a going away party this week.'

Bakery Employee: 'What do you want on the cake?'

Customer: 'Best Wishes Suzanne' and underneath that 'We will miss you'.

You just have to laugh!

(By the way...a friend sent this to me so it may or may not be "real", but the thought is funny anyway!)

You don't really need that index (or that SQL)

I was given this simple looking piece of code to review.

select audit_rec_updt_dts, mod(rownm,4) as rownm, pol_seq_nbr 
from
 (select  a.AUDIT_REC_UPDT_DTS, a.POL_SEQ_NBR, rownum as rownm  
 from TR_POLICY_REGISTER a, TB_TRANSACTION b
 where a.POL_STAT_CD = 'REPORTED' and a.JACKET_SOURCE_CD = 'LAT'and
  a.AUDIT_REC_UPDT_DTS > b.EXTRACT_START_DTS and
  a.AUDIT_REC_UPDT_DTS < b.EXTRACT_END_DTS  and
  b.TRANS_SYS_ID in (select max(trans_sys_id) from tb_transaction)) dt
where mod(rownm,4) = 0;

I was also given 3 other almost identical statements except that the where clause changed the 0 to 1, 2, and 3. Danger Will Robinson! (Yes, that reference dates me. How about you? Remember "Lost in Space"?) It looks like somebody is thinking the result set is so big they need to break it up into chunks. More on this later...

Anyway, in order to get the query to complete in a reasonable time (my test without the index didn't come back within 30 minutes so I killed it), a "temporary" index was being created just before this statement was executed. By "temporary", I mean that they created the index just prior to running the SQL and then dropped it immediately after the SQL completed. Creating the index (parallel nologging) took several minutes to complete.

Here's execution plan for the query with the index in place:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                           |                           |      1 |    995 |   2627 |00:00:00.11 |    9815 |
|   2 |   COUNT                         |                           |      1 |        |  10508 |00:00:00.08 |    9815 |
|   3 |    NESTED LOOPS                 |                           |      1 |    995 |  10508 |00:00:00.07 |    9815 |
|   4 |     TABLE ACCESS BY INDEX ROWID | TB_TRANSACTION            |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX UNIQUE SCAN          | TB_TRANSACTION_PK         |      1 |      1 |      1 |00:00:00.01 |       2 |
|   6 |       SORT AGGREGATE            |                           |      1 |      1 |      1 |00:00:00.01 |       1 |
|   7 |        INDEX FULL SCAN (MIN/MAX)| TB_TRANSACTION_PK         |      1 |    289 |      1 |00:00:00.01 |       1 |
|   8 |     TABLE ACCESS BY INDEX ROWID | TR_POLICY_REGISTER        |      1 |    995 |  10508 |00:00:00.07 |    9812 |
|*  9 |      INDEX RANGE SCAN           | TR_POLICY_REGISTER_DMIDX1 |      1 |      2 |  10508 |00:00:00.01 |      73 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MOD("ROWNM",4)=0)
   5 - access("B"."TRANS_SYS_ID"=)
   9 - access("A"."JACKET_SOURCE_CD"='LAT' AND "A"."POL_STAT_CD"='REPORTED' AND
              "A"."SYS_NC00029$">SYS_OP_DESCEND("B"."EXTRACT_END_DTS") AND
              "A"."SYS_NC00029$"       filter((SYS_OP_UNDESCEND("A"."SYS_NC00029$")>"B"."EXTRACT_START_DTS" AND
              SYS_OP_UNDESCEND("A"."SYS_NC00029$")<"B"."EXTRACT_END_DTS"))

Looks good, right? The query executed in less than 1 second (.11 secs) and returned 2627 rows. Remember the predicate? There were 4 queries. Each one retrieved 1/4 of the data (2627 x 4 = 10508). That's a pretty small result set. Why run the query 4 times when once will do? And, it's not doing what the developer thought. I think the developer thought it would only access 1/4 of the rows each time and that somehow that would be faster than accessing all of them at once. Nope. Even though running this query 4 times in a row is still a fairly insignificant amount of resources used, it's not necessary. Each execution is getting all the rows and then throwing out 75% of them. Why not just run it once?

The next question is why is an index created just before and dropped just after this? Does having it be permanent cause a problem somewhere? Plus, there's already a persistent index that would/should work although it does not include all the columns used in the temp index. But, obviously something went wrong with the query that it didn't use that index properly or they wouldn't have created the temp index and attempted to break up the query into 4 parts, right?

OK. So, how about just rewriting the query to see if the current index can be used efficiently enough to meet their needs? I tried this:

select  a.AUDIT_REC_UPDT_DTS, a.POL_SEQ_NBR
 from TR_POLICY_REGISTER a
where a.POL_STAT_CD = 'REPORTED'
  and a.JACKET_SOURCE_CD = 'LAT'
  and a.AUDIT_REC_UPDT_DTS > (select EXTRACT_START_DTS from tb_transaction where  trans_sys_id = (select max(trans_sys_id) from tb_transaction))
  and a.AUDIT_REC_UPDT_DTS < (select EXTRACT_END_DTS from tb_transaction where  trans_sys_id = (select max(trans_sys_id) from tb_transaction))

And here's the execution plan:

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID   | TR_POLICY_REGISTER    |      1 |    995 |  10508 |00:00:00.07 |   14259 |
|*  2 |   INDEX RANGE SCAN             | TR_POLICY_REGISTER_N6 |      1 |    269K|  23576 |00:00:00.01 |     138 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TB_TRANSACTION        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX UNIQUE SCAN          | TB_TRANSACTION_PK     |      1 |      1 |      1 |00:00:00.01 |       2 |
|   5 |      SORT AGGREGATE            |                       |      1 |      1 |      1 |00:00:00.01 |       1 |
|   6 |       INDEX FULL SCAN (MIN/MAX)| TB_TRANSACTION_PK     |      1 |    289 |      1 |00:00:00.01 |       1 |
|   7 |    TABLE ACCESS BY INDEX ROWID | TB_TRANSACTION        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  8 |     INDEX UNIQUE SCAN          | TB_TRANSACTION_PK     |      1 |      1 |      1 |00:00:00.01 |       2 |
|   9 |      SORT AGGREGATE            |                       |      1 |      1 |      1 |00:00:00.01 |       1 |
|  10 |       INDEX FULL SCAN (MIN/MAX)| TB_TRANSACTION_PK     |      1 |    289 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("A"."JACKET_SOURCE_CD"='LAT' AND "A"."POL_STAT_CD"='REPORTED'))
   2 - access("A"."AUDIT_REC_UPDT_DTS"> AND "A"."AUDIT_REC_UPDT_DTS"<)
   4 - access("TRANS_SYS_ID"=)
   8 - access("TRANS_SYS_ID"=)

I can live with that.

It took me a lot longer to type this than to rewrite the SQL and test it! Now, the query doesn't need to be "chunked up" nor does the index need to be temporarily created. Maybe there's another way to do it but this was my first thought and it worked as desired, so all is well!

Good deal...

Photo of my favorite kid...mine!

She fell and busted her upper lip yesterday but seems to be none to worse for the wear.  Nothing like a ride in the car while watching a DVD and eating Teddy Grahams!  

Posted via email from Karen Morton's posterous

The Right Stuff

Now this is seriously cool. I was sent a link to an article called "They Write the Right Stuff." It's about the software that powers the US space program and the team of developers that write it. Here's a paragraph from the article:

But how much work the software does is not what makes it remarkable. What makes it remarkable is how well the software works. This software never crashes. It never needs to be re-booted. This software is bug-free. It is perfect, as perfect as human beings have achieved. Consider these stats : the last three versions of the program -- each 420,000 lines long-had just one error each. The last 11 versions of this software had a total of 17 errors. Commercial programs of equivalent complexity would have 5,000 errors.

The article goes on to discuss what the team does to ensure the software is this good and why it's so absolutely critical that it is flawless. The answer to "why" is fairly obvious. Think of a multi-ton spacecraft lifting off and shooting into space. Think of a spacecraft moving at 17,500 miles per hour and needing to adjust course precisely to hit a re-entry point. Think of the crew sitting inside. Wow! It gives you a whole new perspective on what it could mean if you have bugs in your code.

I agree with the article that software development seems to be in the "cave man" phase of evolution. I see a lot (and I mean a lot) of buggy code and bad SQL. And the prevailing attitude is usually "log a defect...we'll prioritize it and get it fixed when we can". What if the code that controls the space shuttle was treated that way?

I get it that having bugs in the software that runs our businesses doesn't mean life or death. I also understand that often the speed with which new features in software are delivered is more critical than delivering that feature "bug free" or, for that matter, delivering it knowing it will perform well! But knowing that the ability to create code of this quality is possible makes me wish (even more so than I already do) that the software development process was approached with greater care by everyone. Not just by the developers of code that must be bug free or people could die.

What if the code you wrote would mean someone died if it had a bug? What would you do differently? While I know it may be a poor analogy, this makes me think of the "2 minute offense" in football. The team has been playing poorly until the last 2 minutes of the game. Then, they go into this hurry up offense and really push themselves to score. My question has always been why don't they just play all out the rest of the game?

It seems to me to be similar to how software development is approached. The job gets done, often not very well, but everybody goes into 2 minute mode when the code goes into production and people start screaming about bugs and performance. If the screams are loud enough, things get fixed. It just seems that it could be done so much better to start with.

I understand the arguments for why it doesn't happen "the right way" first. But after reading this article, it makes me marvel at how cool it would be to be part of a team that looks at their code as being something that MUST NOT have bugs...ever. And, they create the processes that support achieving perfect code. I'd think those folks really like what they do and take great pride in what they develop knowing that their diligence and good work keep people alive and keep our space program running.

Cool. Very, very cool.

Once again: why guess when you can know?

I recently was asked to review a bit of SQL that was performing poorly. By poorly I mean that a single execution of the query may take hours to complete (that's pretty poor). The SQL was quite simple:

SELECT ph.cust_bus_nm
     , ph.clup_fmt1
     , ph.batch_period_month_yr_dt
     , ph.batch_sys_id
     , ph.our_prem_amt
     , ph.liab_amt
     , ph.code
     , ph.pol_effect_dt
     , ph.pol_nbr
     , ph.agent_prem_amt
     , ph.uw_sys_id
     , ph.cost_cntr_sys_id
     , ph.state_cd
     , ph.loc_rep_sys_id
     , ph.cust_sys_id
     , ph.clup_sys_id
     , ph.item_type
     , NVL(ph.pol_type_cd, p.pol_type_cd) AS pol_type_cd
     , ph.acct_period_month_yr_dt
     , ph.hist_rev_ind
     , ph.validation_dts
  FROM tr_pol_h_endr_h_v ph 
             INNER JOIN tr_policy p ON ph.pol_sys_id = p.pol_sys_id
 WHERE ph.liab_amt >= 2500000
   AND TRUNC(ph.acct_period_month_yr_dt) BETWEEN TO_DATE ('200902', 'YYYYMM') AND TO_DATE ('200902', 'YYYYMM')                                    
   AND DECODE (0
             , 0, ph.uw_sys_id
             , 0) = ph.uw_sys_id
   AND DECODE (0
             , 0, ph.cost_cntr_sys_id
             , 0) = ph.cost_cntr_sys_id
   AND DECODE ('ALL'
             , 'ALL', ph.state_cd
             , 'ALL') = ph.state_cd
   AND DECODE (0
             , 0, NVL (ph.loc_rep_sys_id, 0)
             , 0) = NVL (ph.loc_rep_sys_id, 0)
   AND DECODE (0
             , 0, ph.cust_sys_id
             , 0) = ph.cust_sys_id
   AND DECODE (0
             , 0, ph.clup_sys_id
             , 0) = ph.clup_sys_id
/

Note that the DECODEs look a bit odd but are there to accommodate how Crystal Reports has to handle (or doesn't handle well) variables. That wasn't really part of the problem.

The query used a view (tr_pol_h_endr_h_v) so I started by reviewing the view source. In the view source I found a few things that were suspect.
1) The view source included a join between tr_policy_history (alias pol_hist) and tr_policy (note that these tables are both 70 million+ rows each and are growing in size). This is the table being joined to the view in the offending SQL. As a matter of fact, the view source join was identical to the join in the SQL (INNER JOIN tr_policy p ON (pol_hist.pol_sys_id = p.pol_sys_id).
2) The view source defined the acct_period_month_yr_dt column as TRUNC(pol_hist.acct_period_month_yr_dt). There was a function-based index on TRUNC(pol_hist.acct_period_month_yr_dt).
3) The execution plan indicated a full scan using a filter on acct_period_month_yr_dt was occurring on the pol_hist table even though the FBI was in place.

Here's where I question whether this was an oversight or just a lack of understanding.

First, since the view source accesses the tr_policy table, why not perform the NVL check on pol_hist.pol_type_cd in the view source? Why join back to tr_policy again in the SQL that uses the view to make the NVL check?

Next, the view source uses TRUNC(pol_hist.acct_period_month_yr_dt) to create the column acct_period_month_yr_dt. But, in the SQL statement, the coder used TRUNC again. Why? By using it again, the effect is like writing TRUNC(TRUNC(acct_period_month_yr_dt). Since the function-based index is based on the expression using a single TRUNC() function, the index is ignored for use since the expressions don't match.

So, what to do?

Change the view source to execute NVL(pol_hist.pol_type_cd, p.pol_type_cd) AS pol_type_cd. Then, change the SQL as follows:

SELECT ph.cust_bus_nm
     , ph.clup_fmt1
     , ph.batch_period_month_yr_dt
     , ph.batch_sys_id
     , ph.our_prem_amt
     , ph.liab_amt
     , ph.code
     , ph.pol_effect_dt
     , ph.pol_nbr
     , ph.agent_prem_amt
     , ph.uw_sys_id
     , ph.cost_cntr_sys_id
     , ph.state_cd
     , ph.loc_rep_sys_id
     , ph.cust_sys_id
     , ph.clup_sys_id
     , ph.item_type
     , ph.pol_type_cd
     , ph.acct_period_month_yr_dt
     , ph.hist_rev_ind
     , ph.validation_dts
  FROM tr_pol_h_endr_h_v ph 
 WHERE ph.liab_amt >= 2500000
   AND ph.acct_period_month_yr_dt BETWEEN TO_DATE ('200902', 'YYYYMM') AND TO_DATE ('200902', 'YYYYMM')                                    
   AND DECODE (0
             , 0, ph.uw_sys_id
             , 0) = ph.uw_sys_id
   AND DECODE (0
             , 0, ph.cost_cntr_sys_id
             , 0) = ph.cost_cntr_sys_id
   AND DECODE ('ALL'
             , 'ALL', ph.state_cd
             , 'ALL') = ph.state_cd
   AND DECODE (0
             , 0, NVL (ph.loc_rep_sys_id, 0)
             , 0) = NVL (ph.loc_rep_sys_id, 0)
   AND DECODE (0
             , 0, ph.cust_sys_id
             , 0) = ph.cust_sys_id
   AND DECODE (0
             , 0, ph.clup_sys_id
             , 0) = ph.clup_sys_id
/

I also added the liab_amt column to the FBI so that both conditions could be satisfied/matched in the index to eliminate a TABLE ACCESS to filter on liab_amt.

The result was execution times that fell into the 4-6 second range. From hours to seconds. Not bad.

But, I really wonder how something so simple was overlooked for so long. Apparently, this query had been on the top of the "bad SQL" list for ages and multiple attempts had been made to optimize it. But, within just a few minutes, I noted the issues and was able to recommend the fix. I don't think the fix was that hard but it seemed to be a stumper for some time. Why?

I think it may have been both a bit of oversight and a bit of lack of understanding. The oversight was in accessing a table (tr_policy) twice. Mantra => Never do anything more than absolutely necessary.

It may have also been oversight that the FBI wasn't getting used. But for that one, I'm going to point the finger at lack of understanding. The expression in the index definition must match the expression in the SQL. I don't think the coder realized that when the view source used TRUNC, that was all that was needed. And, I think that the reason nobody caught it was that nobody was looking at an actual execution plan for the query. They may have either only been looking at an explain plan (which may have shown an index use???) or they didn't look at a plan at all and just assumed the index was in use (this is what I think is most likely).

So, the moral of the story once again is: why guess when you can know? I haven't included the tests I conducted here for the sake of brevity (well OK... this post is not short, but it could've been a lot longer!), but with the test data and actual execution plan information in front of me, this one seemed quite easy. Guessing at the problem and possible solutions had allowed an easy fix to be overlooked for months. With good data, the problem was quite obvious and the solution easy to implement.

Repeat after me: why guess when you can know?!

Wizard

I love magic tricks and have even recently included a couple of card tricks in my "Managing Statistics" presentation. So, as I walked around Downtown Disney yesterday, I found this hat and couldn't resist trying it on!



I may still go back and buy it...and maybe a wand too!

Strange road sign

I flew into the Sanford airport in Orlando this week for the Collaborate conference. It's about 20 miles from the main Orlando airport. As I drove to my hotel, I passed a road sign unlike any I've ever seen before. It was located just before I crossed a bridge and it read:


I had visions of hoards of mosquitos or killer bees attacking my moving vehicle so I made sure the windows were rolled up tight, turned the vent to recirculate and sped up until I felt I would be out of the danger zone.

Only in Florida.... :)

Things are usually never as bad as they seem at first

Have you ever just had one of those weeks? Well, what started out about a week ago as what I thought was the end of the world is looking pretty bright now.

Anyway, all my personal drama of the past week made me think of a time about 3 years ago when I had my wisdom teeth removed. I had it done in my dentist's office with local anesthetic and a good dose of "I don't care about anything" drugs. Afterward, I thought I was going to die from the swelling and pain. So, I basically crashed and had to be woken up every so often to change the ice (uh...bags of frozen veggies) that I used to keep me iced down to prevent swelling. [By the way, if you ever need ice for an injury, and haven't tried it, bags of frozen vegetables, particularly corn and peas, are fantastic for that purpose.] Anyway, at one point my sweet caretaker put a bag of blueberries on me as we’d run out of other veggies. While I slept, the juice leaked out all over the towel we had wrapped around my head. When the next wake up time came and she saw it, she thought I’d been bleeding profusely and we both about had a panic attack!

It didn't take long to figure out it was just blueberry juice, but the point is that it sure looked like I was in bad shape initially. In the end, it turned out fine. In that case, we switched back to frozen corn and I eventually stopped swelling and hurting and got over the ordeal pretty quickly.

Winston Churchill once said, "If you're going through hell, keep going." Even if your experience doesn't necessarily qualify as hell, I think it's good advice. It's sometimes hard to remember when you're in the midst of a stretch of bumpy road, but I was reminded this past week that a bumpy road doesn't last long and there are turn-offs and other roads much less bumpy ahead. (And big thanks to friends who gave my shock absorbers a boost this week!)

Sorry for the lengthy absence from posting, but I'll get back to posting more regularly in the days to come.

If

I stumbled across this poem by Rudyard Kipling today and it struck a chord with me so I thought I'd share it. It speaks of character and persistence and so much more.

If

If you can keep your head when all about you
Are losing theirs and blaming it on you,
If you can trust yourself when all men doubt you
But make allowance for their doubting too,
If you can wait and not be tired by waiting,
Or being lied about, don't deal in lies,
Or being hated, don't give way to hating,
And yet don't look too good, nor talk too wise:
If you can dream--and not make dreams your master,
If you can think--and not make thoughts your aim;
If you can meet with Triumph and Disaster
And treat those two impostors just the same;
If you can bear to hear the truth you've spoken
Twisted by knaves to make a trap for fools,
Or watch the things you gave your life to, broken,
And stoop and build 'em up with worn-out tools:
If you can make one heap of all your winnings
And risk it all on one turn of pitch-and-toss,
And lose, and start again at your beginnings
And never breath a word about your loss;
If you can force your heart and nerve and sinew
To serve your turn long after they are gone,
And so hold on when there is nothing in you
Except the Will which says to them: "Hold on!"
If you can talk with crowds and keep your virtue,
Or walk with kings--nor lose the common touch,
If neither foes nor loving friends can hurt you;
If all men count with you, but none too much,
If you can fill the unforgiving minute
With sixty seconds' worth of distance run,
Yours is the Earth and everything that's in it,
And--which is more--you'll be a Man, my son!

--Rudyard Kipling

It's about the audience

I love this quote. Every time I give a presentation I hope it's obvious that I feel strongly about my subject. But the most important thing is that the audience takes away something that moves them.

While recently attending a course taught by Garr Reynolds (PresentationZen) and Nancy Duarte (Duarte Design, Slide:ology), Nancy said something that is a great corollary to the Salladay quote. She said, "You can give a great presentation or you can give a presentation that moves the world." Amen sister!

What does one TRILLION dollars look like?

If you haven't seen this, it's worth a look. I'd happily take the 1 million dollar package (100 packets of $10,000) which looks like this...

...and would still have room in my bag for my laptop.

Isn't it amazing that something that looks so small can mean so many big things?

Sigh...

Expense reports - The Next Generation. Wow!

I was filling out expense reports this past weekend and tweeted about it:



A short while later, I got a reply tweet:



My first thought was "Oh crap! Twitter spam!" But for some reason, I clicked on the link and ended up at a new site called Expensify.



Maybe because I hate filling out expense reports (particularly the part where I have to tape all my receipts to copy paper) or maybe it's because I was just bored of doing it and thought something new and different would at least be not boring, so I signed up.

Within a few minutes, I had linked up the credit card I use for all my business expenses and imported all the transactions for that card into Expensify. From that list of transactions, I chose the expenses that were from my latest business trip and built a new expense report following the very simple instructions. There were a few expenses that I didn't charge to my card, so I added those manually.

They said I could email a JPG of the receipt to receipts@expensify.com and it would be made available for me to include on my report just like charge card receipts. I tried this and it didn't work. Oh well...I figured it was either user error or perhaps it was the fact that this site is still labeled "beta" and I had just hit a bug that was preventing it from working.

But, for the part that worked, I was very happy. Every expense from my credit card had a certified electronic receipt. What this means is that there is proof of my charge direct from my credit card vendor that the IRS accepts so I don't need to provide the physical receipt for those expenses. In other words, if I charge everything to my card, I have electronic receipts for all those charges and my entire expense report can be handled without me having to deal with one scrap of paper or receipt! Now that is awesome!

I was a bit concerned about the cash receipts JPGs not showing up though so I sent a feedback note (by clicking on the site's Feedback link) saying that I couldn't get the JPG email to show up properly. I figured the feedback would just disappear into a support black hole, but then !bam! I got an email from a real person with a real name with a real email address:



I forwarded him the original email I had sent with the attached JPG as he requested and got this reply:



Wow! Real. Live. Actual. Responsive. Customer. Service. Wow!

I re-sent the email with the attachment and checked my Expensify account and !wah-lah! it was there, pretty as you please.

Wanting to be thankful but seeing something else I was now wondering about, I replied:



And very shortly, I received this note in response:



Again, I say Wow! I got an acknowledgment and a work-around.

I'm seriously impressed with these folks. It reminds me of TripIt and how much easier using it has made organizing my travels. Expensify seems to be to expense reporting what TripIt is to travel. Granted, I think they've still got some maturing to do but I'm highly impressed with what they've got and with their excellent customer service. And, even better...it's free to use!

My customer service experience is so critical to how I view a product or service. If customer service and support is top notch, I think that speaks volumes. And, it didn't take much for the Expensify folks to make me feel well taken care of and heard. I appreciated it so much I've just spent 20 minutes writing this blog post singing their praises!

So, if you hate doing expense reports and want a way to make it easier and faster to do them than you ever thought possible, I highly recommend that you check out Expensify.

Tagged

My friend Cheryl tagged me for this a couple of weeks ago and so I'm playing along (better late than never)!

Here's the deal:
1. Go to your My Documents/My Pictures
2. Go to your sixth file
3. Go to your sixth picture
4. Blog about it
5. Tag 6 people

Well, first of all, I'm a Mac user so there's no such thing as My Documents/My Pictures. So, I improvised. I went to iPhoto and chose the 6th Event category (it was entitled "Misc Fun"). Here's what I found:



You know I followed the instructions because why else would I ever choose this humiliating photo of myself to display to the public! Thanks a lot Cheryl!

So, here's the deal. I have a thing for hats. I don't wear them often, but I have several I keep around for things like hiding bed hair when I have to run to the store early in the morning to pick up something we need for breakfast. But mostly, I like to try on different hats and have fun making up stories about what I'd be doing or who I'd be if I was the owner of that hat.

Can you guess who I'd be in the hat pictured? Yep...that's me...the Grand Poobah!

This hat was one of many I tried on at a shop at Universal Studios Florida called the Brown Derby. That day I tried many hats and had many photos taken, but this one was the one I was most tempted to purchase. I could almost imagine the stares I'd get the next time I wore it to cover up bed hair!

I'll admit to ending the tag game at me. I mean really...the Grand Poobah does have the power and authority to change the rules just a bit doesn't she?!?!

Tweetie

For those of you who Twitter and own an iPhone, you may like this app - Tweetie - if you haven't found it yet.

Don't keep secrets from your doctor

Check out this article from CNN.com. It's a list of 10 'secrets' you shouldn't keep from your doctor. I was amused enough at the list that I thought I'd share it for those who don't want to read the full article.

1. Smoke, drink excessively, do illegal drugs
2. Have peeing or pooping issues
3. Take herbs
4. See a chiropractor or an alternative healer
5. Are having financial troubles
6. Want cheap medicine (i.e. generic drugs vs. brand name)
7. Feel depressed or anxious or stressed out
8. Defied the doctor's orders
9. Don't exercise and eat badly
10. Have a small problem you think is unimportant

I'm not sure why this struck me, in some ways, as funny, but it did. My primary physician is one person I really do tell everything to! After a heart attack two years ago and the realization that I'm getting older and older every day, I figure I'd better keep my doc "in the know".

First of all, I pay her to make my health a priority (for her and for me). Second, some of these things I think she'd know if I tried to lie. For example, defying her orders or not exercising and eating badly will show up in her examinations of me for the most part. If I leave her office and return six months later and have gained 20 pounds I think she'll figure out I've not been a good girl with diet and exercise. Finally, what's the point in keeping secrets? It's kinda funny but mostly sad to think that people would not tell their doctor about things because they were embarrassed or frightened of the reaction they'd get or maybe they just don't want to be told any bad news so they lie to avoid it.

Anyway, if you have a fear of being honest with your doctor, try finding one that you feel comfortable enough with to be completely open. I think you'll be glad in the long run and likely a lot healthier too.

My word of the day

pro·te·an (prō’tē-ən)
Exhibiting considerable versatility or diversity.

Black and White - Under 40? You won't understand.

My friend Larry sent this to me and I liked it so much I thought I'd blog it. The point is to help remember that life's most simple pleasures are very often the best.



You could hardly see for all the snow,
Spread the rabbit ears as far as they go.
Pull a chair up to the TV set,
'Good Night, David. Good Night, Chet.'

My Mom used to cut chicken, chop eggs and spread mayo on the same cutting board with the same knife and no bleach, but we didn't seem to get food poisoning.

My Mom used to defrost hamburger on the counter AND I used to eat it raw sometimes, too. Our school sandwiches were wrapped in wax paper in a brown paper bag, not in ice-pack coolers, but I can't remember getting e.coli.

Almost all of us would have rather gone swimming in the lake instead of a pristine pool (talk about boring), no beach closures then.

The term cell phone would have conjured up a phone in a jail cell, and a pager was the school PA system.

We all took gym, not PE and risked permanent injury with a pair of high top Ked's (only worn in gym) instead of having cross-training athletic shoes with air cushion soles and built in light reflectors. I can't recall any injuries, but they must have happened because they tell us how much safer we are now.

Flunking gym was not an option, even for stupid kids! I guess PE must be much harder than gym.

Speaking of school, we all said prayers and sang the national anthem, and staying in detention after school caught all sorts of negative attention.

We must have had horribly damaged psyches. What an archaic health system we had then. Remember school nurses? Ours wore a hat and everything.

I thought that I was supposed to accomplish something before I was allowed to be proud of myself.

I just can't recall how bored we were without computers, Play Station, Nintendo, X-box or 270 digital TV cable stations.

Oh yeah ... and where was the Benadryl and sterilization kit when I got that bee sting? I could have been killed!



We played 'king of the hill' on piles of gravel left on vacant construction sites, and when we got hurt, Mom pulled out the 48-cent bottle of Mercurochrome (kids liked it better because it didn't sting like iodine did) and then we got our butt spanked. Now it's a trip to the emergency room, followed by a 10-day dose of a $49 bottle of antibiotics, and then Mom calls the attorney to sue the contractor for leaving a horribly vicious pile of gravel where it was such a threat.

We didn't act up at the neighbor's house either because if we did, we got our butt spanked there and then we got butt spanked again when we got home.

I recall Donny Reynolds from next door coming over and doing his tricks on the front step, just before he fell off. Little did his Mom know that she could have owned our house. Instead, she picked him up and swatted him for being such a goof. It was a neighborhood run amuck.

To top it off, not a single person I knew had ever been told that they were from a dysfunctional family. How could we possibly have known that?

We needed to get into group therapy and anger management classes? We were obviously so duped by so many societal ills that we didn't even notice that the entire country wasn't taking Prozac! How did we ever survive?

LOVE TO ALL OF US WHO SHARED THIS ERA, AND TO ALL WHO DIDN'T; SORRY FOR WHAT YOU MISSED. I WOULDN'T TRADE IT FOR ANYTHING.

New paper and CJ Date advert

I've uploaded a paper entitled "Managing Statistics for Optimal Query Performance" that I'll be delivering at a couple of conferences over the next few months. This was a fun and interesting paper to write and I'm looking forward to actually delivering it at SEOUC, the Hotsos Symposium and Collaborate.

I'm also really thrilled that C.J. (Chris) Date will be coming to Dallas in April to teach a new course he's developed entitled "How to Write Correct SQL and Know It: A Relational Approach to SQL". You may have heard Mr. Date's name as you were learning relational theory as he, along with Ted Codd, are known for their work in the field. I can't wait to attend an event from one of the "founding fathers". I don't think it really matters what database platform you use, the experience and application of relational theory that this guy has in his little finger is likely to be more than I've ever dreamed of knowing. Check out the class and come join me. It should be a good one!

Groundhog Day

So, I heard that according to the groundhog, we're to expect 6 more weeks of winter. Given the odd weather we've had here in Portland this winter - from more snow than has been seen in years to days of absolutely freezing temps and biting winds with sunny skies (which is very unusual for the typically grey and dreary weather we usually see this time of year) - I almost expect volcanoes to erupt (possible since we're near Mt. St. Helen's) or a swarm of locusts to engulf the city!

I think Bill Murray said it best in the movie GroundHog Day.