I am digging this learning thing

One of the mixed blessings of Oracle is that there is always something you can learn, no matter how good you are, or how much experience you have in the field. And I still have a lot to learn.

This week I had the unique pleasure of speaking directly with an Oracle Developer about an SR I had file in regards to query. In fact, this query is what lead me down the path of last week's discovery. We have a 3rd party ERP (Banner from SunGard), and one of the tables is GJBCOLR, a "collector" table for various jobs. My understanding is that it used like a scratch table, and jobs will routinely insert rows, work on them, then delete them. Sound like a TEMPORARY table at all? I am told that SunGard needs this to be a permanent table for some reason, and I am still trying to hunt down that reason. But I digress.

This table routinely has a high water mark. In the SR I filed for Oracle, we observed that the stats pegged it with 21 million consisten reads (HHWM), even though it really had about 290 rows. Sure, we can drop the HHWM (ie, truncate table, alter table shrink, etc) as explain in my previous post, but the table will just grow again due to how Banner uses it. So my next thought was to delete the stats and force dynamic sampling. To my horror, the CBO was able to find a low cost FTS, favored over a Index access by a factor of 10. According to my developer buddy, he highly suspects this is due to a bug in dynamic sampling, since the 10053 trace shows the dynamic stats put the number of blocks at an incredibly low 95. We are talking about OSEE 10.2.0.2, and he found a bug fix in 10.2.0.3 that is only slightly related but he firmly believes it probably affects the same thing. One of the workarounds I wanted to run past the developer is faking the stats, putting them enormously high so that indexes are always favored, and he tentatively agreed. So hopefully that will solve our problem.

However, realizing what a rare opportunity I had to be talking to a developer, I asked him a few questions about the optimizer and the particular query we were looking at. I am sure a month from now I will be smacking my head about not asking him other questions, but hey.... =)

The query plan looks like this:

--------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |   210K|    50M|       | 14353   (6)|
|   1 |  SORT ORDER BY          |         |   210K|    50M|   106M| 14353   (6)|
|*  2 |   FILTER                |         |       |       |       |            |
|*  3 |    HASH JOIN RIGHT OUTER|         |   210K|    50M|       |  2412  (18)|
|   4 |     TABLE ACCESS FULL   | FTVDTYP |    54 |   378 |       |     3   (0)|
|   5 |     TABLE ACCESS FULL   | FFBMAST |   210K|    49M|       |  2387  (17)|
|*  6 |    TABLE ACCESS FULL    | GJBCOLR |     1 |    50 |       |    29   (4)|
--------------------------------------------------------------------------------

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

  2 - filter( EXISTS (SELECT /*+ */ 0 FROM "GENERAL"."GJBCOLR"
             "GJBCOLR" WHERE "GJBCOLR_VALUE"=:B1 AND "GJBCOLR_ONE_UP_NO"=4549363 AND
             "GJBCOLR_JOB"='FFRAGRP' AND "GJBCOLR_NUMBER"='01'))
  3 - access("FFBMAST_OTHER_SRC_DTYP_NUM"="FTVDTYP_SEQ_NUM"(+))
  6 - filter("GJBCOLR_VALUE"=:B1 AND "GJBCOLR_ONE_UP_NO"=4549363 AND
             "GJBCOLR_JOB"='FFRAGRP' AND "GJBCOLR_NUMBER"='01')

I include the predicate information because that becomes important later on. Not knowing much about the FILTER operation other than that it works on two children, I asked him how it works. It is almost exactly like a nested loop operation. However, he specifically said the Hash Join is a source and the GJBCOLR subquery is a probe, which indicates to me a hash join, not a nested loop. But, like a nested loop, each result of the HJ child is compared to the GJBCOLR subquery, hence the subquery is executed 210K times. I asked why the HJ child is not second (as opposed to first), and he mentioned it had to do with how Oracle used part of the GJBCOLR subquery as a filter predicate. I am not entirely positive why that is the intended result, but I hope to chew on it a bit more. As you can see, the subquery was transformed into a EXISTS clause (not present in the original), and I believe that is part of the answer.

Now if only I could talk to a developer everytime I file an SR.....

ceterum censeo

No, I am not advocating that Carthage must be destroyed. I think someone already beat me to that anyway.

But I did want to encapsulate what I can "bring home" from the RAC experience I had in Chicago. And I am obligated to include a sumptuous feast at Fogo de Chão. Vegetarians may wish to avert their eyes for the rest of this paragraph. I had heard that the salad bar was really good, stocked with fresh veggies, side dishes galore, deli meats, seafood, etc. I glanced at it once, from my chair on the other side of the room, and I saw a lot of colorful food. The rest of my attention was systematically focused on obtaining delicious cuts of meat from the passing "Gaúcho chefs". However, the, umm.... "chefs" were not really "Brazillian cowboys" at all (obviously), but it certainly sounds more impressive than "waiter". And since that is the worst of the cheese, I could live with that. But the meat! Wow! Slabs of hot, aromatic sirloin, lamb, pork, chicken or sausage passed by our table at least once a minute. They ask how you well done you want it, and they carve it off. And this is not your typical beef buffet; these are high quality cuts that are grilled in ways that Outback can only dream of. After a while, one can narrow the choices of what you like and dismiss other selections. The gaucho guys just keep coming back, again and again. I treated my dad for Father's Day; I decided that is the way to do it. =)

So, is that a good segue to bringing home the "meat" of the RAC class?

First and foremost, RAC is not a panacea. Yes, it scores a lot of cool points, but do not let that fool you into thinking it will solve your problems. The Oracle Sales people are pushing it hard because it is expensive, not because of how well it helps you attain your goals. If anything, RAC is probably most optimal for a niche market, customers who have applications that are well designed for parallelism, or at the very least, DML segregation.

After you swallow that pill, most everything else is rosy. One can work around performance issues (ie, a customer wannabe who thinks he is in the niche market) by decreasing or eliminating bottlenecks. Where will our bottlenecks be? That is probably one of the hardest questions to answer at this stage of the game, because a portion of the application is still being developed. Keep in mind that our initial foray into this field will be via a highly visible, yet low-load, online program. So, here are some items I think we should start with, so as to avoid having to worry about them in the future.

1. As much as practical, maximize disk i/o for redo and controlfiles. Put them on independent, fast, highly available disks (perhaps DMX in RAID10).
2. Provide expansion capabilities for the interconnect, either by allowing more (multiplexing) or swapping in a bigger pipe.
3. In regards to the portions of the overall application that we have direct responsibility for, work hard to focus on making the DML either segregated or parallel. Do not merely copy Banner coding methods, which would have horrifying results in the long run.
4. Be generous with the buffer cache
5. We need to decide how we want to move forward with application failover. Is it good enough to implement TAF? Or do we go with the Cadillac of FAN (using ONS)? Personally, I think FAN is like asking a electromechanical physicist to invent an automated device to rapidly accelerate a 2mm thick, 4" x 4" sheet of nylon towards a living Musca domestica. Some people refer to that as a "fly swatter".

In the context of administration, I think our group has to prepare for the coming paradigm shift. We use Grid Control a little, mostly for monitoring a couple databases here and there. That is going to change with RAC, where all the "easy" tools are distributed in a not-so-user-friendly fashion throughout Enterprise Manager. Not only that, but we are going to have to get used the concept of connecting to an instance vs administrating the database. We will have to learn srvctl, crsctl, ocrconfig, crs_stat, .... you get the picture. RAC is not merely a database with two different memory regions running two different sets of background processes; we have Clusterware, OCFS2 and ASM to monkey with. RAC does not increase the number of headaches by a factor of 2. No, it is more ambitious than that. Try a factor of 10.

I wrote in an earlier entry that we should probably take advantage of Resource Manager. Emphasis on probably. As with any and all new technologies, we have to seriously consider our business needs, and determine if the business needs drive the requirements for said technology. I am of the opinion that any "business need" that is fashioned into a generic statement like "we need 5 9's" should be fed into a shredder, burned, dissolved in acid, boiled in plasma and sent on its merry way to Sol. Ergo, ceterum censeo.

With all the documentation we have (whitepapers from Oracle and Dell, reference manuals, purchased books, recommendations from consultants and special interest groups), I am confident that we will be able to deploy RAC using Best Practices. Deploying our application in such a manner is going to be a different story.

OCM, histograms and more Oracle

I debated about how publicly I wanted to announce that I am taking the OCM. Seeing as how nobody reads this blog, if anyone ever finds out, it will be after the fact. My impression of the OCM is that it is rather tough to take, but is mostly a PR thing - Oracle gets an easy $2000, and in return, I get bragging rights with a denim shirt. Looks good for our HR department though. The thing is, there are a lot of really smart DBAs out there who could easily pass the OCM and simply have not the time or inclination to do so. I think there is also the stigma that someone who is a certified Master should not make any mistakes and knows everything. *laugh* Don't expect that from me.

So, on to histograms. I am still beleaguered by how best to describe the data. I have tossed the idea of unlimited histogram buckets to Oracle, but the rub I get back is that the overhead is not worth it. Overhead = physical storage + cost of getting bucket information. I will be the first to admit that my number theory is not the strongest - in fact, I am one of the worst kinds of methodical scientists because I am not, well, methodical. I just want to try out a 10-million bucket histogram and see if it actually is worth it for myself. In talking to Wolfgang Breitling, I see there are other ramifications to consider as well, like join cardinalities. Call me naive, but it seems that if you have 100% accurate histograms (no matter how large/how many), your join cardinalities will be 100% accurate as well. That is the problem with a little bit of knowledge - there is so much more you do not know, but you do not necessarily know it. *grin*

More Oracle: Actually, I should say, more SCT. We do a lot of work with SCT (now called SunGard Higher Education, or SHE) BANNER, an ERP from California. As with any monstrous ERP, there are a number of issues, but probably the worst is that they were the lowest bidder, and we are their largest customer. Do the math. Good for them, bad for us. And we who do the performance diagnostics and troubleshooting affirm that every day. Again, going back to Wolfgang, I wish there was an outside expert/consultant in the realm of Oracle/BANNER, a real Application DBA. At this point in my career, I am saying to myself "That's what I want to be when I grow up." Maybe not necessarily for SCT/SHE (and they sorely need them!), but .... maybe.

Another dream I have is to get inside the guts of the Oracle kernel. This is hard to do when

1. you don't work for Oracle
2. you despise the scientific method
3. you play a lot of video games

But, I find that I have a number of questions. Questions I would love to document here, when I get a chance to kick myself in the butt and update this blog more regularly. Questions of how things work, and why they were designed that way. I am fully confident that a majority of the design desicions were made by very smart people who think, breathe and live databases. But sometimes you just wonder.

So if you know of any rogue Oracle developers that teach Internal classes, let me know. *grin*