OraJourn

Debug mode for MOS

2011-07-27T08:06:00.000-05:00

Had an SR in which I learned about a debug mode for FLASH MOS (tried it in HTML, no go *grin*). Hold down the Control key and click on the Oracle My Oracle Support Logo in the upper left-hand corner:

Here is a short video, using Oracle's recommendation of CamStudio:

MOS 5.3: Search and Knowledge

2011-07-20T09:19:00.000-05:00

After my initial MOS 5.3 post, I received some most excellent feedback and an opportunity to speak with Jan Syssauw. He was exceedingly gracious and honored me with a 1-hour phone conference in which we covered many different aspects of Search. In response, I thought it would only be appropriate to follow up here.

I have a lot of respect for Jan (pronounced Yan). He is a very good listener and has a very professional demeanor, accepting my comments (and sometimes criticism) without belying how personally he cares for his team and his product. And yet he does - Jan very much wants Search and the KB to be an awesome experience, so I appreciate that he is reaching out to see what at least one user thinks.

In retrospect, I think I may have disproportionately emphasized certain features or areas by leveling the playing field (from my point of view) and perhaps giving the impression that I thought all areas were equal and required an equal degree of attention. Let me say that I wish to correct this. :) And I'll focus first on what I think the big issues are, and leave the small things for another time.

Search Suggestions

First and foremost, "Search Suggestions" are a huge improvement. I initially called these "type-ahead" or "autofill", but the proper term (so I have learned) is "search suggestions". I really like the direction they are going with this. We talked about some of the nuances; for example, the issue with versions I raised in my initial post (typing "Oracle server ent" gives me several suggestions, but none of them are 11g and only one is 10g). The reason Jan gave for this was that there are lots of suggestions that kind of fall off the end of the list - if I set my preferences to see only 5 "search suggestions", then even more of those suggestions would fall off the list. Jan hopes that the more popular and more relevant suggestions will start to percolate to the top of the suggestion list. I still find it odd that Oracle 7.3 would supercede 11g.... *grin*

Need for Speed

Ok, so FLASH is REALLY slow for me, and I REALLY do not like the way FLASH was implemented for MOS. However, setting FLASH aside and focusing on Search, I was struck again by a need for speed. I think these are both very related, but I am going to break it into two different paragraphs to help spell it out.

First, there are times when typing in a somewhat generic term (ie, "install"), I have to wait several seconds (I counted 9 seconds to retrieve 1.1 million records). What about first_rows_k?? *grin* Seriously, Oracle's flagship product is supposed to be the database, and they call themselves the "Information Company". While 9 seconds out of my life is peanuts, other companies (do I need to spell it out?) don't have you sitting around for a few seconds wondering how your fingernails got a little dirty. I have even filed a bug on a case where you can do a search on nothing and the backend server times out - as if I wanted to see every single document. *laugh*

Second, Jan mentioned a couple times when we were talking about setting limits for maximum number of Search Suggestions and Number of Records to Display that setting the limit higher would negatively impact performance. How much? If I want, let's get extreme, 100 search suggestions and 1000 results per page, will my search slow down to 10 seconds, or 10 minutes?

Search needs to be fast! Freakishly fast. They need Jimmy Johns to come and tell them about fast! *grin*

Preferences, options, filters....

This one is quite tricky, and I partially found the right words when I spoke to Jan and I hope I communicated it clear enough. I and other customers have asked for more options, more flexibility, more controls. And by golly, Oracle delivered! We know have Preferences, various ways to set filters via PowerView, Product, Task/Intent, etc. In my opinion, the way these options are presented to the user are a bit confusing. Take for example PowerViews. PowerViews came out a little while ago (MOS 5.1, 5.2?) and thus are a separate form of customization than, say, refining a search via the Knowledge Browser options (by Product, Task, etc). If I set a PowerView to view only Solaris platforms, that affects all my searches. So even though the PowerView summary is at the top of the page, I often forget I have a PowerView set and I rather puzzled when various search results are filtered out. Imagine trying to look for patches specific to Red Hat if you have a PowerView set for Solaris. :)

I do think it is awesome that Oracle is giving us, the customer, this much flexibility and control, and I applaud that. I only hope that we can work together to make the presentation of that power and flexibility more aesthetic and easier to grasp. In some ways, it is almost like going from a FPS game with simple controls to a Flight Simulator with tons of controls. I think this issue will be a matter of acclimation and user training for advanced users that want to take advantage of such knobs and levers; all other users wanting a quick, "just get me the results like Google" approach should not have to worry about these extra bells and whistles. Perhaps one possible way to approach this is to have an "Advanced Search" preference, and consolidate all such advanced features in one area. Maybe. At the very least, have the one preference to toggle the kitchen sink on and off. :)

And some lower-priority items

Some other low-hanging fruit we can take advantage of might be to reduce confusing by consolidating the use of "Task" and "Intent". My guess is that a bunch of folks got together and brainstormed on what to call this, but could not come to a final decision without offending others, so they compromised. :) So I will say, as one customer, just call them Tasks. Forget about "Intent". In my mind, "intent" sounds like you want to practice your ESP or something. But you knew that before I typed it, right....

Redo the way an article is displayed. For a while now, the articles slide in from right to left, squeezing the left-hand side. Either get rid of that effect, or make it happen in .4 seconds max.

I like the "intent" (tongue in cheek) behind the "auto-detection" features. When on the conference with Jan, he demonstrated what this auto-detection is currently doing; for example, if you type "install" in the search box, you wait a few seconds than get a screenful of suggested articles. At the top of the list is a box asking you to clarify your intent (hmm... there is that word again), which can be used to help filter results. A similar thing happens if you search for database (refine by product, for example). This is somewhat similar to what we developed at "The Bridge".

MOS 5.3

2011-07-14T01:01:00.003-05:00

re: Document 1326487.1. My Oracle Support Release 5.3 Features

I knew that new features/enhancements were coming to MOS this past weekend, and I was looking forward to seeing what they were. I had heard that Search was improving. So when I read this Release Document, I was just a little underwhelmed at the level of detail. The "Release Highlights" all sound good, but I wanted meat. And then just today I realized that the details are actually in another document, My Oracle Support Release 5.3 Detailed Benefits Table (Doc ID 1329876.1)

More details in this document. But still not quite what I was hoping for. None-the-less, I decided to look into them.

Please note, I am a bit cynical in my comments. I realize a lot of hard work went into these enhancements, and there is a ton of "stuff" going on behind the scenes that I do not know about.

[EDIT: Egads, Blogger really mucked up my tables!!! Holy off-the-page, batman!]

Knowledge preferences

Customize your preference page

Knowledge preferences page on the Settings tab allows you to customize your knowledge settings

There are only 4 things you can customize, "Search Term Suggestion" (and associated number, max of 10), "Search Intent Clarification", "Search Result Set Length" (with a measly max of 25), and default "Searched Sources". That's it. I am disappointed. The "Searched Sources" only has two options, either the KB or ALL. Some preference. Better than nothing, I guess.

Support Identifier Management

Ability to delete multiple Support Identifiers at once

New window added for sizing control to change number of rows displayed for Support Identifier and managed users

meh. Could be helpful for organizations with really large numbers to manage. Glad we don't.

Search Improvements

Search Term Suggestion; search box has a type-ahead feature offering search suggestions using our knowledge base dictionary

Faster search results

Here we go, the fabled Search Improvements!
I have put this to the test already and it does help a bit. The only downside is that it is only available in FLASH (of course), since FLASH slows things down for me.

Message Center on the Dashboard

This new feature consolidates all the pending user action for Automated Service Request, Configurations, and new user Support Identifier requests

I had to look carefully for this - it is really small, and in the upper right-hand corner, and often occluded by the annoying green slider message box that I was told was going away. Automated Service Requests (ASR) are the domain of Sun products, thus not relavant for me personally. I could care less if a "pending user request" popped up, since I get mailed about them anyway. I am actively looking for a way to make this "Message Center" go away. Not sure why this is under the "Search Improvements" section.

Auto-detection of product names

Quickly refine your search results for a product

This is still a bit buggy. When I type "Oracle server ent", I get 8 products, 6 of which are more than 6 years old, nothing for 11g or 9i, only one for 10g. "Quickly refine"?!? Or maybe I am simply using it incorrectly - not really obvious to me.

Auto-detection of common tasks and intents

Quickly refine your search results based on selected task

Not exactly sure how this works. Obviously, not relevant for the search bar on every page, since there is no "task" selected. And it seems you can only select a "task" once a product is selected, no way to skip right to it. When I did look for a "task", the "Customer Recommended" document was #23 out of 25. I see that it is sorted by date, but not finding any way to sort by, say, reverse date, or even "Customer Recommended" for that matter. Or even by rating. The "Refine Search" bar on the left is really slow.

'Customer Recommended' indicator on search hit list

Benefit from the feedback of other users who liked knowledge base documents

Mentioned above. It is not clear exactly how a document gets the fabled rating. Seems similar to the "like" button.

More comprehensive and intelligent search tips

Additional guidance to get the most out of the search engine

Not much to say about this one - I'll have to take their word for it. The "type-ahead" auto-fill thing is probably the most visible aspect, and I like it so far.

More search sources in the form of additional product documentation

Expands the knowledge base of possible solutions

So, now, I can search for JDEdwards and Sun documentation. Umm.. yippee?

Ability to set default search source to "All Sources" (in preferences)

Expands the knowledge base of possible solutions

Mentioned above, but don't oversell it. A small step in the right direction. :)

Browse / Navigation Improvements

Redesigned Knowledge Management tab with new "Browse Knowledge" region

Easier to find content that solves your problem

Not seeing how this works. Thus not easier at all. How do I do a search for a specific product and a specific intent with my keywords? Seems like when I enter keywords in the search bar, it completely ignores what I selected for product and intent. Where are the enhancements?

Direct navigation to "premium content" using task, product and release refinement

Easier to find content that solves your problem

Easier, eh? Still can't find it. I must be really stupid. :)

Removed product browse tree (in favor of Knowledge Guidance)

Easier to find content that solves your problem

Umm... they just replaced "tree" with "hierarchy". And moved it into a drop-down button. Oh wait, they had that before as well.

Health Recommendations

Ability to set lifecycle (Production, Stage, Test, Development) of multiple Systems at once

Easier to maintain and manage Systems and Targets

Interesting. I guess that is a good thing, and it seems to work. Gotta love the little warning message (which should be removed, imo):
"The change will take approximately 2 seconds to complete. A message will inform you of the final result."

I am not going to comment on the other 5 sections of "Health Recommendations" because I do not use them. For folks that do, I can see how these might be good things, but given what I have see so far with other "enhancements", I have to question how much of an improvement this actually is.

Guided Resolution

Task-based Advisor helps to guide through a series of steps in a task

Helps you solve problems based on the process or workflow

I have no idea what this means. Where does one find this "Task-based Advisor"? Going to have to skip this whole section because I have no idea what they are talking about. It does recall to mind the old Libraries they used to keep. Again, sounds like a good idea, just not really obvious. Or maybe I really am dumb.

My Oracle Support Community

New user profile enhancement

Allows user to enter biographical or specialized information and the ability to control emails sent from Oracle.

Not finding anything I want to fill out in this "profile enhancement". Provide my education?? Manage Certification logos? Gah! And the only knob for "controlling emails" is a radio button that says "receive emails based on your community subscriptions". Not what I was expecting at all.

Ability to upload documents, pictures, and videos

Add information in the format that communicates your question or situation clearly

This sounds like a good thing - I did not realize you could not do that before. But I only have 8 Community points, so....

Allow users to view and manage the documents they subscribe to

Helps to manage solutions so you can solve problems faster

This made me laugh.

General notes:

still see annoying green slider box in upper right.
when displaying a document, still see the annoying "slide left" effect that takes a couple seconds.
Good ideas and intention. Disappointed with the implementation.
still hate the lack of any decent "back" functionality (a FLASH thing). Clicking through breadcrumbs erases options I might have selected earlier. (Big problem with search!!)
new tab for "Advanced Customer Services". Really, an advertisement?!? How do I delete that?

To Do:

Learn more about Health Recommendations and Guided Resolutions. Dare I have high hopes?

Bug 11858963: optimization goes wrong with FIRST_ROWS_K (11g)?

2011-06-17T08:07:00.001-05:00

At the beginning of March, I noticed some very odd things in a 10053 trace of a problem query I was working on. I also made some comments on Kerry Osborn's blog related to this matter. Oracle Support turned this into a new bug (11858963), unfortunately an aberration of Fix 4887636. I was told that this bug will not be fixed in 11gR1 (as 11.1.0.7 is the terminal release), but it will be included in future 11gR2 patches.

If you have access to SRs, you can follow the history in SR 3-314198695. For those that cannot, here is a short summary.

We had a query that suffered severe performance degradation after upgrading from 10.2.0.4 to 11.1.0.7. I attempted to use SQLT but initially run into problems with the different versions of SQLT, so I did the next best thing and looked at the 10053 traces directly. After a bit of digging, I noticed several cases where the estimated cardinality was completely off. For example:

First K Rows: non adjusted N = 1916086.00, sq fil. factor = 1.000000
First K Rows: K = 10.00, N = 1916086.00
First K Rows: old pf = 0.1443463, new pf = 0.0000052
Access path analysis for FRRGRNL
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for FRRGRNL[FRRGRNL] 
Table: FRRGRNL Alias: FRRGRNL
Card: Original: 10.000000 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00

So, the idea behind FIRST_ROWS_K is that you want the entire query to be optimized (Jonathan Lewis would spell it with an "s") for the retrieval of the first K rows. Makes sense, sounds like a good idea. The problem I had with this initial finding is that every single rowsource was being reduced to having a cardinality of K. That is just wrong. Why is it wrong? Let's say you have a table with, um, 1916086 rows. Would you want the optimizer to pretend it has 10 rows and make it the driver of a Nested Loop? Not me. Or likewise, would you want the optimizer to think "Hey, look at that, 10 rows, I'll use an index lookup". Why would you want FIRST_ROWS_K to completely obliterate ALL your cardinalities?

I realize I am exposing some of my naivete above. Mauro, my Support Analyst corrected some of my false thinking with the following statement:

The tables are scaled under First K Rows during the different calculations (before the final join order is identified) but I cannot explain any further how / when / why.
Keep in mind that the CBO tweak -> cost -> decide (CBQT is an example)
Unfortunately we cannot discuss of the CBO algorithms / behaviora in more details, they are internal materials.
Regarding the plans yes, they are different, the "bad plan" is generated with FIRST_ROWS_10 in 11g

The "good" plan is generated in 10.2.0.4 (no matter which optimizer_mode you specify, FIRST_ROWS_10 is ignored because of the limitation) or in 11g when you disable 4887636 (that basically reverts the optimizer_mode to ALL_ROWS).
Basically the good plan has never been generated under FIRST_ROWS_10 since because of 4887636 FIRST_ROWS_10 has never been used before

I still need to wrap my head around "the limitation" in 10.2.0.4 and how we never used FIRST_ROWS_K for this particular query, but I believe that is exactly what Fix 4887636 was supposed to be addressing.

Here are some of the technical details from Bug 1185896:

]]potential performance degradation in fkr mode
]]with fix to bug4887636 enabled, if top query block
]]has single row aggregation
REDISCOVERY INFORMATION:
fkr mode, top query block contains blocking construct (i.e, single row aggregation). Plan improves with 4887636 turned off
WORKAROUND:
_fix_control='4887636:off'

I assume fkr mode is FIRST_ROWS_K, shortened to F(irst)KR(ows). The term "blocking construct" is most interesting - why would a single row aggregation be labeled as a "block construct"?

Also, this was my first introduction to turning a specific fix off. That in itself is kinda cool.

alert.log appears not be updated

2011-05-16T15:09:00.001-05:00

After a few days of spinning my wheels and subjecting the poor recipients of oracle-l to multiple posts, I have identified an issue in Oracle code that I believe needs to be looked at.

First, some background.
We are running Oracle EE 11.1.0.7 on Solaris 10. We also have a job that occasionally bzips (compresses) the alert.log. The logic in the job is supposed to check if the file is actively being written to before zapping it, but by pure chance (so it would seem), in this particular case the alert.log was still open by the database when the file was scorched. This led to the appearance of the alert.log not receiving any more updates from the database. We attempted to bounce the database which had no discernible effect. I also changed the diagnostic_dest, which caused us to go from slightly strange to absolutely bizarre, and what opens the door for the rest of this post.

What I found
After changing diagnostic_dest several times, posting on oracle-l, the Oracle Community forums and playing tag with an Oracle Support Analyst, and doing lots of truss commands against sqlplus, I started to focus on this result from truss:
access("./alert.log", F_OK) = 0

Now, you may notice that this "access" command is saying that the file in question ("./alert.log") is legit. This caused no small amount of head-scratching. I got the same results no matter which directory I ran the commands from. In my system, I only had two files with this name, one in $ORACLE_HOME/dbs and one in $DIAG/trace. Neither were actively updated by the database. It was not clear to me, at first, that Oracle was finding one of these log files. Especially since it never did anything with it. I searched file descriptors in /proc/*/fd and found nothing. I even grepped keywords from all text files looking for strings that should show up in this particular alert.log.

For the life of me, I could not figure out what directory ./alert.log was in. When I compared to other databases, this same access always returned Err#2 ENOENT. So I knew this must be key, but not sure exactly how. On a whim, I decided to delete the alert.log in $ORACLE_HOME/dbs. Lo and behold, the problem seemed to go away magically.

The BUG
So here is the root problem, in my opinion. The Oracle code line is looking for $ORACLE_HOME/dbs/alert.log, but completely fails to write to the file if it is found. Instead, the branch simply exits out. How is that helpful?

In retrospect....
I believe when I changed diagnostic_dest to a non-existing directory, Oracle automatically created alert.log in $ORACLE_HOME/dbs. I guess I learned a few things from this. :) Also, I learned a few tidbits along the way. One can use KSDWRT to write messages to the alert.log. Dan Morgan's library (still hosted by PSOUG) shows this. Also learned a little more about truss and dtrace as I was researching this issue.

Now the hard part; convincing Oracle that this is a problem and needs to be corrected.

"The Bridge": Day 3 (part 2)

2011-05-10T08:53:00.001-05:00

I have received some pictures (not all, but most the important ones).

First, a recap of Day 2:
Our "realistic" picture evolved a little bit; Ahjay added some grouping tags ("WHERE", "WHAT") which we incorporated from there on out.

And here is what our OBJECT list finally looked like; complete with attributes and verbs:

Day 3
Hard at work.

After hashing things out in the morning, we finally had something akin to a prototype forming at our fingertips.

I really struggled with the overall complexity; I wanted simplicity. As a compromise, we worked very hard to make as much optional as possible, attempting to capitalize on pre-filled defaults and "quickfill" options, trying to use the technology and data that should already be available to reduce user interaction. For instance, if the user might be presented with the most recent Products at the top of one's list. Or setting your default QuickFill option (Previous SR, Profile or OCM) in your global Preferences. You will see, also, at the top left blue stickies for "Support Recommended" and "Product specific tips"; these are to be dynamically populated as you type and fill in information - the more information the user provides, the more relevant and specific the search becomes. I do not have any pictures, but on one of our white sheets we put in a meter as a gimmick to relate how more information upfront helps the user and the analyst focus on the problem (akin to the Password Strength Meter).

Near the end of the day, our final draft prototype was looking like this:

Again, you can see how "insta search" is being populated in the right-hand side, hopefully not too distracting, but also hopefully to be filled with information that would perhaps prevent an SR or guide a customer down the right path. Again, we are assuming huge improvements to Search. :) This picture also demonstrates one possible "multi-screen" approach, trying to cram in as much as possible "above the fold". I argued for the "one-screen" approach, but compromised and suggested that a Preference be added to allow either one-page or multiple pages.

Another thing that might be slightly less obvious is that we are trying to keep the big picture in mind, or "tell a story" as Kelli put it. We are trying to describe a problem, which has a beginning (ie, the environment), a middle or body (the Description) and an ending (optional files, template questions, further elaboration, etc).

In the end, it still feels like way too much complexity to me. I noted earlier that I really want to talk to a human to route the issue (which obviates the whole "Category" mess). I do not mind filling in all the technical details, but what if you had a "Contact Analyst" button that, like Amazon and many other companies, auto-dialed you (the user) and attempted to get a IHUB person on the phone asap? Yes, I realize from Oracle's standpoint this is impractical. But does anyone else want that?

It will be interesting to see what comes out of this project. I think I am excited. The workshop itself was definitely very productive, eye-opening and an awesome experience that I am fully thankful to Oracle for.

Before we all parted ways, we did get a group photo. Say "Cheese!"

"The Bridge": Day 3 (part 1)

2011-05-05T21:33:00.000-05:00

Still no pictures yet, so this is Part 1 of Day 3.

Day 3 was crunch time; by 5:pm we were aiming to have a working prototype. Because we expanded our scope (rather significantly) and spent so much time on tangential (but very important and sometimes relevant) details, the idea of getting a working prototype seemed rather dubious. But I think we did it. To a degree.

Picking up where we left off, we started to tackle the actual UI design itself. We had already done a lot of work on Search, so we needed to focus on the SR part of it. I came in a little earlier and drew up my own mock ups - they are horribly cluttered, but I personally think they are kinda cool. :) Basically, my mockup capitalizes on the vast similarities between Search and Creating an SR; providing keywords (ie, title), a product (and version) and you can start going to town. Category is a bit tricky, and I will cover it a little more in the last paragraph, but if you can nail down Category you can potentially narrow down your Search (called "Task Intent") rather dramatically and better yet, you are primed to punch in and route an SR. So why not do both in parallel? Maybe even on the same screen. You start filling in information, and in one pane you start seeing search results aggregated by facets (like what Advanced Search does now, but much more dynamic and insta-search), while at the same time your "Create SR" button lights up. And maybe even a "Post to Forums" button. I briefly argued for this approach, and I readily admitted that the huge downside is that the screen gets very cluttered very fast. I think we adopted a hybrid (eg, compromise), where the "Related articles" shows up insta-matically in a somewhat unobtrusive region floating off to the side.

We did a couple of usability tests; frankly, I think we need specific "Usability Test" training to learn how to do these better. :) I was not entirely satisfied with the particular way we approached this topic. But the good news is that we discovered many holes in our current prototype. Late in the day, we voted and started to tackle some of the more critical (or easy-to-fix) issues. Near the top of that list was whether or not to display the entire SR Creation process as one page or multiple pages. Again, some were very concerned about cluttering the screen and wanted "screen-sized" sections. I want everything on one page. In the end I posited that the user should have a preference for how he/she wants to view this process. We will see what happens with that.

Actually, this topic consumed a bit of time. After we green-lighted the idea of multiple pages, we got to work going through several permutations of possible screen layouts. Again, I found it ironic that we kept coming back to a design that is very similar to what we have today in MOS. Granted, we are added a lot of behind-the-scenes features that auto-fills (and insta-searches) as much as possible - that is not to be overlooked. But our final "look and feel" does not diverge much from the current design, in my opinion. In fact, if I count correctly, our final design may actually look more complicated. It is hard to say without having a real GUI to step through. Even though it looks more complicated, we are actively working to allow the user to input as little as possible to get the SR filed.

I have mentioned this previously, but it bears repeating. We were very much biased by the current implementation. In some ways, we spent a huge chunk of time trying to "fix" and patch current brokeness, instead of redesigning from the ground up. This is not to say we did not think out of the box (or at least try to). And right now as I type this, I cannot think of one single "out of the box" new thing we pushed. Maybe I am simply tired and not remembering well.

Another point of discussion that came up, and in retrospect I wished we spent more time on, is the current super-criticality of "categories". Currently, SRs are routed based on the sub-category (or category if no sub exists). These are currently filtered by which product one chooses. In our experience, choosing the most appropriate sub/category is often tedious and seems like a relatively useless step from the users point of view. We briefly talked about driving the sub/category off keywords in the Description field, and to be done in the "insta-search" way (you start typing, and the list of possible sub/categories to choose from grows smaller). But the bigger issue, in my opinion, is all about the routing in the first place. Oracle has placed a lot of emphasis on building automated logic to get the SR to a specialist team. I have a problem with that, at least how it is done currently. In my personal "Bleu Sky" vision (Day 1), I created a big easy "Create SR" button, with no requirements whatsoever. How the heck is that any good? Well, think about it, what happens? Rather, what if you changed the button to say "Chat with a human being"? By the end, we made comparisons to various other companies (ie, Amazon) that allows you to fill in call-back information, a computer actually calls you 1 second later, and then attempts to connect you to a live person. I love that concept!! As you can imagine, the managers and directors and support representatives at the meeting hated that idea. :) Yes, currently, it is hugely impractical - the IHub would be drowned to oblivion. Currently. But if we are thinking Utopian thoughts.... There are other ideas to simply routing. For instance, drastically reduce the number of routes. How? Well.... we didn't talk about that, yet. :)

"The Bridge": Day 2

2011-05-04T01:00:00.000-05:00

It is late and I am exhausted. And I have no pictures from today (/me looks at Richard Miller).

We started off on a good foot, having already taken a good stab at objectifying the tasks. We further hashed out all more objects, added more attributes, added verbs and relationships. Some objects were much easier than others; for instance, Product really only consists of a product name and version number in the scope of an SR. Yes, it is a "child" member of other objects.

In retrospect, we got bogged down in many areas, and sometimes it seemed like those areas were really minor and we were spinning our wheels. But the "spinning wheel" did demonstrate that even in our small group there is ambiguity and misunderstanding of core elements. For example, is a Primary Contact a subset of a "Contact Type"? Or is it different enough from other contacts (ie, Secondary, Manager) that it deserves it own types. One member argued quite vociferously that it is its own object because it is hanlded differently, like populated from a User Profile, while the others are not. After ripping up and redoing Contacts in various permutations, we finally decided on a single Contact object with various conditional properties and verbs.

There were other examples of the same thing, I just do not remember them off the top of my head. These little excursions took up a bit of time. On top of that, we also delved heavily into the Knowledge Base and Search, since we had decided to expand our scope the previous day. While much of our journey through this topic is quite useful in the context of filing and resolving an SR, it consumed time as well. So even though we had covered a bit of ground, Richard Miller declared that we were several hours behind in the late afternoon. :) I am not sure what that means for tomorrow.

Some very newsworthy things that came out of our session. I have not signed any non-disclosure agreements, but I do think the Managers want to keep a lot of new developments under wraps. So I'll go about it indirectly. We chatted up some more "Blue Sky" features as we discussed things we did not like about the current implementation. One of the key features to our new approach is using Search heavily at the outset of a possible SR creation process. I know, you are thinking this is a HORRID idea. But if Search were actually much improved (in terms of performance and relevance), we see this as being a huge boon. We could be wrong, time will tell. Basically, you have a form that provides an opportunity for the user to provide a ton of information. Much of the form is optional, but the idea is that the more you provide, the better the search results. Using ideas like Google's word-completion and instant results, and eBay's and Amazon's left-hand pane of refining and drilling-down, we explain how these kinds of features would significantly enhance the user's perception of Search by providing fast, dynamic feedback on the criteria entered. On top of that, the user may have a chance to save the search filters/results and shove all the pertinent information entered into an SR, or maybe even a Community forum post. Some of the above ideas have already been developed and we saw some simple demos. Like using quickfill and/or word completion in various areas. Very nice to see that they are already make in-roads in that direction.

I am particularly torn about the latest prototype GUI mock-up that our group has achieve so far. I claim my role, so I am not blaming everyone else. I say I am torn because the pages/screens that we "developed" today still look very busy and crammed full of things to fill out. It almost looks like we have merely re-arranged the existing SR fields that one normally fills out. I think the key importance in our approach today is that we are aiming for two things:
- allow as many optional fields as possible
- thus giving the user a choice between providing less detail and possibly a more vague search, or more detail and possibly a more accurate search

We are both assuming that search will be improved significantly, and providing ideas on how exactly to do that. The dynamic feedback mechanism is crucial I think, since it gives the user a good idea as to how many docusments are returned and how to refine it. I think. It looks good on paper right now. :)

Ok, that's it for me. I hope to procure more pictures tomorrow.

Do people really do this in real life?

2011-05-03T10:44:00.000-05:00

"My name is Newton Sequeira and I am an Author Relationship Executive at Packt Publishing. Packt recently green lit a book on Oracle 11g R2 RAC Administration Cookbook and we are now searching for an author to develop the book.
I was reading through your blog and wondered whether you might be interested in this project?
Thanks for considering this proposal. I would appreciate if you could please let me know your views."

This simply scares me. Do publishers really approach potential authors this way? Believe you me, I am the very last person any publisher would want to be writing this particular book. Even if I were to complete such an ambitious project, I would be the laughing stock of the Oracle community. For at least a year. And based on what little I know of author-publisher relationships, the author endures crushing timelines and relentless editors and enjoys a very small fraction of the royalties. Yeah, awesome incentive there.

If you see my name on this book, don't buy it. Please.

"The Bridge": Day 1

2011-05-03T00:42:00.000-05:00

Today we had a great session. Obvious introductions were first; another functional user who files a lot of SRs for Finance and HR modules, a couple of upper-level managers, a IHUB engineer and a front-end "user experience" ADF developer (was that too redundant?).

Honestly, I was a bit overwhelmed at first, having never done task flows in a group like this before. But I liked the concept. Our moderator/taskmaster Mitch is a good guy, and at times we tried his patience. :) We started off defining what we thought the "Big Picture" is - note how we labeled it "Create SR". At one point, one of the managers said, half-jokingly, "My god, what have we created?!?":

As you can see, we have lots of stickies. Mitch loves sticky notes (aka, post-it notes). We identified key processes and showed how they related to each other, and finally we marked it up with pink stickies for problematic areas. We also identified some "out of scope" topics. This first go was a really rough draft but provided a framework from which to build.

After we had the current picture in mind, Mitch asked us to dream about what we wanted it to look like. To dream a little. To think of a Blue Sky. Spelling errors (wrong to call them typos when you write them out? *grin*) were the trademark of the evening, but we pushed forward. The following picture is what the other user and I came up with:

I did the one on the left. I kept it really basic, because that is what Mitch indicated. And I wanted to emphasize how we need to keep the process simple and as fast as possible. The other user representative has a lot of experience filing SRs so has essentially figure out how to "game" the system to make it work fast. Included on the other side are a number of additional (and some optional) items, some of which overlap mine. I do not have a picture of the diagram the managers/developers came up with, but it wanted more forms and more questions answered. :) After getting it all down, we users then marked each step with how desirable it was (H = High, M = Medium, L = Low), and the developer group marked how feasible it was (H = Hard, E = Easy). The goal was to find as many highly desirable and easily feasible points as possible. I kinda think we did not pay too much attention to that. OH well. The next phase was putting these two (Realistic + Desirable) together. In the middle, we experience a Scope Changed because we started to see how important "Search" is to this process, and how we users much rather find existing information that solves our problem then filing an SR in the first place. Thus our Scope evolved into "Solving Problems" and this "Step 1" reflects how many of the things that could be used to initialize an SR could actually be pointed at the Knowledge Base. Like so:

Lest you despair (some consider KB to be a four-letter word), we had lots of talks about improving the KB search functions, and especially focusing on using the Advanced Search capabilities. This reflects the combination of our "Blue Sky" ideas - of simplifying the existing framework and trying to think of what is the bare amount needed to go search for information, while still providing plenty of robust functionality for power users who want to provide a ton of extra detail.

Here is a shot of us "in action" - you can briefly glimpse the chaos:

In the end, we also worked on "Step 2", which was the SR Creation portion of it, and discussed at length how these two steps play together, and how Sev 1 changes the ballgame a little (more often than not, if you file a Sev 1, you are not going to take the time to Search). With 30 minutes left of the night, we dove into objectifying the tasks. We merely scratched the surface, but I think we all felt it was significant progress.

I am certainly very impressed by this process. There are some obvious inter-group challenges when certain folks dominate the discussion, but overall we are making wonderful progress and I am very happy we are having these discussions. I only hope we are drastically pushing the managers and developers in a direction we will later regret. :) I am also struck by the complexity and the number of pieces involved. We very briefly touched on how OCM/EM play a role in providing data to the SR creation process, and we obviously tacked the bigger "purple elephant" of Search and the Knowledge Base (how many people are using it and finding what they need?).

And now I am mentally exhausted. And need to grab some sleep.

MOS Workshop: Fixing SRs

2011-04-25T11:42:00.000-05:00

So I am heading to Oracle the first week of May (May 2-4) to talk about improving MOS, specifically the SR creation process.

I have two similar previous posts on this topic:
http://orajourn.blogspot.com/2011/04/heading-out-to-talk-to-mos-devs-in-may.html
http://orajourn.blogspot.com/2011/04/mos-mashup-summary-or-saga.html

The agenda is:

The Service Request process is undergoing a redesign and a specific customer intensive feedback type session called “The Bridge” is being used to evaluate changes to the design. This process works over a 3 day period with two customers, the business owner, a lead developer, a designer and two facilitors to help the structured process to move forward.

The results are extensive requirements and user interfaces which are tested and approved during these sessions by development, customers, and business owners. This process works because the customers are in the room.

I ask for your help because I am but one person. I have filed a goodly number of SRs, ranging from the stupid to the complex, so I feel comfortable in that I can represent my own thoughts in what I would like to see improved. And I'll detail those ideas below. But if there are other pressing matters that I miss, please speak up! :)

Overall, I want the SR creation process to be easier on me, the client. I see the merit of Configurations and suppling an extra level of detail to the SR Analyst, and I see how the OCM intends to make the collection of such configurations mostly transparent. But above and beyond what is best for the analyst, I want to have a satisfying and confident SR creation experience.

Currently, it takes a minimum of 17 steps (skipping all optional steps) in both the Flash and HTML versions to get to the point of hitting the "Create SR" button (different steps to be sure, but they amount to the same thing). Some of the steps are completely redundant, some are nonsense. I would contend that 95% of those steps can be deferred until after the SR is created - basically, you just need the SR to end up in the right Support group. A note about the OCM - In the HTML version, I found that it was faster (according to the wall clock) to not use the OCM because the pop-up window to choose the system/host can take a long time to churn through the available systems (at least for us). The Flash version is a bit smarter and fills-in as you type, which is perhaps one of the best things about the Flash version.

What information does Oracle need at an absolute minimum to file the SR with the right group? Well, for starters, how about displaying all the possible groups? Currently (in both the HTML and Flash versions), the LOV (list of Values) that populate the "Problem" drop-down menu are determined by the Product that is chosen. Personally, I would prefer to pick an area of Support to send my SR to, instead of having to wade through various menus that play out like a "choose your own adventure" story.

Once the proper group within Support is selected, I want a "File it NOW!" button. All the other information can be entered after the SR is filed. I would even be ok with Oracle spitting out a message like "the analyst is going to ask a lot of questions unless you can provide more details". This makes sense. If all you have done is quickly file an SR without providing the product, version and some details of the problem, what is the analyst supposed to do? Practice ESP?

Next, I want the ability to fill in information that is pertinent to my case. If I use the a configuration, I want a list of configurations that gives priority to usage such that those configs that I use more often would percolate to the top. Same with the product and versions. I want the whole operation geared around getting it done as fast as possible. I want it all to be saved as I go so that if my connection is lost or I timeout (emergency meeting with the boss), I want to be able to slide back into it where I left off with no hassles.

In terms of "Related Knowledge" or other relevant documents, I do not mind if Oracle wants to spin extra cycles looking and filtering for possible metalink docs that might help me out. Just do not be obnoxious about it. Run the search in the background and populate a sidebar that I can click on at my convenience. In fact, I would want all related docs to be here, including any others that the analyst might find and possible bugs.

I want my SR to be filed with an analyst who shares my working hours. I prefer they speak my language proficiently, but initially and more importantly, I want to know that when I am at work, so is my analyst. I want the option of specifying different work hours. There has been a bit of talk about indicating the skill level of the DBA filing the SR so as to get a competent analyst, and this idea has been shot down with good reason. Rather, I want Oracle to provide top quality analysts from the get-go. If you have a newbie who is taking the SR, fine, but make sure there is some oversight from an escalation manager right off the bat. I do not want to escalate the SR simply because I am smarter than the analyst.

Lastly, I want my experience to be completely independent of my browser choice. I realize this is a huge obstacle as HTML "standards" are not standard at all.

Here are some things that Oracle is doing well, and I want the basic functionality to be retained. In both the HTML and Flash versions, there is an attachment link where you can view uploaded files. I like how the Flash version allows you to map a system after filing the SR. Although, I do not like how you have to change other parameters as well just to make that stick. I like how entries in the SR can be filtered and/or sorted. I like the concept of the OCM (as mentioned previously) - I think there is still untapped potential there. I like how the Flash version allows one to navigate the various sections of the SR creation process (the HTML version only has a "back" and a "next" button). Pre-filled values - the more the merrier.

I am toying with the idea of generating a step-by-step example of my concerns. I have already down two recorded webX sessions with Oracle about this, and it would be simpler just to make those public. :) But I did not record them, Oracle did.

MOS Mashup: the summary or the saga?

2011-04-20T15:18:00.000-05:00

Jonathan Lewis started a small conversation; what I gleaned from that thread is he and Tanel (and other experts?) mainly use MOS for "bug hunting" and looking up specific documents. Not so much for filing SRs. Therefore their comments revolved around the utility of the site in that context. However, the general consensus is that they do not use the Flash version, only the HTML version.

Lots of threads on oracle-l - I'll provide the first thread from the freelists and let you read through it if you like.

Robert Freeman "Do you ask the question: How do I work with Oracle Support....?":
Lots of varied comments here, great for "mining" what users are expecting. If I have a ton of free time, I would love to go through and categorize what I find here more thoroughly. In summary, 1 overtly positive comment, 12 negative and 11 on the fence (both good and bad). This thread was mostly about the analysts and not MOS, per se, but a few comments did tickle MOS (negative).

Jared Still "Just my opinion - the move of MOS to Flash is still a bunch of crap":
14 negative comments and 2 "neutral" - I did not find a single person who absolutely loved Flash, let alone MOS. In fact, the majority seem to feel that Flash is REALLY BAD(tm) and the HTML version is passable. General sense that the design was driven from top-heavy management structure, not from collected opinions of the user-base. A little progress seen on fixing bugs, but not nearly enough. MOS still excruciatingly slow.

Yon Huang "Anything Flash MOS can do HTML MOS cannot?":
A number of browser differences ("you got an error in XXXX broswer, try the YYYY browser"). Some comments about how Flash was initially better at creating SRs, but now it seems the HTML version is more robust. With the possible exception of annoying timeouts. As if it would take an hour to file an SR, say it ain't so!!!

Andrew Kerber "more MOS pain":
I think the initial issue might not have been the interface (MOS) itself, but more about how some documents are not published ("unpublished"). I agree, I also find this practice highly annoying. If I cannot see, don't mention it.

Don Granaman "Obtuse errors at MOS":
I also have seen a number of these errors, even recently. This can be generalized into a category of the "Unexplainable", strange messages that pop up for no apparent reason with no apparent solution path. Or like when the entire GUI is in Japanese.

Amit Bansal "Problems with MOS":
Browser and performance issues.

Jon Crisler "Metalink fiasco":
This long strand of messages wandered all over the place and I could not bring myself to read all of them. There are some good efforts to point to specific problems and possible solutions. I am dearly hoping that someone categorized that already.... you know, reinventing the wheel and all. :)

There is a ton more on oracle-l - what I have above only scratches the surface. Not to mention the proliferation of myriad blogs. But two I do want to mention are from the folks at Oracle who have started a couple blogs which have garnered their own collection of colorful ideas.

Chris Warticki's "Support":
I actually took it as a good sign when Oracle briefly pulled the plug on Chris after a noticeably contentious article. Chris knows there are issues with the GUI and Support in general, and he tries really hard to put a positive spin on all of it. Its just that there is only so much positive spin one can put on.... anyway, many of the folks who commented on the oracle-l articles are active here as well.

Support Portal - maintained by members of the Dev team:
I have had a lot of great conversations with Richard Miller, and I am glad he started blogging a bit more. As I blogged about earlier (a long time ago it seems), they have been doing a great job of collecting feedback and Richard did write a series of posts (1, 2, 3) about that collection process. Good stuff. The only major downside is... what did they actually do with all that awesome feedback? How is MOS better for it? *pause* I do not hear anyone singing the praises of MOS.

Whew.... that is a lot of stuff. Here is my Very Basic, Gross Summary(tm).
Customers want the online Support Site to be very fast and they want it to work. They do not want to see silly little nonsense messages. They do not want to jump through hoops and tie themselves in knots to do basic things. Customers want to talk with and interact with humans. Not monkeys reading scripts. Not a cumbersome website. Customers want a powerful search utility that helps them find documents and information quickly. Lastly, customers expect that when they are asked for their feedback, something will magically happen. When nothing happens, the pool of that feedback can quickly turn sour and/or dry.

Heading out to talk to MOS Devs in May

2011-04-19T15:17:00.000-05:00

I have been invited to a workshop to talk about enhancements to MOS. I am dearly hoping to collect and possibly organize feedback from the user community in general. So here is what I am looking for:
1) What do you like about MOS? Ie, the things you do not want to see changed.
2) What do you not like about MOS? The more specific the better, and bonus points for suggesting an alternative.

I'll be compiling my own list in the next few days. I realize many user communities have tossed this topic around ad nauseum, so this little effort is mostly my feeble way to gather all that wonderful feedback into a small concise package that can be communicated in a very clear and distinct manner.

Concepts Guide: 11/27 - Oracle Utilities

2010-06-25T15:14:00.000-05:00

Wow, this chapter was hugely disappointing! I mean, it makes for a better sales pitch than technical introductions to useful features. I believe I could summarize this chapter using a pseudo-code:


products[] = getListofProducts();
foreach product in products[]
do
printHeader "Overview of $product"
print "$product is a powerful utility to manage your data quickly"
end

What is even more disappointing is that I have used all of these features/products (with the exception of the Data Pump API) and know first-hand that they are all quite useful and handy. DataPump in particular is blazingly fast at moving raw data (but amazingly slow with the subsequent ddl like indexes and stats). I mean, I could go on and say a number of excellent things about these products and the specific "things" they do, and only scratch the surface at that, and I would have surpassed what is covered in the Concepts Guide.

The one thing I did learn was that I did not realize DBID could be used to set the DBNAME. I'll have to get that a try sometime.

Woot, two chapters in one day!

Concepts Guide: 10/27 - Application Architecture

2010-06-25T14:57:00.000-05:00

Again, I am struck by the archaic terminology (minicomputers and mainframes?). In a way, I guess the fact that the underlying technologies have not changed all that much speaks to the stability of those particular designs. And that's a good thing, right?

The architecture described in the first few pages is interesting. With a title like "Application Architecture", I was mislead into thinking this chapter was more about the application, but rather it is the fundamental pieces that Oracle has built to interface with various applications. I am a bit cautious about the apparent benefits of scaling vertically and horizonatally; obviously, everyone wants the option to scale if needed. While Vertical scaling seems to be the most common solution, I am a bit discouraged how hard Oracle PR has pushed Horizontal scaling in the form of RAC, almost as if it were a panacea for all functional and performance issues. But I digress.

I was excited to see the section "How Oracle Net Services work". As with previous technical material in this document, I was again disappointed with the high-level summary provided, instead of the real nuts and bolts. Ironically, in light of the coverage, I was surprised to find mention of "industry-standard higher level protocols"; seems to be a bit of bandwidth to advertise how compliant they are. I would think the reader would be more interested in the details that specifically relate to how Oracle talks to itself, leaving the underlying transports systems for a book of another scope. The whole point of an API is to abrstract out the details that one does not really care about. So I was glad to move on to the next section about the Listener and Services.

Yet my concern did not stop there. Check out this quote from the Listener section:

When multiple databases or instances run on one computer, as in Real Application Clusters, service names enable instances to register automatically with other listeners on the same computer. A service name can identify multiple instances, and an instance can belong to multiple services. Clients connecting to a service do not have to specify which instance they require.

Wow. Ok, so RAC runs on one computer?!? Since when? I have to admit that I am greatly impressed by how PMON communicates not only with the local listener, but also remote listeners on different computers. But there is no mention of local_listener, remote_listeners or how those play a huge role. Worse, "services" have not even been covered in sufficient detail yet; it would probably help to point out that while a service may map to multiple instances, all such instances must be part of the same database. Regardless, I have to repeat that I am duly impressed by the slickness we call "services" (head nod to Jeremy Schneider for his paper on making it a little more public). If only more beans were spilled out of the can here in the Concepts Guide.

And then the chapter ends right there. Egads! 6 pages covers Application Architecture?!?

Concepts Guide: 9/27 - Process Architecture

2010-06-04T13:26:00.001-05:00

"Figure 9-1 can represent multiple concurrent users running an application on the same computer as Oracle. This particular configuration usually runs on a mainframe or minicomputer."

Wow, this section of the documentation must have been recycled for a number of years. =)

Good pictures, descriptions of various processes.

In general, I like the "See also" sections, but I wish the link would go directly to the relevant section of the reference, instead of the top-most TOC page.

This section confused me:
"When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.

The statistic DBWR checkpoints displayed by the System_Statistics monitor in Enterprise Manager indicates the number of checkpoint requests completed."

If The CKPT process is responsible for updating the datafile headers and DBWR is responsible for something else (writing blocks to disk), why is the statistic called DBWR checkpoints? That is quite misleading, and perhaps leads to the confusion that spawned the warning about the DBWR in the first place. =)

Both PMON and SMON "check regularly". What is "regularly"?

While there are a lot of good ideas imbedded in Oracle, it is surprising that some of the still have such an antiquated and/or obfuscated interfaced. For example, the job scheduling system. The job queue processes are quite cool, but using them is a pain in the arse. The EMGC GUI is not too shabby, but what really sucks is the API; what about a simple API for those of us who do command-line work? VPD and Streams are the same way (have not yet seen any GUI for VPD). At least Shared Server is a little easier to grasp and implement, but it is still very easy to shoot yourself in the foot.

In terms of performance in the context of Shared Server, would not immediate results from FIRST_ROWS_N operations be queued as well? So it would be possible that queued results would actually return slower than when using a dedicated server?

Overall I found this chapter disappointingly light on details, or examples for that matter. I would love to see the program flow, end-to-end, of requesting, establishing, executing and concluding a transaction. Likewise, the last few sections (under "The Program Interface") don't really say much at all - it is most useful as a dictionary or appendix, nothing really that describes what things are or how they work, or the role they play in the larger picture. I mean, they do a little, but not a whole lot.

VPD + bad ANYDATA practices can really bite

2010-02-18T13:20:00.002-06:00

After several days of intense testing, 4 SRs with Oracle Support (and another with the ERP vendor), and the very helpful information from Maxim Demenko about "out-of-range" date values, I have developed a testcase that demonstrates how using bad ANYDATA practices in the context of VPD can really mess you up.

Some background:

We have an application that recently started to utilize ANYDATA. Unfortunately, the application did not implement validation checks, and the nature of ANYDATA makes table check constraints a near impossibility (I have not found any good ways to go about it). So we (not I, but colleagues) developed VPD rules to validate data. After a month of testing, a tester noticed that we had some really funny dates, ranging from 4290 BC to 5090 BC.

We tried tracing (10046, 10053, 10730), but nothing jumped out at us; except we may have uncovered a new bug, but more on that in a second. We tried using LogMiner, but Oracle Support finally convinced us that LogMiner does not support ANYDATA. :-( Finally we just started shooting in the dark, testing different combinations of rules and data inputs.

We stumbled upon the fact that using CAST to convert ANYDATA into a datatype has bad consequences. In particular, if you try something like cast(some_anydata_column as varchar2(1)) and the column is a DATE, for example, you get a ora-3113/ora-7445 (under 10.2.0.4 + JanPSU2010). The fine folks who had written our RLS policies had used CAST extensively, and the ironic part is that no errors were being generated on the application side. Instead, bad dates were sneaking into the dataset.

After reading the documentation a bit more, I discovered that ANYDATA is an object-oriented object (much to my surprise), and it has member functions. We had a hard time trying to figure out exactly how to use the member functions since one needs to instantiate a member first, and the documentation does not give any examples, let alone explain the usage of "object-oriented" in a relationship database. Finally I stumbled upon using sys.anydata as an instantiation, which seemed to work well for us.

Why did Oracle develope ANYDATA?!? It seems anti-RDBMS. And it makes things messy for us DBA types. As I explained to my colleagues, object-oriented data buckets are great for developers, up until they break. Then they are a pain to figure out.

I still have an outstanding question of exactly how the ANYDATA column overflows into the DATE field and gives us whacked out dates. If any Oracle gurus out there want to chime in, please do so.

Here is the code I used to replicate our issue:

drop user test cascade;

drop user test_no_vpd cascade;

create user test_no_vpd identified by test4#;

grant create session, EXEMPT ACCESS POLICY to test_no_vpd;

create user test identified by test3#;

grant create session, alter session, resource, create any context to test;

grant execute on dbms_rls to test;

connect test/test3#;

CREATE TABLE GORSDAV (

GORSDAV_TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,

GORSDAV_VALUE SYS.ANYDATA NOT NULL,

GORSDAV_ACTIVITY_DATE DATE NOT NULL,

pill_1 number default 1,

pill_2 number default 2,

pill_3 number default 3)

;

insert into gorsdav values ('some_table_1',sys.anydata.convertnumber(1),sysdate,0,0,0);

insert into gorsdav values ('some_table_1',sys.anydata.convertdate(sysdate),sysdate,0,0,0);

insert into gorsdav values ('some_table_1',sys.anydata.convertvarchar2('Y'),sysdate,1,0,0);

insert into gorsdav values ('some_table_2',sys.anydata.convertvarchar2('Yes'),sysdate,0,0,0);

insert into gorsdav values ('some_table_2',sys.anydata.convertvarchar2('Y'),sysdate,0,0,3);

insert into gorsdav values ('some_table_2',sys.anydata.convertvarchar2('No'),sysdate,0,0,0);

insert into gorsdav values ('some_table_3',sys.anydata.convertvarchar2('MaybeSo'),sysdate,0,0,0);

commit;

-- Using FGAC example from http://www.orafusion.com/art_fgac.htm

-- A dummy procedure to satisfy the CREATE CONTEXT command; does not actually do anything

PROMPT Create Application Role Procedure

create or replace procedure

set_testapp_role(p_user varchar2 default sys_context('userenv', 'session_user')) is

v_ctx varchar2(16) := 'testapp_ctx';

begin

dbms_session.set_context(v_ctx,'rolename','APP_OWNER');

end;

PROMPT Create context

create or replace context testapp_ctx using set_testapp_role;

-- This is just a mock up test; I am not concerned about real-life roles or security,

-- thus I am returning the same predicate no matter who the user is

PROMPT Create security function

create or replace function testapp_security_function (p_schema varchar2, p_object varchar2)

return varchar2 is

begin

return '(sys.anydata.accessvarchar2(gorsdav_value) = ''Y'' and pill_1 = 1) or pill_1 <> 1';

end;

PROMPT Create RLS Table Policy

declare

begin

DBMS_RLS.ADD_POLICY (

object_schema => 'TEST',

object_name => 'GORSDAV',

policy_name => 'TESTAPP_POLICY',

function_schema => 'TEST',

policy_function => 'TESTAPP_SECURITY_FUNCTION',

statement_types => 'SELECT,UPDATE,INSERT,DELETE',

update_check => TRUE,

enable => TRUE,

static_policy => FALSE);

end;

PROMPT Inserting a control row into the table to show the date and insert are fine

insert into gorsdav values ('some_table_4',sys.anydata.convertvarchar2('123456789'),sysdate,0,0,0);

commit;

PROMPT Selecting data from table - should return eight rows with no errors

select * from gorsdav;

-- The following function uses CAST to get the varchar2 data; however, a majority of the

-- data is larger than the CAST target, thus we get an error. Even if we use varchar2(200),

-- some datatypes are DATE and NUMBER.

PROMPT Create "bad" security function

create or replace function testapp_security_function (p_schema varchar2, p_object varchar2)

return varchar2 is

begin

return '((cast(gorsdav_value as varchar2(1)) = ''Y'' and pill_1 = 1) or pill_1 <> 1)';

end;

PROMPT Inserting into table - this will work with no problems.

insert into gorsdav values ('some_table_4',sys.anydata.convertvarchar2('Y'),sysdate,0,2,0);

commit;

PROMPT Inserting into table - this will complete successfully, but will insert a "bad" date

insert into gorsdav values ('some_table_4',sys.anydata.convertvarchar2('123456789'),sysdate,0,0,0);

commit;

-- PROMPT Selecting data from table - should hang for about 10 seconds and kick you out with

-- PROMPT ORA-3113 and ORA-7445 in the alert.log

-- select * from gorsdav;

grant select on test.gorsdav to test_no_vpd;

PROMPT Connecting as a non-VPD user (exempt access policy)

connect test_no_vpd/test4#

select * from test.gorsdav;

TreeDump

2010-01-05T15:36:00.003-06:00

Daniel Morgan alerted me to an index rebuild package he worked on; as I was reading up on sys_op_lbid (which is incredibly interesting, btw), I came across Richard Foote's "Index Internals", another awesome read.

Getting past the humurous myth busters (does anyone escape his righteous wrath?!? *grin*), I was delighted to learn about treedump:

alter session set events 'immediate trace name treedump level &index_object_id'; -- smartquotes removed

I had been experimenting with block dump on index blocks, trying to slug my way through the various header and pagetable blocks. While that is eye-opening in itself, the treedump really paints a human-understandable picture. As Richard has stated in other documents, indexes rarely ever look like the typical pyramid scheme (the one that everyone on the Planet uses, including himself *smile*); instead, more often than not, the "trees" get really wide very fast, and are usually rather shallow (not too many levels deep). The treedump not only exposes this commonality, but succinctly demonstrates why. Namely, each branch block of size DB_BLOCK_SIZE can contain hundreds or thousands of references to children blocks (be they further branch blocks or leaf blocks).

Mr. Foote's "Internals" presentation goes on to divulge other useful tidbits and I intend to revisit it to reinforce my learning. He is one smart dude.

My only request now is a live, dynamic graphical representation of the index "tree"; when teaching students (for example) how indexes are built, having a visual component really helps to emphasize what is going on. When I do them by hand (whiteboard and marker), I find it also goes a long way to demystify otherwise confusing (and thus prone to myths) concepts as deleted index entries, "fragmentation", "unbalanced" and index block splits. What can I say, I am visually oriented.

Thanks to Dan Morgan for vigilantly working to improve Oracle for us lower-lifeforms (ie, the index rebuild/coalesce package is really helpful). Without his nudging me, I would not have yet found sys_op_lbid, nor explored the internals a bit more.

Concepts Guide: 8/27 - Memory Architecture

2009-10-08T14:08:00.002-05:00

Like the picture on the 2nd page. Yes, I am visually oriented.

On page 4:

When automatic SGA memory management is enabled, the sizes of the different SGA

components are flexible and can adapt to the needs of a workload without requiring

any additional configuration.

However, the SGA components are flexible regardless of the setting for automatic SGA memory management (aka, automatic shared

memory management, ak ASMM). While I agree that ASMM as a conceptual feature is rather cool, there are known issues when

allocations and deallocations oscillate rapidly, causing locks on the underlying pools and decreasing performance overall.

To be truly effective, dynamic memory management would allow the kernel to dynamically allocate memory segments from the OS.

As it stands, 100% of greater(SGA_TARGET,SGA_MAX_SIZE) is allocated when the instance starts; if SGA_MAX_SIZE is greater, the

memory is allocated but not used, saved away for a potential future use. How is that dynamic?

From page 5:

"As the workload changes, memory is redistributed to ensure optimal performance."

*cough cough* Unless of course you hit a bug....

Good overview and discussion of the SGA and buffer cache in general.

Page 6:

"If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer

to the write list and continues to search."

I did not realize that the user process could move dirty buffers to the write list; I thought only the DBWriter processes

could do that. Hmm...

Slightly disappointed that there is no talk about the negative ramifications of setting a large buffer cache, specifically

how it adversely affects the time to scan the LRU. The positive effects are fairly obvious. =) I was also hoping for more

coverage of the Redo Log buffer. But, the couple of paragraphs in the document (and more laterin Chap 9?) at least introduce

the concept. I still find it strange that the defaults are so small.

By the time I get to page 10, I am ready for another picture; it would be nice to start with the overall picture, and then

"zoom in" on each section to go along with the text a little bit.

Data Dictionary Cache, aka Row Cache. Is this the same cache that 11g is using to store sql results?

Like the detailed steps on page 12; this is the kind of details I have been waiting for. And now I can rip them up. =)

The first bullet of #2 states that the ANALYZE command (when used to update/modify stats) flushes the cursor from the shared

pool. People still use the ANALYZE command in 10g? I thought the dbms_stats procedures were preferred and more popular. In

any event, the underlying mechanics are still the same; if you alter the stats, the cursor is no longer valid and dropped.

Now if only we had a way to surgically splice out individual cursors without having to make relatively "impactful" changes...

Bullet 3 is interesting, I had no idea that changing the global name flushed the shared pool. Wow.

The couple of paragraphs about the Large Pool are succint, a bit too much so. How does Oracle decide when to use Large Pool

memory, as opposed to the Shared Pool? What are some good rules of thumb in terms of how much to allocate (probably covered

in the Performance Tuning Guide, but the Concepts Guide should say _something_)? No Large Pool Advisor? =)

The section on Streams Pool is even worse. It does not tell me what exactly the memory is used for, how to tune it, what to

do with it, nothing. And the 2nd paragraph is quite confusing; the Streams Pool may come from different parent pools

depending on how SGA_TARGET is set? Who signed off on that little bit of tom-foolerly?

The section on Dynamic SGA is almost misleading. The shared memory allocated to the server instance does not actually change

at all; 100% of the requested memory (even if it is not used) is given during instance startup. This concept of a "dynamic

memory usage" feature totally ignores that fact. So if the instance gets 50GB of memory but only uses 500MB, you are wasting

45GB for no good reason. That is not dynamic memory.

"Both the buffer cache and the SGA pools can grow and shrink at runtime according to some internal, Oracle-managed policy."

This is the Concepts Guide, for crying out loud! Some internal, Oracle-managed policy?!? Who wrote that?!? Worse, this

particular policy has some serious drawbacks, especially earlier in 10g. We observed that Oracle would thrash itself trying

to resize the "dynamic" pools many times a second, apparently oscillating between two different "optimal" thresholds. Some

policy.

LOCK_SGA: I have never heard of this parameter. And, oh, here is the reason why; we run Solaris. Good thing the

Concepts Guide cleared that up. So, this is the Concepts Guide, right? I am not reading some PR BS, am I? Why would

LOCK_SGA be a bad thing? If you are running a Production database, especially on a host that is dedicated to Oracle, why

would I ever want memory pages to swap out?

SHARED_MEMORY_ADDRESS: Again, never heard of this one. In fact, Google is suspiciously quiet on this one as well. Why would I

want to set this parameter? Don't get me wrong, I am glad the Concepts Guide at least says *something*, but some explanations

would be nice. I assume this parameter is offset; if you set the same thing in multiple databases, I would have a hard time

believing that this parameter actually is meant to grab an absolute, OS Memory address. That would be just silly. Actually, I

am surprised this is not a underscore parameter. Which makes me wonder, is this short little blurb the only thing that keeps

this parameter from being "internally supported"?

Didn't realize the PGA had two independently-maintained subsections (pesistent area and run-time area). Good to know.

It is puzzling that the author would choose to include v$sesstat and v$sysstat as means of collecting performance statistics for the PGA without giving any insight whatosever as to what to look for; in fact, just browsing v$statname (the lookup table for the statistic#) does not make this any more clearer either. I personally find it quite unhelpful a document that purports to "demystify" something flippantly references a source that is not at all obvious.

I'll wrap up with that. I still find it hard to wade through these chapters; drains my brain. *grin* Overall I felt that I learned a few small things about the Memory Architecture, but I still feel like I have a ways to go before I fully grasp the skeleton that holds everything together. I hope this becomes more lucid as the chapters progress.

Concepts Guide: 7/27 - Data Dictionary

2009-09-23T09:36:00.003-05:00

I still cannot believe "someone" had the gall to reduce the data dictionary to 5 pages. =) And one blank page to rub salt into the wound.

I had an interesting challenge when trying to explain the dictionary to a colleague. So we all have a general idea of what the data dictionary is and what it does (just read the first page of this chapter in the docs). When does it get built? Interesting question. Usually we say that catalog.sql builds the dictionary, or even bsq.sql. However, did you realize that 601 fixed tables (10.2.0.4) exist in memory just for starting the instance (NOTE: no database!)? Try this one on for size:

/u01/app/oracle/product/10.2.0.4/dbs: echo "db_name = SAC" > initSAC.ora
/u01/app/oracle/product/10.2.0.4/dbs: . oraenv
ORACLE_SID = [DUMMY] ? SAC
ORACLE_HOME = [/u01/app/oracle] ? /u01/app/oracle/product/10.2.0.4
/u01/app/oracle/product/10.2.0.4/dbs: sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 23 09:32:35 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SAC_SQL > startup nomount
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2286584 bytes
Variable Size 114105352 bytes
Database Buffers 268435456 bytes
Redo Buffers 34603008 bytes

SAC_SQL > select count(*) from X$KQFTA;

COUNT(*)
----------
601

If you want all the names of those fixed tables, select KQFTANAM.

So, even without running any scripts, we have "data dictionary" objects residing in memory. This helps me to understand what the dictionary is and what it does. For an analogy, it is like the overhead address space a program uses to store all its global variables. In this case, it is just happens to be organized into relational tables with gobbly-gook table/column names and normalized up the wazoo! =)

I have to confess, I was quite disappointed with this chapter. Of the 5 pages, the first 4 tell you not to change anything and give a brief overview of the differences between USER, ALL and DBA views. The last page starts to get just a tiny bit juicy and at least mention dynamic performance views, but then like a cheap battery dies out too soon.

There is some really cool stuff locked away in the x$ tables, let alone the rest of the db metadata. For a quick glimpse under the covers, I found eygle's list quit informative and helpful; it is allegdegly a copy of Metalink note 22241.1, which is supposed to be internal only (I cannot see it at this point in time); it merely lists the fixed tables to demonstrate the naming convention, but it also gives us an idea how things are organized and what types of things the kernel tracks. I hope one day Oracle wakes up and realizes the advantage of sharing more information like this.

Yong Huang also has a listing.

Learning about parameters

2009-09-21T09:06:00.004-05:00

We are kicking off an effort to analyze and hopefully standardize parameters across some 200 databases. Fortunately, we have a history of parameters which is made possible by an in-house job that scrapes them from each database every night.

So this post is more about my own little path of discovery; I am sure most of you know this already.

Lesson 1: Pay attention to what the documentation says

For instance, db_cache_size has this little blurb in the 10gR2 docs:

The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value).

Let us assume the documentation means to say 4*1024*1024, as opposed to 4 megabytes; 4 megabytes * granule size (which is also in bytes) would give you bytes squared, which makes no sense. We will also assume the granule size = 8KB, but in order for the numbers to be realistic, we will say 8 bytes. That first 4M is really throwing things off. 4M * 8 bytes = 33,554,432 bytes, or 32 megabytes.

So let's say we have a generic setting of, say 200M (bear with me). If you upgrade your host from using 5 CPUs (4M * 5 cpus * 8K = 32MB * 5 = 160MB) to 10 CPUs (160MB * 2 = 320MB), the setting for db_cache_size will automatically jump up to 320MB, despite your explicit setting of 200M. If you had budgeted your databases to max out memory, you might want to be a tad careful. =)

Lesson 2: Comments in the init file are stored in x$ksppcv.ksppstcmnt (and thus v$parameter.UPDATE_COMMENT)

Of course, one has to put the comments in the right place. We pretty much discovered this by accident; if you put a # followed by text after the parameter=value, you are all set. Of course, if you ever look at spfiles, they have the same thing. Just something we have missed for a very long time. =) We have found this to be very handy in identifying parameters that deviate from a given "standard" or are set as a result of a bug. Or an upgrade (which are just really massive bug fixes, as well as new bug introductions, right? *grin*).

Lesson 3: Undocumented/unpublished x$ tables really sucks

I really wish Oracle would document the x$ tables for us. So I am looking at X$KSPPI.ksppity; all the online blogs/white papers/articles that I can find decode this value into 4 groups (boolean, string, number, file). But wait, in 10gR2 I have 5 distinct types (1-4, 6). 6 seems to correspond to "Big Integer", that little convention of using [K|M|G] after a number to distinguish a shorthand for its size. Not sure why 5 was skipped - we do not have any values for 5.

Overdramatic? Saying that such a small thing sucks? This is merely the tip of the iceberg - there is so much out there that is not available for the general public, and the experts end up making guesses (really good and quite often very accurate guesses, but guesses none-the-less).

Well that is it for now. Still exploring, learning... seeing what's out there.

Concepts Guide: 6/27 - Dependencies Among Schema Objects

2009-09-11T15:25:00.002-05:00

Almost nice to have a short chapter. I cheated a peeked ahead and saw that Chapter 7 is only 6 pages; how the heck did they squeeze the Data Dictionary into 6 pages? Guess we will have to wait and see.

I like how Oracle automatically attempts to recompile dependent objects if the referenced object is changed - the discussion in this chapter highlights Oracle's infrastruture (although does not detail it) and how it all happens like black magic. =) Here is a quick example:

SQL > create table t1 (a char(1));

Table created.

SQL > create view v1 as select * from t1;

View created.

SQL > create view v2 as select * from v1;

View created.

SQL > select object_name, status from dba_objects where object_name like '__';

OBJECT_NAME STATUS
------------------------------ -------
V1 VALID
V2 VALID
T1 VALID

SQL > alter table t1 add (b char(1));

Table altered.

SQL > select object_name, status from dba_objects where object_name like '__';

OBJECT_NAME STATUS
------------------------------ -------
V1 INVALID
V2 INVALID
T1 VALID

SQL > select * from v2;

no rows selected

SQL > select object_name, status from dba_objects where object_name like '__';

OBJECT_NAME STATUS
------------------------------ -------
V1 VALID
V2 VALID
T1 VALID

Note how V2 depends on V1, which depends on T1. Even though the text is not clear on whether or not a recurssive recompilation will occur, we can see that in practice, it does. Cool stuff.

The notes on page 6 (Object Name resolution) are key to remember. It seems easy sometimes to forget the order of precedence:

own schema
public synonym
schema matches fully-qualified object owner

There is an important ramification to this:

Because of how Oracle resolves references, it is possible for an object to depend on the nonexistence of other objects. This situation occurs when the dependent object uses a reference that would be interpreted differently were another object present.

For instance, it is possible that V1 needs to have T1 not be in the current schema, but rather a synonym.

Right below that is a section on how SQL also maintains a dependency on the referenced object. Because Oracle (as of Oracle 11gR1) does not fully support surgically removing individual sql cursors, one workaround is to change the dependent object, which immediately invalidates any associated cursors. Great for if you want a new query plan because something changed and you do not want to redo the stats. Well... kinda great; not to many shops allow the DBA to make changes to the table on the fly in Production. =)

The section on remote dependencies was slightly interesting - I did not realize that Oracle checked not only on timestamps, but also on signatures. When you think about it, it is amazing how much Oracle does behind the scenes.

RAC Attack!

2009-09-09T14:01:00.002-05:00

Jeremy Schneider graced us with RAC Attack last week - it was quite awesome! Jeremy brings such a wealth of knowledge and passion for the technology that often times I found myself hard pressed to keep the workshop going. As I was the "organizer" person, I felt some responsibilities in those directions.

It also opened my eyes on several fronts. This is the first time I have helped to facilitate such a workshop, and there were a number of interesting obstacles, logistical and technological. Jeremy handled it all with his usual easy manner and we got it all worked out quite well. For instance, the harddrives of the individual computers were just a tad too small to accomodate all the jumpstart VM images that Jeremy likes to deploy; as a result, we ended up hosting files on various computers and mapping network drives. Not the quickest thing in the world, but hey, it worked. Also, again from the perspective of a facilitator, I found it challenging to address the numerous questions that folks had from time to time, which gave me a greater respect for those who do this kind of thing on a regular basis. Not only did Jeremy answer questions, but took advantage of several opportunities to delve into the deeper details of "how things work".

In retrospect, we are faced with the ubiquitous puzzle of how to address different styles of learning. For those, like me, who crave the hands-on aspect, this workshop is excellent! For those who need more lecture, this lab was a bit of a wake-up call. *grin* Actually, if only we had more time, we could certainly have entertained more dialogue; RAC is rich with controversy. =)

Jeremy was also able to spill the beans a little on Oracle 11gR2, since someone decided to release the Linux version the Tuesday before the workshop began. So we were treated to a few sneak peeks and tidbits. Good stuff.

Personally, I was challenged to discover new ways to do these kind of labs/workshops. I heard a lot of positive feedback about the wide variety of skill sets and job roles in the class, but as a result of that, the various backgrounds required different levels of "background information". Going forward, I would try to break the labs into more modular components (opposed to a totally open lab time) and preceed each lab with some solid instruction. What Jeremy did was great for DBAs, but we had some folks who needed a bit more hand-holding. That is just the nature of the beast. The good news is that Jeremy equipped us to do exactly that - we can now hold our own lab and choose any pace we want. I am hoping to pursue this a little and get others involved, especially in terms of disucssing how we as an organization want to tackle overlapping job roles in regards to cluster and storage management.

The virtualization aspect was also very nice. I think it gave us a glimpse into what we can do with virtualized resources, something we can definitely utilize more fully for future labs and group sessions.

Thanks, Jeremy,

Concepts Guide: 5/27 - Schema Objects

2009-06-24T15:16:00.003-05:00

I found that reading the Guide is quite hard if you are already tired. ;-)

As always, I like pictures. Figure 5-1 on page 5-3 does justice. Although they would make their point more clear by labelling the schemas.

Was not aware of intra-block chaining (pp 5-5) - interesting concept. Especially since it does not affect performance (does not increase the number of IO calls).

Figure 5-3 is good in that it really helps to see the various pieces of a block and the row, including the headers.

As much as I hate how nulls are handled in Oracle, the one good thing is that I like how Oracle just does not even record information for null-trailing rows (ie, column-length not stored in block). Except, of course, if you have LONG data - DO NOT USE LONG! =)

I was not aware how table compression actually worked. Now that I know a little more about it, I am surprised the guide did not mention any limitations. For example, if a block has 100% unique data (uncompressable), would the symbol table still be built and populated? If not, what is the cut-off? At what point does Oracle deem compression worthwhile, pragamatically?

I have never seen a practical use for nested tables, but I'll keep my eyes open. I jumped to 27-7 as referenced just to see what it said. I still have never seen a practical use for nested tables.

The fact that sessions are "bound" to temp tables was new to me; I did not realize you could not do ddl on a temp table if is already bound to a session. Kinda makes sense, though. I wonder why they do not simply call it "locking"? =) Afterall, that is how normal tables are handled.

Ojbect Views really confuse me, not being familiar with the concept. And this being the Concepts Guide, I found that the short little blurb did not really help me much at all.

I also did not realize that one could put declaritive constraints on views; interesting way to propagate metadata information for restricted environments.

The short paragraph on Materialized View Logs did not do the concept any justice. I get the impression that either space and/or time was restrained when this section was done. =)

The intro section to Dimensions left my head whirling. I am not a Warehousing guy by any means; while I appreciate the extra background and the quasi-example, I find that it deep-dives too quick for me. And using an example of a year being tied to a year-row is just the most absurd thing I have ever heard. Why not a practical, real-life example that "anyone" can grasp?

Good discussion for sequences; I like how the good is balanced with the bad - the "Caution" is stronger than I expected, but I think very important and am glad to see that the authors made it stand out.

Nice long section on indexes. *grin* I always find it strange to find in 10g documentation references to "analyze table", when most of the time I believe they really mean collect stats, for which we are told to use dbms_stats instead. The intro to deterministic (user-defined) indexes was quite interesting. I would personally consider this an example of "black magic" in Oracle. Another one of those cases where there is a strong lack of practical examples.

Figure 5-7 starts out looking like a great pictoral example. However I found it to be quite confusing. Actually, what I really want to see is how an index is built, starting with one row. At the very least, it would be helpful to augment the figure with text explaining the function of the values for the branch blocks. However, excellent information on how searches are mathematically bounded (big-O notation).

Excellent piece on bitmap indexes; great examples, wonderful discourse. I appreciate the balanced approach to addressing the pros and cons of bitmap indexes, which may at the outset seem to be the pancea of query performance issues. The sidebar on cardinality was very well done as well.

The section on Index-organized tables was also quite interesting, however I wonder why, if they are so highly recommended for OLTP applications, why are they not more popular?

Application Domain indexes, and the Oracle Data Cartridge in general, are another area of black magic that I fear ever going back to. I dabbled in it once when attempting to define custom statistics for a function and never looked back. =) I am sure they have their place on some "True Expert"'s shelf, but not here....

Like IOTs, the Concepts Guide does a good job selling Clusters and Hash Clusters as beneficial methods, but I do not see many folks using it in Real Life. Is it merely the learning curve that keeps the standard DBA away from these features? We have a lot of third-party apps; shall we assume that the vendors simply do not have the expertise to utilize these fun but advanced toys?

Interesting stuff none-the-less.

Fusion Tables

2009-06-12T13:24:00.003-05:00

So I admit it, I read slashdot (who doesn't?? *grin*). While some topics I really do not care about, for some reason "Oracle" in the headline does. =) And I am not opposed to Oracle-bashing, because I do a fair share myself.

Slashdot post: http://developers.slashdot.org/story/09/06/12/1658206/Oracle-Beware-mdash-Google-Tests-Cloud-Based-Database

I love how folks at Google Labs come up with all this crazy stuff. And not just GL, but Apple and lots of other places as well. The way technology moves is absolutely spellbinding, and I mean that in the most literal sense possible. *grin*

What I hate is techno-marketing gibberish:

"So now we have an n-cube, a four-dimensional space, and in that space we can now do new kinds of queries which create new kinds of products and new market opportunities"

Ok so I can grapple with n-cube or 4-space. Show me a query that can create a new kind of product. Heck, show me a query that can make an old product! Create new market opportunities?!? Come on, everything in the galaxy is a market opportunity. You couldn't hit a house fly with a query. And I mean that in the most literal sense. *wink*

Concepts Guide: 4/27 - Transactions

2009-05-21T11:04:00.003-05:00

Good info on transactions. Lots of detail, great overview. The example of a bank and financial transactions is a great analogy and fits well with the discussion. The statements about asynchronous commits was rather interesting. I had my doubts, so I looked up the referenced Oracle Database Application Developer's Guide - Fundamentals. Chapter 2 of that book (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref182) informed me about the commit_write parameter. I found out that one could specify NOWAIT - as the caveats mention, one has to be aware that when using this parameter data could potentially be lost. I wonder how many folks have "accidentally" set this parameter thinking they were getting better performance for free.

Naming transactions - I had no idea LogMiner could search for named transactions. That is really cool, and I'll have to keep that in mind.

I have to reread the part on two-phase commit and distributed transactions.

The coverage of autonomous transactions and pl/sql is really helpful as well. This is the kind of stuff that crosses the lines between DBA and Developers - clearly, the text states that developers are responsible for avoiding deadlock conditions. A "pure" DBA is an oxymoron in the sense that any DBA worth his/her salt is going to have to know a little about sql and pl/sql. And the more one knows, the more effective of a DBA one can be. Which cements my belief, and one of the few areas in which I agree about the recent changes in Oracle Certification, that SQL and PL/SQL need more limelight as a fundamental DBA skill.

Short chapter. But a great chapter. Whitespace on 3-9 and 3-10??

Concepts Guide: 3/27 - Tablespaces, Datafiles, and Control Files

2009-05-15T12:57:00.002-05:00

Again, I like the figures on 3-1 and 3-3,3-4; good to have something other than just plain words.

Interesting how the discussion starts with Bigfile tablespaces, especially since these are not default and are a new feature. I strongly disagree that bigfile tablespaces + OMF "simplify datafile management" (3-5). While they do make many things transparent to the user (which is mostly good), and yes, even make some things easier for the DBA, it by no means makes things more simple. This is especially true for OMF. The caveats listed on 3-6 are noteworthy, especially in regards to recovery. We do a lot of clones were we copy databases across the wire, and we find that these operations work better on multiple small files in parallel, instead of gigantic, fewer files. The addition of OMF potentially just makes things that much more confusing when trying to diagnose issues, not to mention trying to teach concepts and architecture to Jr. DBAs. "Don't worry about where the files are or what they are called" is not exactly a "teachabel" moment.

Who uses exabyte storage centers anyway? *grin* The few, the proud?

The short blurb on 3-7 for the SYSAUX tablespace had a very intriguing note. "It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases." Really? So in 9i we had fewer tablespaces, by default, and yet SYSAUX somehow reduces tablespaces in 10g? What did I read wrong?

Based on these short accounts, it is not overly clear what the purpose of the SYSTEM or SYSAUX tablespaces actually do for a living. Ok, so they store metadata that make the database run. What does that mean. It will be interesting to flesh out the details a bit.

On the other hand, the descriptions of Undo and Temporary tablespaces is quite helpful, and I thought the author did a great job at introducing those concepts. Not so sure about the "use" statements on 3-9. Use a tablespace to create tablespaces?

Following that, I appreciated and value the explanations of Extent and Segment Management. I still do not quite understand the justification for extents, but I do see the importance of Locally Managed Tablespaces and ASSM. Kinda makes me wonder when (not if) the older, manual methods will be deprecated.

I like the detail the author goes into for the Temporary Tablespaces. However, the blurb about sort segments has me wondering. Does Oracle really only use one shared sort segment? The wording is a little confusing; at one point it sounds like there is one sort segment (and only one?) per TEMP tablespace, yet the last sentence on 3-13 states that the sort segment will grow to be as large as all the storage requirements for all sorts in the instance. Perhaps the implication is that the sum of all sort segments will grow to accomodate such space usage?

The Controlfile overview is good - I was hoping for another picture. =) I can't wait for a deep-dive on this critical file.

Overall, a great chapter. I thought the organization was a bit backwards, starting with BigFile tablespaces and ending with controlfiles, but maybe that's just me. A great introduction into the core pieces of the lower-level storage mechanisms.

Concepts Guide: 2 of 27

2009-05-07T14:40:00.002-05:00

The figures (diagrams) in this chapter really help explain the basic structures of blocks, which are in themselves basic building blocks for understanding how Oracle works. After reading this chapter, I am quite unclear what the purpose of the extent is. But I'll get to that shortly.

The discussion about high water marks is good, but slighlty misleading due to the fact that Oracle does not have a water mark, but rather two; a low high water mark (LHWM) and a high high water (HHWM). *grin* And the space above the LHWM is not guartanteed to be free (only above the HHWM).

I rather enjoyed the section on data blocks (starting 2-3). While the bit about data being stored in a byte on the OS is a tad confusing, the pictures and discussion really helps one to gain a toehold as one starts the journey up this monstrous moutain. I did not realize the "overhead" section was actually so small, considering all the information stored in the header. This section left me want more details, which I hope are forthcoming in later chapters. *grin*

With the many advantages of ASSM (automatic segment space management), I am surprised that Manual mode is even available. Perhaps there is a use case for particular warehousing applications that need to manually control the freespace? Might as well throw LMT (Locally managed Tablespaces) in as well - if Oracle has such a strong recommendation against the older, more manual methods. =)

On 2-6, I found myself appreciating the information about free space and the various storage parameters and row chaining/migration. Again, the figures really help here, and I can only hope that they continue throughout the rest of this Guide. I did learn that Oracle only coalesces free space at the time of DML when needed - Oracle does not even attempt to coalesce during "idle" times. Which is interesting - why not?

So on 2-9 we finally get to extents. They seem like middlemen in this game, acting as a second layer of abstraction between "blocks" and "segments". Why? What role do extents play? While I did not find a clear answer by reading this chapter, I would have to assume that the most important role they do play is to allocate a group of blocks together for a particular segment (ergo, segment type), kinda make them sticky. Could not segment headers be made a little larger and subsume all the responsibilities of extents? Just curious. Perhaps I am missing something fundamental here. Maybe the whole point is to keep the respective object headers small? I did find one page that does not answer the question what-so-ever. *grin* I also see a short blurb about the Segment Space advisor (in the context of Extents, keep in mind) - call me naive, but I would expect Oracle to automatically resolve fragmentation quietly, internally, automatically.

The subsequent explanations of the various segment types were good, and I liked the way the Undo concept was spelled out. The way Temporary extents/segments and Undo retention is detailed is excellent! Again, a great overview of some very basic things (and not so basic, appropriately).

Sojourn through the Concepts Guide: 1 of 27

2009-05-06T10:28:00.004-05:00

I have been challenged on at least three occasions to read the Concepts guide (thanks to Tom Kyte, Rich Niemiec and Job Miller).

The Concepts Guide (Book? Epic Saga?) is quite large. 542 pages at 8.07mb (PDF version), compacting the knowledge about the Oracle RDBMS into a mere 27 chapters. Since I am most familiar with 10g, I decided to start there. I noticed that the Primary Author (Michele Cyran) is also noted as Primary for the 9i version, and I imagine that she is listed as a Contributing Author in the 11g version merely because so much of the content is reused (which makes sense - hard to improve upon a such a good thing). After reading the first few pages of each, I confirmed that much of the original content is unchanged. Again, this makes sense - we are talking about concepts, and the basic, fundamental concepts of the RDBMS have not changed that much, if at all (aside from new features).

The first chapter, as an overview, is excellent! I found myself being surprised that it was possible to sum up the entire thing in just 38 pages without obviously cutting out too much. Given that, I was equally surprised that there is so much attention given to the Grid Architecture (one blatant, rather large addition since the 9i version).

So, going through the first chapter chronologically.

I found the opening statements quite appropriate - again, an excellent summary. How often have DBAs been asked by lay folks and family members "What is a database?". I like how the author starts at ground zero and builds upwards.

The next 7 pages spent a bit of time on the Grid Architecture. Surprising, as it spans not only the roadmap of the database but extends to Applications and even to the enterprise as well (from Storage to how Information is handled as a commodity). While I agree with the theory of these ideas, and I see how it would be easy to get caught up in the novelty, from practical experience I know these features are whitewashed pretty well for general consumption as the PR folks try to sell it. But I digress. The bottomline is that I value the insight we are provided by these pages, as it gives those of us outside the Oracle Corp a general idea where Larry is going.

The "How Oracle Works" section on 1-17 is excellent as well. This is the kind of stuff that makes me want to go and read about all the nitty-gritty details. The flow and overly-simplisitic nature of this section is perfect in this part of the Guide.

Another interesting point was the short blurb on Quiesce (1-20). I have never thought to use Quiesce to put the database into a sort of "restricted mode". In fact, the few occasions in the past that I have found my database in Quiesced mode, SYS could not do anything (other than shutdown/startup and change the mode back). Shows how little I know about this feature.

The rest of the chapter was appropriately basic, in my opinion. Lots of good bits of information, and I think they set the stage well for the rest of the Guide.

runInstaller problems: a tale of no support

2009-05-05T08:23:00.004-05:00

Granted, I am in a rather cynical mood; after painfully working my way through several SRs this past week (about 6 on Enterprise Manager and/or agents alone), I was not in the mood for any more games. So I gritted my teeth rather loudly when I had to open another SR about the Oracle Universal Installer. Or maybe we should call it the "Oracle Undetermined Installer"? Oracle Unbeknowst Installer?

We were doing a lot of Oracle Home installs. Yes, I could simply have clone a Home (choose your poison), but this was for a class and I wanted students to go through the experience. And we were using the silent installer, because that is just so much cooler. And much faster too boot. So half-way through, we start seeing something really strange - we would get the normal banner, the "Please Wait...." message and then... nothing. No matter how long you waited, nothing. The annoying part is that runInstaller likes to kick things off in the background, so it is not unusual to see some messages flash by, but one can still navigate. So if you see "Please Wait..." and yet can still type, one thinks the installer is still running. One would be wrong.

The Installer was simply aborting. The logs had absolutely no meaningful help in them whatsoever. So I pleaded my case with the smart folks on oracle-l and filed a case with Oracle. As always, I received some interesting information from oracle-l quickly, and Oracle Support just sorta... slowly... asked for an RDA (why?!? For all that is sacred and worthy, why on earth do you need an RDA for this?!?), asked for versions, and without blinking an eye told me my OS was unsupported.

Flustered, I fired up the Installer in graphical mode. Lo and behold, I get an error message saying /tmp was full (or rather, only 50mb was free, which is apparently not enough room for the temp JRE it wants to kick off). So after deleting some crap that the installer left behind from previous runs, I can now run the silent installer.

Thanks, Oracle Support, for being so helpful.

The OUI seems really antiquated. No, I take that back. It does some really awesome stuff when dealing with clusters! Amazing! But simple things like having a fully documented silent installer with cookbook response files are quite lacking. And the total absense of helpful error messages? But like many things with Oracle, they concentrate on the "bright shiny new things" and often do not turn back to polish the older things. No, that is not fair either - that is quite wrong. Oracle does go back and fix some things rather well. But the things that get left behind or fall through the cracks, they cause one to stumble, and are thus more noticeable. =) Unfortunately.

If Oracle were an MMO, what skills would you be trained in?

2009-04-02T14:17:00.002-05:00

Been thinking a lot about certification and skill assement lately. Had some excellent correspondence with Dan Norris, commented on Paul Sorensen's article, and talked with a new acquatance who is pursuing research on a Skills Management White paper. Dan Norris also hooked me up with Dan Morgan, who has been chewing on a new way to do certification that closely follows what the Medical Board does. All the while I am teaching a class that is supposed to prep the students for Oracle Certification (OCA, possibly OCP for those that can hack it), and really disliking the book.

Then in my free time, Stefan Knecht has re-introduced me to EVE Online (ummm... thanks? *grin*). EVE has a very cool, but very slow, skill training system. I am sure most folks would probably be familiar, in concept at least, with games like Blizzard's Warcraft that give "experience points" to characters for doing certain things. In EVE, you basically just pick a book and x hours later, you know that skill. A little strange, perhaps. But the cool thing (IMO) is the tech tree is based on the skill set, with a very thoughtful dependency relationship and considerable time investment for more advanced skills.

So... what happens when you smash these things together? What if you had some kind of classification like "Standard DBA Level 3", and, by definition, there were a set of prerequisite skills that the owner of said classification would have demonstrated proficiency in. Perhaps such granular classification does not make a perfect analogy in the game world, but still my mind is chewing on it. Perhaps being "certified" in every single skill a "standard" DBA does is not only impractical but unwieldly. But surely there would be a way to have some sort of dependancy tree. Almost anything is better than Oracle University's current system. =)

As I dwell on this topic, I have been thinking about what those skills might be. For instance, there are obviously broad categories like "Backup & Recovery", "Performance Tuning", "High Availability". Each of these could be further divided into component pieces; at some point, you get down to atomic skills. What are those atomic skills? There is obviously a similarity to the Mercer Salary Survey as well. Perhaps that is the closest thing I kind find in the IT world that attempts to do something like this. I am sure there are other efforts.

Another way this does fit with the game analogy is that it would not be feasibly for a person to attempt to specialize in everything (kinda counter-intuitive, anyway). As with EVE, you choose a career-path and pursue it (invest time). DBAs, or any professional career for that matter, are like this - start with something fundamental and then build on it in one direction or another.

Default number of rows displayed for "SET FEEDBACK"

2009-04-01T10:33:00.004-05:00

A colleague walked into my office and asked me what the default threshold was for the number of rows in SET FEEDBACK. I said "I am pretty sure it is just on", but wanting to be absolutely positive, I looked it up.

SET FEED[BACK] {6 | n | ON | OFF}

How odd! Why would the default be set to 6?? What kind of number is that? Why would you have a default number at all?

The colleague walked out with a smile.

Resolving file header contention on tempfiles

2009-03-04T14:54:00.002-06:00

We are finding many bottlenecks as we attempt to optimize a character-set conversion for our 450gb database. One of the tricks is using a custom-made script (paresh) to build indexes in parallel (ie, many indexes at once), since datapump likes to do them one at a time, albeit using a degree of parallelism.

So, the most recent bottleneck was buffer busy waits. So after a little digging around, we finally came up with this query:

select p1 "File #", p2 "Block #", p3 "Reason Code" from v$session_wait where event = 'buffer busy waits';

The tricky part was that we were getting back p1 values that exceeded the highest file_id found in both dba_data_files and dba_temp_files. Tanel Poder and K Gopalakrishnan helped us out by stating that these extraordinary numbers were actually the "real" file_id for the tempfiles, which are numbered file_id+db_files, and you get db_files from v$parameter.

With that out of the way, we realized that were hitting file header block contention (Reason Code 13) on the temp files. As Tanel succintly put it, "it's the block 2 in the file, this is where the LMT extent management bitmap header block lives." So our index builds were hammering the few lucky header blocks in our tempfiles that contained the extent bitmap.

My first thought was to increase the size of the tablespace extents, thinking that perhaps if there were few extents to worry about, the extent bitmap would not be in such high demand. Turns out the effectiveness of that idea was rather poor - or at least unnoticeable. Then I decided to increase the number of header blocks. One simple way to do that is add more tempfiles. And Viola! Now I create x number of tempfiles for x number of slave processes, and I have yet to see any more file header block contention on my tempfiles. Coolness.

On to the next bottleneck...

Complaint about Enterprise Manager (version 10g)

2009-02-19T11:29:00.003-06:00

First off, let me say that EM has some really cool stuff going on! This post will be largely negative and critical, but I do not want to discount the fact that the developers of this tool have done a lot of hard work to make some things very neat and slick. Real-time data for performance monitoring at various levels is quite nice. While the interface is kooky (very!), I have to give credit for the massive conglomeration of features that are packed into this "free" tool. And most of them work to boot! *grin*

Ok, so my first gripe is just administrating the EM itself. The logfiles are a complete mess. There is no one log file that summarizes all the activity. If you get an error in the GUI, it will most likely say "see log file for further details". Which log file? Granted, most of them are in one place (depending on if you are using DBControl or GC), but not all of them. Worse, it is very common to find a logfile that repeats what you saw on the GUI and it refers to itself for further details! How is that helpful? I want one file that can either tell me where exactly the problem is, or at least point to the file where the real further details are. Same for the config files. There are xml fiels sprinkled throughout, and .properties. Since EM is comprised of so many different components, there are many different log files and many different configuration files. But nothing ties them all together. Say I want to alter the ports of the components (I have tried this, and it is not pretty). I had to touch about 5 or 6 different files, each with multiple entries for various port numbers. And then there are those ports that are defined by global variables, with no indication where those are set. If one wanted a headache, a lemon-encased gold brick to the skull would be more appealing.

What next.... The GUI itself is extremely cluttered and the opposite of intuitive. I understand that some tools take a while to learn how to navigate (ie, TOAD - still catching on), but there are some functions I use quite often and I always find myself in the wrong place. Here is a tricky example, how about "Historical SQL". Do you mean the sql that EM has generated on your behalf, or AWR SQL? What about "features" that do not work quite well. We have tried Data Masking and Change Capture, both of which hung and finally died without any visible productive work. And trying to debug that?? See above paragraph. =) Or how about that infamous "remove datafile" option in the Tablespace screen? Cool, I did not know you could remove a datafile from a tablespace, this must be new. Yeah, try it and get back to me on that. I have had the opportunity to Queisce my database by accident; if you flip through the Maintenance Window management screens (just click ok or next), you can freeze your database because one of the options to specify the type of the current maintenance window defaults to Quiesce, even if that is not the current type. Handy, eh?

I am curious what the 11g EM will be like. I briefly looked at the 11g Database Control, but not enough to make any kind of judgement. Would it be foolish to even hope that the administration side of things has been made easier?

'nuff for now.

A tuning performance example using 10046, SQLTXPLAIN and 10053

2009-02-16T13:10:00.003-06:00

In my tribute to Carlos Sierra, I implied (well, rather, I just said) that I had more to write. Well, here it is.

I was alerted to a "slow performing" job in our ERP (the one out in Massachusetts). My first response is to turn on a 10046 trace; 99% of the time, this is extremely effective. The other 1% I either hit that strange issue where the event itself will change the query plan (I hate that!) or the job does too much stuff client-side for me to be of much use ("Yeah, umm... you only used .01 seconds of CPU time for your 5-hour job").

I have a script I use to create a trigger that turns on event 10046 (and others). If blogger had a better way to post links, I would put it up. But the guts are pretty easy to figure out - I accept parameters, then basically build a query that does the following:

CREATE OR REPLACE TRIGGER SYS.$trig_name
AFTER LOGON
ON DATABASE
begin
if (USER = '$user') then
execute immediate 'alter session set max_dump_file_size = UNLIMITED';
execute immediate 'alter session set tracefile_identifier = ''$user''';
-- execute immediate 'alter session set statistics_level = all';
execute immediate 'alter session set events = ''$trace trace name context forever, level $level''';
end if;
end;
/

If the session is already connected (hmm... that was redundant), I like to do the following:

set feed off verify off
set lines 112
select 'EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => '||sid||', serial_num => '||serial#||', waits => TRUE, binds => TRUE);' "Turn Trace on for user" from v$session where username = '&&username';
select 'EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => '||sid||', serial_num => '||serial#||');' "Turn trace off for user" from v$session where username = '&&username';
set feed on verify on

I have never seen a really good reason to use only level 4 or only level 8; I would use only level 1 if all I want is the SQL, but for the most part, level 12 all the way, baby!

Just to see what I can see, I run the resulting trace file through tkprof with sort options (sort=prsela,exeela,fchela), which puts the queries with the highest run times at the top of the tkprof output file. Lo and behold, I get a few solid candidates with this particular example.

SELECT SSBSECT_TERM_CODE,SSBSECT_PTRM_CODE,SSBSECT_REG_FROM_DATE,
SSBSECT_REG_TO_DATE,SSBSECT_CRN,SSBSECT_SUBJ_CODE,SSBSECT_CRSE_NUMB,
SSBSECT_SEQ_NUMB,SSBSECT_SSTS_CODE,SSBSECT_CAMP_CODE,SSBSECT_CRSE_TITLE,
SCBCRSE_TITLE,SSBSECT_SCHD_CODE,SSBSECT_INSM_CODE,SSBSECT_NUMBER_OF_UNITS,
SSBSECT_DUNT_CODE,SSBSECT_LINK_IDENT,SSRXLST_XLST_GROUP,
SSBSECT_RESERVED_IND,SSBSECT_MAX_ENRL,SSBSECT_ENRL,SSBSECT_SEATS_AVAIL,
SSBSECT_WAIT_CAPACITY,SSBSECT_WAIT_COUNT,SSBSECT_WAIT_AVAIL
FROM
SSVSECT order by ssbsect_term_code, ssbsect_subj_code, ssbsect_crse_numb,
ssbsect_seq_numb

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.27 0.27 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 996.36 1970.42 88 956167 176632 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 996.63 1970.70 88 956167 176632 0

I was told that this usually takes closer to 5 minutes (not 33 minutes). Stranger yet, all examples of this query with valid predicates trimmed the run-time down significantly (sub-second), which tells me that predicates are being pushed into the view (a good thing).

Just as a litmus test, I ran this query with a privileged account (SYS), and noticed that the query plan was indeed much better - so at least I knew there was a better plan. And then I realized that we use FGAC policies against the underlying tables to enforce VPD. Great. Tuning FGAC is no fun at all. Because I like to have the complete query in front of me, I got the view source (formatted in TOAD), then ran the query with the original user id and event 10730 turned on (captures VPD predicates). I was then able to reconstruct the query that finally got passed into the parser.

Trying to get the original query to use a better plan, I started playing around with hints and statistics (in independent tests). The hints are a pain because, as Jonathan Lewis will often point out, you want to use a ton of 'em (not just one or two). Which got me to thinking about the 10053 trace and how that gives you the hints to reconstruct a query plan in a nice format (easier than reading other_xml out of v$sql_plan). I also stumbled upon some new index statistics that gave me the "better" query plan. As I attempted to explain which statistics did the trick, I turned to SQLXPLAIN to compare the queries for me, and it did a wonderful job (as stated in previous post)! It helped me to narrow down the problem to the fact that the the combination of more rows and fewer leaf blocks had altered the clustering factor enough to be noticeable.

"Now wait", you say, "how do you get more rows and fewer leaf blocks?!?" Easy. If you rebuild your index and do not collect stats for a few months on a busy table... viola! *evil grin* Ok, I need to include the fact that we recently went through a character set conversion which required us to completely rebuild the database; at the time, we also decided to import all the old stats, which is much faster than generating new ones. Unfortunately, we overlooked the fact that indexes will generate new stats when you build them, so importing old stats is a bad idea (in general) for indexes.

So, we have an altered clustering factor. How did that affect the query plan to go from 5 minutes to 33 minutes? To be honest, I do not have all the answers yet. I am still researching the "why" behind this. What I have gathered so far is that the index selectivities have changed dramatically (these came out of the 10053 trace):

OLD/SLOW - uses PK index
Index: PK_SSBSECT Col#: 53 1 2
LVLS: 2 #LB: 4104 #DK: 649804 LB/K: 1.00 DB/K: 1.00 CLUF: 30917.00

Access Path: index (IndexOnly)
Index: PK_SSBSECT
resc_io: 3.00 resc_cpu: 21564
ix_sel: 7.6946e-07 ix_sel_with_filters: 7.6946e-07
Cost: 3.01 Resp: 3.01 Degree: 1

NEW/FAST - use table scan instead
Index: PK_SSBSECT Col#: 53 1 2
LVLS: 2 #LB: 3072 #DK: 689157 LB/K: 1.00 DB/K: 1.00 CLUF: 34646.00

Access Path: index (IndexOnly)
Index: PK_SSBSECT
resc_io: 2294.00 resc_cpu: 119163783
ix_sel: 0.74604 ix_sel_with_filters: 0.74604
Cost: 2326.68 Resp: 2326.68 Degree: 1

So, CLUF increased by 12% and LB decreased by a whopping 25% and DK up by 5.7%. So how did I end up with a ix_sel that was different by 10^6? I mean, I see why a tablescan was preferred! =) But what the heck?

Did I mention I am eagerly awaiting for that 10053 parser? =)

Kudos to Carlos Sierra

2009-02-13T11:58:00.003-06:00

I have been playing more and more with Oracle's SQLTXPLAIN; or rather, let me give the full credit to Carlos Sierra, who has written and fully supports this nifty little gem.

Jonathan Lewis get me hooked on dbms_xplan, and I like it a lot! Just the other day I picked up +PEEKED_BINDS as an option from various posters (Tanel Poder, Greg Rahn, Jonathan Lewis) and started using it right away. And I never really tackled SQLTXPLAIN just because.... I had used the earlier version (TRCANLYZR...or something). Until recently.

Carlos has really done a bit of quality work on this piece; he has provided excellent documentation and some good examples. He has shown a wonderful amount of foresight in generating various traces (10053, 10046) and packages functionality like generating testcases, comprehensive web reports and sql plan comparisons. The last of those I had an opportunity to explore today, and it helped me find a problem with some indexes (that will be a seperate post after the dust settles). I just really like the presentation and functionality of this tool. The "main" html report is very tasteful with expand/collaspe java snippets, go flow, and tons of useful information. The comparison did a great job at highlighting areas of concern, and his "Observations" often make me think about something, even if only to affirm. I like the flexibility of grabbing a sql_id, sql hash or plan_hash.

True, it is not perfect yet. Very close, though. I do not like how the install scripts ask for a "schema user" or "application user". To work around that, I create a new role (SQLTXPLAIN_ROLE) and provide that as my "application_user". Then, whenever I want to run SQLTXPLAIN, I just grant/revoke that role to the real application userid. Also, altering the parameters are a little kludgy. I love that Mr. Sierra has made the parameters available and has provided documentation for them (a pleasant surprise), but it would be nice to specify them at run time in a more "normal" fashion. Like, having a flag to turn off the table exports. And what about using datapump instead? =)

One other thing that I am still working on is to get it to work as a client-side piece. After working through a few kinks (ie, my bads) with Sierra, the only portion that bothers me a little is that it keeps asking me for the password for exporting tables. For the most part, I want to skip that.

Anyway, this tool is AWESOME! I love it! I will still use dbms_xplan a bit (afterall, this tool does as well), since it is much lighter weight. But I am definitely adding SQLTXPLAIN to my toolbox and telling others about it. I have also had a few exchanges with Carlos, and he is definitely on the ball.

I am extremely curious about one thing yet. There are a number of interesting tables in the SQLTXPLAIN schema. What is SQLT$_10053_PARSE for? I have never seen it populated with any information. Is there a tool that parses the 10053? Can I have it?!? =)

Now I wonder what other tools BDE has hidden away....

Teaching and Certifcation

2009-01-26T09:05:00.004-06:00

I have been presented with the opportunity to teach at a nearby community college, and I was instantly interested. Little did I know what would be under this newly turned stone.

The class is the 4th part of a 4-part series based (loosely) on Watson & Bersinic's "Oracle 10g All-in-one Certification Exam Prep". After going over the book and finding it dishearteningly weak on practicality, I opted to rewrite the curriculum a little. Or rather, a bit.

The book is interesting; it is a comprehensive, rehashed summarization of the online documentation that aims to get you ready to take the OCA and OCP tests. Perhaps it does that. Ever since Bush's "No Child Left Behind" poorly-implemented federal program cursed our educational systems, I have been convinced that "teaching to the test" is horrible in the long-run and only encourages bad habits of short-term consumption and regurgitation. However, the book is also rife with errors, both in the form of typos, bad index pages, and also technical errors and self-contradictory statements. To top it off, the list price is a freakin' $105!!

Having had dreams of utilizing the tenets of the OCM practicum in a classroom setting, I took this bull by the horns and decided to make all the online homework based on the published OCM outline. Some of the outline topics are rather easy to work up a lab for (ie, creating a database, using RMAN, etc). Some of the topics are a bit more difficult to recreate; for example, what exactly does the 10g OCM practicum test in terms of RAC setup and configuration? But for my purposes, I do not really care. Just as a disclaimer, I have never taken the 10g OCM.

This is a relatively entry-level class, so I am concentrating on the "basics"; I am going to make sure the students know how to create, configure, connect to, manage, backup, recover, tune and optimize the database. The first homework assignment was awesome (IMO). Awesome because these students, after attending the prerequisite classes, had never created a database from the command-line before; they even struggled with some of the things like where to put datafiles, how to size the memory parameters and running the catalog/catproc scripts. And I think that is a good kind of struggle. They asked questions, they explored, they made mistakes. In general, they got their hands dirty doing what we in the real world do. Opposed to answering some silly multi-choice question about which line of sql has an error in it.

And what does this have to do with Certification? Would you hire someone who held an OCP card yet had never created a database? Never had the frustration of a ora-4031 (or any number of ORA errors)? What the heck is Oracle actually certifying as a "Professional" anyway? Oracle ostentaciously says that they require the instructor-led training classes specifically for that "hands-on" aspect of the OCP. Have you ever taken those courses? The labs are a joke. The classes are mostly boring lectures. Unless you end up with someone passionate like Andy Fortunak or Sue Jang. Still, I find the whole scheme rather offensive.

I spotted a blog entry by Dan Norris, who in turn had stumbled up on a thought-provoking blog entry by Bex Huff. Dan also mentioned that Oracle has a Certification blog, so I quickly made my way over there and spouted a little. I really mean what I said there; rewrite the certification paradigm and actually make it worth something, instead of a cash cow for Oracle U; isn't Larry already rolling in enough dough?

To top it all off, my students will not be eligible for certification even if they do pass the OCP. Why? The community college where I teach is not a "Oracle Certified Teaching Partner". So, even though the students will most likely get a much better education at the community college than some class they have to travel to and pay $3500 for, they will not be able to be certitifed as those that are able to expend the time and money to do so.

I dearly hope my students appreciate the hell I am putting them through. =) Fortunately, I have recieved a lot of feedback that they thoroughly enjoy it. It is going to be a good semester.

Upgrading clusterware/ASM from 10g to 11g - a complaint

2008-12-04T07:52:00.003-06:00

I have a RAC setup with 3 environments; DEV, BCP and Production. So, starting with my development cluster, I made an attempt to upgrade the clusterware and ASM from 10g to 11g. That was quite painful. I will not spend much time complaining about the documentation here (and there is a lot I could say *grin*). But allow me to take a little tangent.

I collected all my "constructive criticisms" and suggestions into what I hoped was a cogent document and fired it off to Oracle Support and Erik Peterson of the RAC SIG. Erik forwarded my note to a document manager, and his response was overwhelmingly encouraging and very understanding. The Oracle Analyst in Support that picked up my case was also quick to understand my frustrations. Both of these two different groups were able to collaborate and redeliver a nutshell document that was supposed to supplant/supplement the online documentation.

For the clusterware upgrade, they hit it on the nose. I was able to upgrade the clusterware in my BCP environment with no problem. Well.... I admit, I am still very confused about the CRS and ASM software owners. There seems to be a push to use different users (in both cases) other than the normal "oracle" user. There is not much justification in the documentation, IMO; rather a bland "for security reasons" statement.

The ASM upgrade was a little more tricky. All the documentation that I have, including the new nutshell cookbook, indicate that ASM can be upgraded in a rolling fashion. That is a lie! *grin* I have a new SR open that is exploring this little nuance (or not so little, depending on how much time you have spent trying to wrap your head around this). Apparently, ASM Rolling Upgrades are available in 11gR2. Oh..... well, guess I'll just go download 11gR2 then. Wait - it is not available yet?!? So the documentation lists steps that are not applicable to any version that is currently available? Did I read the right?

So confusing. The good news is that I was able to complete the BCP upgrade with less pain. Since the documentation strongly suggests that one create a new ASM user, there are a ton of manual steps to follow if you choose to go that route. However, smart DBA that I am *sly grin*, I kept the same user and was able to use the DBUA to go from 10g to 11g (10.2.0.3 to 11.1.0.6) - that was sweet. Why would I want to switch users and go through many manual processes? What is the rationale for that? The patchset upgrade from 11.1.0.6 to 11.1.0.7 was less friendly. As I mentioned above, all the documentation states that you should do a rolling upgrade. This is not possible. So I hacked my way through; had to shutdown abort my instances which left shared memory segments hanging around that I had to manually kill off. Not a big deal, but the error messages can be very confusing (ie, ora-12547 TNS lost contanct).

I am looking forward to the document changes that will result from my contacts with Oracle.

A Tuning experiment

2008-09-18T15:43:00.003-05:00

As I learn more about the CBO and Banner, I start to wonder about "system tuning". I like Cary Millsap's "Method R" concept; I was thinking about how to adapt that in the narrow field of sql statements. If, for example, I turn set statistics_level = ALL, I know I can generate rowsource statistics at the cost of perhaps 5% overhead to my system. Doing this in a development environment, I could utilize Wolfgang Breitling's "Cardinality Feedback" to find sql statements that are not performing optimally, and maybe even rank them by degradation. Maybe.

Why would I want to do that? Good question. In and of itself, it is a hard sell if my only purpose is to go on a witch hunt and try to tune the system purely from the sql. However, I think the bigger advantage is to use the various sql statements as teachable moments, a way to develope real-life examples for our DBAs that demonstrate how our application(s) behave, and how to attack "slow" statements. The fortunate side-affect is that we end up making some things faster.

Just an idea. I have not been able to drill down into 11g's automatic tuner yet, but I have to believe it is using something similar to cardinality feedback. If memory serves, I believe it does spend some extra overhead reconsidering previous sql plans in an attempt to find those that are not optimal (or that crazy phrase "suboptimal"). Now I have to go back and read up on that again just to refresh myself....

Character Sets

2008-08-27T13:30:00.004-05:00

I have been tasked with presenting on the topic of converting the character set for our Banner system databases. We have been through the exercise several times, which has been quite an eye-opening experience in itself (The DataPump Index work-around, dbms_pclxutil.build_part_index, creating tablespaces). Essentially, the vendor has decided in all its god-like wisdom to force all customers to start using a multibyte Unicode character set that will support international characters (AL32UTF8) when they upgrade to the latest version 8. Yea, not very practical for us at a Big Ten school.

Anyway, I have taken this thing to heart and have been learning a lot about codepoints and various facts about character sets. We will be converting from WE8ISO8859P1 to AL32UTF8, a single-byte ISO 8859-1 West European somewhat basic (eg, simple) codepoint to the latest'n'greatest multibyte grandaddy of Universal character sets. The big issue is making sure that all the data stays the same, even if physically it is stored a little different. Along the way, I have had to come to terms with phrases such as "Lossy Data" and "Truncation Data". April Sims has been a great help in answering some questions and getting me to focus my searches. In the end, Metalink note 252352.1 laid out an example that finally opened my eyes for Lossy Data.

I was having a hard time understanding why one must go through WE8MSWIN1252 first to resolve lossy data. And what was meant by the notation in Metalink Note 260192.1:

if you have a WE8ISO8859P1 database and lossy then changing your WE8ISO8859P1 to WE8MSWIN1252 will most likly solve you lossy [sic]

Why? Well, the Euro example in note 252352.1 is great. To paraphrase, the Euro is stored as codepoint 128 in WE8ISO8859P1. In this case, that character set essentially has an empty hole there, no previous definitions for that codepoint. So if one were to attempt to select it in some kind of "native" WE8ISO8859P1 environment, there would be nothing. So how did it get there? It is passed in from a different character set. And if using that same character set on the client side, you can easily retrieve the correct symbol.

Since WE8WSWIN1252 is a superset of WE8ISO8859P1 (meaning it has all the same codepoints for existing symbols, and then some), and it is most likely that a client in the US inserted data with a US Microsoft character set, the codepoint will be mapped to a valid symbol. Since that codepoint is "valid" in WE8MSWIN1252, it can then be converted to any other character set that also supports that symbol.

Where this falls apart (the "likly" part) is if the client using a "foreign" character set inserts a symbol that is mapped to a completely different symbol (via the codepoint) in our target client character set. In that case, you are screwed. =) Unless you would want to convert that data manually for each case of incompatible character sets. Not I.

Truncation data is a little easier to grasp. Metalink note 119119.1 mentions the English pound sign (£):

The pound sign for example is indeed 163 ( A3 in hexadecimal) in the WE8ISO8859P1 and WE8MSWIN1252 charactersets, but in AL32UTF8 the pound sign is code 49827 (C2 A3 in hex).

In our case, the fix is handled by setting nls_length_semantics=CHAR (opposed to the default BYTE). We also found out through experimentation that nls_length_semantics is completely ignored for SYS and SYSTEM objects; in fact, the data dictionary is always with BYTE semantics. Interesting, eh?

Metalink Notes:

260192.1 Changing WE8ISO8859P1/ WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8
225938.1 Database Character Set Healthcheck
252352.1 Euro Symbol Turns up as Upside-Down Questionmark
279068.1 Usage of Character Set Scanner Utility (csscan) version 1.1
137127.1 Character Sets, Code Pages, Fonts and the NLS_LANG Value
241047.1 The Priority of NLS Parameters Explained
158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
179133.1 The correct NLS_LANG in a Windows Environment
119119.1 AL32UTF8 / UTF8 (Unicode) Database Character Set Implications

Others:
Oracle® Database Globalization Support Guide 10g Release 2 (10.2)

Software Configuration Manager

2008-08-01T09:16:00.002-05:00

I learned that Oracle Support has a couple Oracle blogs up:
http://blogs.oracle.com/supportportal/
http://blogs.oracle.com/Support/

I think I was just checking on them when I noticed that the supportPortal was being a little more aggressive looking for more feedback. Over the years, I have participated in many of the Survey's that Oracle sends you after you close an SR, and have made no small number of comments (both good and bad) to a growing number of contacts I have established within Oracle, so this seemed like yet another opportunity to voice my opinion. I think I do that much more when I get the impression Oracle is actually listening; I don't share the same thoughts here because, well.... I highly doubt Oracle is reading this. =) No offense.

So let me blur the lines a little. First, I think Oracle is doing a much better job at collecting client feedback. However, the more critical part is what they are doing with that feedback. Unfortunately, for us end users, those comments seem to disappear into a black box, never to surface again (much like Enhancement Requests). I chatted with some really nice folks at Oracle (Kristal and Cherryl) and they both reassured me that Oracle is taking our comments seriously, and in fact hinted that the ongoing evolution of Metalink 3 is largely due to user feedback, and even newer things are coming down the pike.

Taking the invitation from the SupportPortal blog seriously, I called up for an appointment and got hooked up right away. I think the lady running the feedback session was working at home (heard a small child in the background), and she had some setup work to accomplish before we got down to it. But 90 minutes later I had made numerous observations about many aspects of the Software Configuration Manager and Metalink 3 in general. It was refreshing (and exhausting) to not only state some things I either liked or did not, but also to demonstrate via a recorded web session. Very satisfying; I recommend this to anyone who deals with metalink on a frequent basis.

So, what did I say? In general, I like the eye-candy of the new interface, but it is also a little slow and sometimes downright confusing. For instance, there is no way to bookmark bugs. The options to configure the layout are a nice thought, but still a little limited; it reminds me of how limited blogger is compared to WordPress, if that helps at all. Don't get me wrong, I think it is quite handy to configure the layout, but I really look forward to improvements. The key point, Software Configuration Manager, is a great concept. Unfortunately, I have some political roadblocks to fully utilizing SCM with my employer, which makes it a little frustrating when attempting to comment on the SCM because it is not SCM's fault at all. =) We require that we either have separate CSI numbers, or implement some sort of access to control such that not all our end-users have equal access to a site-wide license. But if we ignore that for a little while, I think SCM is awesome and has a lot of potential. Just the fact that it helps speed up SRs significantly is a big bonus. I have heard some misgivings about allowing Oracle to retrieve information in such a fashion, even if only passively, but I personally do not have an issue with it.

I do not like how some of the questions are completely inane. For instance, 99% of my SRs are for the Oracle Server - Enterprise Edition. 99% of the time I forget what exactly my "Product" is called and have to hunt for it amongst a list of several hundred product options, most of which we are not even licensed for. Why? What about a list of 10 most recent products for my CSI? What about a list that only contains products I am licensed for? And when attempting to find a category that somewhat matches the subject of an SR, I hate having to select one from the list of 30 or so. I want a searchable list. And a fast search at that; the LOVs in the patch pages really suck.

I concluded by saying that it would be vitally important to me see how Oracle is using this feedback. I have repeated this message to my Oracle contacts and with nearly all feedback I give to Oracle. I long to see what difference I make. Don't you?

Kudos to the teams involved in the feedback gathering operations. And BIG PROPS to Cheryl, Joey and Kristal (all of Oracle).

create tablespace ddl

2008-06-26T10:05:00.003-05:00

I recently went looking for ways to recreate tablespaces, as we prepare to migrate our production database to a different character set (necessitated by the vendor, grrr). DBMS_METADATA is really cool, and I like it alot. Data Pump essentially uses DBMS_METADATA (I believe), so I thought one of those cool tools would be the way to go. Close, but not quite. If you do a complete export/import of your database, you end up reorganizing all your data, in essence defragging the whole thing. Which is good (nay, GREAT!). But Data Pump only lets you create tablespaces with the original datafile sizes.

Granted, considering that this is Production, prudence would say that one will have tested this several times (as we are currently doing) before actually running the process in Production. And in doing so, one will know exactly how large the files should be, and can in fact extract the ddl (metadata api, data pump, TOAD, etc) from the test database. As a simple exercise, I still want a way to generate slightly customized tablespace ddl. So I ended up writing my own.

Some of the key things I wanted was to be to dynamically adjust the initial sizes of the datafiles. Also, for the sake of portability, I added a runtime parameter for maxsize. I am still torn about LMT being locked into UNIFORM, but since that is all we use here, I am not too worried for us. =) But I do want LMT and ASSM to be default; for 10g, I see no reason to turn these off at all.

Blogspot is going to horribly screw up the formatting for this, and I have not found a way around it. Sorry.


REM create_tablespace_ddl.sql
REM Charles Schultz
REM 25-Jun-2008
REM
REM Generates tablespace DDL
REM Assumes the following:
REM - Excluding ('SYSTEM','SYSAUX','USERS','TEMP','TOOLS','UNDOTS')
REM - All LMT
REM - All ASSM
REM - All datafiles AUTOEXTEND, initial size = half of current, or size of initial extent, maxsize defaults to 4000M
REM
REM Retains INITIAL_EXTENT settings for LMT
REM
REM Builds 5 inline materialized views
REM - tablespace_specs: gets the data from dba_tablespaces and dba_data_files, ordering by file_id
REM - first_ddl:        generates the initial "CREATE TABLESPACE" statement, along with the first datafile
REM - last_ddl:         generates the tablespace options (LMT, ASSM)
REM - mid_ddl:          generates statements for any datafiles that are not the first
REM - tablespace_ddl:   Combines all ddl, ordered by tablespace name and section
REM
REM Limitations
REM - Does not allow for BIGFILES, but this is a trivial change
REM - Does not allow for LMT Autoallocate. Again, not a difficult modification

PROMPT This script will generate tablespace ddl, making the following assumptions:
PROMPT - All tablespaces are Locally Managed (LMT) with UNIFORM sizes
PROMPT - All tablespaces have automatic segment space management (ASSM)
PROMPT - All datafiles autoextend
PROMPT

accept sqlfile default 'create_tablespaces.sql' char prompt 'Enter name for generated sql file [create_tablespaces.sql]: '
accept initial_size default '50' char prompt 'Percent by which to shrink datafile initial sizes, 0 being none [50]: '
accept max_size default '4000' char prompt 'MAXSIZE of autoextend datafiles, in megabytes [4000]: '

set head off newpage 0 pages 0 verify off feed off lines 200
spool &SQLFILE
with tablespace_specs as 
(select row_number() over (partition by tb.tablespace_name order by df.file_id) rn_up,
 row_number() over (partition by tb.tablespace_name order by df.file_id desc) rn_down,
 tb.BIGFILE,
 tb.TABLESPACE_NAME,
 df.FILE_NAME,
 greatest(df.BYTES*((100-greatest(least(&INITIAL_SIZE,100),0))/100),tb.INITIAL_EXTENT) file_size,
 df.INCREMENT_BY*tb.block_size INCREMENT_BY,
 tb.INITIAL_EXTENT
 from dba_tablespaces tb,dba_data_files df
 where tb.TABLESPACE_NAME = df.TABLESPACE_NAME
 and tb.tablespace_name not in ('SYSTEM','SYSAUX','USERS','TEMP','TOOLS','UNDOTS')
 ),
first_ddl as
(select tablespace_name,
 1 section_num,
 'create tablespace '||tablespace_name||' datafile '''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
 from tablespace_specs where rn_up = 1),
last_ddl as
(select tablespace_name,
 3 section_num,
 'EXTENT MANAGEMENT LOCAL UNIFORM SIZE '||INITIAL_EXTENT||' SEGMENT SPACE MANAGEMENT AUTO;' text
 from tablespace_specs where rn_down = 1),
mid_ddl as
(select tablespace_name,
 2 section_num,
 ','''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
 from tablespace_specs where rn_up > 1),
tablespace_ddl as
(select text,tablespace_name, section_num from first_ddl
 union
 select text,tablespace_name, section_num from mid_ddl
 union
 select text,tablespace_name, section_num from last_ddl
 order by tablespace_name, section_num)
select text from tablespace_ddl
/
spool off

Stragg

2008-06-11T14:38:00.002-05:00

Tom Kyte popularized the STRAGG function, a sometimes critical piece missing from Oracle's arsenal of cool commands, has been bandied about quite a bit:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21961626004

We were recently tasked with using a STRAGG-like function but with dynamic delimiters. Tom Kyte's standard STRAGG does not handle that. Enter Adrian Billington, who took it one step further (look for "10g goes one further"). I really liked his example, and it caused me to research the COLLECTION functionality a little deeper. A little - I do not claim to have mastered it by any means.

Unfortunately for us, we seem to be hitting bug 4381035 (not exactly sure about that, but the best hit I can find). This is under 10.2.0.2.

BANDEV_SQL > CREATE OR REPLACE TYPE oracle.stragg_type AS TABLE OF VARCHAR2(4000);
/
2
Type created.

BANDEV_SQL > CREATE OR REPLACE FUNCTION oracle.stragg (
nt_in IN stragg_type,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN varchar2 IS

v_idx PLS_INTEGER;
v_str varchar2(32000);
v_dlm VARCHAR2(10);

BEGIN

v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;

RETURN v_str;

END stragg;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Function created.

BANDEV_SQL > drop table test;

Table dropped.

BANDEV_SQL > create table test (a char(2), b char(1));

Table created.

BANDEV_SQL > insert into test values ('aa','1');

1 row created.

BANDEV_SQL > insert into test values ('aa','2');

1 row created.

BANDEV_SQL > insert into test values ('aa','3');

1 row created.

BANDEV_SQL > select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a;
select a, oracle.stragg(CAST(COLLECT(b) as ORACLE.STRAGG_TYPE)) b from test group by a
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -

This was a bummer. After a bunch of reading and experimenting, I finally hit upon something that seems to be working:

BANDEV_SQL > select a, oracle.stragg(cast(multiset(select b from test) as oracle.STRAGG_TYPE),'|') b from test group by a;

A
--
B
--------------------------------------------------------------------------------------------------------------------------------------------
aa
1|2|3

I am not exactly sure why MULTISET is working better than COLLECT in this case; it seems to me that both should be producing nested tables.

I am digging this learning thing

2008-05-16T14:13:00.003-05:00

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

Learned a couple things this week

2008-05-09T15:29:00.003-05:00

We had a couple examples of how a high watermark affects FTS, especially when that FTS gets executed on the wrong side of a FILTER operation. Which got me to delving into the black magic of high water marks.

First off, Oracle only mentions them in passing. If you search for LHWM (low high water mark), you will get a couple hits in metalink for a bug and nothing in the documentation. So, what the heck is a low high watermark anyway? And a high high watermark?

Apparently, after a couple lessons from K Gopalakrishna, Ric Van Dyke and couple others, the LHWM is the boundary at which all blocks below it are gauranteed to be formatted and used at least once. Above the HHWM we are gauranteed that all blocks are unformatted and never used. Between the two we have the "grey area"; namely, a mixture of blocks that have and have not been used. I believe there are two bitmaps (or two levels of bitmaps?), one that maintains the freelists for below the LWHM, and another for above the HHWM. On Full Table Scans, Oracle will scan everything below the LHWM, and then use the L2 bitmap to find blocks that need to be scanned above the LHWM.

Oracle seems to have gone to great lenghts to make these two watermarks as confusing as possible. In the documentation, and when using packages like dbms_space, we are led to believe there is only one HWM, even for an ASSM tablespace.

Thankfully, folks have discovered "dump file". Norman Dunbar gives a couple quick examples. This is good stuff; there is a lot in the dump files that would warrant a closer look. But for right now, let's look at the watermark bits of information:
alter system dump datafile 5 block min 1 block max 100;

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01403030 ext#: 1 blk#: 7 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 7809
mapblk 0x00000000 offset: 1
Disk Lock:: Locked by xid: 0x000a.013.000003a4
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01403030 ext#: 992 blk#: 7 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 7809
mapblk 0x01402aa1 offset: 177
Level 1 BMB for High HWM block: 0x01403019
Level 1 BMB for Low HWM block: 0x01403019

This is not necessarily the world's best example, but heck, it is raw data. =) I have yet to decipher what all that means, but I am working on it. I believe that the LHWM "blocks below" will always be less than the HHWM "blocks below", but the difference would count the number of blocks in that grey area.

As an aside, I also found that you can dump all the file headers with:
alter session set events 'immediate trace name file_hdrs level 10';

The level is a bit confusing, as I tried various numbers and the resulting trace seem to be exactly the same.

On the RMAN side, we came across a peculiar curiosity. With no recovery catalog, if you recover a controlfile and have archive logs beyond the time of the controlfile, we found that on OSEE 10.2.0.1, RMAN is not smart enough to roll forward if you specify an "UNTIL TIME" in the future relative to the controlfile. Instead, you will see errors that resemble messages if the backup is corrupted; RMAN will attempt to go backwards in time to find a file that matches an appropiate SCN. Of course, it will never find one. This behavior is "fixed" in OSEE 10.2.0.4 (we did not try any of the intermediate patch levels), and RMAN will now roll the database forward beyond the controlfile time.

dbms_pclxutil.build_part_index

2008-04-16T10:11:00.002-05:00

While working on our datapump workaround, I stumbled upon dbms_pclxutil.build_part_index. This is a handy little bugger that has been around since at least 8.1.5, and has some interesting limitations. For starters, the said index has to be partitioned and unusable. Not a biggie, but why? I also found out (the hard way) that not only do you have to run the procedure as the table owner, but also the index owner as well. Or to put it in other words, you have to create the index in the same schema as the table, and run the procedure from that schema only. That rather confounds me; when I asked Oracle Support about this, I was given some schtick about how they can't have users running around building partitioned indexes on other people's tables. If you ask me, I am thinking some developer back in the early '90s wrote this handy piece of code, but made some assumptions, and those assumptions were never corrected going forward. Strange how after all these releases, it is still not very well documented.

ah well....

11g and DBControl, Part 2

2008-04-11T08:32:00.003-05:00

After finding two bugs with 11g DBControl already (and that was just the installation), I am finding another basic problem. I am experimenting with RAT and attempting to replay a workload. The ability to export a workload from a 10.2.0.4 database and replay it in 11g is a huge thing. If it works. If it works, we may have a huge demand for it; shipping off our workload to 3rd-party vendors (software and hardware) for benchmarking, internal testing of various upgrade options, etc.

So I set out to generate and capture a workload in a freshly upgraded 10.2.0.4 database. First I have to set pre_11g_enable_capture to true, which is a little odd. Why is it default to off? From there, you can loosely follow the online documentation:
backup source database
capture workload
restore database elsewhere
preprocess workload
replay workload

I was good up until the last step. Since the topic is DBControl, I decided to use DBControl to preprocess the workload after I datapumped my source into a target 11g database. DBControl then directs you to hit a link to replay the workload by configuration and initiating worker threads (aka, Client Connections). My workload was a mere 20 minutes (just for an initial test). My "Client Connections" have been attempting to start for 14 hours so far. They would probably run indefinitely if I let them.

I have Oracle Support working on this, but I have to admit, all these little buggy problems with my first experience in 11g is not very encouraging. No, let me restate that - they are downright discouraging. The "base" realease is 11.1.0.6, which seems to indicate the 5th minor patch. How did these issues make it through 5 rounds of testing? I am rather confused. Perplexed. Mystified. Discombulated. Nothing like kicking the tires and finding that they are flat.

Just as a side note, I also ran a workload capture for 14 hours (different box). Oracle generated over 37 thousand files! Wow.

Updated 19:35 pm
Here are all the bugs that matched from WebIV (kinda scary):
Bug.5932196 (80) DEADLOCK BETWEEN MV_REFRESH (DEL) AND AUTO GATHER_STATS_JOB Gen V111:
3 Bug.6668534 (80) REPLAY CLIENT HANG UPON REPLAYING KILLED SESSION (ORA-28) Gen V111:
3 Bug.6668157 (80) WRC CLIENT HANG WHEN START REPLAY FAILS Gen V1110:
3 Bug.6661772 (80) TRANSACTION ID AT CAPTURE FILE HAS AN INVALID VALUE Gen V1110:
3 Bug.5637692 (80) CANNOT REPLAY TKTUIM1.TSC WORKLOAD. WRC CLIENT ENCOUNTERS SIGNAL 3. Gen V111:
3 Bug.5636024 (80) TEST WITH UPIALL BUNDLED FUNCTION HANGS DURING REPLAY Gen V11:
3 Bug.6608197 (80) WRC CLIENT HANG WHEN 2 MMONS WAIT ON EACH OTHERS MSG Gen V111:
3 Bug.6596423 (80) WRC REPLAY PROCESS HUNG WHEN TESTING GLOBAL PORTAL EXCHANGE DUE TO I
NCIDENT Gen V111:
3 Bug.5587101 (80) INTERMITTENT HANG DURING REPLAY WITH OCI CONNECTION POOLING WKLD Gen V11:
3 Bug.6521269 (80) WRC REPLAY PROCESS HUNG WHEN TESTING GLOBAL PORTAL EXCHANGE DUE TO LOB Gen V111:
3 Bug.6141226 (80) REPLAY HANG AFTER 10 HOURS CAPTURE Gen V111:
3 Bug.6068506 (80) WCR REPLAY HANGS WAITING FOR COMMIT THAT WAS NOT SUBMITTED Gen V1110:
3 Bug.6046429 (80) TST&PERF - WRC HANG WHEN WORKLOAD REPLAY Gen V1110:
3 Bug.6025549 (80) WCR REPLAY HANGS WAITING FOR ROW LOCK

Metalink 3 coming "next year"

2008-04-03T14:38:00.004-05:00

While communicating with an Oracle Global Customer Care employee today, I learned that Metalink 3 is coming next year. This should be interesting.

I filed 3 feedback SRs today:

Allow Bugs older than 14 days to be views on Headlines
Remove "News & Notes" from headlines
Correct problems with "Certify & Availability" section of Headlines

I also some outstanding requests to view more than 365 days worth of SRs, and allow some sort of hierarchies within a single CSI.

Update Thu Apr 3 15:44:03 CDT 2008
From the horse's mouth:

Faster problem resolution
- Speed problem resolution with quick access to the information you need with our new personalized dashboard
- Facilitate faster resolution with a robust knowledgebase and powerful self-service tools
- Optimize system availability with enhanced Service Request (SR) management
Extended preventative, automated support capabilities
- Receive a simple, fast, easy way to maintain your Oracle systems
- Mitigate risk with simplified configuration management solutions such as Software Configuration Manager
- Proactively prevent problems and accelerate problem resolution with automated HealthChecks and Product Alerts
Increased personalization
- Utilize increased personalization and knowledge integration via OracleMetaLink 3's new user interface for powerful information management
- Personalize your homepage content for easier access to self-service tools and configuration data
- Quickly access your Service Request history with MySRs

11g and DBControl

2008-04-02T14:48:00.005-05:00

After hearing and reading about so many of the wonderful new toys with 11g, I decided to give some of them a whirl; I tend to learn better when I can break it myself.

Unfortunately, I ended up breaking something else even before I could begin. When installing the software with the basic default options (starter database, DBConsole), emca blew chunks when trying to start dbconsole. This is not the kind of headache I wanted, but I am hoping to use this as a learning opportunity. Somehow. Hopefully I will be able to provide a resolution when this is over with.

For starters, the trace and emConfig.log are a bit hard to read. I did find some key occurences of "OC4J Configuration issue", so I turned to Metalink and google. Metalink was particularly unhelpful. The closest match I could find was Note 549079.1, which is good in and of itself, but 1) it says 10g, not 11g, 2) none of the 3 cases in step 5 applied (I tried all of them). I am still slogging through Google hits, but so far the only solutions I am finding are for 10g, and they do not seem to apply to 11g.

From emConfig.log
CONFIG: OC4J Configuration issue. /u01/app/oracle/product/11.1.0/oc4j/j2ee/OC4J_DBConsole_server1_TEMP not found.

Apr 2, 2008 2:12:05 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /u01/app/oracle/product/11.1.0/bin/emctl start dbconsole
Apr 2, 2008 2:12:05 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at /u01/app/oracle/cfgtoollogs/dbca/TEMP/emConfig.log for more details.
Apr 2, 2008 2:12:05 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error starting Database Control
at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:869)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:250)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:213)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:235)
at oracle.sysman.assistants.util.em.EMConfiguration.run(EMConfiguration.java:460)
at java.lang.Thread.run(Thread.java:595)

Now, why would a required file not be found? Keep in mind this is coming straight out of the OUI, so I have this expectation that all required files would be automatically created.

I manually created the missing directories (recursive copy of existing directories) and the emca -repos create command was able to do a lot more. Now I get this really strange message:
CONFIG: Unable to determine local host vide /u01/app/oracle/product/11.1.0/server1_TEMP/sysman/config/emd.properties : No such file or directory

What is a "host vide"? Nothing on metalink or Google for that one.

UPDATE Fri Apr 4 13:56:56 CDT 2008
Resolved. See comments.

Love/hate, Part I

2008-02-14T08:08:00.002-06:00

I have this love/hate relationship with Oracle. Although I rarely hear it, I would find it hard to believe that anyone who works with Oracle for very long does not feel the same way in these days. But hey, perhaps I am wrong and the people who gush over Oracle really do just have a passion for the software.

Allow me to ramble a little. This is a blog after all.

On the plus side, I have learned a lot, and that Oracle RDBMS-specific education has really boosted my career. While I still have a long way to go, I have come a long way from the Sybase newbie I started out as, not having any clue what SQL meant, let alone what it was or how to use it. I gained a solid appreciation not only for all the hard work that goes into all the cool stuff that Oracle does, but the wealth of support from the User community as well. There are some really slick, intelligent and brilliant professionals out there who are not Oracle employees. And not merely a handful, but a throng.

Now a negative. The folks who are leading Oracle are way too ambitious in my opinion. They want to control everything. There is way too much emphasis on the bleeding edge, and not enough support for the "tried and true". I am a DBA, and my niche focus hardly ever veers off into other Oracle products like BI, AS, SOA or any other slew of acronyms. If the database server is truly Oracle's flagship product, it is not because of its superior quality, in my opinion, but rather because they charge an arm and a leg for support. I could write a whole book about Oracle Support.

Another positive thing. Oracle's RDBMS is fodder for some most excellent theoretical and scientific arguments. Watch some interchanges between Oracle gurus on the usenet, oracle-l or the Oracle forums; some of those discussions are worthy of a doctoral thesis. Oracle does some really mind-blowing things, especially in the areas of manipulating data. In general, the software is fast, scalable and rather solid.

And the pendulum swings. Oracle tries to do too much. Let's look at the RDBMS. 8i came out in 1999, 9i in 2001, 10g in 2003, 10.2.0.1 in 2005, 11g in 2007. Each new release introduced hundreds of new features, and thousands of new bugs. The worst part, every two years a stable-but-older version is falling off the support list. This problem is exacerbated within patch levels, where the rate of turn-over seems to be about 1 patch per year, each with new features and new bugs, killing support for the new two-off patches. Oh how I wish Oracle would commit to sticking with a "stable" version, making it bullet-proof.

All these new features are a double-edged sword. The media loves "new features", Oracle Support, I am sure, hates them. As a customer, user and DBA, I love the new technologies and the learning opportunities, but I hate how there is often poor documentation, lack of support and a host of accompanying bugs.

Am I complaining? You bet. Am I a whiner? I could care less. Taking a step back, I have a big beef with software development in general. Example, Microsoft Vista. Like the Grammy's, there should be awards for best-written and most-stable products. Or perhaps that would be too depressing.

In my opinion, one of the most ironic statements to come out of Ellison's mouth was the whole "War on Complexity". Oracle definitely lost that war. Look at the monstrosity called "Oracle Enterprise Manager". Sure, its free, and that is why other companies are capitalizing and making money by offering a commercial, better product for managing databases. If there really is a war on complexity, then I will walk to the moon. What I see is a war on simplicity. And it is not even fair to call it a war; rather, a wholesale rape.

I have had the opportunity to interact directly with Oracle employees at various levels. For the most part, I have been blown away by the graciousness, intelligence and customer-focus these men and women have. In talking with them, I have been convinced that that there are a lot of good people working hard to make an excellent product. What I dislike is the general direction of the product, and the decisions being made that influence it thus. And again, for the record, when I say "the product", I only really care about the RDBMS. I do not have anything good to say about other offerings. =)

My first chain blog [sic]

2008-01-08T08:26:00.000-06:00

I usually delete emails that have anything to do with "being tagged", forward to x number of buddies and family members, etc. Irregardless of who they are from, I just marked them as spam and forget about them. So I get this email from Dan Norris and my finger is hovering over the kill button when I start to read a little of the preview. Hmm... a blog tag. Still, I am not overly excited, but it is from Dan, so I open it up and read it. Then I read the links (to his blog and his buddy Jake), then I keep reading more links. Interesting stuff.

Ok, so just this once I will indulge these silly chain mail things (well don't I sound mature). Actually, I was honored to be included in Dan's 8, and I am having a hard time coming up with 8 folks I would contact. But not being a big fan of such things anyway, I have no problems bending the rules a little.

And just for the record, I did enjoy this exercise, especially reading about other folks with whom I have communicated, and some I have never met.

1. I was awarded a "Citizenship Award" from the American Daughters of the Revolution. To this day, I have never taken the time to find out who that group (cult?) is. I was in high-school at the time, and when asked what I wanted to do with my life, I said I wanted to make video games. Yes, I was an outstanding citizen.

3. My beautiful wife originally was attracted to me when I had a monster beard and earlocks to boot. Make no doubt, the hirsute appearance has not the draw; she liked me despite that.

4. In boyscouts, our troupe was a drum and bugle corp. I played a contra bass which I thoroughly enjoyed even though I was not that great (and that is being generous). I enjoyed traveling around Illinois, even done to Missouri once, not to mention several parades in downtown Chicago.

2. I put a pitchfork through my foot when I was kid. It hurt. It was right after my dad said "Be careful with that thing, you could hurt yourself." I proved him right.

10. When I started blogging about Oracle stuff, I had only just heard about the late Lex de Haan. He seemed like a really impressive and well respected man, so I named my first blog in his honor. Ironically, blogger updated their software and I have not been able to go back to the old blog.

7. I am the oldest of 5 children, and I am proud of them all. One brother plays professional volleyball and travels a lot (even outside the states), another brother is helping special needs students at our old high school and working on his Education certification; one is taller than me, the other is bigger, and I am not a small person by any means. One sister was working at McGraw Hill until she switched to a smaller publisher that she enjoys much more, and my other sister, on a lark, picked up Muay Thai with the well-known Team Toro for kicks.

13. One of these is false.

6. Going to college at the University of Illinois in Champaign-Urbana (I know, it is called UIUC), I had the wonderful experience of trying out the grassroots of the WWW with Mosaic. I maintained a webpage that blatantly said "I am NOT the author of Peanuts", but I still received fan mail. I even had a teacher ask me to write a special comic strip for her classroom. I was touched, but on the other hand, I got to thinking that some people just want to believe something no matter what you tell them.

0. I bailed hay once, and we had roosters for a short time while I was growing up. To put this in perspective, I grew up in the Chicago 'burbs, 10 miles outside of downtown. There are no farms within 10 miles of downtown, at least none that are not called zoos. I bailed hay because my parents at one time thought we were going to move out to a farm (Minnesota), so we tried out farm-life for a week. Crazy idea that. A couple years later, we somehow brought home roosters as pets and kept them in the basement of our neighborhood bungalow. Another crazy idea that. They did not last long; one died and the other was called out because our neighbors did not like how it crowed before the sun came up.

1.6180339 I jumped out of a perfectly normal, safe, solid, airplane at over 14000 feet. Instead of a parachute strapped to my back, I had a another guy who was risking his life for a measly $200. Also, if you did not get the golden ratio hint, my first dbms was Sybase (in a dusty attic somewhere in my memory).

I am going to tag Job Miller (great excuse to start a blog!) and Ravi Gaur.

The DataPump Index work-around

2008-01-02T15:58:00.001-06:00

As I mentioned in a previous post, I did not like the way Data Pump does indexes. Apparently, many other folks feel the same way.

UPDATES: We are working on a new version which we hope to have ready in the next couple days. While it does not do anything fancy like Data Pump does, it does successfully parallelize those operations that current (10.2.0.3) limited to one worker process. Additionally, I have communicated with the fine folks working on Data Pump, and am very excited with the work they are doing to improve it.

Here is the script I am using to run indexes in parallel (the formatting sucks - thanks Blogger!):
#!/bin/ksh
####################
## Charles Schultz 19-Dec-2007
## Copied from http://sysadmintalk.com/showthread.php?threadid=762
## Assume paresh = Parallel Execution Shell
##
## Additionally, the original script was written for bourne shell. I have switched it to
## use kshell since that is what we use; I have left as much of the original code in tact,
## and attempted to comform to the style.
##
## Still could use some cleaning up. The error reporting is not stellar, but I believe it
## is passable.
####################

SETX=""
# SETX="set -x" # Uncomment this line to set -x
$SETX

# OLD SYNTAX: paresh command_file parallel_count
# NEW SYNTAX: paresh command_file parallel_count [$ORACLE_SID]

## GLOBAL VARIABLES -- Added by Charles Schultz 19-Dec-2007
LOCAL_BIN="/u01/app/oracle/local/bin" # Directory where dbhome and oraenv are located
TMP="/var/tmp" # Customizable TEMP directory
OUTPUT="paresh_output"
SLEEP_SEC=.1 # Duration of pause between get_shell requests
shell_status=0
egrep="grep -E"
DRYRUN=${DRYRUN:-"N"} # Did not want to mess with command-line parameters,
# so I made this either a hard-coded or environment var
# DRYRUN="Y"

#-------------------------------------------------------------
# message
# Establish a timestamp and echo the message to the screen.
# Tee the output (append) to a unique log file.
#-------------------------------------------------------------
#
message()
{
$SETX
timestamp=`date +"%D %T"`
echo "$timestamp $*" | tee -a $logfile
return
}
#-------------------------------------------------------------
# run_sql
# Added by Charles Schultz 19-Dec-2007
# Takes SQL as a parameter and runs it via sqlplus
# Connects "/ as sysdba"; avoids having to deal with passwords,
# but could be a security risk if not careful about the input file
#-------------------------------------------------------------
#
run_sql()
{
$SETX
shell_status=0
$SQLPLUS /nolog <<> $SQLLOGTMP 2>&1
connect / as sysdba
set echo on
set timing on
@$SQLFILE
exit
EOS

errors=`$egrep "ORA-|SP2-" $SQLLOGTMP`
if [ "$errors" != "" ]
then
cat $SQLLOGTMP >> $SLAVE_ERROR_LOG
shell_status=1
fi
cat $SQLLOGTMP >> $SQLLOG
return
}
#-------------------------------------------------------------
# get_shell
# This function is responsible for establishing the next
# command to be processed. Since multiple processes might
# be requesting a command at the same time, it has a built-
# in locking mechanism.
#-------------------------------------------------------------
# MODIFICATIONS Charles Schultz 19-Dec-2007
#
# Workfile will have SQL DDL statements, and thus span more than
# one line. I have adapted get_shell to read a chunk of lines
# up to either the CREATE or DECLARE statement. This is specific
# for DDL generated by 10.2.0.3 IMPDP index metadata.
#
# Also, updated the worker thread to go against sqlplus instead
# of executing the command "as is".
#-------------------------------------------------------------
#
get_shell()
{
$SETX
echo "`date` $1 Shell Request $$" >> $lklogfile
while : # until a command or end
do
next_shell="" # initialize command
if [ ! -s ${workfile} ] # if empty file (end)
then #
break # no more commands
fi #
if [ ! -f $lockfile ] # is there a lock?
then # not yet...
echo $$ > $lockfile # make one
echo "`date` $1 Lock Obtained $$" >> $lklogfile
if [ "$$" = "`cat $lockfile`" ]
then # we created it last
## START -- Added by Charles 19-Dec-2007
## Get the next line number of the last statement we are interested in
## FOR ALTER DDL (ie, Constraints)
# chunk_end=`$egrep -n "ALTER" $workfile|head -2|sed 's/:.*//'`
## FOR Data Pump Index DDL
chunk_end=`$egrep -n "CREATE|DECLARE" $workfile|head -2|sed 's/:.*//'`

if [ "$chunk_end" = "" ]
then
break # No more chunks found, exit
fi

line_num=${chunk_end##* } # if two line numbers found, line_num != chunk_end
# grab the last number as the line number

if [ $line_num = $chunk_end ] # if only one line found, run everything else
then
## Run whatever is left in the workfile
next_shell=`cat $workfile`
echo "" > $workfile
else # else get the next chunk
line_num=$((line_num-1))
next_shell=`head -${line_num} $workfile` # Get chunk of work
sed -e 1,${line_num}d $workfile > ${workfile}.tmp # Chop off chunk of work
mv ${workfile}.tmp $workfile
fi
## END -- Added by Charles 19-Dec-2007

rm -f $lockfile # turn off lock
echo "`date` $1 Shell Issued " >> $lklogfile
return # done, command in
else # variable "next_shell"
echo "`date` $1 Lock FAULTED $$" >> $lklogfile
fi # double check faulted
# else # locked by other
# echo "`date` $1 Lock Wait $$" >> $lklogfile
fi
sleep $SLEEP_SEC # brief pause
done # try again
return # only if no commands
}
#-------------------------------------------------------------
# paresh_slave
# This code is executed by each of the slaves. It basically
# requests a command, executes it, and returns the status.
#-------------------------------------------------------------
# Modified by Charles Schultz 19-Dec-2007
# Passes next_shell to run_sql
#-------------------------------------------------------------
#
paresh_slave()
{
$SETX
export SQLFILE="$OUTPUT/paresh_${1}.sql" # Sql file for this slave
SLAVE_ERROR_LOG="$OUTPUT/paresh_${1}_sql.errors" # Error log file for this slave
echo "" > $SLAVE_ERROR_LOG
SQLLOG="$OUTPUT/paresh_${1}_sql.log" # Sql log file for this slave
echo "" > $SQLLOG
SQLLOGTMP="$OUTPUT/paresh_${1}_sql.log.tmp" # Temp Sql log file for this slave
shell_count=0 # Commands done by this slave
get_shell $1 # get next command to execute
while test "$next_shell" != ""
do # got a command
shell_count=`expr $shell_count + 1`
message "Slave $1: Running sql: $next_shell"
echo "set sqlblanklines on" > $SQLFILE # This was added to avoid errors with blank lines in source
echo "$next_shell" >> $SQLFILE
shell_status=0
if [ "$DRYRUN" = "Y" ]
then
message "NOTE: This is a DRYRUN; no actual work will be done"
else
run_sql # execute command
fi
# shell_status=$? # get exit status
if [ "$shell_status" -gt 0 ]
then # then message
message "Slave $1: ERROR IN SQLPLUS status=$shell_status"
echo "Slave $1: ERROR IN Shell SQLPLUS status=$shell_status" >> $errfile
fi
# message "Slave $1: Finished Shell"
get_shell $1 # get next command
done # all done
message "Slave $1: Done (Executed $shell_count Shells)"
return # slave complete
}
##############################################################
# paresh_driver
# This code is executed by the top level process only. It
# parses the arguments and spawns the appropriate number
# of slaves. Note that the slaves run this same shell file,
# but the slaves execute different code, based on the
# exported variable PARESH.
#-------------------------------------------------------------
#
paresh_driver()
{
$SETX
rm -f $lklogfile # start a new log file
if [ "$1" = "" ] # first argument?
then # no?
master_file="master.list" # default value
else # yes?
if [ ! -f "$1" ] # does file exist?
then # no?
echo "$0: Unable to find File $1"
exit 1 # quit
else # yes?
master_file="$1" # use specified filename
fi
fi
if [ "$2" = "" ] # Second Argument?
then # no?
parallel_count=4
else # Yes?
if [ "$2" -lt 1 ] # Less than 1?
then # Yes?
echo "$0: Parallel Process Count Must be > 0"
exit 1 # quit
else # no?
parallel_count=$2 # Use Specified Count
fi
fi

## Added by Charles 13-Dec-2007
export PATH="$LOCAL_BIN:$PATH" # Setup Oracle Environment
export ORACLE_SID=${3:-$ORACLE_SID} # Set ORACLE_SID
export ORAENV_ASK=NO;. $LOCAL_BIN/oraenv
export SQLPLUS="$ORACLE_HOME/bin/sqlplus"
if [ ! -e $SQLPLUS ]
then
echo "Oracle Home $ORACLE_HOME not valid for Oracle SID $ORACLE_SID - exiting"
exit 1
fi

mkdir -p $OUTPUT
## Added by Charles 13-Dec-2007

message "------------------------------"
message "Master Process ID: $PARESH"
message "Processing File: $master_file"
message "Parallel Count: $parallel_count"
message "Log File: $logfile"
message "Working Output Directory: $OUTPUT"
message "------------------------------"
cp $master_file $workfile # make a copy of commands file
while test $parallel_count -gt 0
do
if [ ! -s $workfile ]
then
message "All Work Completed - Stopped Spawning at $parallel_count"
break # Quit spawning
fi
$0 $parallel_count &
message "Spawned Slave $parallel_count [pid $!]"
parallel_count=`expr $parallel_count - 1`
done
wait
message "All Done"
return
}
#-------------------------------------------------------------
# main
# This is the main section of the program. Because this shell
# file calls itself, it uses a variable to establish whether or
# not it is in Driver Mode or Slave Mode.
#-------------------------------------------------------------
#
if [ "$PARESH" != "" ] # If variable is set
then # then slave mode
workfile=$TMP/paresh.work.$PARESH # Work file with parent pid
lockfile=$TMP/paresh.lock.$PARESH # Lock file with parent pid
lklogfile=$TMP/paresh.lklog.$PARESH
logfile=$TMP/paresh.log.$PARESH # Log File with parent pid
errfile=$TMP/paresh.err.$PARESH # Error File with parent pid
paresh_slave $* # Execute Slave Code
else
PARESH="$$"; export PARESH # Establish Parent pid
workfile=$TMP/paresh.work.$PARESH # Work File with parent pid
lockfile=$TMP/paresh.lock.$PARESH # Lock File with parent pid
lklogfile=$TMP/paresh.lklog.$PARESH
logfile=$TMP/paresh.log.$PARESH # Log File with parent pid
errfile=$TMP/paresh.err.$PARESH # Error File with parent pid
rm -f $errfile # remove error file
paresh_driver $* # execute Driver Code
rm -f $workfile # remove work file
rm -f $lklogfile # remove lock log file
if [ -f $errfile ] # Is there was an error
then
message "*************************************************"
message "FINAL ERROR SUMMARY. Errors logged in $errfile"
cat $errfile | tee -a $logfile
message "*************************************************"
exit 1
fi
fi
exit

Data Pump Rocks!

2007-12-21T14:36:00.001-06:00

For the past week, we dove into Data Pump with a vengeance. I have never played with it before; I think I may have tried a table export or two, but nothing really serious. So last week I had no clue what a huge improvement over the "traditional" exp/imp Data Pump is. Of course, it goes without saying that there will be bugs and gotchas, but that pretty much comes with the territory. Sad but true.

The purpose we engaged in this activity in the first place is that our ERP ostentatiously decided that they want to support international characters, which means that all of us state-side have to upgrade regardless of any character needs. They didn't ask me! *grin* But the silver lining is that we have learned so much about Data Pump.

Just for the sake of numbers, we were able to pump the data from a 478gb database to a set of dumpfiles totaling 175gb in about 3 hours, and then turn around and pump that back into an empty database in about 4 hours or so. Yes, there are some hidden truths in there. For instance, we completely ignored indexes; they will explode your timeframe significantly.

EXPDP
We hit some issues early on with the export. At first, we thought we were hitting the LOB issue. After filing an SR, we learned of Metalink note 286496.1 which covers tracing and an "undocumented" METRICS parameter. METRICS seems like a vastly handy little piece of information, so I am quite flabbergasted that it is "undocumented". According to the note, we set TRACE=480300, which has some trace information for the Master and Worker processes. In addition, I set event 10046 to get the waits. That was eye opening. The database was spending an enormous amount of time waiting on "library cache pin" while creating Global Temporary Tables. Very odd. After playing ping-pong with the Support Analyst and thinking about it for a while, I realized that all of the objects experiencing a wait had FGAC enabled for VPD. Ok, one strike against me for not choosing a VPD-free login (ie, one that has been granted EXEMPT ACCESS POLICY), but one strike for Data Pump for doing something rather poorly. I am hoping to hear more about this particular behavior and how it will be resolved.

We also set our TEMP space to extend without limit and set the pools (shared pool and buffer cache) a bit higher than normal.

The parameter file we used:
directory=DPUMP
dumpfile=${ORACLE_SID}_full%U.dmp
logfile=${ORACLE_SID}_full_debug.log
full=y
parallel=16
metrics=y
userid="xxx/yyy"
TRACE=480300

IMPDP
The very first thing I tried was NETWORK_LINK; for "traditional" exp/imp, we use a pipe as an intermediate file instead of exp to a dump file and imp from the dump file. Since Data Pump writes asynchronously, this is not possible, but the alternative is to communicate via a Database Link. Unfortunately, LONG objects are not yet supported via this method, excluding this option as a viable method.

The next problem we encountered was that the metadata contained a tablespace specification for a non-existent tablespace on two of our partitioned tables. This turned out to be extremely counterintuitive. If you precreate the table (on the proper, existing tablespaces), IMPDP will fail trying to create the table (on the wrong, non-existing tablespace). Even if you specify TABLE_EXISTS_ACTION=TRUNCATE!! Our Support Analyst is telling me that this is the expected behavior. Was not my expectation at all. To fix it, we create the tablespace and viola, we have a working import process.

Lastly, we struggled for a long time with the arduous process of creating indexes. Data Pump
says it is creating indexes in parallel. In reality, it using the parallel degree clause of the CREATE INDEX statement, utilizing the RDBMS parallel server processes. This seems rather antithetical to the rest of Data Pump, especially if you consider that no matter how much parallelize, you bottleneck with the query coordinator. I much prefer that Data Pump use a Parallel Degree of 1 for the index creations, but launch multiple creations at the same time. In fact, I downloaded a script called paresh. I had to modify it a bit to use the DDL generated by IMPDP Metadata for indexes, but it seems to work quite well. I need to modify it more for error checking, but at least it creates indexes truly in parallel.

So, with that out of the way, we are now working on a "clean" import given these exceptions. For the init.ora, we use:
nls_length_semantics = CHAR

## Import Debugging
max_dump_file_size = unlimited
# event="10046 trace name context forever, level 12"

## Import Speed-up parameters
shared_pool_size = 1000M
sga_max_size = 2000M
parallel_max_servers = 24
_disable_logging = TRUE
DB_BLOCK_CHECKSUM=FALSE ## DEFAULT = TRUE
## DISK_ASYNCH_IO=TRUE ## DEFAULT
## DB_BLOCK_CHECKING=FALSE ## DEFAULT

A note of WARNING about _disable_logging. It is an underscore parameter, so all the usual warnings accompany that. I found out the hard way what happens if you shutdown abort while attempting to rollback sql statements:
SMON: following errors trapped and ignored:
ORA-01595: error freeing extent (3) of rollback segment (1))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [114], [232], [], [], [], [], []

More information can be found in Metalink Note 39282.1.

Our IMPDP parameter file is similar to EXPDP, but excluding some objects:
directory=DPUMP
dumpfile=${ORACLE_SID}_full%U.dmp
logfile=${ORACLE_SID}_full_no_indexes.log
full=y
parallel=16
metrics=y
userid="xxx/yyy"
TRACE=480300
EXCLUDE=index,constraint

We are still playing with the fastest way to migrate constraints. One thought is to do two passes:

import metadata
disable constraints
import data
enable constraints in parallel
build indexes in parallel

Another thought is to disable the constraints on the source, but that may not be practical for our situation. We will see.

In the End
I am optimistic that our latest changes are going to produce some fast numbers. Of course, there are other areas that could be tweaked (place dumpfiles on independent mount points, for example), but we went with the low-hanging fruit, and I think we scored. Another outcome is that we have made contact with the Data Pump Product Manager and her supervisor, which is priceless! They are excellent people and very patient and willing to listen. Amazing folks over there!

All I want for Christmas

2007-12-07T10:43:00.000-06:00

We have a particularly naughty database this week. However, it still has high hopes for the "giving season":

SQL > select dbms_random.string('U', 4) from dual;

DBMS_RANDOM.STRING('U',4)
------------------------------------------------------------------------------------------------------------------------
RIBS

That was for real. We had quite a laugh about that little coincidence this morning.

Managing CRS, part 1

2007-11-26T15:27:00.001-06:00

This CRS beasty is a bit much to chew on. Maybe it is just me.

Anyway, I started asking around how to check our mount and start options for databases registered with CRS. Strangely, nobody had an answer for me. Probably lack of me asking the right question, rather than lack of knowledge. But, I did find an easy answer:

srvctl config database -d DB_NAME -a

This helped me understand why our standby RAC databases were opening in read-only mode as opposed to mounting into a recovery mode. I had been following the MAA documentation for setting up a standby, which ostensibly does not cover the mount and start options (nor role) for standby databases. Very curious. I modified our standby databases with this kind of command:

srvctl modify database -d DB_NAME -r PHYSICAL_STANDBY -s mount -y AUTOMATIC -p +DATA/db_name/spfiledb_name.ora

While these options are well documented, you have to find it first. For those of us who are new to the scene, we do not always have the faintest idea of where to look. Is it a CRS command, or an OCR command? While you can usually depend on folks in the RAC SIG and oracle-l to help out, sometimes they are just too busy (hmm... it is Thanksgiving....). Or, in a twist of Jonathan Lewis' quotes, "Sometimes, you just get unlucky."

So now that I figured out that one small piece to the puzzle, I have stumbled upon other questions. How do you get a report for all the start/mount options for all databases? I do not really want to run srvctl config for each database. What about other services, like the listener? I tried playing around with srvctl config listener, but I am not getting anything useful out of it. Especially since there is no -a flag. I am currently trying srvctl config service, but all my half-baked guesses are not getting me anywhere. I tried variations on the listner name and what I thought the service was, and I also tried the name reported by crs_stat.

The lack of comprehensive tools (like a good 'du') still bug me with the ASM as well.

Reverse mapping ASM disks

2007-11-16T14:42:00.000-06:00

As we have been working with our sysadmin and storage folks, I often have to do some digging to find out which ASM diskgroups belong to which volume, and what devices those volumes are on. Fortunately, we only have 4 at the moment, so it is a quick dig. However, I am always disappointed that Oracle did not provide an easy way to do this. Or if they did, they did not make obvious mentions in any of their documentation.

Google showed me a great, concise script that Alejandro Vargas wrote. I enhanced it a little to go against the ASM instance to grab diskgroup information as well.

--- start ---
export ORACLE_SID=+ASM
export ORAENV_ASK=NO
. oraenv

$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOS 2>&1 |grep [A-Z] > asmdisks.txt
set head off feed off
select a.group_number||' '||b.disk_number||' '||a.name||' '||b.label
from v\$asm_diskgroup a, v\$asm_disk b
where a.group_number = b.group_number
/
exit
EOS

printf "%-9s %-30s %-3s %-10s %-3s\n" "ASM Disk" "Device Path [MAJ,MIN]" "GRP" "Disk Group" "DSK"
/etc/init.d/oracleasm querydisk `/etc/init.d/oracleasm listdisks` | cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' | while read v_asmdisk v_minor v_major
do
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'`
grp=`grep $v_asmdisk asmdisks.txt|cut -f1 -d" "`
dsk=`grep $v_asmdisk asmdisks.txt|cut -f2 -d" "`
diskgroup=`grep $v_asmdisk asmdisks.txt|cut -f3 -d" "`

printf "%-9s /dev/%-25s %-3s %-10s %-3s\n" $v_asmdisk "$v_device [$v_minor, $v_major]" $grp $diskgroup $dsk
done

\rm asmdisks.txt
---- end ----

Day 3: Understanding and Assisting the CBO

2007-11-12T14:43:00.000-06:00

Session 1: Basic Cost Arithmetic
Anyone that has read any of Jonathan's previous works will recognize the information surrounding the costing formulas; he also gives generous credit to Wolfgang Breitling and Alberto Dell'era. What is really great is that all three of these guys are very generous in sharing of their knowledge and experience, wishing to enhance the community by helping others. Just amazing!

One of the first things Jonathan went over was the environment; there are a lot of factors that play a part in calculating costs of various different pieces of Oracle. The system stats are very important in that they determine cpu speed and io latency, which in turn determine how fast basic Oracle operations occur. Another important number is multiblock read count. I found it very interesting that the hidden underscore parameter _db_file_exec_read_count defaults to db_cache_size/processes (if db_file_multiblock_read_count is not set). Processes; so let's say you set the number of processes really high "just because". You can see that your exec read count will be quite small, and for no good reason.

Jonathan also talked about the importance of sizing the memory parameters appropriately. I think the general impression is that you do not want to gimp your system by being too frugal with memory. Obviously, this will affect large operations (sorts, hashes, etc) more than anything else, but those large operations can get real nasty real fast (think multipass for hashes). Two underscore parameters that Jonathan highlighted were _pga_max_size and _smm_max_size (unfortunately, there were not many details on what these two did, or I missed them).

He made a very interesting statement in that truncating a table does not reset the stats. That sounded very strange to me. Is that true in 10g?? If Oracle goes to the trouble to reset the highwater mark, why would the stats not be updated? They merely have to be zeroed out.

We spent a good chunk of time on clustering. Not just in this session, but in others as well. There is quite a serious flaw in how clustering is calculated for data that is scattered. Consider the case where row(n) is in block(mod(n,2)), or in other words, all the odd rows are in block 1, even rows in block 0. To determine clustering, Oracle will walk the data and count each time the block id changes. Since the id changes for each row, Oracle will calculate the clustering factor really really small, when in fact, the clustering is actually pretty good (total of two blocks, half your data is in one block or the other). A low cluster factor translate into a high IO cost. An articially high IO cost may lead to a sub-optimal plan (where a good plan that has a false high IO cost is ignored in favor of a lower-cost other plan).

This also prompted me to learn more about clustering factor.

Related to that, I was convicted several times of a need to identify what our "significant" or "important" data is. That is probably the number one question I returned to again and again. What is the Banner "business important" data? There are several dictionary views and object statistics (including predicate statistics) that I want to investigate further, but I actually do have an overly optimistic hope that our vendor has some idea.

There are a couple other flaws in IO costing that Jonathan pointed out.
Flaw #1: Oracle assumes that index block reads are from disk, not cache. Always.
Flaw #2: Oracle assumes index single-block read is the same cost as table multi-block read. Always.

TO address Flaw #1, we have optimizer_index_cache, which tells us, on average, how many of our index blocks are in cache (expressed as a percentage). Jonathan stressed that this is only relevant for index root and branch blocks, as leaf blocks are not cached. I have a hard time believing leaf blocks are not cached, and that is something else I would want to look into at some point. Perhaps I merely misunderstood him.

For Flaw #2, we have optimizer_index_cost_adj which tells us what percentage of a multiblock read is an index read (probably somewhere in the vicinity of 33%).

However, for both issues, Jonathan suggests that neither be set if (a BIG IF) system stats have been collected and are correct.

Jonathan pointed out a curious rounding error introduced with optimizer_index_cost_adj; the calculated cost will be rounded down, which can potentially lead to the wrong index being chosen. Event 10183 can be used to turn off cost rounding.

On the topic of multiblock reads, Jonathan showed us a chart demonstrating how Oracle scales the parameter away from excessively high numbers. For a value of 4, the adjusted value may be 4.175. But for higher values, say 32, the adjusted value might be 16.407. 128, 40.85. Due to system stats, Jonathan is recommending that most people might want to turn off db_file_multiblock_read_count.

In light of the importance of system stats, it would be good for us to review sys.aux_stats$, and or dbms_stats.get_system_stats().

Since clustering_factor has no meaning with bitmap indexes (think about it), it is overloaded to count the number of entries in the bitmap; "some key values will have multiple rows".

Even though there was only one slide and a short blurb about this, I was struck by the last point of the session. Using first_rows_n optimizer modes is really going to play havoc with explain plans. Costs and execution paths are essentially squeezed to fit into the first row target, much like doing a "where rownum <= n". This fact alone makes it very difficult to interpret exactly what the explain plan is attempting to convey.

Session 2: Join Mechanisms
Understanding Joins is important because Oracle attempts to convert everything into a two table join, including subqueries. This is not necessarily bad, it is just the way Oracle does it.

Jonathan's slides included a few examples of correlated and non-correlated subqueries. Jonathan's observation is that most "bad" join decisions are made because of cardinality being off, be it off by one or off by millions. Being off by one can go a long way. Which, in my mind, makes Wolfgang's Cardinality Feedback all the more important. Also, "off by one" can be really critical when you consider that perhaps the stats are just a tad old and new rows have come into the table, or have been deleted. That could be the difference between a "good" join and a "bad" join. Scary, eh?

There are a number of slides that talk about Nested Loop joins, but I am going to skip over to Merge joins. Actually, skipping all the way to One Pass sorts. There is an Urban Legend that all in-memory sorts are faster than disk sorts. Jonathan prooved this is not always the case. Up until 10.2.0.2, Oracle used binary insertion trees to maintain a map for merges; the bigger the memory, the larger this tree could potentially become (smaller memory would force a flush to disk). Because of such large sizes of binary trees, the CPU requirements shot up exponentially to maintain the index. Hence, with those versions, more memory actually made those merge operations far worse.

Jonathan had a caution about 9i 10053 trace events; the "Max Area Size" listing is completely wrong and useless, for it ignores the Workarea_size_policy parameter.

And more caveats. When Oracle executes asynchronous writes, the wait time is not recorded (because it is asynchronous). You have to keep an eye on direct_path_temp to see if the numbers are stacking up.

He introduced two trace events, 10032 and 10033. The former dumps statistics about sorts, the second traces IO for sorts to disk. Since file sizes are small for event 10033, it is not impractical to set it at the system level when diagnosing a sort issue.

Multipass sorts are extremely bad. Avoid them!! Setting sort_area_size large enough to avoid multipass sorts is highly recommended. He quotes Steve Adam's as saying you should be able to get by with a sort_area_size of 45mb to sort 12gb.

Hash joins are a bit interesting. Among other things, Jonathan talked about how the workarea_size_policy comes into play. If set to manual, each hash queue will get hash_area_size/2 memory. What happens if you have more than 2 queues? That's right, you have queues whose sum of memory exceeds hash_area_size. The worst part is that each queue gets this memory regardless if it is needed or not. Say you want to hash 2k of data. You still get hash_area_size/2 for each queue. Wastage!

Setting workarea_size_policy to automatic allows the queues to only grab what they need.

Jonathan also spend some time on Trace Event 10104, "One of the most useful traces in the Oracle suite". It gives a comprehensive break down of hashes, and can be used with Trace 10046 for even greater detail. For multipasses, look for terms like how much memory is available (Memory for slots, in bytes) vs Estimated Build size. Also "Total number of partitions" vs "Number of partitions that fit in memory". That might be your first clue. There is also a stat for number of rows iterated; the fewer iterations the better.

In the end, Hashes may scale more linearly, if AND ONLY IF you do not hit an exception, and there are a number of those. Nested Loops grow faster, but tend to be smoother especially if the index/data is buffered.

Session 3: Selectivity and Hints
The rules governing selectivity start off relatively basic (although still a little bit of math involved), but quickly get complicated in my opinion. The boundary conditions really throw you. For join selectivity, one thing to be aware of is that Oracle assumes predicate independence. Jonathan has bantered this in his blog (where I first read about it). For example, consider two columns, 'month' and 'number_of_days' which tells the number of days in the month. If you use "where month between 10 and 12 and number_of_days = 30", Oracle assumes that any of those months can have 30 days. We know that is not true.

Jonathan had a great example calculating cardinality in a sample query; I'll not repeat it here for the time being.

One gotcha to remember (among many) is that when the optimizer generates numbers for you, it is not necessarily against the sql query you provided. In fact, you might as well assume it is NOT the same query; the numbers are for the interally optimized plan, of which only snippets are available to you via the 10053 trace.

We also covered transitive closure. Jonathan has a bit to say in his book and blog, and the bottmline is that sometimes Oracle will remove a predicate that it thinks is redundant (ie, a = b, b = c, therefore a = c). Lucky you. This can often lead to Cartesian joins, or alternative access paths being used (remember, generally speaking, access paths should have higher priority than filter predicates).

There are also a number of sanity checks to be aware of. One that Jonathan demonstrated is a case where Oracle will not choose ndv values from opposite tables when doing a multi-column join. I am still reading those slides, as it is taking me a little while to comprehend the ramifications. However, one of the issues seems to be that merely making a cosmetic change to your code opens it up to the possibility of hitting one of these sanity checks, and has the potential to throw the optimizer a huge wrench. Just remember that there are no certainties.

Jonathan also had some very strong words about hints. In fact, he even goes so far to say that "A 'hint' is a directive that the optimizer MUST accept and act upon." Now, as Jonathan pointed out, sometimes in an act of obedience, Oracle will ignore the hint. *grin* Yes, sounds contradictory. If you give a full(t) and index(t) hint, by "obedience" Oracle will consider the plans that both hints dictate (yes, BOTH hints), and will give you the cheapest plan. That is the key point. Oracle uses a Cost-Based Optimizer, and will always generate numbers to determine the cheapest plan. If you use hints, you want to use enough hints to trim out all other plans that the optimizer might possibly choose. The 10053 trace is handy in figuring that out.

He gave two examples to showcase this. One with a noparallel hint on the table; Oracle found a cheap plan using parallel on the index (think of a naughty two-year old). In another case, say you specify parallel on a table, but the plan is serial. That is because the serial plan is cheaper.

In another example, Jonathan shows the use_nl hint, and specifies two tables /*+ use_nl(t1 t2) */. This does *NOT* mean nest loop t1 and t2 with t1 as the first table. No no no. It means /*+ use_nl(t1) use_nl(t2) */. Yes indeed. So it tells Oracle to use a nested loop on t1 and a nested loop on t2, but it does not specify an order. In the example it seems to work because Oracle only came up with two plans, and the one we expected just happened to be the cheaper one. "Sometimes you get lucky". Sometimes, even though you did something wrong, you got the results you were expecting to see. Be careful about that.

As always, be wary of "exceptions". There is a hint (and underscore parameter) that specifically says "ignore hints." Go scratch your head over that one.

The ORDERED hint is funny in that Oracle seems to apply it in the end game of optimization. Jonathan specifically gave us a Hash example in which the Ordered hint was obeyed, but the plan was in the oppositive order. Apparently. Watch out for subqueries because remember that Oracle likes to unnest them, and might screw up your seemingly simple order. In 10g, we can use the LEADING hint instead. Still have to watch out for query block names that are dynamically generated due to optimizations; it is possible to specify non-pre-existing query blocks if you know how Oracle is going to name them. If you do not, your order may be a little different.

10g also makes it easier to logical name the hint operands. You can say "index(t1(id1))" or "index(@sub2 t4@sub2(t4.id)" Obviously, those are not physical names, but labels. Jonathan has observed that it is easier to provide a negative hint than a positive one.

Bottom line, in order to use hints well:

Set the join order
Join method for n-1 tables
Access path for every table
Average two hints per table to do it well

Session 4: Telling the Truth
Last day, last session. =) Need I say more?

Some interesting things about this session. Jonathan gets into some strange anomalies and how they are avoided if you give more, seemingly irrelevant, information. For instance, defining columns (esp. mandatory columns) as "not null" helps the optimizer tremendously when using not null predicates.

The stats play a big role in "telling the truth". Old stats are just as bad as bad stats. However, Oracle does provide a way to synthesize stats if needed; I found this whole concept quite intriguing when reading his book and papers earlier. And it comes back to what is your "interesting" data? What data does the application/business like? Oracle has several rules for dealing with "normal" data, but it is entirely possible that you are interested in anything but. Which makes it important to identify the "important" data, and take advantage of the tools that Oracle has given us to help convery that to the optimizer. Stats, and possibly histograms.

Jonathan went back to the example for clustering factor. It is entirely possible to have data that Oracle thinks is scattered, when in fact it is merely grouped weird. That is a case of "lying" to the optimizer.

For the most part, the default stats work. Oracle CBO assumes a normal, boring distribution of data, so if that is the case, you are all set to go. When the data is grouped into "weird, wacky and stretchy bits", the stats and histograms may have holes. Sometimes is still does a fairly good job to compensate, and sometimes you just get unlucky. =)

He had an interesting blurb for histograms in OLTP. He is suggested that instead of letting the normal stats collect histograms, the front-end should be aware of "important" data, and code for it appropriately. Can you imagine Banner doing that? Neither can I.

I will have to wrap it up with that. I will try to over these notes from the Days with Jonathan Lewis, but I need a break.

Day 2 with Jonathan Lewis: Writing Optimal SQL

2007-11-08T20:17:00.000-06:00

"Writing optimal SQL" seemed a bit ambitious for this day's class. I get the feeling that Jonathan has a wealth of knowledge and it is hard for him to condense it into a mere 6-hour time slice (4 x 1.5 hour sessions). Here are my highlights and thoughts from the material covered today.

Session 1: Background
Jonathan started out by reiterating (for emphasis) the obvious difference between "local" optimization (or 'optimisation' as he writes) and "global" optimization; sure, we can make an individual query go faster, but at what cost? Which brings us to the point that one always has to make a choice on where one spends one's resources.

Today's session concentrated on global optimization strategies (any of which could be used locally if you think about it), which comes down to always keeping your eye open for wasted resources, whether it be expensive CPU operations, unnecessary buffer gets or multiple visits to disk. As with all things in life, there are exceptions, today labeled as Contradictions. For instance, sometimes to increase logical I/O, one must increase physical I/O, and vice-versa. Jonathan gave the example of a typical index read. Say it takes 1 header block, 1 branch block and 4 leaf block reads to get all data. It is possible that the same data could be gotten with a FTS of 3 block read requests. Which one is better? What's in cache?

For the most part, the most efficient (and hence "best") way to get data is to got it precisely. This goes back to the little note yesterday about using access predicates in favor of filter predicates; if you can get only the data you want on the first pass, there is not need to filter data later on (and thus discard data gotten). And even though indexes usually help us target precise blocks (via rowids), they are balanced out by the fact that they generate overhead for DML.

Jonathan had a very interesting section on column ordering in indexes. There is a widely-held thought that you put the most selective columns at the front of the index. Overall, the focus needs to be on how dense the data retrieved from the index is, which may require that you put the most repeated column first in the index. Jonathan states that, "all other things being equal", you generally want to put equality predicates at the front of the index, range predicates at the rear. Be as precise as possible. That word is going to come up again and again.

Which got me to thinking. How does one determine what "business critical" data is? What is the "interesting" data? This question is made more convoluted in a large ERP system. Surely, it is not practical to go table by table to figure out what data is most often used, most often needed. And I have this sinking feeling that the vendor is not too up-to-par on an answer for this particular question either.

There was mention of index-(hash-)join and and-equal joins; I am not very familiar with the latter and am making a note to learn more about it.

We got to talking about bitmap indexes. Bitmap indexes are great for static or "mostly read-only" tables; bad for OLTP systems. To bridge this gap, Oracle has made Bitmap Conversions more easily accessible for B-tree indexes.

Again, Jonathan made mention of using dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'). I want to do more research on that; for it seems like an awfully wonderful tool to use. Must enable row source statistics (again, there is a very handy query-level hint, /*+ gather_plan_statistics */).

Jonathan also showed us some very interesting queries for metadata analysis.

select blocks, count(*)
from (
select /*+ index(t1 t1_pk) */
my_id,
count(distinct substr(rowid,1,15)) blocks
from t1
group by my_id
)
group by blocks
order by blocks
/

This query counts the number of blocks for each value (knocks off the row number from rowid to get file id + block id).

select /*+ index(t,"T1_I1") */
count(*) nrw, -- number of rows
count(distinct sys_op_lbid(49721, 'L', t.rowid)) nlb, -- number of leaf blocks
count(distinct hextoraw(sys_op_descend("DATE_ORD") || sys_op_descend("SEQ_ORD"))) ndk, -- number of distinct keys
sys_op_countchg(substrb(t.rowid,1,15),1) clf -- clustering factor
from "TEST_USER"."T1" t
where "DATE_ORD" is not null
or "SEQ_ORD" is not null
/

Talk about a little bit of black magic! I get the impression Jonathan ripped this out of a 10046 trace for calculating index stats, based on some of his non-standard uses. The last argument of sys_op_countchg should be the number of freelists; 2 through 16 if using ASSM.

There was a great word about documentation, especially since we have embarked upon a broad documentation project at work. Among other anecdotes, there was a good observation that "The harder it was to fix, the more documentation you need".

Session 2: Basic Coding
I did not like this section that much. Perhaps it was just me. But it seemed like a lot of the "standards" are quite subjective; is Jonathan teaching us his own peculiar style of coding?? =)

I did come away from this session with a few things, however. Due to Oracle's mostly-successful (dare I say, Mostly Harmless?) CBO, one can usually write a pretty intuitive sql and have it optimized properly. Sometimes, when we try to get clever and optimizer it ourselves, we botch it, or duplicate the work that the CBO would have done anyway, at the cost of making the code even more obscure and hard to read. As Occam's Razor infers, Keep it simple.

As popular as analytics are becoming, they are most definitely not the end-all be-all. They are meant for number crunching on small data sets.

Jonathan pointed out a rather bizarre observation of subqueries; Oracle assumes subqueries will reduce the rows to 5% (not BY 5%, but TO 5%). "This is insanely stupid. Sometimes." Yes, Jonathan qualifies everything he says. Usually.

Session 3: Sundry Methods
To kick things off, we dove into examine the costs of Nested Loop (NL) joins vs Hash Joins. Essentially:

Cost of NL = C1 + N * C2
Cost of HJ = C1 + C2

For a nested loop, C1 is the cost getting rows from table 1, C2 is the cost of getting rows from table 2, per row from table 1. Hence, if we can keep either N (the number of rows gotten from table 1) or C2 down, the total NL cost is relatively low. We can keep C2 low by using indexes and/or getting at the data precisely.

For a hash join, we are doing full table scans. We hash the join columns (and store the hash results in memory), the use the second table to probe the first, hashing the join columns on the second table to see if they match the hashed versions of the first. Note that the growth of cost is linear, whereas for nested loops is not (multiply by N).

Another problem with nested loops is that Oracle assumes that the tables are statistically independent. Jonathan (and others including Alberto Dell'era) have made a number of observations about this online.

One thing to keep in mind about hash joins is that it greedily gobbles memory. Do not try to make a large table the first table of a hash join.

Another important part about writing sql is that it helps to have a clear description of what exactly the query is to do, and to have a picture or diagram to further enhance the description. Sometimes a query is not optimal merely because of the way the "english" description implied how the query get data. There is more than one way to skin a cat.

Jonathan used an example of a "typical" FIFO index supporting a workflow process, in which a status is pushed through several states. The index will eventually have a long tail of mostly empty blocks, as all the activity is on recent timestamps. To address this issue, it helps to 1) only index relevant data instead of empty of null data using a function-based index, 2) have multiple smaller indexes. The benefit of #2 is that it is much easier to coalesce it, thereby keeping it well maintained. While talking about FBI, Jonathan quipped:

Encapsulation is wonderful. Hiding things is naughty.

Repeating a horrid modern-day developer practice of pushing procedural logic into sql, Jonathan touched on filter subqueries (where you have one subquery that handles one case and another subquery for another case). Jonathan made mention of the lnnvl function, which stands for "logical not null value"; a good way to test the value of a potentially null value.

He also talked about several "what if..." scenarios. In these, he states things like "if it is cheaper to get into table 2 through table 1...". I am not quite sure what he means by this. Something I need to follow up with him about.

We also covered the "untunables", or particular queries that did not lend themselves to being optimized by Oracle very well. Jonathan went to details to say that Oracle prefers and forces queries into a "left-deep" tree. Some queries actually perform much better when the access paths and join order is not "left-deep", but instead "bushy tree", or more balanced. This was showcased by a query with a 5-table join, with entries at either end. A "bushy tree" works towards the table in the middle, thus reducing the rows needed to propagate predicates and join conditions.

Session 4: More Methods
Jonathan showed us a fairly simple example of query rewrite using dbms_advanced_rewrite.declare_rewrite_equilvalence. This really does raise some questions; like, how different can source_stmt be from destination_stmt? Does it strictly follow the same rules as cursor sharing? Or can you completely rewrite the query as long as the columns are of the same datatype?

We also looked at subquery caching. I had a great talk with Ric Van Dyke (awesome dude!) afterwards about this. I was curious if there was any way to see or monitor subquery caching in affect, other than attempting to guess at its potential use via results in an explain plan. He did not know of a way, but guessed that it might possibly be exposed somehow in one of the x$ tables. I find subquery caching to be quite interesting, if for nothing else that it can really play havoc with your explain plan and make them unexplainable unless you really know what is going on. Surely, there is a way to tell when your plan used subquery caching, and to what degree.

Jonathan showed us "update join views", which I thought were quite clever. I have used dynamic materialized views (using the "with" clause, or "out of line views") to generate a view from a table join for an update, but having an update join view seems much more elegant. It does have some strict requirements; the driving table must be key-preserved, meaning that the column we want to update is deterministically joined.

We looked at pagination, aka judicious use of rownum, to efficiently get top-n rows. Another related trick (for more complex situations) is to manipulate index rowids directly. This happens when the logic requires that you get and sort all data prior to using rownum. One can do all sorts of sorting and aggregation, returning index-only columns and the rowid, then take the rownum from that and access the table using the relevant rowids. Quite clever that.

We took a brief glance at OEM, and moved quickly to profiles and outlines. Cool stuff. When talking about profiles, Jonathan made it a point to implore that we ask our Sales/Tech Reps when (NOT if) opt_estimate would be documented and supported. Profiles are essentially a series of opt_estimate hints that alter existing statitstics. How cool is that?!? =)

Jonathan ended on a note about hints. In general, hints are bad. Don't use them. But sometimes you have to, and when you do, make sure you do not hold back. A lone hint on a 7-table join is next to useless; it is better to provide at least 6 hints to determine the join order, plus anything else to determine the query plan you want. Or to put it a different way, and average of 2 hints per table, 1 to determine join method, one to determine access path. Jonathan had some interesting things to say about hints, his seemingly prolific use of them notwithstanding:

They are badly documented
Nobody knows what they do
They change usage occasionally

Jonathan really likes to say that "sometimes you stop getting lucky". Meaning that sometimes you use something (a hint) incorrectly but, lucky you, it happens to work the way you think it should. When it "stops working", it is actually working correctly and you just stopped getting lucky about it.

That is about it for today. My ass is really hurting after sitting in those chairs.

Day 1 with Jonathan Lewis: Execution Plans

2007-11-07T22:21:00.000-06:00

Here is my rough draft from notes I took during the first of Jonathan Lewis's 3 1-day CBO classes. These are merely highlites, for I could not possibly be comprehensive.

Session 1: Finding execution plans
If a database has been upgraded from 9i, the plan table may be of an old version. Additionally, it will not be global temporary table. You can run @?/rdbms/admin/catplan.sql to set it up properly. This also creates a public synonym and grants acces to public.

When tracing, helfpful to use query block names (qb_name) in sql to identify later on in trace file.

The 10132 trace is new, and provides a great alternative to running the 10053 trace (much more condense and practical). Great for baselining all query plans when set at a system level.

tkprof is a little tricky - traceonly does NOT mean that the query is not run; it only eliminates the resulting output of a query. Hence, inserts, updates and deletes still execute, but a straight select will not do much (no output).

Can format autotrace formatting with _plus_exp columns.

The pipelined dbms_xplan.display function is very nice. There are several other functions in that package I was not aware of, like display_cursor, display_awr and display_sqlset. display_cursor is great for getting the plan of a cursor currently in memory.

Since the function is pipelined, it can be used in a table join operation. Jonathan showed a greate example finding the query plan for all cursors that contained a FULL TABLE SCAN.

Jonathan's tests with reporting sql also showed easy ways to implement query-level optimizer paramters via hints (ie, /*+ opt_param('parallel_execution_enabled','false') */).

Rowsource execution statistics can be generated by setting statistics_level = all, or more surgically with _rowsource_execution_statistics = true. Can also be set with a hint (/*+ gather_plan_statistics */).

What I really like about rowsource execution stats is that you can easily generate Estimated and Actual stats, and can use something like Wolfgang's Cardinality Feedback to get a fairly good idea where performance problems may be popping up. The "ALLSTATS LAST" parameter of dbms_xplan.display_cursor displays this quite nicely. I need to do some more research on this function so we can use it more readily. Great tool for developers and DBAs alike.

Finding cursors in v$sql is made efficient with hash value and sql address. Unfortunately, 10g statspack has an "old" hash value that is inefficient. Do not use it.

When looking at parsed cursors, especially via sprepsql, the version count can be quite useful. Since the stats are aggregated, you have to be careful about summarizing and translating what the stats actually are trying to say about the query.

Any "explain" may be untrue.

Like House says, "Everyone lies."

Section 2: Interpreting Execution Plans

The good news about reading explain plans is that it is (should be and can be) much easier than it might first look. For the most part, it comes down to the good old standby, "Divide and Conquer". To that extent, Jonathan has two basic rules when breaking down plans, which essentially follow the concept of finding all your child cursors until you run out of descendants (think left tree-walk). The bad news is that there are, as always, exceptions. But the rules cover a vast majority of plans. And they are simple.

There are no complicated execution plans, only long execution plans.

First off, it really helps to look at the query plan with some aids. The PID (Parent ID) column is essential. Having the children indented incrementally also helps.

RULE 1 - Single-Child Parent
The child follows the parent and feeds the parent.

RULE 2 - Multi-Child Parent
The parent is an operation that determines how the children's results are combined.

All explain plans are a combination of Rule 1 and Rule 2. So what you do is break it down using those rules. That is what this section does; I'll leave it to you to go through Jonathan's slides yourself. =)

Rule 1 is relatively free of tricks. Rule 2, however, is a lot more convoluted when you start looking at the various different operations, and the various ways those operations are implemented. So while it may be "easy" to group them together, interpreting them takes a little time to understand the myriad subrules. However, the context usually helps that a bit. For instance, if you see a BITMAP AND operation, you are likely to see BITMAP CONVERSION operations; knowing what an operation is expecting (ie, ROWIDs, bitmap numbers, etc) helps you figure out what the child operation is passing up. I did not take many notes on this section because the slides are quite informative and easy to grasp once you see them.

When talking about filter predicates, I learned that access predicates are much more preferred if they can replace filter predicates. The reason being that filter predicates usually happen after you get the data, while access predicates determine how you get the data. This can be showcased by an inappropriate range scan; if you have an index with 3 columns and the query only specifies column 1 and column 3, the optimizer has to do a range scan for column 2. If the query can be adjusted to included column 2, the optimizer no longer has to do a range scan, and can move the filter predicate to an access predicate. Basically, without column 2, the range of "interested rows" in the index is bounded by the smallest value of (column 1, column 3) and the largest value; a range which may (quite possibly) include values that you are not really interested in (and have to be FILTERED out). If you define all columns, you only grab the rows you want in the first place.

select * from t1 where id = nvl(:b1, id)

"Horrendously bad practice."
"The ideal is to go back and smack someone."

The execution plan has to include a path for both :b1 is null and :b1 is not null. Instead, the application should have two different queries for these conditions. Additionally, the above code does not catch rows when id is null.

Jonathan stressed the idea of watching your query plans to see if the optimizer uses any unusual enhancements, so that one may learn from them. For instance, Oracle has a MIN/MAX optimization for indexes. This is especially useful for correlated subqueries that can be optimized by (redundantly) copying a predicate from the parent query that just happens to contain extra columns of an existing index. Sometimes work will be completely removed, like in the case with ORDER BY elimination.

Section 3: Problems with Plans

So now that Jonathan has razzle-dazzled us with all this awesome stuff, here comes some bad news.

Explain plan may be telling lies
Trace files don't always have stat lines
tkprof might not dump the stat lines even if they are in the trace file
tkprof may dump the "wrong" stat lines
v$sql_plan might be out of date
v$sql_plan can have holes

Explain plans are not aware of the datatypes for bind variables. Hence, if you are trying to get an acurate explain plan, it is a good idea to explicitly declare the datatypes of bind variables with a cast or to_ function.

Jonathan made some interesting comments about "good" applications. In particular, he stated that they do not close their cursors. We have been telling our developers to close cursors. Why is that? I think the statement needs to be taken in the context, or at least the assumption, that the cursors will be held open for the intent of reusing them, opposed to forgetting to close them when not needed. When cursors are not closed, their stat lines are not dumped to a trace file. Hence, if you want plans and statistics for a unclosed cursor, have to get them out of v$ views.

In cases where a cursor has multiple versions and/or executions, tkprof will only report on the first one in the trace file, even though it summarizes the stats for all of them. It is entirely possible that one bad execution caused a majority of the "bad" stats, and that the explain plan in tkprof is for a completely different execution.

While showing us some sql to demonstrate subquery factoring, he showed us his materialize hint (/*+ materialize */). There is also a inline hint that does the opposite.

Scalar subqueries are slightly problematic in that they are not costed properly, due to the fact that Oracle does not know how many times it will run.

Jonathan talked about "hash table cache" and "scalar subquery cache", both of which I am quite unfamiliar with, and want to research them a little more. Oracle will automatically cache hashed results of a scalar subquery so that it does not have to execute the subquery over and over. However, if two values of the subquery happen to hash to the same address, the first one in wins, and the second value has to be calculated again and again and again...

Additionally, Oracle has introduced "order by" elimination with 10g. Unfortunately, sometimes "order by" is quite useful when dealing with scalar subqueries and thus one may need to use the no_eliminate_oby hint.

There is another hint that can be used to swap the join inputs of a hash join (/*+ use_hash(t3) swap_join_inputs(t3) */). 10g also allows use of the no_swap_join_hint to further fine-tune exactly which table you want to be thrown into memory as the "first" table.

Section 4: Advanced Features

The MIN/MAX optimization that we talked about earlier has another nice little feature. It is not a filter predicate, but rather an access predicate, meaning it gets exactly the one little piece of data that it wants and nothing else.

Have to watch query plans. If you expect an optimal subquery but see a filter operation for it, you may want to re-evaluate it.

Remote operations are quite an interesting phenonemon when it comes to query plans. Although remote statistics are fetched, they are not fully used when generating a query plan. For instance, a 3-table join may be ordered such that a remote table is joined to a local table, which is then joined to a remote table. How much sense does that make? In most cases, the query will perform better if remote operations are done in as large a chunk as possible (and reasonable), instead of making many small trips back and forth.

Parallel operations are quite unique. Contrary to popular belief, they are not always a good thing.

Parallel query is designed to be brutal and inefficient.

I believe Jonathan said this because the idea of parallel query is to use up more resources (ie, more CPU) than a serial query would. In fact, it attempts to max out the CPU in an attempt to get the query done as fast as possible. I think he even said that parallel query should never be used in an OLTP database, but I did not write that down, so I could be wrong about that.

Anyway, parallel query has to be carefully tuned, for it is far too easy to thrash your system. Looking at an explain plan and trace files, we have to be careful about what we find. The infamous "idle" waits associated with parallel operations may indicate problems, or the may not, it depends on where they are happening. If an explain plan shows many P->P (parallel to parallel) operations, that could easily be a source of contention. Much better to have PCWP (forgot to write the definition) operations. In the case of a HASH join, you do not want to broadcase a large table to all the parallel query slaves; instaed, broadcast the small one (for memory) and read from the disk directly for the larger table (and joined locally). For nested loops, it is better to join and aggregate smaller chunks at a time, thus reducing "an entire layer of slaves".

Near the end, I had trouble keeping up with my notes (both because my butt was sore from the hard chair, getting tired, and being overwhelmed with so much information). We moved on to partitioned queries, and talked about the benefits of being careful when designed partitions and how to take advantage of parallel query. To fully realize the potential of partition-wise joins, it is best to partition early in the plan so that correllated, similar work can be parallelized.

On a slightly off-topic of partition statistics, Jonathan mentioned:

You ought to be faking your statistics. Sometimes.

Especially true since subpartition statistics are not dealt with properly at all.

And finally, if you see a "PX SEND BROADCAST" in your query plan, that automatically means you are not doing any partition-wise joins. They are mutually exclusive.

One last quote from Jonathan Lewis:

You stop getting lucky

In reference to performance problems that happen all the sudden even though "nothing changes"

I hate comment spam

2007-10-26T13:28:00.000-05:00

I finally made it to the state where I qualify for comment spam. GRRRR. Where is the "do not call" list?

My apologies for having to turn on capcha.

Redo log gaps with RAW disk: There has got to be an easier way...

2007-10-26T12:50:00.000-05:00

Of course, I admit right off the bat that we are still new with RAC, ASM and Raw disks. I have been asking around trying to find out what the "common practice" is when resolving the redo log gaps between a primary and standby database, specifically when using RAW disk. I think RAC just makes this scenario a little more complicated by the fact that you have two redo streams you have to keep an eye on.

So, here is the beef of my complaint. Let's say your archive log destinations are explicitly set for the standby SERVICE, letting the other destination "default" to the db_create_file_dest, which just happens to be the +DATA diskgroup under ASM (a RAW disk). As an aside, I am curious why it does not default to the db_recovery_file_dest, but that is a post for another day. Let's say your standby is down for whatever reason, and eventually you want to bring it back up. Oh yeah, there is no Data Guard, so forget about "automatic" gap resolution. How are you going to copy the archive logs from the primary to the standby?

There are no copy commands in ASM. At least, not for copying from a diskgroup to a device managed by a different file system, say, a cooked filesystem. One could use dd. But I would have to do a lot of reading to figure out what the offsets are, not to mention that the archive logs will most likely not be nicely laid out in contiguous blocks that you can gobble up with one dd command. The only option is to use RMAN. RMAN stands for Recovery Manager. You are going to use the Recovery Manager to copy files. Oh joy.

I am still searching for the best way to do this, but this is what I have now. Mostly for my own reference. =)

First, find the last log sequence number that was applied to the standby. Add 1. Call this minseq. If you have multiple instances, call this minseqN, where N = the instance number.

On the primary site:
rman target /
RMAN> backup archivelog from logseq :minseq;

Again, if using multiple instances:
rman target /
RMAN> backup archivelog from logseq :minseq1 thread 1;
...
RMAN> backup archivelog from logseq :minseqN thread N;

[ for now on, I will assume one instance because it is easier to type ]

Next, restore the archive logs to a cooked filesystem. In this case, I am going to use a standard ufs3 mount called /u02/backup.
RMAN> run {
2> set archivelog destination to '/u02/backup/';
3> restore archivelog from logseq :minseq;
4> }

Your archive logs are now "normal" files that can be accessed with normal OS commands. Copy them over to the standby site (we use scp). Wherever they end up on the standby site, I find it helpful to make a listing (including full path), one file per line. You will see why in a moment.

On the standby site, recover the standby database:
SQL > recover automatic standby database;

When you are prompted for a archive log, select the appropriate member from the list you made; you can copy the whole line and paste it into the prompt. Repeat until you cannot find any more entries on your list.

And Viola! You have resolved the archive log gap. Finally.

Some notes
If you know of a better way to do this, please let me know. =) I would love to do the whole thing from RMAN, but I have not figured out how to recover from specific archive logs; I keep getting the unhelpful "RMAN-06054: media recovery requesting unknown log:", even if I specify a archivelog destination. Obviously, I need to learn more about this aspect of RMAN.

I left out a small fact; we are not using a recovery catalog for this operation. Why not? In retrospect, it probably would have made things easier. But we are migrating away from a recovery catalog, so that is what we started with. If it turns out that using a recovery catalog is going to help us significantly, I would make a case to implement it. I believe it would have resolved the RMAN-06054 above, but I am not yet positive about that.

RAC: "How not to design an application"

2007-09-25T15:15:00.001-05:00

We have a new Luminis database that was being tested for an upgrade (4.01 or something like that). We found that we were constantly maxing out db_recovery_file_dest_size (database hangs, look in alert.log). The following is what I sent to my team members.

In RAC class, Andy warned us about the redo logs and how they can be a source of contention for some very basic things. Let us use LUMDEV as an object lesson.

Luminis has this little heartbeat function (I do not know the official name or the official purpose, but this is my observation) that updates 1 row of 1 table; a table with only 1 row. It does this continually. Better yet, this is execute not from one session, but from 7 sessions! So you have 7 sessions banging against this 1 row of this 1 table all the time. Obviously, the data in that row is not meant to stick around a long time.

Usually, in a normal database this is not such a big deal. Might cause some waits because of an exclusive transaction, but nothing that causes the rest of the database to wait or contend. In RAC, the world changes drastically.

Andy taught us that each instance masters its own list of global cache locks. These locks exist for every single block in a database (you can imagine what a nightmare it is for large databases). The list of master block locks are split up evenly between all participating nodes (and must be remastered when a node is added or deleted). These locks are often upgraded and downgraded (exclusive and shared), and often sent across the private interconnect (fancy word for NIC) to other instances. When an exclusive lock is requested on a different node than what is already holding the exclusive lock, the lock must first be downgraded, then re-upgraded for the requesting instance. Every time a lock is downgraded like this, the redo log buffer must be flushed.

See where this is going? You have 1 table with 1 row, meaning one block. Because the application is repeatedly requesting an exclusive lock from both instances, the global cache lock is constantly downgraded and upgraded. This produces many many many many many many archived logs for all those log buffer flushes. LUMDEV can easily consume over 18gb of redo space on this one operation alone.

Crazy, eh?

On a similar thread, I am not all that crazy about the dual management of both the ASM FRA diskgroup and db_recovery_file_dest_size. Here is what I posted to the ASM Oracle Forums:

For those of us who run multiple databases on a box with shared disk for FRA, I am finding the extra layer of ASM and db_recovery_file_dest_size to be a minor inconvenience. The Best Practice white papers I have found so far say that you should use db_recovery_file_dest_size, but they do not specify how you should set it. Currently, we have been setting db_recovery_file_dest_size rather small, as the databases so far are small and even at 3x the database size, the parameter is still significantly smaller than the total disk available in that diskgroup.

So, my question; is there any downside to setting db_recovery_file_dest_size equal to the total size of the FRA diskgroup for all databases? Obviously, this means that the amount of free space in the diskgroup may be consumed even if db_recovery_file_dest_size is not yet full (as reflected in the instance V$RECOVERY_FILE_DEST). But is that really a big deal at all? Can we not simply monitor the FRA diskgroup, which we have to do anyway? This eliminates the need to worry about an additional level of disk management. I like to keep things simple.

The question is relevant to folks using other forms of volume management (yes, I know, ASM is "not a volume manager"), but seems germane to the ASM forum because most articles and DBAs that I have talked to are using ASM for FRA.

Most importantly, what ramifications does "over-sizing" db_recovery_file_dest_size have? Aside from the scenario above.

I am really toying with the idea of making the recovery destination super-large and relying on the management of the FRA. To check space issues in the FRA, I can use asmcmd (the "du" output sucks, IMO) or query against the instance itself:


+ASM1_SQL > select FREE_MB, trunc((TOTAL_MB-FREE_MB)/TOTAL_MB,2) "% Space Used" FROM V$ASM_DISKGROUP
2  where NAME = 'FRA';

FREE_MB % Space Used
---------- ------------
  43426          .37

I am still looking for a way to generate a "du"-like report for all databases in the FRA.

In the database, I can check the amount of space in the recovery area:


SQL> select (SPACE_LIMIT-SPACE_USED)/1024/1024 "Space Left (MB)", trunc(SPACE_USED/SPACE_LIMIT,2) "% Space Used" from V$RECOVERY_FILE_DEST;

Space Left (MB) % Space Used
--------------- ------------
        18624          .04

I would hate to have to login to each database to find out how much recovery space is left before the database hangs.

UPDATE:
Arul made a most informative and excellent response:
http://forums.oracle.com/forums/thread.jspa?forumID=385&threadID=566242#2101818

Basically, set your db_recovery_file_dest_size appropriately, and you will not have to worry about the ASM diskgroup at all; let Oracle maximize/optimize the space when the parameter set to size that tailored to the needs of the database.

New blog buddies

2007-08-22T11:15:00.000-05:00

For some reason, blogger is not letting me add new elements to the blogroll. And I do not have time to ferret out the issue.

Anyway:
Dan Norris
Jeremy Schneider
Kirk McGowan

Seems like there was one other....

RAC is not easy

2007-07-24T08:38:00.000-05:00

I have a good friend to whom I like to complain a lot (sounds like my good friend is getting the short end of the stick, doesn't it?). With good intentions, he sincerely suggested that RAC is easy. I put in a fence, working 43 hours over a 4-day weekend. Shall I call that easy? It only lasted 4 days. He is still a good friend, he just thinks everything is easy. *grin*

K Gopalakrishnan once said:

Believe me RAC install is very simple and straight forward if you religiuoly complete the pre requisites.

Ok, enough of the griping. My hopes in posting up some of our specific dilemmas is to document what we are doing, since so often I try to google for specific errors and never find anything useful (I am not counting the Chinese sites, because even after Google translates them, I am not sure I see any resolutions apart from the questions).

So, first problem. I am following Metalink note 357261.1, very religiously. That was a mistake, as it is not complete, yet. Anyway. You attempt to remove asm:
srvctl remove asm -n urbdb1,urbdb2

You check to make sure it was removed:
srvctl config asm -n urbdb1
+ASM1 /u01/app/oracle/product/asm

It is not, so try again:
srvctl remove asm -n urbdb1
PRKS-1033 : Failed to remove configuration for ASM instance "+ASM1" on node "urbdb1" from cluster registry, [PRKS-1023 : Failed to remove CRS resource for ASM instance "+ASM1" on node "urbdb1", [CRS-0214: Could not unregister resource 'ora.urbdb1.ASM1.asm'.]]
[PRKS-1023 : Failed to remove CRS resource for ASM instance "+ASM1" on node "urbdb1", [CRS-0214: Could not unregister resource 'ora.urbdb1.ASM1.asm'.]]

What do you do?

Oracle Support has told me that crs_unregister is buggy and not supported. *cough cough* But I am going to attempt it anyway, since Bill Wagman had some luck with it (if you follow the discussion from oracle-l, you will see that Peter McLarty suggested it).

/u01/app/oracle/product/crs/bin: crs_unregister ora.urbdb1.ASM1.asm
CRS-0214: Could not unregister resource 'ora.urbdb1.ASM1.asm'.

/u01/app/oracle/product/crs/bin: oerr crs 214
214, 0, "Could not unregister resource '%s'."
// *Cause: There was an internal error while unregistering the resource.
// *Action: Check the CRS daemon log file.

Grrr... How quaint, check some log file somewhere on your system, and that will solve all your problems. Having no idea where my "CRS daemon log file" actually is, I use RDA to browse around and finally come up with /u01/app/oracle/product/crs/log/urbdb1/crsd/crsd.log. Unfortunately, the CRS daemon log file is not helping me much. What am I looking for?

Update: 5:19 pm
After a day of reading manuals and discussing options with the fine folks on oracle-l, we still have the same problem, albeit now I have quite a few new tools on my belt. Yes, RAC is not easy, I think I have proved that. To be a little more granular, working with the OCR is a pain in the butt.

So, new tools.

strace: a very low-level OS trace utility. I did not benefit from this, but I was able to show the output to others smarter than I. I used it on srvctl and crs_unregister.
The "force" flag (-f) of certain commands, like srvctl. I believe it removed something, but I do not know what; I still have my root problem.
Appendix A of the Clusterware Deployment and Admin Guide: has a ton of information, most of which would probably be helpful under "normal" circumstances. Did I mention we still have our root problem? However, I have to give credit to the authors, for they did a great job. There is a lot of information about log file locations (wish I knew about that earlier), how to debug various components and resources, and some descriptions of the syntax used for commands. I thought the OCR section was quite thin; perhaps I am biased because I am looking for a specific solution.
SRVM_TRACE=TRUE: This is documented in the above Appendix A, but I point it out because it spews out a bit more information. While not immediately helpful, it seemed like something that I should file away.
USER_ORA_DEBUG: mentioned one time in the Appendix, I found out that you could crank this all the way to 5. I have no idea what it does or what the appropriate values are - google is not giving much on it, yet.

More to follow. My Support Analyst just requested that I reboot the nodes because he has no idea why the resources exist in the OCR, as ocrdump does not list them.

Update: 11:22 AM, Wednesday
LS Cheng on oracle-l pointed out what ended up being the winning goal.
crs_stop ora.urbdb1.ASM1.asm

I still do not completely understand why this is an issue. Or even how one determines that is the solution. I hope to hear back more from LS Cheng so we can understand how he arrived at that conclusion.

What we ended up doing was restoring ocr to a point before we attempted to follow note 357261.1. Since the services were already down, it was straight-forward to delete the databases, the ASM instance and finally the ASM database. I was actually surprised it worked so well, given all the problems and headaches we had yesterday.

I will add another update when I learn more. Right now we are happy we have a RAC install back in working condition and can move forward with our projects. Oracle Support did not score any points in this round. This is becoming a bad trend.