MOS Workshop: Fixing SRs

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?

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

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

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

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

"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

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;