Karen Morton

Hotsos Symposium

2012-01-21T13:57:00.001-05:00

My favorite conference of the year is just around the corner.

The Hotsos Symposium will be held March 4-8 and will be celebrating its tenth anniversary. I'll be speaking again this year and, as always, am eagerly looking forward to the week. Every year the speakers, topics and opportunities for networking continue to excel and I'm sure this year will be no different. This year's Training Day event will be conducted by Jonathan Lewis so make sure to stay the extra day to take advantage of spending a day with him as he discusses "Designing Optimal SQL".

If you haven't signed up yet, there's still time. I'll look forward to seeing you there!

Bug update

2011-07-13T18:56:00.003-04:00

We received notice of a patch to correct the bug I discussed with gathering stats on tables with virtual columns/function-based indexes in parallel in my previous post. The patch is number 10013177 and can be found at https://updates.oracle.com/download/10013177.html.

After applying the patch, all stats collections worked without error. Whew!

Bug alert!

2011-06-30T10:19:00.004-04:00

While testing Oracle 11.2.0.2 (on Red Hat Enterprise Linux), I recently bumped up against a problem with stats collections using DEGREE > 1 that results in an ORA-600.


20:56:41 SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS('TR_OWNER','TR_POL', Estimate_Percent=>dbms_stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>FALSE, Degree=>dbms_stats.AUTO_DEGREE); END;
20:56:41   2  /
BEGIN DBMS_STATS.GATHER_TABLE_STATS('TR_OWNER','TR_POL', Estimate_Percent=>dbms_stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>FALSE, Degree=>dbms_stats.AUTO_DEGREE); END;
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-12801: error signaled in parallel query
server P024, instance db100.srv.com:TRXTPLT2 (2)
ORA-00039: error during periodic action
ORA-00600: internal error code, arguments: [17114], [0x2AE3C33489B8], [], [],
[], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 23112
ORA-06512: at "SYS.DBMS_STATS", line 23205
ORA-06512: at line 1

If I execute with an estimate_percent of anything other than auto_sample_size, the ORA-20011 goes away, but the ORA-600 remains. Interestingly enough, if I execute with Degree=>1, everything is fine.

After doing more research and finding that I could execute a stats collection on other objects without any problems, I narrowed down the problem to the existence of certain types of function-based indexes. In particular, if the table has an index that uses either a DECODE or a CASE expression or a user-defined function, the problem occurs. As long as the table doesn't have one of these, everything works fine.

The contact to Oracle Support in regard to this issue replied as follows:

It possible you're getting the problem described in Bug:11774077 which is closed as a duplicate of Bug:10013177. For more information please read Note:10013177.8. Functional index causes max decode group by sql to truncate values / can cause dump.
Please check if one of the workarounds will solve your problem:
- Set _replace_virtual_columns=false
OR
- Set _disable_function_based_index=true
OR
- Gathering table stats using degree 1.

Ouch! Turning off the use of virtual columns and function-based indexes is *not* an option. We rely quite heavily on them in our application. And, for the tables that have this problem, gathering stats without doing it in parallel (degree 1) is doable, but not desirable as we have a limited window of time to collect stats and have relied on being able to collect in parallel to reduce the total time it takes to complete the collections.

We're still researching other options or workarounds and pushing on Oracle Support for a fix for this problem, but are not making much progress on either front at this point.

I'll update again soon with more information as I have it.

Hotsos Symposium 2011

2011-01-26T19:27:00.004-05:00

The 2011 Hotsos Symposium begins in just a little over a month (March 6) and I can't wait! I've been fortunate to have attended every Symposium except one (last year) since these events began. When I was asked to conduct the Training Day following this year's main Symposium (the Training Day is on March 10) I was honored to accept and thrilled that I would be back in attendance this year.

I believe the Symposium is unique in many ways. It is the one and only conference that I know of focused specifically on Oracle performance. Over the span of just a few days, attendees are privileged to hear the best speakers from all over the globe. This year's event is overflowing once again with a stellar line-up of presenters on topics such as "Database I/O Performance" (Alex Gorbachev), "Contemporary Latch Internals" (Andrey Nikolaev), and "Five Things Every Programmer (and DBA) Should Know about Oracle" (Andrew Zitelli). Cary Millsap will be delivering his award-winning presentation "Thinking Clearly about Performance" and Kerry Osborne will be delivering the Keynote address as well as presenting on "Tuning Exadata". The week will end up with Tom Kyte's birds-eye view of the week's sessions as he provides his representation of those topics in the insightful and engaging way only Tom can.

I have the privilege of following this great line-up of speakers by presenting the Training Day. My topic (albeit a very wordy title) is "Managing SQL Performance - Practical Information and Tools for Writing and Maintaining Optimally Performing SQL". I'm looking forward to sharing many of the things I do every day that help me to take poorly performing SQL and making it hum! Don't you just love it when you can get a win like reducing a query's response time from nearly an hour down to a few seconds?! I know I do. The thing is, it isn't magic. Admittedly, it's fun to have people shake their heads in wonderment when I'm able to accomplish such "miracles", but I'll share my secrets for how I go about doing it. Well, OK...they're not really secrets. What I do is simply the application of a systematic, repeatable approach that anyone can learn and utilize. Like anything, the more you practice, the better (and faster) you get at the process.

I think one of the most fun things about becoming more and more proficient at optimizing SQL is that you appear to have abilities that seem almost magical. I thought I'd share this video where Penn & Teller demonstrate how what seems like magic can be uncovered and shown to be just well-timed execution of known and practiced actions.

I hope the Training Day will allow me to show you what lurks under the covers and make the magic seem practical for you! See you there!

Left outer join equivalence - Oracle vs ANSI syntax

2010-10-25T09:43:00.003-04:00

I was reminded last week while visiting on customer site that it's easy to confuse the syntax you should use if you switch between Oracle's syntax and ANSI syntax for outer joins. Here's an example using the good old standby SCOTT schema to show what I mean. First, I'll show the data from both tables we'll use (emp and dept) so the rest of the examples will be easier to follow.


SCOTT@ORA11R2> select * from emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SCOTT@ORA11R2> select * from dept ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

Now, let's join the tables to produce a result set that includes all rows from emp but limits the dept table info to only be included if the deptno = 20.


SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno(+) and d.deptno(+) = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7902 FORD               20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7566 JONES              20 RESEARCH
      7369 SMITH              20 RESEARCH
      7839 KING
      7934 MILLER
      7782 CLARK
      7900 JAMES
      7844 TURNER
      7698 BLAKE
      7654 MARTIN
      7521 WARD
      7499 ALLEN

14 rows selected.

Note that we get back the entire set of 14 rows from emp, but only 5 rows have the deptno and dname column values populated. This is because the filter on deptno(+) = 20 is written so that it is applied before the join between emp and dept occurs. Therefore, only rows with deptno = 20 will be joined to their matching emp rows. The remaining rows for employees who are in the other departments will be returned due to the outer join as no rows from dept will be available to match to.

If we wrote the join without including the (+) on the filter, we'd have gotten back only rows for deptno 20 employees as follows:


SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno(+) and d.deptno = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH

5 rows selected.

However, if we want to return rows only for deptno 20, then we shouldn't use an outer join at all. If the filter is applied after the join, then the rows for other departments will have null values in the deptno and dname columns (as shown earlier) and therefore will not match the filter condition and be eliminated from the result set. You can see that the result is the same if you remove the outer join operator.


SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno
  4  and d.deptno = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH

5 rows selected.

When using ANSI join syntax, you need to make sure to use the filter condition properly as well. With ANSI syntax, the difference is in where you place the condition. In order for the filter to be applied before the join, you must place the filter inside the join condition.


SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e left outer join dept d
  3  on (e.deptno = d.deptno and d.deptno = 20);

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7902 FORD               20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7566 JONES              20 RESEARCH
      7369 SMITH              20 RESEARCH
      7839 KING
      7934 MILLER
      7782 CLARK
      7900 JAMES
      7844 TURNER
      7698 BLAKE
      7654 MARTIN
      7521 WARD
      7499 ALLEN

14 rows selected.

If you place the filter in the WHERE clause, the filter is applied after the join.


SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e left outer join dept d
  3  on (e.deptno = d.deptno)
  4  where d.deptno = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH

5 rows selected.

I tend to use the Oracle syntax most often and am so used to it that when I was shown the ANSI syntax, it took a minute to recall the difference in placement of the filter condition (thanks for the nudge Karl!). It's fairly obvious when looking at a simple example like this as to where the condition belongs, but when you've got a much more complex SQL statement with multiple joins, the effects of placing the filter in the wrong spot may be overlooked.

Hope this little reminder helps!

You know you're a true gadget geek when...

2010-10-01T19:56:00.006-04:00

...the power goes off in your home during a major thunder/lightning/rain storm and instead of going to the junk drawer to pull out the flashlight you reach for your iPhone and turn it on so that the soft glow illuminates your surroundings.

I even went so far as to go to the App Store and download a free flashlight app that I then played with until the power came back on. I used my nifty new little app to light my way to the water cooler to refill my water bottle and then returned to the comfort of my couch to play with all the different flashlight choices in the app until the power came back on several minutes later.

Actually it's kinda scary to think that I've crossed some line that makes my iPhone the "go to" solution in almost any situation.

Do not feed...

2010-07-21T20:17:00.003-04:00

What exactly am I supposed to think when the park near our house has this sign posted in numerous places:

This cracks me up for at least two reasons:
1) because it doesn't say "Beware of alligators" or anything like that, it simply tells you to not feed alligators or other dangerous critters like cats, raccoons and possums, and
2) because there are enough of all of those animals hanging around the park that a warning (and actually a legal ordinance) is needed!

As I was pondering the sign further (while my daughter played in this danger zone), I was thinking that if all these animals happened to be around at the same time, and the alligator looked hungry, I'd grab one of the others and toss it over to the gator, grab my kiddo and run. So, I wonder if it's OK to do that? I mean, since they're all on the sign is it OK for them to eat each other?

Geesh...I think of weird things. Oh well, eat or be eaten I say!

My favorite parts of today

2010-06-24T20:16:00.004-04:00

Every night over dinner my daughter asks me "what was your favorite part of the day Momma"? Well, tonight I had several favorite things to share. My first favorite was that I found an Oracle bug that has to do with subquery factoring (I'm going to blog about this one soon). And my second favorite (well, it's really my *most* favorite) was spending some fun time at the beach with my kiddo. Looks like we had fun doesn't it?!?

I should've been in movies...

2010-06-20T21:55:00.003-04:00

Have you seen the movie Avatar? If not, this will just look like a crazily distorted blue photo of me. But, if you saw the movie, you'll see me as a Na'vi. Cool, huh?!?

I loved the movie (it was fabulous in 3D!) and if you haven't seen it yet, check out the trailer.

PowerPoint is the Enemy

2010-04-27T19:20:00.003-04:00

I went to Starbucks about an hour ago to treat myself to a mocha (even though it was nonfat). And while waiting for my coffee to arrive I noticed the front page of The New York Times. Right smack in the middle of the page was a horrendous PowerPoint slide and the title of the article underneath read "We Have Met the Enemy and He Is PowerPoint".

Even though I knew I could get the story online when I got home, I just had to buy the paper and read it immediately.

I'm a huge believer in the concept of "death by PowerPoint". There certainly have been a multitude of presentations that I have suffered through that nearly killed me! I follow and attempt to learn from the presentation methods of Garr Reynolds, Nancy Duarte, Seth Godin and others who are out there promoting more effective ways to deliver presentations. While this article's title may paint with broader strokes than I would by saying "PowerPoint is the enemy", I mostly agree with the sentiment. PowerPoint itself may not the enemy, but surely the way it is commonly used is close to a criminal act.

Just look at that slide the article displays! Could you ever make any sense of that thing? It was shown to the General leading the American and NATO forces in Afghanistan last summer and he was quoted as saying, "When we understand that slide, we'll have won the war." Other high ranking military officers said things like "PowerPoint makes us stupid" and "It's dangerous because it can create the illusion of understanding and the illusion of control." One General even went so far as to ban the use of PowerPoint presentations!

In my experience, PowerPoint presentations have replaced meaningful documents (like white papers) and are used in an attempt to distill all the knowledge deemed pertinent on a topic into a few bullet points. The hope is that a busy person (i.e. usually an executive) can "get the idea" of what is being conveyed without having to spend much time reading something more detailed. But, the supposedly concise presentations I see most often have entire slides filled with sentences (all preceded by the infamous bullet point) that are so busily annoying that I can barely stand to look at them. A presenter is really not even a necessary component of the presentation anymore as most people intend for their slide decks to stand alone and tell the story without need for a person to deliver the message verbally. When these presentations are delivered, they are (more often than not) simply read by the presenter. On top of that, many presentations are not verbally delivered by those directly responsible for the content, but instead are delivered by a high level manager who only shows up on the day of the big presentation to lead the show. I don't know...that just seems odd to me. I'm not going to say there are no merits at all to this use of the tool and this particular approach, but I do find that what is delivered is rarely what I personally want to see/hear.

The article states that military commanders say that "slides impart less information than a five-page paper can hold, and that they relieve the briefer of the need to polish writing to convey an analytic, persuasive point." The technical presentations I see suffer from this in that they are not very polished nor persuasive. But even a "bad paper" that makes an attempt to provide detailed information on a topic is better than being bored to death with a slide presentation that either puts too much or too little meaningful content in front of me. But, the fact that the use of PowerPoint has become so commonplace, it is rare to ever get a detailed paper...you only get the slides. And even if you do get a paper, it is often full of grammatical issues and seems to have been written after the slides such that the paper doesn't offer that much more information but only slightly lengthens the bullet points from the slides.

There are a lot of people out there trying to change the way PowerPoint is used. They work to show that presentations can be used to support the speaker and the topic effectively. I think that most of these advocates separate presentation design and use from documents providing detailed information. I just wish there was a faster way to affect change in this area!

What happened to the days of speakers standing alone in front of the crowd? When was the last time you attended a session that didn't use PowerPoint? Maybe we all need to ban the use of PowerPoint (and all presentation software) for a while and return to a more simple way of sharing our message. Perhaps instead of propagating the use of PowerPoint, organizations could start hosting Toastmasters meetings and teach people how to become more effective speakers.

As I said earlier, I don't think it's necessarily the software that's the problem. But, it's sure an easy target on which to place the blame for the sad situation we find ourselves in every time we are forced to sit through another agonizingly boring presentation.

Oh, the places you'll go!

2010-03-16T18:53:00.004-04:00

How do you feel about the place you're in, the places you've been, and the places you hope to go? As I was reading "Oh, the Places You'll Go" by Dr. Seuss to my daughter this evening, I was struck by how much wisdom was held in that simple children's story. So, I did a quick search and found the words of the story for you.

For those sports buffs out there, check out this cool video using this story that aired as an opener for the 2006 SuperBowl.

Note (5/8/2010): This post originally included a video link that is no longer available so I removed the reference.

Yes, I'm still here

2010-02-19T19:37:00.002-05:00

I admit it. I'm a binge blogger (I borrowed this term from a friend of mine who posted on the same topic). I make several blog entries over the course of a week or so and then it may be a month or two before I show up again. I suppose my good intentions of blogging regularly just get swept under the rug of day-to-day reality and how/where I choose to spend my time. But, regardless of frequency, I suppose it's fairly obvious from this post that I'm still here.

I was doing a bit of blog surfing tonight to catch up on a long list of blogs I enjoy reading and came across the following from a recent Seth Godin post:

"Firing the customers you can't possibly please gives you the bandwidth and resources to coddle the ones that truly deserve your attention and repay you with referrals, applause and loyalty."

Amen! This reminded me a a time early in my days as a self-employed consultant back in the mid-90's. I took on a client that turned out to be a royal pain-in-the-backside. They'd call me to complain about their fax not working (among other odd things) and even though the services I had contracted with them to provide had nothing to do with most of the things they called to rant about, they expected me to address and fix them all. I ended up giving them back the initial fee they'd paid and canceled the contract. I felt better immediately and the time I was spending dealing with them was quickly filled with several new (and much nicer and "normal") customers.

So, I can relate to this quote. I also think it just makes good sense across the board. Whatever you give your time and attention to (careers, relationships, etc) should yield positive (whatever positive means to you) results. If you find that you're always unhappy when dealing with certain aspects of life, then I think it's entirely reasonable to look for ways to eliminate/reduce whatever it is and replace it with something that brings more happiness and positiveness to you. It may seem difficult and a bit scary to do so, but I think in the long run it's the way to go.

On a different note, I spoke today at the MAOP (Mid Atlantic Association of Oracle Professionals) Conference held today in Reston VA. I really enjoyed being there and got some good feedback and questions after speaking. In my current work I don't get the opportunity to speak in front of folks like I used to when I taught classes at least a couple of weeks a month. So, I'm really grateful to MAOP for asking me to speak and for having the opportunity to get a "fix" from presenting to a great audience!

And speaking of conferences, the annual Hotsos Symposium is coming up March 7-11 in Dallas. This will be the first one I've missed since they started having them 8 years ago (bummer!). But, if you want to attend one of the best conferences anywhere, and the only one focused specifically on Oracle performance, there's still seats available. I highly recommend it!

2000 columns - take 2

2010-01-05T16:39:00.004-05:00

Thanks to everyone who commented on yesterday's post. After Randolf's comment about the 1000 column limit, I realized that so far, I've not attempted to create the full table in one go. Instead, I've been building 20-ish separate tables that contain the needed raw data plus the computed/aggregated columns. This speaks to Noons comment about having separate tables that access and group data up that can be executed in multiple streams

The SAS guys want to be able to access a single row that represents all the information they need for one type of data (for example, a shipment). So far, as I build the separate tables, I've been using a couple of views that combine several of the tables. The SAS guys then use the view to build a SAS dataset and merge multiple datasets together.

Yesterday's post was prompted by the prospect of having to build a single table in Oracle that does what they're currently doing via SAS. And to Joel's point, SAS is certainly not Oracle! One of the original goals was to ultimately have a table built and maintained in Oracle and only accessed by SAS, not multiple objects in Oracle that have to be merged into SAS datasets.

I've been so busy and focused on getting the data formulated/aggregated, and doing so as efficiently as possible, that I hadn't even made the attempt to build out a full table with all the columns of all the tables included in that one table. When I started thinking about it yesterday, I got all freaked out about doing it and that's what prompted my post. I hadn't even thought about the Oracle imposed limits on the number of columns! I've been told more than once that the team had delivered 1000s of columns before, so I just hadn't given it much thought. But, given there is an Oracle limit, I'm guessing they've never delivered a final Oracle table like they want to do this time...so, it's either been done in SAS previously or the Oracle tables didn't exceed the 1000 column limit.

So, I've got lots to figure out over the next few weeks. This just may be more fun than I can stand! :)

2000 columns

2010-01-04T19:57:00.003-05:00

How many columns does the largest table you've ever worked with contain? The current project I'm working on has 1 table with almost 2000 columns (and it's likely to add more!). This is the most highly denormalized design I've ever encountered and there's something about it that makes the performance optimizer in me cringe. But, the statisticians that have to munch and crunch this data in SAS tell me this format best suits their needs (based on similar designs used successfully in previous projects).

I think I'm really more concerned about the work that has to be done to populate these columns as most of the columns contain aggregations or formulations of some sort or another. So, perhaps it's not the number of columns that really is niggling at me as it is everything that must occur to produce the values contained in a single row (it's a lot).

What's your experience? Did the number of columns help, hinder or make no difference in the design and performance of the application that used such wide tables? This phase of the project is a proof of concept so it'll be a while before I get hard data on how well (or not) this design really works, but it has certainly made me curious about what other folks have experienced.

Let me hear from you if you have any comments or insights!

Compression to the rescue

2009-11-30T20:48:00.008-05:00

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

2009-10-28T18:46:00.005-04:00

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"

2009-10-20T08:29:00.002-04:00

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

2009-10-15T16:55:00.000-04:00

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

2009-10-09T15:12:00.003-04:00

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

2009-10-01T08:52:00.002-04:00

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

2009-09-30T12:21:00.005-04:00

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

2009-09-24T08:22:00.001-04:00

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

2009-09-22T20:18:00.000-04:00

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

2009-09-18T13:47:00.000-04:00

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

2009-09-15T13:59:00.000-04:00

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

2009-09-14T09:42:00.000-04:00

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

2009-09-09T08:43:00.000-04:00

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)

2009-09-03T12:38:00.000-04:00

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

2009-09-02T13:46:00.000-04:00

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

2009-08-30T13:55:00.000-04:00

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...

2009-08-26T18:34:00.000-04:00

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

2009-08-16T18:37:00.000-04:00

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

2009-08-09T13:02:00.000-04:00

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

2009-07-14T12:41:00.000-04:00

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)

2009-06-18T11:55:00.000-04:00

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!

2009-06-08T18:23:00.001-04:00

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

2009-05-27T22:04:00.001-04:00

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?

2009-05-16T22:04:00.001-04:00

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

2009-05-08T10:44:00.000-04:00

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

2009-05-06T17:39:00.000-04:00

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

2009-04-23T21:36:00.000-04:00

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

2009-04-05T16:13:00.000-04:00

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

2009-04-01T22:55:00.001-04:00

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?

2009-03-22T21:02:00.000-04:00

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!

2009-03-18T22:30:00.000-04:00

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

2009-03-17T00:04:00.001-04:00

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

2009-03-10T20:13:00.000-04:00

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

2009-03-05T21:18:00.000-05:00

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

2009-02-23T19:03:00.000-05:00

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

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

2009-02-14T21:51:00.000-05:00

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

2009-02-12T18:51:00.000-05:00

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

2009-02-02T16:20:00.000-05:00

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.

"Indexed" and Isolation

2009-02-02T16:10:00.000-05:00

I've followed a blog called Indexed for some time. The author/blogger is Jessica Hagy and she gives her perspective on the world using small drawings on index cards. I think it's quite clever. She has a book of the same name which is a collection of many of her "indexed" thoughts. I like the way she makes a point in such a simple and visual way.

I was looking through some older posts and came across one called "Self Defense Today."

I had to laugh. I really (really, really) don't know what I did before caller ID and voicemail! When I see a number I don't recognize, I just don't answer and will wait to see if I get a voicemail message. Or, I'll answer but just cough in the phone like Jack Nicholson did in "As Good As It Gets". Then I listen for a couple of seconds and then hang up. By the way, this is a good trick for handling automated calls. When you answer, you have to make some noise to get the automated message to start playing and then you can hang up. If you hang up before the message starts, or if you don't answer at all, you keep getting call backs. So much for the nationwide "do not call" list!

I try to only do the cough/hang up trick when I really think it's an unwanted sales call (Dish Network for the thousandth time, credit card companies, and so on), but if you happen to call me and get a loud cough in your ear, perhaps you should call back from a number I recognize or just send me an email. Of course, if I don't recognize your email address, that message just might end up in my spam folder.

So, better yet, just drop by if you're in the neighborhood. But that might be a problem too as my doorbell isn't working and if you drop by after 6pm there's a sign that says "knock softly, baby sleeping" and another one that says "no soliciting". If you do knock and I'm upstairs, I won't hear you particularly if the TV is on.

Geesh! I suppose I've effectively cut myself off from society. Maybe I need to rethink things. Hmmmm...

"Dynamic" WHERE clauses that aren't

2009-01-19T23:00:00.000-05:00

What does the optimizer do with "dynamic" WHERE clauses? What I'm talking about is when you have a SELECT statement that has a "simple" WHERE clause like this:


WHERE column1 = NVL(:B1, column1) 
  AND column2 = NVL(:B2, column2)

The intention of this WHERE clause is to allow 4 different scenarios to be covered.
1) If :B1 is null and :B2 is null, then retrieve all rows.
2) If :B1 is null and :B2 is not null, then retrieve only rows where column2 = :B2.
3) If :B2 is null and :B1 is not null, then retrieve only rows where column1 = :B1.
4) If :B1 is not null and :B2 is not null, then retrieve only rows where column1 = :B1 and column2 = :B2.

The logic is that since the two conditions are AND'ed, that both have to evaluate to true in order to produce any rows. If, however, you wish to be flexible and allow for all 4 cases to be possible with a single query, then you have to find a way to ensure that both sides of the AND evaluate TRUE regardless of what inputs you receive. By using the NVL, you can accomplish that. You either pass in a null value or a not null value and the NVL check will make sure that the condition always evaluates to true. You get to either qualify which rows are returned by passing in a not null bind variable or you can leave the selection of rows unqualified by passing in a null value. Simple, huh?

Well, it may be simple in terms of the way the code looks, but what it does to the optimizer isn't that simple. The optimizer has to come up with a plan that can best serve all 4 possibilities equally. It can't/won't come up with 4 different plans.

Let me show you a few tests. I'll be using a test table I had in place from some previous tests for another post. It's a simple 2500 row table with 2 indexes (T2_I3 on the hair column and T2_I6 on the work column). Take a look at what happens when I execute my query 4 times using the 4 different scenarios I noted above.


SQL>set serveroutput off
SQL>variable b1 number
SQL>variable b2 number
SQL>exec :b1 := null;

PL/SQL procedure successfully completed.

SQL>exec :b2 := null ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
           2500

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.08 |    2504 |    342 |
|   2 |   CONCATENATION                |           |      1 |        |   2500 |00:00:00.07 |    2504 |    342 |
|*  3 |    FILTER                      |           |      1 |        |   2500 |00:00:00.06 |    2504 |    342 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      1 |    357 |   2500 |00:00:00.03 |    2504 |    342 |
|*  5 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      0 |      5 |      0 |00:00:00.01 |       0 |      0 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      0 |     32 |      0 |00:00:00.01 |       0 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

SQL>
SQL>exec :b2 := 45 ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
             32

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.01 |      67 |      4 |
|   2 |   CONCATENATION                |           |      1 |        |     32 |00:00:00.01 |      67 |      4 |
|*  3 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      0 |    357 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |    FILTER                      |           |      1 |        |     32 |00:00:00.01 |      67 |      4 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      1 |      5 |     32 |00:00:00.01 |      67 |      4 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      1 |     32 |     32 |00:00:00.01 |      35 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

SQL>
SQL>exec :b1 := 5;

PL/SQL procedure successfully completed.

SQL>exec :b2 := null ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
            357

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.04 |    2504 |    342 |
|   2 |   CONCATENATION                |           |      1 |        |    357 |00:00:00.04 |    2504 |    342 |
|*  3 |    FILTER                      |           |      1 |        |    357 |00:00:00.03 |    2504 |    342 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      1 |    357 |    357 |00:00:00.03 |    2504 |    342 |
|*  5 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      0 |      5 |      0 |00:00:00.01 |       0 |      0 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      0 |     32 |      0 |00:00:00.01 |       0 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

SQL>
SQL>exec :b1 := 5;

PL/SQL procedure successfully completed.

SQL>exec :b2 := 45 ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
              5

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.02 |      67 |      5 |
|   2 |   CONCATENATION                |           |      1 |        |      5 |00:00:00.02 |      67 |      5 |
|*  3 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      0 |    357 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |    FILTER                      |           |      1 |        |      5 |00:00:00.02 |      67 |      5 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      1 |      5 |      5 |00:00:00.02 |      67 |      5 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      1 |     32 |     32 |00:00:00.01 |      35 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

Note how with each different set of bind variable values, the query produces a different result. But the plan remains the same (check the SQL_ID, child_number and plan hash values...they all match). You can also see how one set of the plan operations doesn't execute (note the 0's in the A-Rows, Buffers and Reads columns).

What this tells you is that the optimizer created a plan that would work dynamically. Well, sorta. Basically, the checks (step 3 and step 5) are made to see if :B2 IS NULL. If it is null, then step 4 executes. If it is not null, then steps 6-7 execute. This works fine for when you want the whole table (both bind values are null) or when you want to retrieve rows only where the work column equals some value. But, when you want to retrieve rows where you need to qualify both the work and hair columns or when you want to access rows for a particular hair value, the plan is sub-optimal.

So, OK...you're batting .500. Not bad I suppose. ;) But what if your SLA says you have to bat .900 or better? This plan just won't do. You need each different scenario to produce as optimal a plan as possible. How can you do that?

One way is to write your code to build the WHERE clause on the fly and use EXECUTE IMMEDIATE. The pseudo-code to do this might go something like this:

full_string := 'SELECT (rest of stuff).....';
if :b1 is null and :b2 is null then
   where_string := null;
elsif :b1 is not null and :b2 is not null then
   where_string := ' WHERE hair = :B1 AND work = :B2';
elsif :b1 is null and :b2 is not null then
   where_string := ' WHERE work = :B2';
elsif :b1 is not null and :b2 is null then
   where_string := ' WHERE hair = :B1';

full_string := full_string || where_string;
EXECUTE IMMEDIATE...

This way, you have 4 different statements and therefore 4 different plans. Each one (hopefully) optimal.

Another option would be to change your indexing strategy. Perhaps you could create a multi-column index using both columns. But, even with this option, you're not going to get "perfect" results. Depending on which column you decide to lead the index with, one of the index scans would likely have to do a skip scan or a full scan which wouldn't be as effective.

So, while the idea to use a single statement with conditional logic (in this case, NVL) and binds looks appealing, just remember that the plan you end up with may not provide the performance you want/need for all possible scenarios. If you use this approach, be prepared to create indexes properly so as to get the best possible coverage for all scenarios. I think the bottom line is that if you truly want dynamic SQL, then really use dynamic SQL. The attempt to simplify or to have a single statement that covers many different possible needs can cause performance to suffer for a some percentage (perhaps a significant %) of the scenarios.

You say, "Thank you". I say, "You're welcome".

2009-01-15T23:50:00.000-05:00

You say "Thank you". I say, "You're welcome". At least that's how my mother taught me. It's good manners to respond to a "thank you", with a "you're welcome". But, it seems like many people either didn't learn their manners the same way I did or maybe they just don't have any manners. Either way, I've got a pet peeve to rant on as a hiatus from the heavy tech nature of my last couple of posts (Cheryl, I'm primarily thinking of you as I write this non-techie post).

It's pandemic: waiters, store clerks, gas station attendants, even the phone person who helped me with my recent airfare purchase. All of them responded to my "thank you" by saying something like "no problem" or "yep" or "sure". It may be my upbringing to be mannerly. It may be my Southern roots. It may be just some other voice in my head that translates what I hear, when I hear any of those responses. But whatever the reason, when I hear anything other than "you're welcome", it immediately seems rude to me. When I hear "no problem", I think, "it shouldn't be a problem since I'm paying you for your time and service". Or, when I hear "yep", I immediately picture a gum-smacking, sixteen year old rolling their eyes at me and breathing a long-suffering sigh in my direction as they trudge back to the kitchen to get the baked potato that was left off my order.

Somehow saying "you're welcome" at least gives me the impression that you are having a personal interaction with me and not just giving me your time/attention/service because you have to be there when you'd really rather be anywhere else. It just seems like the "nice" and polite thing to say.

I know it's likely that only I have an objection to such flip responses, but I admit it. I do.

So, if you happen to get a "thank you" from me, I'd really appreciate a "you're welcome".

Thank you for reading. ;)

Round 2: BITMAP AND

2009-01-13T23:05:00.000-05:00

OK. I thought this would be pretty straightforward, but as it turns out, it wasn't (is it ever?). I finally got around to testing out the BITMAP CONVERSION stuff as related to my previous post. I've actually been working on this off and on since my previous post, but am just now to the point where I'm willing to share the results. Jeff Holt was a huge help and determined the correct trace events needed to investigate this fully. Thanks Jeff!

First, an interesting/surprising thing I found in trying to reproduce the behavior originally noted was that I wasn't able to reproduce it! What I could consistently reproduce (on both 10.2.0.1 and 11.1.0.6 instances) was essentially the opposite behavior of what the original client case showed. So, I decided to just make sure I could understand how the two operations (BITMAP AND vs AND-EQUAL) worked for what I could reproduce. This post is just about the BITMAP AND operation. I'll post another day about AND-EQUAL.

From here on out, this post has lots of code and trace data output, so if you just want the punch-line, scroll to the bottom. :)

If you'd like to try the tests yourself, here's how I created the test data and performed the test.


-- Create test table, populate with data and collect statistics (setup.sql)
drop table bitmap_test;

create table bitmap_test as
select rownum id
from all_objects
where rownum <= 50 /* make larger for more data */;

drop table bitmap_t2;

define isto="pctfree 99 storage (initial 5m)"
define tsto="pctused 1 &isto"

create table bitmap_t2
nologging
&tsto
as select /*+ ordered use_nl(v2) */
    lpad('x',20,'x')    facts,
    mod(rownum, 2)  sex,
    mod(rownum, 3)  eyes,
    mod(rownum, 7)  hair,
    mod(rownum, 31) town,
    mod(rownum, 47) age,
    mod(rownum, 79) work
from bitmap_test v1,
     bitmap_test v2
/

create index t2_i3 on bitmap_t2(hair) nologging &isto;
create index t2_i6 on bitmap_t2(work) nologging &isto;

exec dbms_stats.gather_table_stats(user, 'bitmap_t2', estimate_percent=>100, -
method_opt=>'for all columns size 1', cascade=>TRUE, no_invalidate=>FALSE)

-- Test (test.sql)
select value from v$parameter where name = 'user_dump_dest';

-- Get SPID so I can easily find trace file
select p.spid from v$session s, v$process p 
where audsid = userenv('sessionid') and s.paddr = p.addr;

-- Turn on all pertinent trace events
alter session set events '10046 trace name context forever';
alter session set events '10202 trace name context forever';
alter session set events '10711 trace name context forever, level 127';
alter session set events '10713 trace name context forever, level 127';
alter session set events '10715 trace name context forever, level 127';
alter session set events '10717 trace name context forever, level 127';
alter session set statistics_level=all;

set termout off pause off

-- Run once for each query 
-- Run with index_combine hint to get BITMAP AND plan
select /*+ index_combine(bitmap_t2 t2_i6 t2_i3) */ * 
from bitmap_t2 where hair = 5 and work = 45;

-- Run with RULE hint to get AND-EQUAL plan
-- select /*+ rule */ * from bitmap_t2 where hair = 5 and work = 45;

set termout on
exit

Test 1 trace output (STAT lines only)

STAT #2 id=1 cnt=5 pid=0 pos=1 obj=56968 op='TABLE ACCESS BY INDEX ROWID BITMAP_T2 (cr=400 pr=362 pw=0 time=79714 us)'
STAT #2 id=2 cnt=5 pid=1 pos=1 obj=0 op='BITMAP CONVERSION TO ROWIDS (cr=395 pr=361 pw=0 time=78988 us)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=0 op='BITMAP AND  (cr=395 pr=361 pw=0 time=78694 us)'
STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op='BITMAP CONVERSION FROM ROWIDS (cr=35 pr=1 pw=0 time=11851 us)'
STAT #2 id=5 cnt=32 pid=4 pos=1 obj=56970 op='INDEX RANGE SCAN T2_I6 (cr=35 pr=1 pw=0 time=11297 us)'
STAT #2 id=6 cnt=1 pid=3 pos=2 obj=0 op='BITMAP CONVERSION FROM ROWIDS (cr=360 pr=360 pw=0 time=66579 us)'
STAT #2 id=7 cnt=357 pid=6 pos=1 obj=56969 op='INDEX RANGE SCAN T2_I3 (cr=360 pr=360 pw=0 time=65355 us)'

Test 2 trace output (STAT lines only)

STAT #3 id=1 cnt=5 pid=0 pos=1 obj=56968 op='TABLE ACCESS BY INDEX ROWID BITMAP_T2 (cr=282 pr=4 pw=0 time=10833 us)'
STAT #3 id=2 cnt=5 pid=1 pos=1 obj=0 op='AND-EQUAL  (cr=277 pr=0 pw=0 time=8651 us)'
STAT #3 id=3 cnt=38 pid=2 pos=1 obj=56969 op='INDEX RANGE SCAN T2_I3 (cr=147 pr=0 pw=0 time=4553 us)'
STAT #3 id=4 cnt=37 pid=2 pos=2 obj=56970 op='INDEX RANGE SCAN T2_I6 (cr=130 pr=0 pw=0 time=3614 us)'

Here's the result comparison


BITMAP CONVERSION cr=395 pr=361 pw=0 time=78988 us
AND-EQUAL         cr=282 pr=4   pw=0 time=10833 us

I did a couple of simple count queries to verify how many rows matched each predicate. You can also note the number of buffer accesses are required to satisfy each count.


SQL> select /*+ gather_plan_statistics */ count(*) from bitmap_t2 where work = 45 ;

       COUNT(*)
---------------
             32
SQL> @allstats_last

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  9xbjw9v72xnty, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from bitmap_t2 where work = 45

Plan hash value: 806960853
-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |       |      1 |      1 |      1 |00:00:00.01 |      35 |
|*  2 |   INDEX RANGE SCAN| T2_I6 |      1 |     32 |     32 |00:00:00.01 |      35 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("WORK"=45)

SQL>
SQL> select /*+ gather_plan_statistics */ count(*) from bitmap_t2 where hair = 5 ;

       COUNT(*)
---------------
            357

1 row selected.

SQL> @allstats_last

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  5h1vvmx6wd2j2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from bitmap_t2 where hair = 5

Plan hash value: 3466781003
-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |       |      1 |      1 |      1 |00:00:00.03 |     360 |
|*  2 |   INDEX RANGE SCAN| T2_I3 |      1 |    357 |    357 |00:00:00.03 |     360 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HAIR"=5)

From this information, you can see that the buffer gets for a single predicate match the cr values in the bitmap plan. So, what happened with the AND-EQUAL plan? The STAT line rowsource totals for two INDEX RANGE SCAN operations in the AND-EQUAL plan don't seem to make any sense at all.


STAT #3 id=3 cnt=38 pid=2 pos=1 obj=56969 op='INDEX RANGE SCAN T2_I3 (cr=147 pr=0 pw=0 time=4553 us)'
STAT #3 id=4 cnt=37 pid=2 pos=2 obj=56970 op='INDEX RANGE SCAN T2_I6 (cr=130 pr=0 pw=0 time=3614 us)'

The index T2_I3 is on the hair column. According to the counts, there are 357 rows where hair=5. So, why does the execution information show 38 rows returned (cnt=38) and cr=147? Further, why does the scan on the T2_I6 (work column) index show 37 rows returned and cr=130? That makes no sense to me at all. Anyway, as I said, I'll review the details of the AND-EQUAL plan in another post.

Let's go back to the BITMAP AND plan. I learned a lot from reviewing the trace file about how the BITMAP AND operation works. Here's some of the content that shows you what's going on:


-- Trace file excerpt

qerbtStart(8217284): started
qerbtStart(8228444): started
qerbtStart(8228b0c): started
qerbaStart(8217170): started
qerbtFetch bitmap to rowids(8217284):
qerbtFetch rowids to bitmap(8228444):
kdibcoinit(82286b4): srid=011389bb.0000

<< At this point a series of block gets were performed to read the T2_I6 index blocks >>

kdibcoend(82286b4): erid=013252ea.0007status=0

-- The following call is what shows up in the plan as the BITMAP CONVERSION FROM ROWIDS for T2_I6 (work) step
qerbtFetch out bitmap(8228444): bml 127 srid=011389bb.0000, erid=013252ea.0007 objn 56968

-- The following line shows the starting (srid) and ending (erid) rowids for rows where work = 45 (from index T2_I6)
kdibr1r2r(8217184): bml 127 srid=011389bb.0000, erid=013252ea.0007

kdibci3init(8351904): src_stream=a4bd1ac

qerbtFetch rowids to bitmap(8228b0c):

<< At this point a series of block gets were performed to read the T2_I3 index blocks >>

kdibcoend(8228d7c): erid=01325308.0007status=0

-- The following call is what shows up in the plan as the BITMAP CONVERSION FROM ROWIDS for T2_I3 (hair) step
qerbtFetch out bitmap(8228b0c): bml 1054 srid=011389bb.0000, erid=01325308.0007 objn 56968  

-- The following line shows the starting (srid) and ending (erid) rowids for rows where hair = 5 (from index T2_I3)
kdibr1r2r(8217184): bml 1054 srid=011389bb.0000, erid=01325308.0007

kdibci3init(8351938): src_stream=a4bd1ac

-- The following call is setting up the BITMAP AND with the info collected from the two indexes
kdibc3init(8217198): buf(16116)=83527b0, srid=011389bb.0000
kdibc3end(8217198)

-- The following call is what shows up in the plan as the BITMAP AND step in the plan
kdibr2s2r out bitmap(8217184): bml 6 srid=011389bb.0000, erid=01325108.0007   

-- The following shows the final range of rowids that will need to be fetched 
kdibr1r2r(8217298): bml 19 srid=011389bb.0000, erid=01325108.0007 
kdibci3init(82172ac): src_stream=a4bd354

-- The following call shows the number of rows to be retrieved (5) and lists their rowid
kdibc3sids(82172ac): want=57, got=5
 011389bb.0000 01138bec.0000 01138e1f.0000 01139050.0000
 01325108.0000

-- This call fetches the first rowid from the list
qerbtFetch(8217284): rowid=011389bb.0000 tobj 56968

-- DATA block cr 0x011389bb is fetched here

-- Then, the first FETCH line is emitted to the trace data.  
-- Note that it shows cr=396.  That's the total of T2_I6 index block reads plus
-- T2_I3 index block reads plus the 1 data block read that just occurred.

FETCH #2:c=15625,e=78684,p=361,cr=396,cu=0,mis=0,r=1,dep=0,og=1,tim=84895456678

-- The next series of calls gets the remaining 4 rows from the list above. 
qerbtFetch bitmap to rowids(8217284):
qerbtFetch(8217284): rowid=01138bec.0000 tobj 56968
-- DATA block cr 0x01138bec is fetched here
qerbtFetch bitmap to rowids(8217284):
qerbtFetch(8217284): rowid=01138e1f.0000 tobj 56968
-- DATA block cr 0x01138e1f is fetched here
qerbtFetch bitmap to rowids(8217284):
qerbtFetch(8217284): rowid=01139050.0000 tobj 56968
-- DATA block cr 0x01139050 is fetched here
qerbtFetch bitmap to rowids(8217284):
qerbtFetch(8217284): rowid=01325108.0000 tobj 56968
-- DATA block cr 0x01325108 is fetched here
qerbtFetch bitmap to rowids(8217284):
kdibc3sids(82172ac): want=57, got=0

-- The 2nd, and final, FETCH line is emitted showing the 4 block
-- accesses for the remaining rows.

FETCH #2:c=0,e=735,p=1,cr=4,cu=0,mis=0,r=4,dep=0,og=1,tim=84895457801

So, here's the bottom-line with BITMAP AND plans as best I can decipher from my tests:
1) The index blocks are all read first and the applicable rows (i.e. ROWIDs) matching the predicate are placed into memory. The first FETCH line emitted by the 10046 trace shows the reads (cr and pr) that count these index blocks being collected.
2) A separate memory area is associated with each INDEX that is involved.
3) The index ROWIDs are converted to bitmaps.
4) Once all the index ROWIDs are collected and converted, they are AND'ed together to produce a final bitmap that represents the range of ROWIDs needed for the result.
5) That final bitmap is converted back to a list of ROWIDs.
6) Each ROWID on the list is then retrieved. This is where the data blocks are retrieved. You'll see just a cr (consistent read) or a cr and a pr (physical read) if the block isn't already in the buffer cache.

So, what you should see is that the first FETCH (10046 trace data) will have the stats for the collection of all the index blocks needed and placing them into memory. After that, only data blocks are read.

It makes sense when I look at now. I still don't quite understand what happened in the original case noted in my earlier post, but I believe I've come a long way in my understanding of how bitmap conversion operations function.

For those geeks out there like me who really find this interesting and want to do the tests yourself, please have at it! I'd love to hear back from you if you get contradictory results or if your understanding of how the process is working is different than mine. I was able to reproduce my tests with larger data sets but it would be great if you get another set of results you would share. Let me know!

AND-EQUAL vs BITMAP AND

2008-12-29T13:15:00.000-05:00

My friend and colleague, Jan, sends me some great SQL performance issues from time to time. He and I had an email exchange around the Christmas holiday that was another interesting case so I thought I'd share it.

First a bit of background info. The problem query is running under both 9i and 10g database versions. On 9i, the query runs in about .5 secs and uses a AND-EQUAL operation. However, on version 10, the optimizer chooses a BITMAP AND operation and converts the b-tree indexes to bitmaps first. This is where the problem lies. The v10 optimizer choice to use the BITMAP AND operation increases the response time for the query to almost 1 minute (56.5 secs).

I recall that 10g deprecated the AND-EQUAL operation and will now choose this "index combine" operation using bitmap conversions instead most of the time. However, I also recall having read about performance issues occurring at times with this choice. I've seen a couple of tricks to work-around this problem. One is to set the optimizer_features_enable parameter to 9.2.0 in the session before executing the query (many times with a logon trigger). The other would be to split up the query into two queries using UNION ALL to put them together.

As you'll see below in the details, in v10 the majority of the time is spent in the plan step that converts the rowids of the b-tree index to bitmaps. But, the part that I'm not sure of (and haven't built a test case yet for review) is why the INDEX RANGE SCAN that provides the rowids to the bitmap conversion accesses so many more blocks than the range scan for the AND-EQUAL operation.

Now for the details:


select  count(item_t.poid_id0)
from    item_t                           -- num_rows 5,095,494
where 1=1
and   item_t.ar_billinfo_obj_ID0 = :1    -- num_distinct 599,778
and   item_t.ar_bill_obj_ID0     = :2    -- num_distinct 3,155,282

Bind variable values:  
:1 = 53
:2 = 10

The query under 9i uses AND-EQUAL as shown in the STAT lines from collected trace data:


STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=79 pr=23 pw=0 time=514521 us)'
STAT #2 id=2 cnt=6 pid=1 pos=1 obj=352589 op='TABLE ACCESS BY INDEX ROWID ITEM_T (cr=79 pr=23 pw=0 time=162835 us)'
STAT #2 id=3 cnt=6 pid=2 pos=1 obj=0 op='AND-EQUAL (cr=76 pr=20 pw=0 time=142399 us)'
STAT #2 id=4 cnt=21 pid=3 pos=1 obj=368525 op='INDEX RANGE SCAN I_ITEM_AR_BNFO_OBJ__ID (cr=36 pr=1 pw=0 time=13924 us)'
STAT #2 id=5 cnt=16 pid=3 pos=2 obj=368532 op='INDEX RANGE SCAN I_ITEM_AR_BILL_OBJ__ID (cr=40 pr=19 pw=0 time=101036 us)'

Under 10g, using default settings the query uses a BITMAP AND plan:


STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=9688 pr=9519 pw=0 time=56554617 us)'
STAT #2 id=2 cnt=6 pid=1 pos=1 obj=352589 op='TABLE ACCESS BY INDEX ROWID ITEM_T (cr=9688 pr=9519 pw=0 time=56554575 us)'
STAT #2 id=3 cnt=6 pid=2 pos=1 obj=0 op='BITMAP CONVERSION TO ROWIDS (cr=9685 pr=9519 pw=0 time=56554533 us)'
STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op='BITMAP AND (cr=9685 pr=9519 pw=0 time=56554480 us)'
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=0 op='BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=128 us)'
STAT #2 id=6 cnt=22 pid=5 pos=1 obj=368525 op='INDEX RANGE SCAN I_ITEM_AR_BNFO_OBJ__ID (cr=3 pr=0 pw=0 time=90 us)'
STAT #2 id=7 cnt=6 pid=4 pos=2 obj=0 op='BITMAP CONVERSION FROM ROWIDS (cr=9682 pr=9519 pw=0 time=46814337 us)'
STAT #2 id=8 cnt=530398 pid=7 pos=1 obj=368532 op='INDEX RANGE SCAN I_ITEM_AR_BILL_OBJ__ID (cr=9682 pr=9519 pw=0 time=67896016 us)'

Notice how both of them use INDEX RANGE SCAN I_ITEM_AR_BILL_OBJ__ID, but the bitmap plan is doing 9682 cr against 40 for the and-equal. Why would that be the case?

My guess for the increased cr's comes from seeing cnt=530398 in step 8 of the bitmap plan where the index range scan occurs. That step is returning over half a million rows from the index that then are converted by the parent step 7. The parent ends up with cnt=6. What that says to me is that the conversion happened for the half million rowids and then were filtered to end up with only 6. But in the and-equal plan, the index range scan only returned cnt=16. I can see the behavior, my question is why? What's really going on? If any of you have the answer to that question, I'd appreciate hearing from you. I promise to test it for myself too! ;)

Performance optimization (or not) on a snowy day

2008-12-19T13:32:00.000-05:00

A friend sent me this and it totally cracked me up. It's an excellent example of how working on the wrong thing is a bad idea. It doesn't matter how good you think the results of your efforts are, if you've been working on the wrong thing, you've just wasted a lot of time and effort. This also goes to show you that optimizing performance is not just a database challenge, but an every day challenge!

Enjoy!

Unsubscribe

2008-12-01T22:57:00.000-05:00

Today I tried to unsubscribe myself from a mailing list that I'm not sure how I got on. I'm typically very careful about where I use my main work and personal email addresses so as to keep my Inbox from being flooded with junk email. But, I still occasionally find myself receiving emails that I just don't want to receive.

This was the case today when I attempted to unsubscribe from one such list. I clicked on the Unsubscribe link provided in the email and was taken to a page that showed my email address and a list of about 30 different mailing lists that I was (or could be) subscribed to. Out of the full list, two check boxes were marked. Since I didn't know about the other one that was marked, I thought this was good fortune that I could now unsubscribe to both on this one screen. So, I unchecked the boxes and selected the radio button at the bottom of the page that was labeled "Please remove me from all the above lists" and clicked Update.

I was a bit disturbed that I didn't get any confirmation message, but closed the browser and went about my business. Less than 5 minutes later, I got a rush of about 30 emails in a row. Yes, that's right...30. Guess where they were from? They were all email lists that were on the list where I'd just requested to be removed from all lists.

Well, that's precisely the opposite behavior that I wanted and expected. Instead of being removed from all lists, it appeared that I had been added. After a moment of thought, I decided that perhaps I had uncovered a bug in the unsubscribe program and created the problem myself by unchecking the boxes before I selected the "remove from all" option. I'm not happy at this point, but OK...I can fix this...I'll just unsubscribe again.

So, I click on the Unsubscribe link in the first email of the 30 I'd just received. This time I'm presented with an entirely different list of about 30 other mailing lists with the difference now being that the one list I want to unsubscribe from is one of the 30. Crap! I'm now thinking that means that I may actually have to go to all 30 emails and unsubscribe individually from each. Sigh...

This time, I leave the box beside the list I just received an email from and notice that at the bottom, there is another choice (besides the remove from all lists one). It says "Please remove me from all lists affiliated with the sender". I'm a bit afraid after my previous experience but I think "hey...maybe this will save me from having to go to all of the 29 other emails and unsubscribe there". So, I choose that option and click Update. Once again, I do *not* get a confirmation as to what changes, if any, were made from my update request. So, I decide to just keep the browser window open for a while and wait and see if I get another rush of emails.

After 10 minutes pass, I'm starting to feel pretty happy that I'd safely unsubscribed and closed the browser window I'd kept open just in case. I also deleted the 29 other emails I'd received. It's lunch time so I leave my computer for about 45 minutes.

Upon my return from my lunch, I notice the unread email count in my Inbox says 67. What the heck? As I looked over the list of emails, they were all emails requesting that I "click on this link" to confirm my request to unsubscribe to that particular list. I'm usually a very patient and easy-going person, but this is just too much for me. If I had access to whoever was sending me on this wild goose chase, I'd throttle them! I'm NOT happy. As a matter of fact, I'm royally hacked off! All I wanted to do was unsubscribe to one list and now I've been subscribed to a boatload more that all want me to confirm my desire to unsubscribe. Crap, crap, crap!

I swear to you that if I had clicked on the link to confirm my choice to unsubscribe and had been taken to that same screen again, I would've lost it. Fortunately for me (and my nearby possessions which would likely have suffered from my wrath), I was simply taken to a screen that showed my email address and a message saying that my email address would be removed from the list within 48 hours.

I had to do this 65 times (the other two emails in my Inbox were actually real business stuff!). I got through all this mess in less than an hour of my time, but that's nearly an hour out of my day that could've been much more productively spent! I wish I could bill whoever these folks were behind this fiasco for my lost time. Geesh!

The bottom-line here is that the whole process was absolutely not warranted. The list holder should've been able to unsubscribe me immediately from the original link in the original email. Unsubscribe means unsubscribe! Don't ask me about 30 other lists or if I want to subscribe to others, or be removed from all, or any of that stuff. Just take my address OFF the stupid list I've just clicked on the link about. What's so hard about that?!? And, why oh why, in this day and age, do I have to wait up to 48 hours before you will take me off your lists?! I get the whole distributed processing thing, but come on people! If they can send out new, "welcome to our list" emails almost immediately to new subscribers, these folks can certainly take my email address out of their system equally as fast. Besides, who's going to be more upset? Me, who wants off the stupid list but keeps getting emails after I've requested to be unsubscribed, or a new subscriber who has never received anything from you yet at all and has to wait a day or two?

I don't know about you all, but I'm sick and tired of being subscribed to lists I've never heard of. There are a few sites from which I have requested to be notified, but there are multitudes more that I care absolutely nothing about. All I feel I can do is scream out to the universe to STOP THE MADNESS! If you hear a sonic boom coming from the Pacific Northwest in the near future, it may just be me...imploding after having received another unwanted email.

In the meantime, I'm just doing some deep breathing exercises and trying to stop my mental thrashing of this particular list vendor. Surely it can't be good for my blood pressure. Sigh....

The Charles Schultz Philosophy

2008-11-21T12:45:00.000-05:00

The following is a philosophy of life attributed to Charles Schulz, the creator of the 'Peanuts' comic strip. You don't have to actually answer the questions to get the point. Just ponder on them.

1. Name the five wealthiest people in the world.
2. Name the last five Heisman trophy winners.
3. Name the last five winners of the Miss America pageant.
4. Name ten people who have won the Nobel or Pulitzer Prize.
5. Name the last half dozen Academy Award winners for best actor and actress.
6. Name the last decade's worth of World Series winners.

How did you do? The point is, few of us remember the headliners of yesterday. These are no second-rate achievers. They are the best in their fields. But the applause dies. Awards tarnish. Achievements are forgotten. Accolades and certificates are buried with their owners.

Here's another set of questions. See how you do with these:

1. List a few teachers who aided your journey through school.
2. Name three friends who have helped you through a difficult time.
3. Name five people who have taught you something worthwhile.
4. Think of a few people who have made you feel appreciated and special.
5. Think of five people you enjoy spending time with.

Easier?

The lesson:
The people who make a difference in your life are not the ones with the most credentials...the most money...or the most awards. They simply are the ones who care the most.

For me, I thought this was something good to ponder on the weekend before Thanksgiving. I realized how easy it was for me to answer the second set of questions and how many people have touched my life. There have been family, friends, teachers, mentors, and colleagues who have given me something of themselves. I feel very, very thankful and my life is richer.

So, what are you thankful for?

One last hurrah on database independence

2008-11-17T22:31:00.001-05:00

I had mentioned in my previous post on database independence about Toon Koppelaars presentation on a database centric approach to application development. I contacted him and he said he was going to post his presentation on his site soon. But in the meantime, I thought I'd show one of his slides which I think speaks volumes to the performance aspects of trying to keep with a database independent approach.

The slide shows:
a) If you put business logic in the mid tier, you’ll call the dbms an order of magnitude more.
b) If you also put data logic in the mid tier, you’ll call the dbms two orders of magnitude more.
c) Finally, if you also don’t use SQL effectively, you’ll call the dbms three orders of magnitudes more.

So, a single click on a "Save" button by the user can ultimately mean 100 calls the dbms has to execute to handle all the middle tier madness!

You should be able to download Toon's full presentation from RuleGen.com very soon. Give it a look...it's good stuff.

Database Independence?!

2008-11-12T22:30:00.000-05:00

OK. Call me dense. Call me biased. Call me performance conscious. But I just can't groove on the concept of database independence. You know what I mean? The whole idea of having all your application logic built into your application and the database does basically nothing...well, it just doesn't make sense. Even after hearing all the arguments, I just don't get it.

There are so many optimizations that can't be utilized when the database isn't involved. The database ends up serving back boatloads of "simple" query results (usually max of 2-3 tables per query and mostly single table queries) and the application does everything else. The application basically processes the joins, applies the constraints...everything. What would amount to simple work that could occur in minimal time for Oracle, amounts to seconds of time processing the data at the application layer. Heck...if you're gonna do that, why not just have flat files to store your data and skip the database all together!? It'd sure save you a bundle on Oracle license fees!

Toon Koppelaars recently delivered a presentation at Miracle Oracle Open World that I attended that was about a database centric approach. I hope he'll post it somewhere soon for "public consumption" as it makes an extremely strong argument for going towards a "fat" database and getting the bulk of logic and function out of the application layer. Amen brother!

I'm ranting, so I'll stop. But, I'd love to hear from you about your thoughts. I'm an open-minded kinda gal, but when it comes down to it, I find all the reasons people share with me about why they absolutely have to write database independent code and put all the code in their application layer to be just a bunch of excuses. They are excuses that cover up 1) lack of sufficient knowledge of the databases they want to use and 2) a preference for what they think is easy. I mean, when you have a stable full of developers who can code Java fast and furious, but have been taught to not care about the database, designing for a database centric approach just isn't the forte...so it doesn't happen. But, when performance problems start to occur, they want to blame the database. Again...I just don't get it.

Can anyone help me out here?

Recent Presentations

2008-11-01T18:30:00.000-04:00

I've posted my most recent presentations from Miracle Oracle Open World in Denmark and the OPP2008 (Oracle PL/SQL Programming Conference) in Chicago to SlideShare. By the way, SlideShare is pretty cool if you've never seen or used it. It's a great way to easily share presentations. You can also add audio to create SlideCasts. I'm thinking of adding an audio track over the presentations I've already uploaded, but that'll have to wait until I have more time to do so. So much to do...so little time!

I've also added a link to my blog sidebar that will access all my presentations I upload to SlideShare. So, after this post ages off my main blog page, it'll be easy to find them from the sidebar link. I plan to post all my presentations there after I've delivered them.

To access the presentation you want, just choose the name of the presentation on the right side and it will display in the view area on the left. Happy viewing!

| Get your Presentation Pack

Miracle Oracle Open World

2008-10-29T19:00:00.000-04:00

My first trip to Denmark was most excellent. The good folks at Miracle were great hosts and the event was unique in many ways. I delivered two presentations and heard several others that were superb. Jonathan Lewis delivered an excellent session (well, 2 actually) on hints and Tanel Poder's presentation on advanced troubleshooting was top notch. My only regret is that I didn't get to see all the presentations. It never fails that when I speak at a conference there is a session being delivered that I want to see at the same time as my presentation. That was once again true. I missed Robyn Sands presentation on variance, but was lucky enough to be able to chat with her about it a bit over dinner.

One of the things that I found unique about the Miracle event was the amount of time available to network, meet new people and talk. Each evening dinner was a multi-hour affair fueled by excellent conversation. Oh, and the food and drink were excellent too and were made available in very large quantity! One thing that can be said for sure about Miracle is that they don't skimp on quality or quantity!

Besides the event itself, I had the opportunity for exposure to a new place. Since it was my first trip to Denmark, I thought I'd share a few things that I noted.
1. They have some seriously cool doors there. Yep, that's right...doors. Some of the doors don't have the traditional door lock/dead bolt combination that I'm used to. Instead, the doors lock closed by lifting the handle up and the door then vacuum seals or something like that. I was too impressed by it that I didn't even bother to look closely at how the mechanism works, but it is seriously cool. I want doors like that at my house.

2. The public water closets had doors that were about 8 feet tall starting about 1 inch off the ground. OK. This may seem silly for that to be something I noticed, but being 6'4, I almost always find the top and bottom 1/3 of my body is visible in most ladies rooms. Let's just say I liked not having my head waving over the door like a giraffe for once!

3. The traffic signals not only go Green-Yellow-Red, but Red-Yellow-Green as well. That way if you're stopped, you know when the light is about to change. I'd like that in the US. I often find myself leaning to see the other side of the light so I can tell when the change is coming. New traffic signals would eliminate much neck strain on my part.

4. Danish currency is cool. Of course, practically any country outside the US has cooler money than we do. US currency is so dull. Doing currency exchange math is a bit tedious, but I pretty much figured it out: 2 million Danish kroner used to equal 1 US dollar, but now it's the other way around (given our current financial state here in the US). On the first evening of the event, the Americans were offered a collection of US dollars from our Danish friends. They gave the half dozen of us about $14 total to split amongst ourselves to help us through the current tough times. :)

5. Denmark is flat. I was told the highest points in the whole country are only about 150 and 170 meters high. Heck, that wouldn't even qualify as a bump in the road in Tennessee where I grew up! Oh, and the funny thing is that they actually call one of those places "Sky Mountain".

6. Danes are quiet, thoughtful, and reserved. Not! I've never met a more party-hearty bunch! I will say that they're fairly quiet during the day, but as dusk comes, they gear up to a whole other level. Not a dull moment! To be fair, I have to say that perhaps not all Danes are of the same genre as those attending the Miracle event, but the good folks I met sure weight the average to the fun side. :)

7. Danish hot dogs look very odd (to my American eye), but taste yummy. We stopped in downtown Copenhagen and partook of a vendor stand hot dog on the day before our departure. I didn't watch how it was made, nor look at it closely before taking a bite. It's one of those things where it's just better not to know. I found this YouTube video that after watching it makes me glad I didn't look at what I was eating!

I know there's a lot more I could add but you get the idea. I loved my trip and hope to return before too long.

Statistics musings

2008-10-17T13:49:00.000-04:00

I've been thinking about a few problems that seem to turn up with Oracle query optimization lately. Jonathan Lewis posted on one case where the high value statistic was likely "off" at times and caused the plans to switch between good and bad. I had recently seen a similar case where a new partition was added each day and query plans that involved the new partition would pick the wrong index due to a high value problem. The stats were manually set to help fix it.

Anyway, these situations and several others I've seen have got me to wondering about whether or not using dynamic stats collections more aggressively would help some of these types of problems. I mean, if stats were missing and dynamic sampling kicked in, could/would better plans be derived more frequently?

I'm going to set up a set of tests to see where these musings take me, but I thought I'd mention it first to see if anyone has any experience with doing this and what kinds of results you've seen. If you have any input, let me hear from you!

None of it works!

2008-10-08T14:10:00.000-04:00

A friend of mine sent this to me. Oh how true it is! Don't you just hate getting those cutesy emails that promise you to have eternal youth and good fortune if you only would forward the email on to 10 of your friends?

OK, I admit that I've done it once or twice. And for those of you who have been on the receiving end, please allow me to apologize. I guess there have just been moments when I really wanted what was promised to be granted to me. And if it only meant forwarding on one little email, then what's the harm, right?

Harumph! Hope may spring eternal, but there is a line between hope and not being rational.

Not that I'm against hoping and wishing for things that I want to happen, but in regard to this particular method of achieving my dreams, I think from now on I'm simply going to reply to the sender with this graphic. Then, I'll go buy a lottery ticket.

:)

Something to ponder...

2008-10-03T17:52:00.000-04:00

The courage to learn

2008-10-01T23:15:00.000-04:00

Cary Millsap recently had a great post on the use of active vs. passive voice when writing. Check it out, it's an excellent read.

Anyway, Cary made a reply to one of the comments he received that has sparked much thought on my part. His statement was:

Learning is a state transition between not knowing something and knowing it. For someone to truly learn, he has to have the courage to admit—at least to himself—that there's something he doesn't know. Some people are unwilling to do that.

Think about that for a second. What do you think? Is the capacity to learn rooted in the ability to simply admit there is something you don't know?

I think Cary's spot on with that assessment. I do believe that fear of admitting what you don't know holds you back from learning/knowing what you need to. It's like having blinders on. I prefer to believe it is fear based and not arrogance, but I think arrogance is at the heart of many people's unwillingness to seek out knowledge. I mean, what if they're proved wrong about something they've stated as truth? If you think yourself an "expert" and your ego can't stand the hit it would take if you are proven wrong, aren't you destined to be found lacking knowledge you claim to possess at some point?

Although I can't find the exact quote, I remember reading something to the effect of "A wise man knows he doesn't know everything." For me, my personal goal is to be continually in the process of learning. I love to learn. Sometimes the process is painful or embarrassing when I state something I believe to be fact and am shown that my information is either lacking or just plain wrong. But, even then, I'm glad to learn. Without coming under critique, how do I confirm what I claim I know? In the end, it's empowering for me...and humbling too. I like knowing and I like sharing what I know. But in order to know, I have to first learn. And I think that learning is the real thrill for me. Sharing what I learn is just icing on the cake.

I think it's easy to get complacent and not seek out new knowledge. Many times I see people who do basically the same job and see the same issues day after day, month after month. It's easy to get lazy. It's easy to turn around and your company is considering implementing Oracle 11 and you've never bothered to learn anything more than you knew when you learned back in Oracle 7. It's easy to assume that once you know something, that knowledge stays fixed and there's no need to "upgrade" that knowledge.

For me, I do what I do because not only do I like to mentor and teach, but I primarily love to learn. My first job was in the government sector. I was the new, young kid working with a bunch of guys who had been at their jobs for 20-30 years. When I finished projects ahead of my allotted time, they'd chide me. They told me I needed to learn to "take it easy" and that I "wasn't in school anymore" and didn't need to complete my assignments in record time or try to impress my professors. I just needed to sit back and relax and learn to "not tax my brain". I tried (for a very short while). But, I stagnated. I couldn't stand sitting still. I couldn't stand not learning and not doing more than just what I knew. I wanted more. I wanted challenge. I wanted to expand what I knew so I could do my job better and more efficiently.

In the end, I stayed in my first job for less than a year. And, I know now as I look back, it was the simple fact that I wasn't learning that prompted me to leave. I grew bored and uninterested in my job and I just couldn't stand it.

It takes courage to be a learner. It requires effort. It requires being willing to admit there's a lot I don't know. But I know for me there's no other choice. Learning is like a drug and I'm an addict.

STAT lines in 11g

2008-09-22T22:20:00.000-04:00

My friend and colleague, Ric Van Dyke, has been preparing a presentation for the UKOUG conference and in his work discovered an anomaly in the roll ups for the time values in the STAT lines emitted in 10046 trace data from an 11g database. I had seen this behavior as well but didn't follow up on it for myself since I wasn't using Oracle 11 for anything other than a bit of testing here and there.

A couple of weeks ago, I received an email from Rudi K. as a follow up to my post on Plan Execution Statistics. In some of Rudi's own testing, he came across this anomaly in the rowsource execution statistics as well and asked me about it. So, given Rudi's question and a recent discussion with Ric on the subject, I thought I'd blog about it. Rudi - I apologize it's taken me so long to properly respond to you, but here you go! :)

First, let's look at an example of what the problem looks like. Take a look at the rowsource execution statistics (from the trace file STAT lines) for the following very simple query in Oracle 10g:


select /*+ use_merge(e,d) */ * 
  from emp e, dept d
 where e.deptno = d.deptno ;

STAT #3 id=1 cnt=13 pid=0 pos=1 obj=0 op='MERGE JOIN  (cr=11 pr=8 pw=0 time=21096 us)'
STAT #3 id=2 cnt=4 pid=1 pos=1 obj=54389 op='TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=2 pw=0 time=17342 us)'
STAT #3 id=3 cnt=4 pid=2 pos=1 obj=54390 op='INDEX FULL SCAN DEPT_DEPTNO_PK (cr=2 pr=1 pw=0 time=16060 us)'
STAT #3 id=4 cnt=13 pid=1 pos=2 obj=0 op='SORT JOIN (cr=7 pr=6 pw=0 time=3539 us)'
STAT #3 id=5 cnt=13 pid=4 pos=1 obj=54391 op='TABLE ACCESS FULL EMP (cr=7 pr=6 pw=0 time=3284 us)'

In order to understand the rowsource execution statistics (the information in the parentheses at the end of each line), you need to follow one simple "rule": the values listed in a child operation roll up into their parents. The bottom-line is that the top line should be the roll up of the child operations listed below it. To understand the relationships, take a look at this tree diagram of this query's execution plan.

So, you could break down the rowsource time stats as follows (I'm only showing the time stats as these are the roll ups that appear to have problems in 11g but all the rowsource statistics roll up the same way):


-------------------------------------------------------------------------------------
| Id  | Pid | Operation                    | Name           | Time  | Self  | Kids  
-------------------------------------------------------------------------------------
|   0 |     | SELECT STATEMENT             |                | 21096 |       |       
|   1 |  0  |  MERGE JOIN                  |                | 21096 |   215 | 17342+3539 
|   2 |  1  |   TABLE ACCESS BY INDEX ROWID| DEPT           | 17342 |  1282 | 16060 
|   3 |  2  |    INDEX FULL SCAN           | DEPT_DEPTNO_PK | 16060 | 16060 |     0 
|   4 |  1  |   SORT JOIN                  |                |  3539 |   255 |  3284 
|   5 |  4  |    TABLE ACCESS FULL         | EMP            |  3284 |  3284 |     0 
-------------------------------------------------------------------------------------

So far, so good. Now, let's look at the same info from 11g.


STAT #3 id=1 cnt=13 pid=0 pos=1 obj=0 op='MERGE JOIN  (cr=6 pr=3 pw=3 time=166 us cost=5 size=767 card=13)'
STAT #3 id=2 cnt=4 pid=1 pos=1 obj=71193 op='TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=2 pw=2 time=53 us cost=2 size=80 card=4)'
STAT #3 id=3 cnt=4 pid=2 pos=1 obj=71194 op='INDEX FULL SCAN DEPT_DEPTNO_PK (cr=2 pr=1 pw=1 time=36 us cost=1 size=0 card=4)'
STAT #3 id=4 cnt=13 pid=1 pos=2 obj=0 op='SORT JOIN (cr=2 pr=1 pw=1 time=25 us cost=3 size=507 card=13)'
STAT #3 id=5 cnt=13 pid=4 pos=1 obj=71195 op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=1 pw=1 time=84 us cost=2 size=507 card=13)'
STAT #3 id=6 cnt=13 pid=5 pos=1 obj=71200 op='INDEX FULL SCAN EMP_DEPT_IDX (cr=1 pr=1 pw=1 time=29 us cost=1 size=0 card=13)'

The plan is a bit different in that it used an INDEX FULL SCAN for the EMP table access but otherwise, it's basically the same.

Here's the break down of the rowsource time stats:


--------------------------------------------------------------------------------------
| Id  | Pid | Operation                     | Name           | Time  | Self  | Kids  
--------------------------------------------------------------------------------------
|   0 |     | SELECT STATEMENT              |                |   166 |       |       
|   1 |  0  |  MERGE JOIN                   |                |   166 |    ?? | 53+??
|   2 |  1  |   TABLE ACCESS BY INDEX ROWID | DEPT           |    53 |    17 |    36 
|   3 |  2  |    INDEX FULL SCAN            | DEPT_DEPTNO_PK |    36 |    36 |     0 
|   4 |  1  |   SORT JOIN                   |                |    25 |    ?? |    ??   
|   5 |  4  |    TABLE ACCESS BY INDEX ROWID| EMP            |    84 |    55 |    29 
|   6 |  5  |     INDEX FULL SCAN           | EMP_DEPT_IDX   |    29 |    29 |     0
--------------------------------------------------------------------------------------

Hmmmm... Something's wrong here. Notice on step 4, the SORT JOIN operation's total time is only 25 microseconds. How can that be possible if it's child operation takes 84 microseconds? The MERGE JOIN step shows a time of 166 microseconds. How can I be sure of that total since step 4 appears wrong (i.e. it doesn't appear to include it's child operation totals)?

Well, if we go with Ric's theory, the problem is with the SORT step (id = 4). It appears that Oracle is no longer rolling up totals when a SORT type of operation occurs. Instead, it maintains that operation's statistics independent of its children. If that's the case, then to get the correct timing roll up, we'd need to add the time from steps 2, 4 and 5. That would be 162 microseconds. That would mean that the MERGE step would take 4 microseconds.

I'm not sure that's really how it's working, but the math seems to work out fairly close. Ric says:

What appears to be happening is that the SORT is now done "while" the child steps are accruing. I think the best way to describe this new behavior is that sorts now happen simultaneously with the child steps. I don't believe that this is a parallel action, it is an action that happens simultaneously. There is a subtle but distinct difference.

Regardless of if there's a resolution or not (perhaps we need to change the way the roll ups are done for SORT type operations which include not only SORT-MERGE joins, but queries that have ORDER BY and HASH joins as well), we do need to take note of the change. I've depended on these rowsource stats quite frequently when optimizing SQL and would love to really understand why this behavior is occurring and how to account for it in trace data.

If anyone has anything more concrete on this, I'd love to hear from you and I'm sure Ric would too!

Miracle Open World 2008

2008-09-22T09:00:00.000-04:00

I'll be presenting at this year's Miracle Open World in Denmark and was asked to prepare a video about my presentation 'Are you an astronaut or a monkey? The Oracle Advisors from a different perspective'. I sure had fun making it. I have a feeling MOOW will be even more fun!

Where in the world is Karen?

2008-09-21T18:16:00.001-04:00

Sorry to be absent for so long. I've been teaching all day for the past two weeks and have one more week to go before I catch a bit of a break. It's been great, but it doesn't leave me with much energy or brain function to post in the evenings. :)

So, in lieu of something more profound, follow the visuals to know where I've been hanging out.

Canada

Ontario

Near Toronto which is near this gorgeous landmark

In the city of Waterloo

I'm having a terrific visit, eh. :)

Teacher's Day

2008-09-05T13:40:00.000-04:00

Today is Teachers' day in India. Every year on this day I receive an email from a student of mine who lives in India sending his thanks and also to ask for my "teacher's blessing" for him and his endeavors. This has been going on for about 4 years now and I look forward to it.

In India September 5th is celebrated as Teachers' day as a mark of tribute to the contribution made by teachers to the society. This date is the birthday of a great teacher, Dr. Sarvapalli Radhakrishnan, who was a staunch believer of education, and was the well-known diplomat, scholar, president of India (elected in 1962) and above all a teacher. You can read more about Teachers' day here.

I love to teach. It's truly a passion. I think it is true that teachers are also the most frequent and enthusiastic students. I know I am. I love to learn. And, even when I am teaching, I'm learning. My students often impart as much helpful information to me as I do to them. How great is that?!?!

In any case, I wanted to take a moment to thank my teachers...all of you. I know my life has been shaped by all the wonderful teachers I have had.

I got to speak to one of my most favorite teachers just last week after more than 25 years. It happened out of the blue when my sister, who is a physical therapist and lives and works in Tennessee, called me during the day and said "I've got someone here for you to say hi to." She then handed the phone to someone who asked if I remembered her. Obviously, I was clueless and so she gave me a hint: "I had you in class for two years in a row." Ah-ha! It was Mrs. Crossno! She was my Latin teacher during my freshman and sophomore years in high school. Wow. As it turns out she was visiting my sister's office for some physical therapy and when she asked about me, Amy just called me up and handed her the phone.

It was very, very sweet to speak to her again after all these years. She retired just a couple of years ago but had remembered me all these years and even kept a little cartoon I drew way back then posted on the bulletin board in her classroom. It was a copy of a Peanuts cartoon showing Charlie Brown with his little sister Sally. Sally was resisting the idea of going to kindergarten and the caption read 'Well, all right, I'll go - but I'm not going to learn Latin.' Mrs. Crossno had laminated it and kept it in her classroom all those years.

She made quite an impact on me that's for sure. Two years of Latin, a "dead" language, equipped me with knowledge I used through college and still find skills I learned then coming in handy now. She taught us etymology (the study of word origins) and from that, I have been able to break many words down into their Latin roots so that I could get at least some understanding of their meaning when the word was new to me. And, it wasn't just what she taught me, it was how she did it. She made it fun. She was encouraging and supportive. She knew her stuff but never made you feel stupid for stumbling. She was an inspiration and gave so much of herself that it made you want to give back all you could.

So, today on this Teachers' day, I want to thank Mrs. Crossno and all my teachers throughout the years. You have blessed me in so many ways.

I'm truly honored

2008-09-04T23:14:00.000-04:00

I received an email this past week with the following (partial) content:

I am pleased to inform you that you have been nominated for the Oracle ACE award. You have been chosen based on your significant contribution and activity in the Oracle technical community. Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a willingness to share your knowledge and experiences with the community.

I am pleased and very honored to join the ranks of Oracle ACEs and will make it my continuing goal to be deserving of it.

Too big?

2008-09-01T14:30:00.000-04:00

What do you think?

Did I make the wrong choice when I bought the super jumbo size tub of "I Can't Believe It's Not Butter"?

Let me help you visualize the size a bit better. Here's the tub next to a cream cheese container that is the same size (8 oz) as my previous size butter tub.

I'm thinking that my logic was a bit flawed when I decided on the 45 ounce size. I thought it was smart. The super jumbo tub was on sale and only cost 50 cents more than the small size. It seemed to me to be an easy choice. The big tub would equate to over 5 of the smaller size and cost only 50 cents more. Sounds like a great deal, right?

However, the problem with my choice became evident when I got home with my purchase. Where was this gigantic tub o' butter going to fit in my refrigerator? I ended up having to rearrange an entire shelf to get the thing to fit. Of course, the arrangement I came up with meant that if/when I wanted to get out the butter, I'd have to get out several other items just to be able to pull the big guy from its spot. Sigh...

Sometimes I suppose you just shouldn't go for what seems to be the obviously better deal, huh?

slide:ology

2008-08-29T22:18:00.001-04:00

My latest read is a terrific book called slide:ology by Nancy Duarte. There is also a site by the same name with lots of on-going material related to the book which is filled with content from the folks at Duarte Design. This book is going to be right next to my copy of Presentation Zen by Garr Reynolds that I posted about back in May.

I'm loving this book so far. It has some great stuff that continues to feed my desire to be a better, more effective and compelling speaker. I've become fascinated by the use of design principles to guide my presentation designs. I've had an opportunity to try out my new presentation style a couple of times now and I find the audience is more engaged and asks more questions and provides more comments and feedback at the end.

So, if you're tired of the same old boring PowerPoint presentation design and delivery that we're all so used to (and tired of), give this book (and Presentation Zen) a read. It'll change you...

What are you doing?

2008-08-27T00:19:00.000-04:00

One of my favorites stories goes like this:

A man came upon a construction site where three people were working. He asked the first, What are you doing? and the man answered, I am laying bricks.

He asked the second, What are you doing? and the man answered, I am building a wall.

He walked up to the third man, who was humming a tune as he worked and asked, What are you doing? and the man stood up and smiled and said, I am building a cathedral.

In my work, I'm like the third guy. I wake up every day smiling because in my own way, I get to build a cathedral. It's about the bigger picture, the higher view, the life choices that have brought me to a place where every day I get to do what I truly love to do.

How about you? What are you doing?

Making your case

2008-08-19T18:00:00.000-04:00

I am surprised at how often I've been on site with a customer and find out that the recommendations I end up making were actually already suggested by the DBAs and/or developers on staff. I wonder why the suggestions of the employees aren't heeded and a consultant gets called in instead? It would seem that the company could have more effectively used the money and time spent to have a consultant tell them the same thing their employees are already saying.

So, how does the staff DBA or developer get heard? It may not even be that what they have to say is set aside in lieu of a consultant's opinion. It may be a case where a DBA or developer suggests doing something that hasn't been done before and they can't get buy in from their managers and colleagues. Why?

It's an interesting issue. My experience has been that many times I will be able to make recommendations or solve problems that on site employees hadn't been able to work out for themselves either due to lack of experience or lack of time to devote to solving the problem. Fortunately, I don't think I've ever left behind recommendations that failed and cost the company I intended to help, but I can't even count the number of times that my recommendations mirrored those that one or more employees had already made.

So, how do you make your case? How do you get heard? Do you quit and contract back in as a consultant making 4 times more money? I've seen that very thing happen, haven't you? Do you offer to quit if your recommendation fails? Hardly.

I think there are a few things that can help anyone make a case for their suggestions/recommendations:

Outline the business case or situation in detail and note the business goal. Keep in mind that a business case is really a tool that looks at financial considerations. No matter what other elements may be viewed as relevant to the decision, if they can't be expressed in terms of their financial impact they'll have no influence on the results of your case.

Provide meaningful options to accomplish the task/project. If the question to be answered can only have a "yes" or "no" option, it's easy to point to the winner. However, when you need to ask "which way" something will be accomplished, it is important to provide the options to allow the reviewers to step back and review options that reflect the most relevant means of accomplishing the business' goals.

State the full costs of the task/project and any on-going support and of transition. This isn't just about the cost of doing the project, but costs of supporting it once it's over. Things like licensing costs, staffing costs and costs of ongoing problems the project didn't intend to fix (perhaps only postpone). The full cost isn't just the cost to do the project, but is the full cost to the business of making a specific decision.

Show both the good and bad impacts of change. It is possible that along with the positive consequences of the project, there may be some negative consequences as well. This could include positions needing re-defined, transition costs and ongoing process problems that the project won't resolve.

Ensure the benefits of your recommendations are attainable...and can be measured. Be able and prepared to show before and after comparisons.

Know what metrics play a role in the decision-making process (and how). There are a host of metrics (some concrete and some fuzzy) that are considered when any decision must be made. And many times there is no collectively agreed upon way of measuring them. The key is understanding what your audience is looking for in terms of metrics and what they are going to make a decision based on. You need to know what they're looking for, what thresholds are relevant to them, and how they are used to seeing these metrics presented and lay them out that way.

Tell a story. The bottom-line is that your case is made by answering two fundamental questions: "Should we do this?" and "Which way is most effective to accomplish it?" Write up your recommendations and plans as if you are telling a story that answers those questions. Establish the background, introduce the problem and define why it is important to solve the problem. If you don't solve the problem, what is the consequence? What are the various way sthat you could solve the problem, and what will happen to the organization in each instance? And based on all of that, what is the conclusion your want your audience to draw and the action you recommend taking? Make sure you tell the story first, and then fit it into the template of your case.

Everyone can learn to make a case strongly and effectively. You can make a case that is polished and persuasive. In time, results will back up your proposals and consultants can be used as they should be and not just to confirm what full-time employees already know.

Strange parsing behavior

2008-08-15T13:00:00.000-04:00

My friend Jan is cool. He sends me lots of neat SQL performance issues he's working on so I canto take a look at. I love it when I get to see performance problems get fixed and have the before/after data to show the differences. Like I said, Jan is cool and sends me lots of good stuff like this.

What he sent me yesterday was interesting because I'd never seen it before. The query that was having problems was a UNION of 30 or so different SELECTs. Many of the SELECTs were similar except for the WHERE clause. Here's what Jan said in his email to me:

[Jan]
Now the fun thing is to follow it during execution. This seems to be happening:

-- parse whole query => v$sql shows sql_id child_number 0
-- start parallel slaves for
-- parallel slaves do work
-- when done, parallel slaves for this part of query wait: 'PX Deq: Execution Msg'
...
-- start new set of parallel slaves for
-- slaves wait 'cursor: pin S wait on X'
-- Query coordinator shows wait: 'PX Deq: Parse Reply' => v$sql shows sql_id child_number 1
-- parallel slaves do work
-- when done, parallel slaves for this part of query wait: 'PX Deq: Execution Msg'
...
-- start new set of parallel slaves for
-- slaves wait 'cursor: pin S wait on X'
-- Query coordinator shows wait: 'PX Deq: Parse Reply' => v$sql shows sql_id child_number 2
-- parallel slaves do work
-- when done, parallel slaves for this part of query wait: 'PX Deq: Execution Msg'
etc, etc

By the time the whole query is done we have child_number 0..30, and about 360 sessions because of all the parallel slaves!

Weirdest part is the 'cursor: pin S wait on X' plus 'PX Deq: Parse Reply' waits, they take > 8 seconds every time! Why would it parse over and over again???

When I start the query a second time, it's way faster, since those parsing waits don't happen (if the plan's still there):
First time: Selected 109 records in 712.485 Secs
Second time: Selected 109 records in 142.829 Secs
[/Jan]

The two things about this that interested me where the cursor: pin S wait on X events and the parsing waits. I had just seen the 'pin S' timed event the day before while reviewing a trace file with another customer. I find it funny how I'll often see 2 or 3 of the same kind of error in a short time frame. I guess it really is true that things happen in multiples, huh? Anyway, the 'pin S' events are related to mutex pinning issues. Admittedly, I didn't know much about that until the issue I researched the day before. Here's what I learned:

The timed event for 'cursor: pin S wait on X' (I'm just going to call this pinS for short from now on) is an event that started in occurring in v10 when Oracle added mutex pins into the scheme in addition to latches. Basically, a mutex is kind of like a latch, but just a simpler, faster version. When you get the pinS wait, it's caused by a session holding an exclusive mutex pin on a cursor for which another session wishes to get a shared mutex pin on the same cursor object. From the looks of things with your big UNION query and all the parallel slave processes, that makes sense. The p1-p3 values give you some info:

P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps

and there are a few dynamic system views that provide more information about this timed event: V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY. You just have to check those views while the problem is occurring to get the additional info.

There's not a whole lot of data on this floating around out there, but I found some interesting info from Tanel Poder and Jonathan Lewis that helped.

Anyway, the second issue was the PX Deq: Parse Reply waits. I was wondering why all the parsing was going on and guessing that if the parsing issue was resolved, that would also reduce or eliminate the pinS waits as well.

I wrote back to Jan about the small bit I knew and told him I thought that rewriting the query to combine several of the separate SELECTs together would likely help the problem. At a quick glance, there were 18 of the SELECTs that shared the same FROM clause and join conditions.

Today I did a bit more research to check out the 'PX Deq: Parse Reply' and came across a great write up at Doug Burn's blog about Parallel-specific wait events (the whole article starts here). Good stuff...check it out.

Then, just a few minutes ago, Jan sent me the update on how he was progressing.

[Jan]
After some more digging this is where we get multiple children (simplified case, I'm sure you can simulate it):

 
select /*+ test */ 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 /*+ test */ '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;

BTW, this table is partitioned, so it's using parallelism.

The original query had mostly group by's. But there where a few SUMs without group by's. That's what is causing the trouble. v$sql_shared_cursor says it's because of a PQ_SLAVE_MISMATCH. When running serially it doesn't happen. After rewriting the troubling parts to do a group by, it's back to normal. After a total rewrite, the query is much faster.
[/Jan]

Again, I love stuff like this. I thought it would be an interesting case to share.

Thanks Jan!

Mac vs. PC

2008-08-14T10:35:00.000-04:00

I saw a post recently that read as follows:

Subject: A bit of Mac vs PC hilarity

A co-worker returned recently from a developers class for some aspect of .Net. He found it quite funny when he discovered some MS employees taking the class, running Vista in a VM.

The host machines: MacBook Pro's

My MacBook Pro is also running Vista in a VM, but I rarely touch it! But, the fact that MS employees are using Macs with VM for Vista says it all if you ask me. All hail the Mac!

Pah-tay-toe, Pah-tah-toe

2008-08-08T09:00:00.000-04:00

After a brief hiatus, I figured I'd better blog a little something to let everyone know I was still out here. So, here you go...

I was reading something recently that caught my fancy in that "this is weird and kinda funny" way. It went something like this:

If deer meat is called venison and cow meat is called steak, why is chicken just called chicken?

How did the chicken miss out? Somehow if I went to a nice restaurant and the menu offered me Prime Cut of Cow instead of NY Strip, I don't think I'd find it quite so appealing. But, when I see Grilled Chicken on the menu, I'm completely fine with that. Hmmmm....

Hopefully next week I'll have something more interesting to say, but for now, I'll leave you to ponder. :)

Wordle

2008-07-28T18:30:00.000-04:00

I was directed to a cool little site called Wordle that takes text, the URL of any page that has an Atom or RSS feed, or a del.icio.us user name and turns it into a cool little graphic.

I tried it twice: once with my blog and once with the text of my Method R bio page. The one from my blog needs to have it's "nerdiness factor" toned down (this feedback according to my colleague Ron...and I agree by the way). I think the one from my bio page is pretty cool. Here they both are so you can see what you think.

From my blog:

From my bio page text:

Just for fun

2008-07-23T16:55:00.000-04:00

I occasionally see something called a meme on other blogs I read and recently saw one that I thought looked like fun so I thought I'd try it.

Here's how it works:

Using BigHugeLabs Mosaic maker to make a mosaic and Flickr to do your searches, type your answer to each of the questions below into Flickr Search. Then, using only the FIRST page, pick an image. Copy and paste each of the URLs for the images into the mosaic maker.

The Questions:
1. What is your first name?
2. What is your favorite food?
3. What high school did you go to?
4. What is your favorite color?
5. Who is your celebrity crush?
6. Favorite drink?
7. Dream vacation?
8. Favorite dessert?
9. What you want to be when you grow up?
10. What do you love most in life?
11. One word to describe you.
12. Your flickr name.

You never know what may turn up on that first Flickr page. Can you guess what some of my answers were? They may not be what you think. :)

Optimizer won't choose Index Fast Full Scan?

2008-07-22T22:10:00.000-04:00

A while back, I was asked to take a look at a situation where a COUNT(*) query was using full table scan instead of an Index Fast Full Scan. The answer turned out to be fairly simple. Can you determine the problem?

Table with about 480K rows and about 800 Mb in size.

It has the following indexes:


SQL> @show_table_indexes
Enter value for owner: aradmin70
Enter value for table: t1478

                               Column                          Column
INDEX_NAME                     Name                           Position
------------------------------ ------------------------------ --------
I1478_3_1                      C3                                    1
I1478_600000214_1              C600000214                            1
I1478_600000215_1              C600000215                            1
I1478_740002011_1              C740002011                            1
I1478_740002012_1              C740002012                            1
I1478_770000000_1              C770000000                            1
I1478_840000044_1              C840000044                            1
IT1478                         C1                                    1

When executing:


select C600000215, count(*)
  from aradmin70.T1478
 group by C600000215;

It uses the following plan:


Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=27954 Card=563 Bytes=11823)
   1    0  HASH (GROUP BY) (Cost=27954 Card=563 Bytes=11823)
   2    1    TABLE ACCESS (FULL) OF 'T1478' (TABLE) (Cost=27922 Card=481061 Bytes=10102281)

Even using a hint, the optimizer still goes for a full scan.


select /*+ INDEX_FFS(tab1) */ C600000215, count(*)
  from aradmin70.T1478 tab1
 group by C600000215;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=27954 Card=563 Bytes=11823)
   1    0   HASH (GROUP BY) (Cost=27954 Card=563 Bytes=11823)
   2    1     TABLE ACCESS (FULL) OF 'T1478' (TABLE) (Cost=27922 Card=481061 Bytes=10102281)

The index should obviously be a better choice:


select count(*) "Extents",
       round(sum(bytes)/(1024*1024)) "MBytes"
  from dba_extents
 where owner='ARADMIN70'
   and segment_name='T1478';

   Extents     MBytes
---------- ----------
       172        807

For the index:


   Extents     MBytes
---------- ----------
        32         17

What should be checked?

...and the answer is...

You should check to see if the indexed column allows nulls. If the column allows nulls, then the index can't be used to count the nulls since rows with null values are not stored for single column indexes.

A simple describe showed that the column C600000215 which was used as the index column was a VARCHAR2(100) and it did not have a NOT NULL constraint. So, the optimizer couldn't choose the IFFS.

Here's the fix that was implemented to allow the IFFS to be chosen:


alter table ARADMIN70.T1478
  modify (C600000215 NOT NULL);

This added the NOT NULL constraint to the column. Now, when the query is executed, the optimizer chooses the IFFS.


select C600000215, count(*)
  from aradmin70.T1478
 group by C600000215
 order by C600000215;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1781 Card=5340 Bytes=112140)
   1    0   SORT (GROUP BY) (Cost=1781 Card=5340 Bytes=112140)
   2    1     INDEX (FAST FULL SCAN) OF 'I1478_600000215_1' (INDEX) (Cost=605 Card=494913 Bytes=10393173)

Of course, if the column had to be allowed to have nulls then the solution would've required some more thought. But fortunately, this one worked fine.

Code formatting/Syntax Highlighting

2008-07-21T17:50:00.000-04:00

I've been trying for a while to find a way to get a code formatting utility to work on my blog. I'd originally seen it in WordPress where there's a built in tag to handle code formatting. But, no such thing exists natively for Blogger.

I found a Google Code utility that I had tried to get added without much success but today, my colleague Ron got it working for us to use in our new forum (coming soon!). Now, with a couple of additions to my Blogger template, I'm able to use a slightly modified pre tag to get my code blocks formatted quite nicely.

All I have to do is enclose my code like this:


  <pre name="code" class="sql">
  </pre>

and I'm all set. To get this example to show up, I had to use "& lt" instead of the actual pre start and end tags (using <). That's good to know as it allows you to also display html as formatted code text and not be interpretted.

Cool stuff. The piece that I needed to get all this working was related to javascript, so when Ron got that part handled, I was in business!

Check it out if you're looking for a way to format your code in a nice, clean way.

Anonymous

2008-07-18T13:00:00.000-04:00

I've been watching the continuing comments related to Tom Kyte's recent post which referenced Cary's recent post on Christian Antognini's new book. There are a couple of things that fascinate me about the whole exchange: 1) all the comments about Cary are happening over at Tom's blog and 2) the person who is firing at Cary is "Anonymous".

To the first point, it really just cracks me up that Tom's very short and simple post about liking something Cary wrote for his foreward to Chris's book has received all the comments. Why, I wonder, didn't "Anonymous" make comments on Cary's blog since it was Cary's words that he took exception to? Odd...

To the second point, I have to rant a bit on the whole idea of anonymity. First let me say that I don't like it. In the context of posting to public forums or blogs or where ever a person chooses to share their opinions and comments, it seems unnecessarily covert to not identify who you are. I'm interested to know what the fear is that makes someone not want to identify themselves. Is it fear of reprisal? Is it fear of looking "dumb"? What?

It reminds me of a couple of quotes:

"I disapprove of what you say, but I will defend to the death your right to say it."
S. G. Tallentyre

"If you aren't going to say something directly to someone's face, then don't use online as an opportunity to say it. It is this sense of bravery that people get when they are anonymous that gives the blogosphere a bad reputation."
Mena Trott

I think there is a place for anonymous commentary. Many times people won't tell you the full truth because they actually like you and think it may hurt your feelings or something like that. But they feel OK to share when they know they won't be identified.

One of the best personal review techniques I've ever heard of is the 360 degree feedback process. It is an review process that involves having co-workers, managers, and as many people as possible involved in providing feedback to a single individual. All their feedback is done anonymously. Each person that participates fills out detailed questionnaires about the person and the results are compiled to provide a very specific report back to the employee about their performance as other people see it. Think about it. If you were asked to talk about your manager (remember, this is the person who does your review and grants you raises and approves your time off requests) many people are less than totally forthcoming when answering questions if they think their feedback will be known to the boss. But, when they are assured their anonymity, they feel free to truly speak their minds.

OK. I get that. And, I'm sure I could find other instances when anonymity can serve a very good purpose. But, I don't see how remaining anonymous when you make a blog post or comment is valid. There's another quote that I heard recently but don't remember it exactly that goes something like, if you wouldn't sign your name to something you say, then don't say it. I think that is right on.

We live in a country where we are free to express our opinions. Only truly inflammatory speech is prohibited. But, even then, you've got to really be over the top for anything you say to be legally actionable.

So, why not own what you have to say? If you're not willing to own it, then just plain don't say it. Lurk on the blogs and keep your opinions to yourself. I may not like what you say. If I don't like it, I can add my voice to the conversation in opposition. But in order to engage in true, meaningful, open debate and discussion, I truly don't see how remaining anonymous serves that purpose.

From the start, an anonymous poster is (at least by me) viewed with suspicion. For the most part, I either ignore or gloss over their comments. But, as I believe so often happens, many people who post anonymously tend to have a recognizable pattern in how they write. So, after a while, the anonymous person is actually recognized...even if it's only a recognition that it's the same person's "voice". Sometimes the anonymous voice can be cross-matched to an actual person from places where they have chosen to identify themselves. Writing style is similar to a fingerprint and can often be used to identify the writer even when they post without identifying themselves.

But it is hard to completely ignore the anonymous commenter. When they have things to say that you don't understand or that you disagree with, you want to respond. But, it just feels "off" to respond to a "nobody".

I do support anyone's right to say things they think/believe even if it is in opposition to my own thoughts/beliefs. I just wish people would own their comments with their real identities. I think it speaks to strong character and intent when you are willing to identify yourself.

I recently read something about how your name is your brand. If you hide your real name behind an anonymous label or even some made up screen name (for example, JoeCoolDBA or SmartGuy), you are not presenting your brand and that creates confusion and waters down your name.

So don’t be tempted to bury your own beautiful name. Love it. Own it. Flaunt it.

With that said, this is Karen Morton, signing off for today.

Analytics to the rescue - followup

2008-07-17T16:55:00.000-04:00

I thought I'd do a follow-up post to yesterday's "Analytics to the rescue" post as the discussion in the comments brought up a couple of things that needed more info than could be easily included in the comment section. So...

What you'll see below is a more complete text of the tests I executed that were discussed in yesterday's post. First, the original query, then for the rewritten query. What I show for each is the actual STAT lines from the 10046 trace data, the EXPLAIN PLAN and some additional stats (collected with a SQL test harness set of scripts I use).

Test for original query


select utrsotp_desc
  from utrsotp, ucbsvco
 where ucbsvco_sotp_code = utrsotp_code
   and ucbsvco_dispatch_date =
                                (select max(ucbsvco_dispatch_date)
                                   from ucbsvco
                                  where ucbsvco_cust_code = 1320908
                                    and ucbsvco_prem_code = '507601'
                                    and ucbsvco_stus_code = 'C'
                                    and ucbsvco_dispatch_ind is not null)
   and ucbsvco_stus_code = 'C'
   and ucbsvco_dispatch_ind is not null


STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=106693 r=59924 w=0 time=19518419 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=74130 op='TABLE ACCESS FULL UCBSVCO (cr=106691 r=59924 w=0 time=19518381 us)'
STAT #3 id=3 cnt=1 pid=2 pos=2 obj=0 op='SORT AGGREGATE (cr=13 r=0 w=0 time=145 us)'
STAT #3 id=4 cnt=2 pid=3 pos=1 obj=74130 op='TABLE ACCESS BY INDEX ROWID UCBSVCO (cr=13 r=0 w=0 time=138 us)'
STAT #3 id=5 cnt=9 pid=4 pos=1 obj=82187 op='INDEX RANGE SCAN UCBSVCO_CUST_INDEX (cr=3 r=0 w=0 time=45 us)'
STAT #3 id=6 cnt=1 pid=1 pos=2 obj=74368 op='TABLE ACCESS BY INDEX ROWID UTRSOTP (cr=2 r=0 w=0 time=22 us)'
STAT #3 id=7 cnt=1 pid=6 pos=1 obj=81903 op='INDEX UNIQUE SCAN UTRSOTP_KEY_INDEX (cr=1 r=0 w=0 time=12 us)'


Explain Plan
--------------------------------------------------------------------------------------
| Id  | Operation                      |  Name               | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |    38 |  1768 |
|   1 |  NESTED LOOPS                  |                     |     1 |    38 |  1768 |
|*  2 |   TABLE ACCESS FULL            | UCBSVCO             |     1 |    12 |  1767 |
|   3 |    SORT AGGREGATE              |                     |     1 |    20 |       |
|*  4 |     TABLE ACCESS BY INDEX ROWID| UCBSVCO             |     1 |    20 |     1 |
|*  5 |      INDEX RANGE SCAN          | UCBSVCO_CUST_INDEX  |     8 |       |     1 |
|   6 |   TABLE ACCESS BY INDEX ROWID  | UTRSOTP             |     1 |    26 |     1 |
|*  7 |    INDEX UNIQUE SCAN           | UTRSOTP_KEY_INDEX   |     1 |       |       |
--------------------------------------------------------------------------------------

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

   2 - filter("UCBSVCO"."UCBSVCO_STUS_CODE"='C' AND "UCBSVCO"."UCBSVCO_DISPATCH_IND"
              IS NOT NULL AND "UCBSVCO"."UCBSVCO_DISPATCH_DATE"= (SELECT /*+ */
              MAX("UCBSVCO"."UCBSVCO_DISPATCH_DATE") FROM "UIMSMGR"."UCBSVCO" "UCBSVCO" WHERE
              "UCBSVCO"."UCBSVCO_CUST_CODE"=1320908 AND "UCBSVCO"."UCBSVCO_PREM_CODE"='507601' AND
              "UCBSVCO"."UCBSVCO_STUS_CODE"='C' AND "UCBSVCO"."UCBSVCO_DISPATCH_IND" IS NOT NULL))
   4 - filter("UCBSVCO"."UCBSVCO_PREM_CODE"='507601' AND
              "UCBSVCO"."UCBSVCO_STUS_CODE"='C' AND "UCBSVCO"."UCBSVCO_DISPATCH_IND" IS NOT NULL)
   5 - access("UCBSVCO"."UCBSVCO_CUST_CODE"=1320908)
   7 - access("UCBSVCO"."UCBSVCO_SOTP_CODE"="UTRSOTP"."UTRSOTP_CODE")


Statistics Snapshot

Type  Statistic Name                                    Value
----- ---------------------------------------- --------------
Latch cache buffers chains                          2,142,103
      library cache                                   116,725
      row cache objects                                19,592

Stats buffer is pinned count                                8
      consistent gets                                 106,693
      db block gets                                        56
      execute count                                        16
      parse count (hard)                                    1
      physical reads                                   59,924
      physical writes                                       0
      redo size                                         9,524
      session logical reads                           106,749
      session pga memory                              594,008
      session pga memory max                                0
      sorts (disk)                                          0
      sorts (memory)                                        0
      sorts (rows)                                          0
      table fetch by rowid                                 10
      table scan blocks gotten                        106,669

Time  elapsed time (centiseconds)                       2,016

Test for rewritten query


select utrsotp_desc
  from utrsotp,
                (
                select ucbsvco_sotp_code sotp_cd, ucbsvco_dispatch_date dispatch_dt,
                           max(ucbsvco_dispatch_date) over (partition by ucbsvco_cust_code, ucbsvco_prem_code) max_dispatch_dt
                  from ucbsvco
                 where ucbsvco_cust_code = 1320908
                   and ucbsvco_prem_code = '507601'
                   and ucbsvco_stus_code = 'C'
                   and ucbsvco_dispatch_ind is not null
                ) svco
 where svco.dispatch_dt = svco.max_dispatch_dt
   and svco.sotp_cd = utrsotp_code


STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=15 r=0 w=0 time=249 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW  (cr=13 r=0 w=0 time=228 us)'
STAT #3 id=3 cnt=2 pid=2 pos=1 obj=0 op='WINDOW BUFFER (cr=13 r=0 w=0 time=218 us)'
STAT #3 id=4 cnt=2 pid=3 pos=1 obj=74130 op='TABLE ACCESS BY INDEX ROWID UCBSVCO (cr=13 r=0 w=0 time=145 us)'
STAT #3 id=5 cnt=9 pid=4 pos=1 obj=82187 op='INDEX RANGE SCAN UCBSVCO_CUST_INDEX (cr=3 r=0 w=0 time=37 us)'
STAT #3 id=6 cnt=1 pid=1 pos=2 obj=74368 op='TABLE ACCESS BY INDEX ROWID UTRSOTP (cr=2 r=0 w=0 time=16 us)'
STAT #3 id=7 cnt=1 pid=6 pos=1 obj=81903 op='INDEX UNIQUE SCAN UTRSOTP_KEY_INDEX (cr=1 r=0 w=0 time=9 us)'


Explain Plan
--------------------------------------------------------------------------------------
| Id  | Operation                      |  Name               | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |    48 |     4 |
|   1 |  NESTED LOOPS                  |                     |     1 |    48 |     4 |
|*  2 |   VIEW                         |                     |     1 |    22 |     3 |
|   3 |    WINDOW BUFFER               |                     |     1 |    25 |     3 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| UCBSVCO             |     1 |    25 |     1 |
|*  5 |      INDEX RANGE SCAN          | UCBSVCO_CUST_INDEX  |     8 |       |     1 |
|   6 |   TABLE ACCESS BY INDEX ROWID  | UTRSOTP             |     1 |    26 |     1 |
|*  7 |    INDEX UNIQUE SCAN           | UTRSOTP_KEY_INDEX   |     1 |       |       |
--------------------------------------------------------------------------------------

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

   2 - filter("SVCO"."DISPATCH_DT"="SVCO"."MAX_DISPATCH_DT")
   4 - filter("UCBSVCO"."UCBSVCO_PREM_CODE"='507601' AND
              "UCBSVCO"."UCBSVCO_STUS_CODE"='C' AND "UCBSVCO"."UCBSVCO_DISPATCH_IND" IS NOT NULL)
   5 - access("UCBSVCO"."UCBSVCO_CUST_CODE"=1320908)
   7 - access("SVCO"."SOTP_CD"="UTRSOTP"."UTRSOTP_CODE")


Statistics Snapshot

Type  Statistic Name                                    Value
----- ---------------------------------------- --------------
Latch cache buffers chains                              9,098
      library cache                                     3,608
      row cache objects                                   370

Stats buffer is pinned count                                8
      consistent gets                                      15
      db block gets                                        53
      execute count                                        15
      parse count (hard)                                    1
      physical reads                                        0
      physical writes                                       0
      redo size                                         9,008
      session logical reads                                68
      session pga memory                                    0
      session pga memory max                                0
      sorts (disk)                                          0
      sorts (memory)                                        1
      sorts (rows)                                          2
      table fetch by rowid                                 10
      table scan blocks gotten                              0

Time  elapsed time (centiseconds)                          18

Difference Report


TYPE  NAME                                           original         rewrite      DIFFERENCE
----- --------------------------------------- --------------- --------------- ---------------
Latch cache buffers chains                            2142103            9098         2133005
      library cache                                    116725            3608          113117
      row cache objects                                 19592             370           19222

Stats buffer is pinned count                                8               8               0
      consistent gets                                  106693              15          106678
      db block gets                                        56              53               3
      execute count                                        16              15               1
      parse count (hard)                                    1               1               0
      physical reads                                    59924               0           59924
      physical writes                                       0               0               0
      redo size                                          9524            9008             516
      session logical reads                            106749              68          106681
      session pga memory                               594008               0          594008
      session pga memory max                                0               0               0
      sorts (disk)                                          0               0               0
      sorts (memory)                                        0               1              -1
      sorts (rows)                                          0               2              -2
      table fetch by rowid                                 10              10               0
      table scan blocks gotten                         106669               0          106669


Time  elapsed time (centiseconds)                        2016              18            1998

Original


STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=106693 r=59924 w=0 time=19518419 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=74130 op='TABLE ACCESS FULL UCBSVCO (cr=106691 r=59924 w=0 time=19518381 us)'
STAT #3 id=3 cnt=1 pid=2 pos=2 obj=0 op='SORT AGGREGATE (cr=13 r=0 w=0 time=145 us)'
STAT #3 id=4 cnt=2 pid=3 pos=1 obj=74130 op='TABLE ACCESS BY INDEX ROWID UCBSVCO (cr=13 r=0 w=0 time=138 us)'
STAT #3 id=5 cnt=9 pid=4 pos=1 obj=82187 op='INDEX RANGE SCAN UCBSVCO_CUST_INDEX (cr=3 r=0 w=0 time=45 us)'
STAT #3 id=6 cnt=1 pid=1 pos=2 obj=74368 op='TABLE ACCESS BY INDEX ROWID UTRSOTP (cr=2 r=0 w=0 time=22 us)'
STAT #3 id=7 cnt=1 pid=6 pos=1 obj=81903 op='INDEX UNIQUE SCAN UTRSOTP_KEY_INDEX (cr=1 r=0 w=0 time=12 us)'

Rewrite


STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=15 r=0 w=0 time=249 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW  (cr=13 r=0 w=0 time=228 us)'
STAT #3 id=3 cnt=2 pid=2 pos=1 obj=0 op='WINDOW BUFFER (cr=13 r=0 w=0 time=218 us)'
STAT #3 id=4 cnt=2 pid=3 pos=1 obj=74130 op='TABLE ACCESS BY INDEX ROWID UCBSVCO (cr=13 r=0 w=0 time=145 us)'
STAT #3 id=5 cnt=9 pid=4 pos=1 obj=82187 op='INDEX RANGE SCAN UCBSVCO_CUST_INDEX (cr=3 r=0 w=0 time=37 us)'
STAT #3 id=6 cnt=1 pid=1 pos=2 obj=74368 op='TABLE ACCESS BY INDEX ROWID UTRSOTP (cr=2 r=0 w=0 time=16 us)'
STAT #3 id=7 cnt=1 pid=6 pos=1 obj=81903 op='INDEX UNIQUE SCAN UTRSOTP_KEY_INDEX (cr=1 r=0 w=0 time=9 us)'

In the original query, there was some question as to whether or not the SELECT MAX() was happening for each row. I had not included the predicate information section for the EXPLAIN PLAN which would have cleared this up. Note the filter on line 2 of the plan for the full table scan.


2 - filter("UCBSVCO"."UCBSVCO_STUS_CODE"='C' AND 
           "UCBSVCO"."UCBSVCO_DISPATCH_IND" IS NOT NULL AND
           "UCBSVCO"."UCBSVCO_DISPATCH_DATE"= 
           (SELECT /*+ */ MAX("UCBSVCO"."UCBSVCO_DISPATCH_DATE") 
              FROM "UIMSMGR"."UCBSVCO" "UCBSVCO" 
             WHERE "UCBSVCO"."UCBSVCO_CUST_CODE"=1320908 
               AND "UCBSVCO"."UCBSVCO_PREM_CODE"='507601' 
               AND "UCBSVCO"."UCBSVCO_STUS_CODE"='C' 
               AND "UCBSVCO"."UCBSVCO_DISPATCH_IND" IS NOT NULL))

The SELECT MAX query is part of the filter on this step. That means that it will get executed once for every row that is examined. One thing to note is that it is most likely that there will be subquery caching that occurs since the MAX query remains the same. In other words, the MAX query will be executed once and the answer stored in a cached array so that it really won't get executed every time, only when the inputs are new. And, in this case, the inputs are always the same so the answer will be retrieved from cache on every check except for the first one. I've got a really good example of how subquery caching works that I'll post on another day. :)

So, the plan chosen does show that the SELECT MAX would occur as a filter for the full table scan and that means it could possibly execute once for each row. It was just "lucky" that the MAX query always used the same inputs and therefore didn't really require an actual execution of the MAX query for each row evaluated by the WHERE clause due to subquery caching.

But, the thing to notice about the original plan is that it does require 2 access operations against the UCBSVCO table where one should suffice. The new analytic syntax does only one access operation on that table. That's good! If you can do something once instead of twice to get the same end result, once is better. It's like needing to go to the store to get bread and milk and you make one trip and get the bread and bring it home, then you make a second trip to get the milk. You could've gotten both items in a single trip and saved yourself a lot of time and effort. It works the same way with Oracle. Don't send Oracle to the store twice if once will do.

There were a couple of other comments I wanted to discuss as well.
From Bundit: Is there any reason to avoid using analytic function due to performance issue?

I'd say that analytic functions aren't a fix for everything. They're just another tool in the toolbox to choose from. I like the saying "just because you have a hammer doesn't mean every problem is a nail". You can't use analytic functions to fix everything, but you can use them to help reduce the resource requirements for certain classes of problems like the one I've demonstrated. They won't always be the answer but they should be one of the syntax options to test when/where appropriate. Only through proper testing would you be able to prove which way of writing a particular query would be most efficient.

From Craig M: Are the columns in the partition by necessary? It seems like it isn't adding anything since you are filtering on those columns.

In this case, you're right. Since the WHERE clause is specifying only a single cust_code and prem_code, the partition by isn't really necessary. For me, it's just a habit I'm in of always clearly indicating on which columns I want grouping to occur.

Also from Craig M: Also, I use analytic functions frequently, however I am not sure I would have ever thought to this problem in this way. Would it provide an advantage over something like


with max_ucbsvco as
(select sotp_cd
   from
    (   select ucbsvco_sotp_code sotp_cd
          from ucbsvco
         where ucbsvco_cust_code = :b1
           and ucbsvco_prem_code = :b2
           and ucbsvco_stus_code = 'C'
           and ucbsvco_dispatch_ind is not null
           and ucbsvco_dispatch_date is not null
         order by ucbsvco_dispatch_date desc
    )
  where rownum = 1
)
select utrsotp_desc
  from utrsotp
 where utrsotp_code = 
       ( select sotp_cd
           from max_ucbsvco);

The only way I can answer this now is to say we'd have to test it and see. But, I think the analytic version would still be better if this version did what I think you're expecting it to do and materialize a temp table with the result of the query against UCBSVCO. First, the plan for this version would require an "almost" sort. The use of ORDER BY to put the rows in descending order would mean the rows output from that step would need to come out in sorted order and Oracle would have to do that operation. Now, because of ROWNUM, it should do an in-line sort vs. a normal sort operation. In other words, as the rows are read, Oracle will check to see if the ucbsvco_dispatch_date column value is greater than the previous row's value. After all the rows are read, then it will have the one row which has the most current (MAX) value. So, I'd want to see how the bit of extra ordering work would be handled as compared to the analytic version.

The other thing is that if the result set is materialized for that query, there will be some small overhead to create that temp table and then read from it. That overhead doesn't exist in the analytic version.

So, my instinct on this one is that the analytic version would still win. I mean 15 LIOs and .18 seconds is likely going to be pretty hard to beat. But, it's just speculation unless I get a chance to visit that client again and test it out. :)

Thanks to everyone who commented on the original post and I hope this follow-up provides more info to clarify issues raised.

Analytics to the rescue

2008-07-16T19:20:00.000-04:00

I see a lot of SQL. Actually, I see a lot of bad SQL. I guess that's because my job is mainly to help people with performance problems and the majority of the time, the problems are rooted in one or more inefficient SQL statements. Yesterday I was doing a demo of our Profiler software and showed one case that I see over and over, so I thought I'd share it here.

I have a profile for a 4 hour and 44 minute trace of an application process that was taking too long. The top 3 events in the profile (accounting for 82% of the total overall response time) were:

CPU service, FETCH calls  8,357.40s (48.9%)
db file scattered read    3,075.95s (18.0%)
db file sequential read   2,577.67s (15.1%)

The SQL statement that topped the list taking 12,695.90s (74.3% of the total response time) was:

select utrsotp_desc
from utrsotp, ucbsvco
where ucbsvco_sotp_code = utrsotp_code
and ucbsvco_dispatch_date =
  (select max(ucbsvco_dispatch_date)
     from ucbsvco
    where ucbsvco_cust_code = :b1
      and ucbsvco_prem_code = :b2
      and ucbsvco_stus_code = 'C'
      and ucbsvco_dispatch_ind is not null)
and ucbsvco_stus_code = 'C'
and ucbsvco_dispatch_ind is not null;

Given the fact that this one statement accounted for 74% of the overall response time, with a quick look at the rest of the profile, you could see that this one query was responsible for almost all the response time consumed by the top 3 events in the profile. So, the cool thing about this profile is that it was easy to see that by fixing one SQL statement, we'd basically get rid of our dominant problem.

Now, a bit more info. This statement was executed 1,332 times during the almost 5 hour period captured by the trace. The query returned only 587 rows and did a total of 141,084,499 block accesses (LIOs). That means that on average it took almost 160,000 LIOs for each execution. Ummm...that's not good. It should be using something like 20 LIOs or less per row (based on a maximum tolerance of no more than 10 LIOs per joined table per row).

It kinda seems obvious that there's a big problem with this query doesn't it? So, why had this process been running for so long without someone catching it? I mean, if we could fix this one statement, we could cause as much as a 74% reduction in response time. That means the 4 hr and 44 minute process could drop down to about 1 hour and 15 minutes.

But the problem was in the way the code was being reviewed on the test instance. First, (mistake 1) only EXPLAIN PLAN was used to review the anticipated plan for the SQL and (mistake 2) the SQL was executed to make sure the response time (for a single execution), seemed reasonable. Another thing worth noting is (mistake 3) that the test instance had only about 10% of the volume of data as production, so virtually anything that was executed took only a second or so due to the lack of volume. So, the response time never was really noted as an issue because it always ran fast. The EXPLAIN PLAN didn't really rasie any eyebrows either because it looked like this:


--------------------------------------------------------------------------------------
| Id  | Operation                      |  Name               | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |    38 |  1768 |
|   1 |  NESTED LOOPS                  |                     |     1 |    38 |  1768 |
|   2 |   TABLE ACCESS FULL            | UCBSVCO             |     1 |    12 |  1767 |
|   3 |    SORT AGGREGATE              |                     |     1 |    20 |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| UCBSVCO             |     1 |    20 |     1 |
|   5 |      INDEX RANGE SCAN          | UCBSVCO_CUST_INDEX  |     8 |       |     1 |
|   6 |   TABLE ACCESS BY INDEX ROWID  | UTRSOTP             |     1 |    26 |     1 |
|   7 |    INDEX UNIQUE SCAN           | UTRSOTP_KEY_INDEX   |     1 |       |       |
--------------------------------------------------------------------------------------

Now, the TABLE ACCESS FULL step might have raised a few eyebrows, but since they saw Rows = 1 at that step, they figured it shouldn't be a problem. I mean, hey, if there's only going to be one row pulled from the table, they thought, how bad could it be? (Remember that they were seeing sub-second response times in test.)

But, once I was able to point out the combined effect of executing that SQL over 1300 times in their production, full volume environment, the problem was (all of a sudden) easy to see. Something needed to be done to make this SQL statement more efficient.

As I mentioned when I first started this post, I see SQL statements similar to this one over and over again. They are very typical in that lots of apps use tables that store both historical and current info in them. For instance, imagine that if you wanted to get the latest price from a product table that stored each price in a separate product row with an effective date column value used to indicate the date the price was put into effect. In order to do that, you'd have to make sure you retrieved the row which had the MAX effective date for that product. The way the query is most commonly formulated to do this is with this WHERE clause:

WHERE price_eff_dt = (SELECT MAX(price_eff_dt) FROM product_tab WHERE product_id = outer.product_id)

This is exactly what was going on in the problem query in this process.

Analytics to the rescue! By using an analytic MAX function instead, Oracle is able to make a single pass over the data to get the answer it needs. Here's the query rewritten to use an analytic MAX function instead:

select utrsotp_desc
 from utrsotp,
      (select ucbsvco_sotp_code sotp_cd, ucbsvco_dispatch_date dispatch_dt,
              max(ucbsvco_dispatch_date) over (partition by ucbsvco_cust_code,
      ucbsvco_prem_code) max_dispatch_dt
         from ucbsvco
        where ucbsvco_cust_code = :b1
          and ucbsvco_prem_code = :b2
          and ucbsvco_stus_code = 'C'
          and ucbsvco_dispatch_ind is not null) svco
where svco.dispatch_dt = svco.max_dispatch_dt
  and svco.sotp_cd = utrsotp_code;

The plan for this query was:


--------------------------------------------------------------------------------------
| Id  | Operation                      |  Name               | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |    48 |     4 |
|   1 |  NESTED LOOPS                  |                     |     1 |    48 |     4 |
|*  2 |   VIEW                         |                     |     1 |    22 |     3 |
|   3 |    WINDOW BUFFER               |                     |     1 |    25 |     3 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| UCBSVCO             |     1 |    25 |     1 |
|*  5 |      INDEX RANGE SCAN          | UCBSVCO_CUST_INDEX  |     8 |       |     1 |
|   6 |   TABLE ACCESS BY INDEX ROWID  | UTRSOTP             |     1 |    26 |     1 |
|*  7 |    INDEX UNIQUE SCAN           | UTRSOTP_KEY_INDEX   |     1 |       |       |
--------------------------------------------------------------------------------------

Tests of the two queries on a full volume data source (for a single set of bind values expected to return 1 row), had the following results:
Original query
160,693 LIOs
20.16s

New query
15 LIOs
.18s

That's a 99%+ reduction in LIOs and response time. Not bad, huh? :)

Simple change, massive difference. I didn't get to stay around long enough on the client site to see the fix implemented in production, but the expected benefit would mean, as mentioned above, that the response time should drop down from nearly 5 hours to just over 1 hour. Nice.

So, I'm always glad to see examples like this one pop up when I'm helping a customer with performance issues. The analytic function is a nice alternative to the traditional way of writing these types of queries that works extremely well and provides the performance needed for high numbers of executions in a high data volume environment.

MIN/MAX and Index Full Scans

2008-07-10T22:30:00.000-04:00

We got a question at work today from a blogger in regard to his blog post entitled MIN and MAX Functions in a Single Query are Disastrous. If you check out his post, you'll see the details of his tests. Basically, he was wondering why the optimizer would choose to do a full table scan instead of using an INDEX FULL SCAN (MIN/MAX) operation for a query that contained both a MIN and a MAX function call on the same indexed column. I thought my answer to his question to us, about why the optimizer makes such a "bad" decision about the execution plan, would be a good topic for my post today.

Normally, if you write a query that uses only one call, either MIN or MAX, the optimizer will use the INDEX FULL SCAN (MIN/MAX) optimization to read only through to the first leaf block (for MIN) and to the last leaf block (for MAX). This is a great optimization that can save hundreds/thousands of block accesses over either a full table scan or even an INDEX FAST FULL SCAN. But, as Momen's tests show, if you execute a query that has both MIN and MAX in it, the optimizer refuses to use this optimization although it would seem to us (the feeble human brain) that the optimizer should know that it could do the operation twice, once to get the first block and once to get the last block, right?

But, the optimizer can't do this. The INDEX FULL SCAN (MIN/MAX) operation will do either an index scan for the first leaf block or an index scan for the last leaf block. It can not do both at the same time. Think about it... The code to handle the operation would have to have some fairly specific logic in it to handle the dual calls.

The question to then ask is why wouldn't Oracle choose to add that logic? Well, since there's actually an easy way to get the result you want using the current operation's code path, I'd say Oracle didn't want to invest development time into writing specific case code when the simple branch of MIN or MAX can work properly as long as the developer knows how to write their SQL to invoke it.

Here's what has to happen.
This query:

select min(empno) min_empno, max(empno) max_empno
from emp;

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |   442   (5)| 00:00:06 |
|   1 |  SORT AGGREGATE       |         |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_EMP  |   917K|  4480K|   442   (5)| 00:00:06 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      196  recursive calls
        0  db block gets
     1959  consistent gets
        1  physical reads
        0  redo size
      476  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        6  sorts (memory)
        0  sorts (disk)
        1  rows processed

becomes this:

select a.min_empno, b.max_empno
from
  (select min(empno) min_empno from emp) a,
  (select max(empno) max_empno from emp) b;

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    26 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    26 |     6   (0)| 00:00:01 |
|   2 |   VIEW                       |         |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |         |     1 |     5 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PK_EMP  |   917K|  4480K|     3   (0)| 00:00:01 |
|   5 |   VIEW                       |         |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |         |     1 |     5 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| PK_EMP  |   917K|  4480K|     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        0  recursive calls
        0  db block gets
        6  consistent gets
        0  physical reads
        0  redo size
      476  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
        1  rows processed

The same results could also be achieved by doing a UNION of the two separate statements. Either way, what is required is that the developer must understand how the optimizer utilizes the INDEX FULL SCAN (MIN/MAX) optimization and write their SQL to take full advantage of it.

Perhaps at some point (I haven't tested this in 11g yet to see if the optimizer behaves any differently) the optimizer will have code path to handle both calls simultaneously, but for now, the developer must provide the optimizer with syntax to allow the optimizer to formulate the optimal overall execution plan.

To me, this is just one more really good example why the database should not be "just a black box" to developers. Writing high performance SQL requires understanding how the Oracle optimizer works, not just an ability to put together a statement that gets the correct answer.

Thanks to Momen for his post and his question to Method R on this. It was good food for thought and nice blog fodder! :)

Addendum - July 11, 2008, 8:57am
Thanks to my colleague Ron who did the 11g test on this and he confirmed that the optimizer behavior has not changed. So, even in 11g, the SQL would need to be written to submit the MIN and MAX calls separately in order to get the optimal execution plan.

Interesting survey results

2008-07-08T19:15:00.000-04:00

I read recently about a study conducted by product review online TV site, ExpoTV, where it was found that many people do actually want to "have a conversation" with a brand. Brand can mean many different things. For example, Mr. Whipple (Charmin) and Tony the Tiger (Frosted Flakes) are examples of spokespersons who become part of the brand. Logos or fonts can also represent brands. Think of the Disney font or the font used for the Harry Potter movies. Branding is also slogans, like Nike's "Just do it". And sometimes the brand is just the company name like Apple or Starbucks.

The study found:

- Consumers not only want to talk to brands, they want to establish a conversation. 55% of consumers want an ongoing dialogue with brands.

- Learning about new products in the pipeline is a top priority. Respondents were most anxious to talk to the product design (49%) department, followed by customer support (14%), marketing (14%) and pricing (13%).

- Positive brand experiences can generate word-of-mouth buzz. More than 60% of those polled said they tell 10 or more people about the products they like while a third tell 20 or more people.

- Listening leads to loyalty. 89% of respondents would feel more loyal to brands which invited them to participate in a feedback group, and 92 percent of those who have a positive experience communicating with a brand will recommend purchasing a product from that brand to someone they know.

- Consumers are open to engaging with the competition. 93 percent of consumers surveyed would be interested or very interested in communicating with competitive brands that expressed interest in their feedback if their first choice is not interested in hearing what they have to say.

I know from my own experience with my iPhone and Kindle for example, that I'm more than happy to share my experiences with others and to recommend the products based on my good experiences. I really like being able to check forums for questions and reviews and I think more highly of those companies whose products I buy that provide them.

I think this is important for any business, or even people in the public eye, to know. People want the opportunity to interact and to share. I think that's one of the reasons why blogging is so popular. Making your brand hard to interact with makes people think you've got something to hide and makes them wary.

Opening up a discussion that allows people to share anything/everything about your brand can be a bit scary. I mean, what if someone says something "bad"? In the end, I think that an open dialogue is most important and if there is something "bad", it gives the brand owner a chance to respond and make corrections and have that be seen as well. I like hearing stories where a company "made it right" with a customer. It makes you believe there is a real concern about the consumer involved and it's not just a big machine cranking out product with the only goal being revenue generation. Revenue is certainly important, but if you treat your customers in a top-notch way, the revenue will come.

Unbreakable?

2008-07-07T22:20:00.000-04:00

Oracle has had the "unbreakable" marketing spiel going for some time now. I don't know that I buy their bit, but today, I found the ultimate in unbreakable and it has nothing to do with Oracle. Move over Oracle. Meet Fisher Price.

Today I bought my daughter a Fisher Price Little People: Noah's Ark with Ark Animals set. She has the farm and loves it so I thought she'd also really like this new one since it has so many cool animals. But when I got the thing home and started to try to get it out of the box (note that the photo above shows the item completely unboxed and there is no warning on the packaging of the arduous task you face to get it that way!), it was like trying to break out of a maximum security prison. There were more twisty ties wrapped around plastic anchors bound to cardboard fortified with packing tape than you can imagine. The thing took me the better part of an hour just to get everything detached! I'm talking secure!

Oracle could take a lesson from Fisher Price I think.... If Oracle had half the "unbreakable-ness" that Fisher Price employs in their packaging, Oracle would be unbreakable indeed!

Plan execution statistics

2008-06-29T13:30:00.000-04:00

I recently saw a thread at AskTom where someone had asked a question about using tkprof for tuning a query. Tom provided an example of tracing the execution of a query and then using tkprof to summarize the trace file into a readable report and finally how to interpret what the report shows.

The part that caught my eye (and the eye of another reader) was the execution plan output display in the report. It looked like this:


Rows       Row Source Operation
---------  ---------------------------------------------------
        1  SORT AGGREGATE (cr=1840797 pr=1840758 pw=0 time=262104893 us)
128000000   TABLE ACCESS FULL BIG_TABLE (cr=1840797 pr=1840758 pw=0 time=384004887 us)

Note the statistics (in parentheses) for each row source operation. The time shown for the first step is time=262104893. This is also close to the approximate total elapsed time shown for the query (~262 seconds). But, look at the second step which is a child of step 1. It shows a time=384004887 (~384 seconds). How can that be? Child steps roll their totals into their parents so that the parent totals are all their children plus themselves. So, there is no way the child took around 384 seconds but the parent took 262 seconds. Tom offered an explanation of why the numbers were different that basically shows how where instrumentation calls are placed can cause measurement errors to creep in.

Well, while I know measurement errors do exist, I think the specific problem here isn't related to any type of measurement error. It has to do with the setting of the STATISTICS_LEVEL parameter. The STATISTICS_LEVEL parameter is set to TYPICAL by deafult, and at the default level, plan execution statistics are DISABLED. You can see this with these two queries:


SQL> select value from v$parameter where upper(name) = 'STATISTICS_LEVEL' ;

VALUE
------------------------------------------
TYPICAL

1 row selected.

SQL> SELECT session_status,
  2         system_status,
  3         activation_level,
  4         session_settable
  5  FROM   v$statistics_level
  6  WHERE  statistics_name = 'Plan Execution Statistics'
  7  /

Session    System     Activation Session
Status     Status     Level      Settable
---------- ---------- ---------- ----------
DISABLED   DISABLED   ALL        YES

1 row selected.

The first query shows my current parameter setting (TYPICAL) and the second shows what setting the plan execution statistics have at this level (DISABLED). The second query also shows that the activation level for collecting this information is at ALL.

So, I recreated the same test as Tom used and here's my plan display (note the time= values are "off", just like Tom's):


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=120 pr=0 pw=0 time=11988 us)
  28736   INDEX FAST FULL SCAN BIG_TAB_SO_IDX (cr=120 pr=0 pw=0 time=287550 us)(object id 52645)

Next, I executed ALTER SESSION SET STATISTICS_LEVEL = ALL and ran the test again. Now look what I get:


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=120 pr=113 pw=0 time=148169 us)
  28736   INDEX FAST FULL SCAN BIG_TAB_SO_IDX (cr=120 pr=113 pw=0 time=93170 us)(object id 52645)

After setting the STATISTICS_LEVEL parameter to ALL, my child time is less than the parent indicating that the parent appears to have correctly rolled up the child into its totals. Therefore, if you want completely correct plan execution statistics, you must have the STATISTICS_LEVEL parameter set to ALL. Otherwise, the only number that is correct is the topmost step in the plan (id=1).

I think the plan execution statistics are extremely helpful when evaluating query performance, so I like to have my parameter set to ALL for session testing.

However, I'd like to know why Oracle doesn't just show the child steps with null values if they are not correct. Notice that the cr and pr values seem to be correct in both cases in my example, but I think that's coincidence. I recall seeing other cases where this hasn't been true (at least in early 10 releases).

What I'd like to know is what is going on under the covers? But, lacking that knowledge, I've learned to ignore all the row source operation execution statistics except for the topmost one if the parameter isn't set to ALL.

What technological advances provide

2008-06-25T16:00:00.000-04:00

There are so many things that technological advances have added to our lives. I know many of them I take for granted. My 96 year old grandmother has seen so much change in her lifetime and I often wonder at her perspective on it all. I asked her once if she could name a thing or two that she thought were the best things she's seen become commonplace in her lifetime and she answered with "indoor plumbing" and "washing machines". My grandmother was a mother of 6 so both of these "bests" seem quite fitting. :)

But, since I've always had indoor plumbing and a washing machine handy, I was trying to think what I'd consider my top two things. I'd certainly not be very happy if I had to head to the outhouse or wash my clothes in a bucket with a scrub board, but I don't think that either of those things is something I'll ever have to revert to doing. I'll bet, if you've read my recent blog posts, you'd think I'd say the Kindle or the MacBook. :) Well, I think I can certainly say that many of the cool gadgets I use every day are in my top 10, I think what is at the top of my list is of a slightly different, although related, caliber.

First, the explosion of services that allow me to do many of the tasks I used to have to do in person to be done over the internet. Banking. Shopping. Research. I can't remember the last time I went into a bank. The closest I've come is the drive-thru ATM. My paychecks are deposited electronically, I pay all my bills with internet banking. I can order books, clothes, toys, gifts, movies, music and even order my groceries and have them delivered to my door. I can research any topic with ease and get piles of information (admittedly, some not so good) within seconds.

Second, how easily and readily I can communicate with anyone, anywhere. I have my mobile phone, internet phone, land line phone, email, blogging... The ability to connect with people is so easy. Some people feel that we've taken it too far and that the pervasive use of communication technology is taking away from the time we have instead of adding to it. I think any tool or technology can be misused so that it leads away from the problem or situation it was invented to help. But, in general terms, having this particular technology available (in my opinion) makes communicating and staying connected with people so much better and easier.

There are so many things that advances in technology bring and I can't really imagine all the things that I'll see in my lifetime if I live to my grandmother's age. But, I have to say I'm glad I'm part of this generation and not my grandparent's generation who had the experience of visiting outhouses and doing wash by hand! If the worst "remember when" I have to share with my daughter is that I once only had a corded telephone, that's OK by me.

Kindle update

2008-06-24T16:27:00.000-04:00

I posted a couple of weeks ago about my new Kindle. Since then, I've already come to love the thing so much that I can't imagine life without it. I've completed three novels and have two more downloaded and ready to read. Plus, I've purchased several of the books I had in paper copy so I've always got them handy on my Kindle. I've learned to make notes and highlight the passages that have special meaning to me or have things I want to remember. And, I've converted a couple of my technical books that were in PDF form to Kindle format and loaded them.

I had wondered if I was going to miss my books. Well, I'm still not ready to say I'm off of paper books entirely, but if I can continue to get the books I want in Kindle format, I'm beginning to think that my bookshelf is going to be much less crowded. I'm not missing my hard copy books so far. As a matter of fact, I'm really liking the ease of having them so readily available on my Kindle. I don't have to worry about transporting several books with me as I travel. For instance, I just made a trip to Dallas for the week and on my flights here, I finished one book and started another and then spent a bit of time reading sections of two others before going back to the main novel I'm engrossed in. And I did that all without doing anything other than use my thumbs!

I did check out the features of my Kindle that are under the menu title of "Experimental". This is where you can use the "free" Amazon Whispernet wireless service to connect to the web. The browser interface is pretty crude, but if you want to just read your Google, or other web mail, it works OK. But, I've decided that feature doesn't really "get me" like the book reader does. I don't envision my Kindle becoming my one do it all device, but for my books, it's aces.

I've read some reviews of other people's experience and it seems to be much the same as mine. Once you start using a Kindle, all the reasons you thought you might want to have "real" books start to disappear. Much like my switch over to a Mac, this switch may be another one that truly changes the way I do things...and very much so for the better!

---- Update to this post ----
I just read a post at Seth Godin's blog entitled Random Thoughts about the Kindle. Some of his ideas are interesting about how he thinks the Kindle could be improved that may appeal to some. I find I have mixed emotions about these "improvements" he mentions. As I mentioned above, I like my Kindle because it makes reading easier, faster and it's just cool (I love it when people stop to comment on it when they see me reading it). Seth talks about having a more interactive component that allows you to comment, see other people's comments, and alot more. But, when I'm reading an "I don't want to think" novel and just be entertained, I'm not sure the thought of stopping in the middle of a paragraph to comment on the passage and read what 40 other people are saying about it is what I want. I think I'd like that functionality available for lookup, but not taking up screen real estate on my display as I'm reading. It'll be interesting to see how Amazon and the public feels about these kinds of features and if any of them get implemented in future Kindle software updates.

Oracle Performance Problems and Root Canals

2008-06-18T22:50:00.000-04:00

Take a look at this picture. Would you know if this guy was having a toothache or a bad Oracle performance problem?

As I had a root canal this morning, I had the opportunity to ponder how a root canal and a nasty Oracle performance problem are alike. Yes, this is a stretch, but my mouth is sore and I'm in a mood...so this is it.

Anyway, when you have a problem with a tooth that requires a root canal to alleviate the pain, there are several things going on.
1) The pain is nearing the point where you're fine with any solution that will just stop the pain. You don't really care about what happens afterwards, you just want the current pain to end.
2) There is no amount of money that is too much to pay to get the relief you want/need.
3) All the soft, gentle words about how everything's going to be alright from the guy who is going to fix the problem only tick you off. (Remember #1.) Just shut up and fix it already!

Have you ever had a really bad performance problem hit and everyone is having a fit to get it fixed? Your users are screaming, your management is screaming...you know the drill, right? Think about it for a second...I think the same 3 things that happen when you're in need of a root canal are the same 3 things that happen when you've got a performance problem and it needs to be solved. I actually had a colleague say to me "this is about as fun as a root canal" once when we were working on a problem that was a very big pain to the business (and therefore to us).

Now that I've experienced both, I can say the similarities are so alike as to certainly be in the same category. But, after today's root canal, I'm of the mind that I'd much rather have the pain of an Oracle performance problem to suffer through. Well...except for a performance problem they don't give you nice pain killers and anesthetic to get you through it! Maybe the root canal isn't so bad after all... :)

MacBook convert

2008-06-17T22:22:00.000-04:00

I used to think Macs were for wimps. I used to think only graphic designers, artists and school kids used them. And people used to think the world was flat.

I have seen the light. I am now a Mac convert. I switched over in February to using a MacBook Pro and while it's taken me a while to fully leave behind my Dell laptop, I think I can officially say that I'm there. I haven't had to use my old laptop to do anything in several weeks. Everything I need or want to do is getting done on my MacBook. Yippee!

This laptop is a thing of beauty. Every time I sit down in front of it, I get happy. It just plain looks cool for starters! It did take me a bit of getting used to being in Mac-land, but now that I've made friends with Finder and got all the applications I need installed and working, I can't ever see myself going back to PC-land.

Since I didn't get my MacBook with OS X Server, I have loaded VMWare Fusion so I could have virtual machines for Windows and Linux so I could have an Oracle database install handy. But, that was no big effort and I can easily start/stop an image and even use the Unity feature of VMWare fusion to make my virtual machine "merge" with my Mac desktop so that things look like I'm in a single environment. I could've gone with BootCamp (comes with Mac OS) or Parallels, but VMWare has been a really good fit for me.

I've come a long way from the days when I thought Macs were for wimps. Not only does my MacBook Pro do everything I need it to, it even does stuff I have yet to use. So, if you're thinking of going Mac, I can highly recommend it. Once you go Mac, you never go back!

Cool....just cool!

I only thought it was the future of presenting...

2008-06-13T12:50:00.000-04:00

In my post from a few days ago, I wrote about a cool new software product, SlideRocket, and the shift I've been making to doing more visually appealing presentations. I still think SlideRocket is one of the coolest things I've seen in a while (especially now since I've gotten a beta account to play with!) but I saw a demonstration of some new technology that was amazing.

So, how'd you like to see a presentation delivered using live holographic video feeds from across the world? It sounds like Star Trek, but it's here now. Cisco CEO John Chambers, speaking live on stage in Bangalore, "beamed up" two of his senior VPs from San Jose, CA using new holographic video conferencing and they had a discussion of the future of Cisco TelePresence. Now that is cool!

Somehow, I don't think people will want to go to all the trouble and expense of holographic technology to be bored out of their minds with a holographic image in front of a boatload of dull, bullet pointed slides. It may be one of the ways that the future of presenting will be driven back to a more personal and interactive approach. Wouldn't it be cool if the hologram looked like it was looking right at you, no matter where you sat in the room? You'd feel as if you were getting a personal session with the presenter. Just the two of you sitting down for a chat. No bullet points needed.

I'm sure the technology will be a while in filtering it's way to the general public, but I was just thinking that perhaps by the time my daughter goes to college (she's 1 now), that we'll be able to talk using our holographic images. How cool would that be? I could see her just like she was standing (or sitting) in the room with me. I could fuss at her for biting her nails or for coloring her hair fuschia. No hiding behind voice only communications! Ah, the possibilities!

It's amazing to me to think of all the things that technology will bring in my lifetime. My grandmother, who is soon to be 96, has gone from living in a home with no electricity or indoor plumbing to all the modern conveniences. I can only imagine what advances I'll look back on if/when I reach her age. And maybe holographic conferencing will be just another everyday thing in my home by then. Amazing...simply amazing.

My new toy

2008-06-12T23:05:00.000-04:00

I am now the owner of an Amazon Kindle, and I am enthralled. This lovely little device is smaller and lighter weight than every book I'm currently reading in hard copy. Once you buy a Kindle version of the book you want, it is delivered wirelessly to your unit within a minute. You can sit it in your lap and click a single button to turn the page. When you're ready to stop, the unit remembers where you left off and will bring you right back to that spot when you return. You can have up to 200 books (that's what the specs say anyway) loaded on the unit and add even more with an SD card should you desire.

I was hesitant about this purchase for multiple reasons. First, I love books. Love 'em. I have shelves of them of all different genres. I love the feel of books. I love making them my own with notes and highlights. I have some books in my library that I've had for 25 years. So, I wasn't sure about an electronic book. I have a few technical books in PDF format that I like having for reference use so that I don't always have to turn to the book to find something I'm looking for. But, the Kindle would mean starting a whole different relationship with my books. Could I do it?

Second, is the cost. It's not a cheap little gadget. But thanks to a cross country trip to visit family and a deal on my credit card to get triple Amazon points, I ended up with almost enough Amazon bucks to cover the cost.

So, I took the plunge. And so far, this little baby is awesome. I haven't even begun to play with features like access to newspapers, magazines, popular blogs and so forth, but I have downloaded my first book. Oh joy, oh bliss! How sweet it is!

This could be dangerous. I can carry a boatload of books with me every where I go. No more trying to figure out which books to carry on the plane with me and which ones to pack. Everything comes with me in one conveniently small package. If I want a new book, I just type in the name, click Buy and there it is! Dangerous...yes, very dangerous.

Even non-Kindle books can be converted to be used on the Kindle with a simple, free converter program. So, that means virtually any book I can get in PDF form can go on my Kindle with just minimal effort. Hmmm...I'm liking this more and more!

I think I'll still have to buy some books in hard copy. Not everything has a Kindle version (yet). And, I don't think I can go completely cold turkey on having my hands on a "real" book. But, if my first few hours of ownership are any indication, I think I'm going to have a very long and wonderfully meaningful relationship with my new Kindle.

Optimizer NOWORKLOAD statistics

2008-06-11T17:10:00.000-04:00

I am often asked about statistics collection. People want to know the magic formula for collecting them. When? How much? But, the truth is there isn't one formula, magic or otherwise, that works for everyone. Think about it... If there were, Oracle would have already implemented it, don't you think? But hope is still alive that someday the mystery about it all will be revealed.

I think Oracle is getting better and better in its attempts to automate the process of statistics collection. But there continues to be alot of questions and discussion on how to "do it right" in the meantime.

So, I thought I'd start trying to put together some of the questions I've received and do a little research and testing to see if I can't answer some of them here in the blog. Today, I wanted to start with a few words about system statistics, particularly about noworkload statistics. Starting with 10g, noworkload statistics are always available. Even if you delete them, if the database restarts, then they are automatically regathered.

The noworkload statistics are basically a few generic benchmarks used as performance measures for your system. The stats that are maintained are:

CPUSPEEDNW
The number of operations per second (in millions) that a CPU can process.
IOSEEKTIM
Average number of milliseconds needed to find data on the disk (default 10).
IOTFRSPEED
Average number of bytes per millisecond that can be transferred from the disk (default 4,096).

I've never touched the noworkload statistics on my laptop test database so I started by checking them to see what they were (they should be the defaults from when my database was first started). I used dbms_stats.get_system_stats to retrieve the values.

SQL> @getsysstats
iotfrspeed: 4096
ioseektm  : 10
cpuspeednw: 773.806

I then executed a single SQL statement and grabbed the execution plan. The statement was a 4 part UNION.

Then, I gathered noworkload statistics to see what they would be now. I started the collection and then executed several queries in another session while they were being collected.

SQL> exec dbms_stats.gather_system_statistics (gathering_mode => 'noworkload');

Using the gathering_mode parameter set to 'noworkload' will collect the values in about a minute. I'm not totally positive, but I believe the CPU speed is likely measured by executing some sort of calibration operation in a loop and the I/O is measured by doing different sized reads on several different datafiles.

These were my new values.

SQL> @getsysstats
iotfrspeed: 29126.045
ioseektm  : 14.856
cpuspeednw: 917.075

So, I ran the same SQL and checked the plan and you can see the difference the new noworkload statistics made.

I notice two key things: 1) the cost for this plan went down, but the estimated %CPU went up. This makes sense if you look at the new statistics. The iotfrspeed was significantly larger than my original value. This would mean that the optimizer now thinks it is much less expensive to transfer data from the disk than it was previously and reduce the cost of those operations.

In my example, the optimizer still chose the same plan it originally chose, but now, in comparison to any other plan choices, this plan is even more likely to be selected due to the much lower cost.

Several people have commented to me that they've chosen not to use system stats in their 10 databases because they had trouble initially when upgrading from 9i. But, what they often don't realize is that they are using system statistics no matter what. They are simply using the noworkload variety is they disable/delete the others. And, as you can see in this simple example, noworkload stats can change and cause the optimizer's calculations to change as as result. This could mean plans change for apparently no reason. This is one of those cases when someone swears "nothing has changed" but then they tell me that the database had just been restarted for some reason. When that happened, the noworkload stats could've been recomputed. Even if that were the only thing that changed, it might be enough to cause some plans to break.

So, as I've mentioned before in a previous post: instead of insisting nothing has changed, evaluate what might have changed that might not be on your radar. Something is bound to have changed somewhere.

More stats stuff to come in later posts.

The Future of Presenting?

2008-06-09T15:45:00.000-04:00

Cary pointed me to an interview with Mitch Grasso, co-founder of SlideRocket. It's impressive. What do they do? From their web site, they say:

"We make a fully featured, rich internet application (RIA) called SlideRocket (TM). It's offered as a service with a variety of prices to suit different needs, including free!"

I've been seriously working on changing my technical presentation style to be more dynamic and visual. I posted back on May 22 about a book by Garr Reynolds called Presentation Zen that has really had a profound effect on the way I look at the content of my presentations.

Here's an example of how I took one slide out of one of my presentations and converted it to a new, more visual style.

| View | Upload your own

The "After" style requires the speaker to provide the details, but I think that's kinda the point, huh? I've heard it said before (and agree) that if your slides say everything, then why are you there? I really like the impact the "After" version will likely have on the audience. I think they'll be much more engaged with me than if I'd just popped up one detailed slide with alot of text for them to read.

I use PowerPoint. I never really thought or knew there was another option. As long as I've been doing presentations that weren't printed on transparencies (oh yeah, I did a bunch of those), I've used PowerPoint. I know there is KeyNote for the Mac, but since I've just recently seen the light and converted to a Mac (my MacBook Pro rocks!), it's never been on my radar.

But, having seen the new SlideRocket, I think they're really on to something. Creating presentations becomes more about making your point (and making it well) with lots of easy options for formatting, adding photos, dynamic content and a bunch of other nifty stuff.

What I've noticed for myself is that my presentations were always put together while thinking in bullet points. So, that's what came out on the slides. If you shift to thinking about your story...the thing you want to communicate to the audience... you immediately move away from bullet point thinking. I think SlideRocket will facilitate this process much better. Take a look at the interview and their web site and see what you think.

Famous last words

2008-06-04T12:15:00.000-04:00

Jonathan Lewis' recent blog post, entitled Nothing Changed, got me to thinking about how frequently I hear "nothing changed" in relation to a performance issue. I really do think those are quotable (and laughable) famous last words. Think about it: if everything was the same, it's not possible for something to change, right?

A query plan doesn't change without reason. Response time doesn't change without reason. In general terms, it's rare that nothing ever changes. So, instead of immediately asking "why" and claiming "nothing changed", I think it'd be much more productive to start asking "what changed?".

When we insist on believing nothing changed, we limit our options of what could have happened. We close off our creativity. We limit our mind's ability to pursue possibilities.

I think one of the best skills a good performance analyst has is the ability to see lots of options. And, I think that the reason why people, like Jonathan Lewis, Tom Kyte, Cary Millsap, and many others, are so good at what they do is that they always stay open to many possibilities. Skill and knowledge is certainly important, but the ability to look at many alternatives and not get stuck demanding that things must be "just so" is just as critical.

In the end, those who continue demanding "nothing changed" might find the famous last words of composer Ludwig van Beethoven most fitting: "Friends applaud, the comedy is finished."