Oracle Certifications

DBA’s require a broad set of skills. Depending on the job they’re in, they may focus on user management, backup/restore, installation, configuration, tuning or other less frequent tasks. However these areas do make up the full skill set required to work on different projects in various settings.

There are mixed feelings about the OCP certification. There are many people who describe certifications programs as gravy trains for the vendor and hamster wheels for the DBA. Tom Kyte famously derides the program, displaying an enviable level of skill and proudly pointing out that he has no certificates. Once upon a time, I felt the same way. Then the reality of HR departments and competitive bidding processes made it clear that certifications do have a value in getting me through the door so that I can talk to potential employers and customers. It’s not the only way to establish credibility, but it is one.

For me, there was an unexpected benefit for studying for all the OCP exams. In my case that was 8i, and there were more exams to write than there are now. I was forced to read manuals I’d never opened before, and to learn about tools I’d never needed. Although I was working as a DBA, my job only required me to know so much. Studying for the OCP forced me to learn new things, and some of them even proved to be useful for the job I was in in at the time.

I’ve kept my OCP certifications up to date right up to 11g.  The consequence is that for each release, not only have I seen the sales materials, but I’ve also had a careful look at the new features. Some of them don’t apply to my worklife, but studying has given me enough information to discuss them intelligently should they come up. Others become good friends. Flashback database makes performance testing the easiest and most reproducible experience I’ve ever had, but I might not have learned that 3 years ago if hadn’t studied.  11g’s database replay is just as promising now.

So, I’m currently studying for the 10gAS OCA exam. I’ve been working with pieces of the middleware for a few years now in Grid, OID, Single Sign On and others. But there’s a lot more to the middletier than I’d yet needed it to do, and now I feel better prepared to handle new challenges as they come up.

Who’s minding the middle tier?

Oracle’s growth and acquisitions are providing and endless parade of new products called “Oracle”. If it’s called Oracle, then surely the Oracle DBA can handle it, right?

Traditionally trained DBA’s know about databases. They can install them, patch them, safeguard and tune them. There are many people working at DBA’s who don’t actually know what’s in the database they shepherd. They consider themselves responsible for the container, but trust that someone else knows and loves the data as well as they do the database itself. The injunction to “Know thy data” has changed the tone of that for DBA’s who are interested in high performance configurations.

But what about the middle tier? Who looks after it? And what happens when it’s branded Oracle like the database?

Peoplesoft long promoted the construct of the “PeopleSoft DBA”. That elusive person had as good a knowledge of the database technology as of Tuxedo and PIA and PeopleTools and all the many pieces of a complete PeopleSoft installation. In the shops I’ve worked in, working that way is impossible through careful Separation of Duties. Application and Database administration are commonly separated into different people, often with different reports. It’s an unsatisfying compromise. PeopleTools generates DDL, but it’s usually a DBA who is skilled to review and adjust it. People on both sides of the equation are frustrated when their colleague doesn’t know or understand the some critical piece of the puzzle. It can be made to work, and I have had many satisfying partnership arrangements with solid PeopleSoft admins.

Oracle’s E-business and the whole Fusion architecture exacerbate the issue. Who builds the single sign on system? Who administers the LDAP directory? These don’t sound like traditional DBA work. But Oracle have branded them, so managers are quick to assume that it’s the DBA. Furthermore, Oracle’s middletier needs its own metadata repository databases. The trend does seem to be toward DBA’s doing more, often outside of their comfort zone, and potentially across longstanding organizational boundaries.

DBA’s may have to open their boundaries on what they consider acceptable work. For example, if you haven’t helped build out the single sign on for “your” application and can’t maintain or troubleshoot those areas, then your ownership of the system is less than you might like. Don’t be surprised if you find yourself excluded from interesting, challenging work should you pass these options by. It’s particularly important because the business owners of a system tend to be very focussed on what the middle tier is capable of. Stay close to the business.

Managers need to look at the problem carefully. Just because it has the Oracle brand on it doesn’t mean that the DBA’s can just handle it. Even if they’re willing and able to step up, it is simply going to mean more work. The team may not have the bandwidth to handle these “new” duties. Alternately middle-tiers have sometimes been handled by web administrators, who will need to learn a great deal about the Oracle stack if they’re going to take on these pieces.

New learning and training seems to be in order. Oracle University are now offering an OCP for the Application Server technologies. It includes the metadata repository, OID, SSO, WebCache and a whole host of technologies normally outside a DBA’s purview. Admittedly, a year ago now when I tried to register for one of the courses locally it was undersubscribed and didn’t run so based on that unscientific sample, interest remains low. The happy news is that certified DBA’s can get grandfathered in and avoid the “training” requirement– only the two exams are required.

DBA’s are well suited to stepping up and handling the growing demands of the Oracle middle tier. The understanding of the technology stack, installers and patchers gives them a head-start in dealing with these newer pieces. High availability configurations will only make the systems more interesting to build and maintain.

APEX Contact Manager on Oracle XE

E-mail makes an inadequate contact manager. APEX was the buzz at Hotsos this year. It came up in conversation after conversation. I haven’t done anything with it in a couple years now, and sure enough it’s been growing up. I checked, and there is a small contact manager sample available on OTN for download. I decided to use Oracle’s Express Edition (XE), since I didn’t have an instance on my Windows laptop.

XE is freely downloadable, and on Windows is simply installed by clicking on the downloaded exe file. The current 10.2 XE includes APEX 2.1, but APEX is up to 3.2. A quick search on the APEX forum turned up procedure to upgrade to 3.1.

The upgrade procedure is simpler than the documentation makes it sound. You don’t need to read three manuals first if you’re working with an empty database. If you have already have a working installation, more research and care will be required. If you have no Oracle experience yet, spend some more time learning how to connect to, start and stop the database before you try anything like this.

1. Unzip the apex3.1 file to a directory. e.g., c:\apex31
2. Change to that directory
3. Connect to the database as sys     sqlplus "/ as sysdba"
4. Install 3.1                                    SQL>@apex\apexins SYSAUX SYSAUX TEMP /i/
5. Change the APEX password SQL>@apex\apxchpwd
6. Load the images                       SQL>@apex\apxldimg.sql c:\apex31
7. Set the password                     SQL>@apex\apxxepwd.sql
8. Logon at                                      http://127.0.0.1:8080/apex

For now, your username is the same as your workspace when logging on.

The contact manager itself is very easily installed. Download Customer Tracker from OTN here, and unpack to a directory.

Logon to APEX. The user account shouldn’t be system. I tried to use an account with just Create Session and Create Resource. To create the supporting objects, I also had to grant the following directly (not through a role):

• create table
• create view
• create procedure
• create trigger

Logon to APEX at http://127.0.0.1:8080/apex

Application Builder=> Import

Use the Browse button to locate the sql file in the customer tracker directory. Click Next to Import the File, Next to Install the Application. Create the supporting objects.

Remove the sample data by logging onto sqlplus as the user you used for installing the application

begin
eba_cust.remove_sample_data;
end;
/

Click on the Stoplight to launch the application.

My initial impression for the application is positive. Although there is a way to link customers to contacts, the reverse doesn’t seem to be true.  Activities also link with customers instead of with contacts. Perhaps I’ll need to modify that.

Year end checkup

Month-ends, quarter-ends, beginnings of terms, inboxes fill up with worried and worrisome threads about something gone off the rails. Applications which behave satisfactorily on a day to day basis can’t necessarily cope with peak loads. The maintenance window for one application may coincide with a heavy workload for an unrelated system that shares the same infrastructure. Many database based applications are fine much of the time, but when they go off the rails,  fingers start pointing.

As an outsider looking in, there’s an urge just to say “Well, sure, look at your tiny buffer cache” or “You’re not using tempfiles?” or “Gee, these redo logs are out of the box small and are switching constantly.” Rarely are such suggestions accepted kindly unless they’re carefully delivered. People are attached to their systems after investing years in them. They probably sweated to get that configuration in place for a go live date at some point, and may need a nudge to revisit it.

Systems change. They get more data, more consumers, new bells and whistles. Hardware shifts underneath: competition for CPU’s or memory, batch and maintenance schedules evolve, SAN layouts or usage is nearly always a (big scary) black box.

When did you last look at your system and ask “Is it running efficiently?” This is a different question than “Are the users happy?” or “Are there alerts being generated?”  We have to respondt to symptoms or complaints from users. But since systems change and grow over time, some of those peak events can be avoided simply by stepping back for a minute. It’s very useful to step back occasionally and take a fresh look at a system.

When did you last review the priniciple memory settings for your database? In many mature systems, the buffer cache and shared pool sizes were set at go live or by some consultant at some point and haven’t been looked at since. They may have been optimal when they were setup, but systems change and so do their requirements.

10g makes reviewing memory parameters easy with the SGA advisor tools in Grid. 9i included cache and shared pool sizing advisors in the statspack report. There were early days versions of these in 8i even that are better than nothing. Have a look at them. Have they changed? (Very careful types will have previous reports to compare them to.)

I can think of at least three systems that I’ve worked on in the last year that were diagnosed with Slow I/O. The SAN and its administrators had been tarred and feathered and put out in the rain. However, the systems still unacceptably slowly. In otherwise smoothly running systems, simply increasing the buffer cache helped because the cheapest I/O is no I/O. There was a very careful line to walk to be sure to say “Yes, the SAN is slow. I think we can do something in the meantime that may alleviate some of the pain.”

I don’t mean to suggest that workload profiling and careful tuning aren’t where the real rewards lie.  I also don’t want to suggest that SAN layout and resource competition don’t matter. Ratio based tuning is as 90′s as 16 bit Windows.

But when did you last look at and re-evaluate the most basic memory parameters for your instance? Could you save yourself some wait cycles simply by using a bit more RAM for the log buffer or the cache? Are you switching logfiles more often than before and starting to accumulate log file switch waits?

Not only are these the kinds of things that might just nudge the ceiling up that extra 5% for high load periods, but they’re the kinds of things that you can’t do on the spot when the urgent tickets start appearing.

v4 timezone files

Please be advised that many releases of p5632264 were incorrect last spring. I’ve found records on Metalink acknowledging this for Sun/SPARC and HP-UX PA-RISC for 10.2.0.3. I found a non-public note referring to 10.1.0.5 for Linux.

The workaround is to find a good patch for your platform and version (but not necessarily patch level), and apply the files manually. DB can be running, but must be restarted.

I’m going to refrain from commenting on how this makes me feel, given the hours of work that went into preparing for DST.

v4 files are needed to get DST changes for all Canadian locales, Western Australia, Egypt and Brazil. They are a pre-requisite for 11g upgrade.

11g: Extra packages

Since Linux came out so many months ago, I really wasn’t expecting any trouble. 11g forced me to add libaio-devel, elfutils-libelf-devel, unixODBC-devel-2.2.11, but they yum’ed without difficulty. A couple kernel parameters more, and it was ready to roll.

The  overlapping documentation has been a challenge for years. The Install guide is a nice idea, but doesn’t agree about the kernel parameters. Metalink happily does agree with the Installer’s lists. Makes me very grateful for the sanity tests added to the installer in 10g with the clean, easy to accommodate lists of failures and warnings.

Can’t keep putting it off: 11g

Life intervened and technology went on the the backburner. In the spirit of New Year’s resolutions and going to the gym, let’s see what can get accomplished by trying.

Yes, it’s finally time. I can’t think of anymore excuses to put off dealing with 11g. It’s full of “new” features, but I’m not sure what’s in it to get me all excited and to jump on board. I want the implicit bug fixes, but haven’t yet seen a New Feature that makes me want to tell a committee they should deal with a version they haven’t yet heard of. Duplicate Database without doing a backup first is awfully appealing to me, but that’s not exactly a big ROI.

So, I’ve done a first 11g install. Because life is never really easy, I did the first install on HP-UX. I’m pleased to report that if you apply the OS pre-req’s, it works. This shouldn’t be a big deal, but the HP-UX install testing has left a bit to the imagination lately.

Well, come to think of it, I installed an 11g client on my desktop some weeks ago. Although I applaud the sentiment to lock-out access to older versions, it is annoying to have to keep an 8i client installed to talk to the remaining 8i and 7 databases.

Currently struggling with DST patches. Happily, 11g uses the v4 timezone files which includes Canada, not just the US changes. However, despite thinking we were fully patched, I’m getting warnings that I’ve got the v3 files. Hmmmm…..

Trying a first upgrade, ASM comes right to the fore. Until now, when I think of it, I think of CSS as part of ASM. However, 11g recommends moving CSS and running it out of the database home.  Forced me to think about it more than I had until now. Although an 11g db can run in a 10g ASM, and a 10g DB can run in an 11g ASM, CSS must always have the highest of the possible number. Given compatibility issues, this makes sense. Moving it into your most up to date home is a nice, simple recommendation.

Partition Pruning

I regularly have dev’s come to me and say “It should be fast. It’s partitioned.” Yes, well, err, maybe.

Then there are the ones who know that the data they’re interested in is stored in particular partitions, so they resort to procedural code to make it “fast”. Just had a case today where some rather worrisome code was being written to get the partition key before joining to the table, but as a result looping and looping and looping through data that wasn’t interesting. Following Tom Kyte, if you can do it in a single SQL statement, you should at least try it once first.

for i in (select key from master_table) loop
select count(*) into rowcount from
                            detail_table partition (i.key),
                            master_table
                            where detail_table.key=master_table.key;
dbms_output.put_line (i.key || ' Count ' || rowcount);
end;

Why so much trouble to get a row count? That required an amusing 10 minute chat with the developer, who jumped to the whiteboard to show me that it was all about being able to insert rows from table 2 into table 3 based on table 1. But it had to be fast, so PL/SQL was better, right?

So, we had a lovely chat about partition pruning, the wonders of the CBO, and reduced it all to

insert into backup_table select * from detail_table where key in
               (select key from master table where...);

But, this was doing a FTS. The time was fine, it’s just that it was doing a table scan, and we all know that they’re RED in Toad. So that means they’re bad. (Maybe I should file an enhancement request with Quest to have FTS show as green.)

All it took was a little hack to make it clear. The table had 150 partitions. So, just to prove a point, limit the number of rows.

select * from detail_table where key in
                   (select distinct key from master table where rownum<10);

The explain plan clearly showed PARTITION RANGE ITERATOR. Then, get us back something with potentially 2/3 of the partition keys.

select * from detail_table where key in
                          (select key from master table where rownum<100);

Now, the explain plan showed PARTITION RANGE ALL. So, just like indexing, if you’re getting back too large a percentage of the table, Oracle won’t prune partitions.

So, is it a bug?

Following up on yesterday’s misadventure trying to find the badly behaved SQL when it didn’t show in the test plan, I wrote a simple test case. I found the problem in 9.2.0.8, but since it’s terminal, I figured I’d see what 10.2.0.3 did. The behaviour is the same.

SQL> select version from v$instance;
VERSION
-----------------
10.2.0.3.0

-- Couple simple test tables
SQL> create table test1 (c1 number);
Table created.

SQL> create table test2 (c1 number, c2 number);
Table created.

-- Test query resembling the problem yesterday.
Query wrapped in a function.
SQL> select nvl((select 1 from test1,test2 where test1.c1=test2.c2),0)
functionout from dual;
FUNCTIONOUT
-----------
0

SQL> explain plan for
select nvl((select 1 from test1,test2 where test1.c1=test2.c2),0)
functionout from dual;

Explained.

-- THE UNDERLYING TABLES APPEAR IN THE PLAN

SQL> @plan

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |       |     2   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST2 |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |  FAST DUAL         |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - access("TEST1"."C1"="TEST2"."C2")

-- Now, create a view
SQL> create or replace view vlevel1 as
select nvl((select 1 from test1,test2 where test1.c1=test2.c2),0) functionout from dual;

View created.

SQL> explain plan for select * from vlevel1;

Explained.

SQL> @plan

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |       |     2   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST2 |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |  FAST DUAL         |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - access("TEST1"."C1"="TEST2"."C2")

-- NOW, a UNION ALL view

SQL> create or replace view vlevel2 as
select nvl((select 1 from test1,test2 where test1.c1=test2.c2),0) functionout from dual
union all
select nvl((select 1 from test1,test2 where test1.c1=test2.c2),0) functionout2 from dual;

View created.

SQL> explain plan for  select * from vlevel2;

Explained.

SQL> @plan

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     2 |     6 |     4   (0)| 00:00:01 |
|   1 |  VIEW            | VLEVEL2 |     2 |     6 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL      |         |       |       |            |          |
|   3 |    FAST DUAL     |         |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL     |         |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

The underlying tables do not appear in the plan, and the filter is not stated. 10.2.0.3 behaves like 9.2.0.8. So, here we have an explain plan that’s missing information.

Feels like a bug. Looks like a bug. Maybe it’s “as designed”. Wonder what Oracle will say?

It’s always the untracked change

I think I need a sign for my cubicle wall.

Thank you for your complaint. To save time, let us agree now that

• it’s not the database pfile
• it’s not the db block size
• it’s not the security patch we applied last month
• it’s not the network
• it’s not the SAN

It’s something you did that you didn’t know mattered and have since forgotten about.

Today I’ve suffered through the horror of deconstructing a select from a view, that was a union all of about 30 more views. The explain plan was a humanly unparsable 1089 lines. I resorted to the tedious tactic of commenting out calls to the views one by one to find the problem(s). In this case it turned out to be multiple views with an in-line view which doesn’t show up in the explain plan (Is this a bug? Add this test to my to do list.) One of the tables in the in-line view was missing an index on an undeclared foreign key.

I found this through the”wrong” methods. 10046 traces failed me. It reported the correct elapsed time (hours), but reported waits of only a few seconds. I ran a level 7 statspack report, and it showed that the busiest segment was table not even in my explain plan. After a sleepless night I started commenting out code to narrow down to portions that had trouble, opened them up side by side, and found an inline view wrapped in an NVL. I only checked the existence of the index on the foreign key out of reflex.

Where did it go? Who knows? It’s a dev/test box. Things happen. I understand that, but I do need my sign.

Level 7 statspack reports came available in 9.2 and are useful things. They report the busiest segments for the duration of the window by physical and by logical I/O. They’re slower to run and too expensive to use in production on a daily basis, but I do fall back on them.

SQL> exec statspack.snap(i_snap_level=>7);

Or, to set them as the default if you’re in a debugging cycle,

SQL> exec statspack.snap(i_snap_level=>7,i_modify_parameter=>true);