Friday, June 25, 2010

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[]
printHeader "Overview of $product"
print "$product is a powerful utility to manage your data quickly"

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?!?

Friday, June 04, 2010

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.

Thursday, February 18, 2010

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 + 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#;

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);


-- Using FGAC example from

-- 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';

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
return '(sys.anydata.accessvarchar2(gorsdav_value) = ''Y'' and pill_1 = 1) or pill_1 <> 1';

PROMPT Create RLS Table 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);

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);

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
return '((cast(gorsdav_value as varchar2(1)) = ''Y'' and pill_1 = 1) or pill_1 <> 1)';

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);


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);


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

Tuesday, January 05, 2010


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.