12cR2 finally has on premises release! has been released for on premises installation.

It’s only available for Linux and Solaris so far, but that’s good enough to get some test installations started.



Nontechnical Skills Important To Being A Good Developer

What’s it take to be a good developer?

Brains? Of course you have to be able to think.
Logic? Sure, you need to have logical constructions.
Detailed? Maybe, you do need to pick out the intricacies of requirements.
Thorough? Yeah, I’ll give you that. The cause of all bugs is failing to cover some scenario.

I could go on about features of good developers and I’m sure readers have their own favorites; but the thing that I think stands out among the best developers is a “need to be right.” Now, of course this doesn’t mean that a developer is always right. I certainly am not. Nor does it mean that a developer needs to win every argument, although we may try.

No, the need to be right is a pursuit of correctness or best. One of the nice features of the software world is that many of our problems have absolutes in correct or incorrect; or, if not, they at least have objective comparisons of better or worse.

Does your calculation produce the correct answer? yes or no?
Does your procedure/function/method fulfill the requirement? yes or no?
Does subroutine A consume more memory than subroutine B while getting the same answer?
Does query X perform more IO operations then query Y to produce the same result?

Of course, a good developer wants to write good code; so it can be humbling when a bug is found, or a better way obsoletes your method. The need to be right though, supersedes the challenge to your ego. The need to be right is a relentless curiosity. If you find a bug in my code, thank you, if you identify why the bug occurs and how to fix it… THANK YOU! If you and I both have functioning SQL statements but yours is more efficient, THANK YOU! When I learn from a mistake or a superior design I “become right.” The need to be right is not about somebody else being wrong, it’s about you having, provably, the most correct and most efficient method possible. If that means you have to revise your solution based on new information, so be it.

One of the danger phrases that worries me is “I got it to work.” There’s nothing wrong, per se, with that phrase. I use it myself after a difficult challenge. The worrisome nature of it is that it’s often used as a declaration of finality. The task strained the brain and finally after much struggle, there is a successful result. “I got it to work, YAY!” But… does it work well? If you have the need to be right then you’ll want that answer. If you don’t, then you’ll call it done, check it in, and ship it off to production.

I’ve had many people ask me why I think it’s not a success. That’s simple. The problem was difficult, right? So difficult that you found it to be monumental when you got a successful run out of it. So then, what makes you think the thing that was so hard it couldn’t even produce the correct results until just now has simultaneously achieved maximum efficiency? Were you so busy tuning it while it was still wrong that it couldn’t possibly be any faster or less resource consuming? If so, that’s kind of an odd work style. Maybe that’s why it seemed so difficult?

It’s all related to the classic engineering joke/truism: Good/Fast/Cheap – pick 2. I believe in a corollary to that rule, it’s cheaper to doing something right once than twice wrong. So, while it might take more effort and cost to build a correct and efficient solution. In the long run that will end up begin cheaper because you don’t have to revisit as often.

However, there are traps in the need to be right. First, and probably the most obvious – you might fool yourself. If you try options A, B, C and determine C is best, that’s great; but if you never thought to look at option D you still might not be right. All you’ve done is prove you’re not the most wrong.

Second, there are problems that don’t have absolutes. “Most easily supportable”, “Best documentation”, “Most pleasing UX” – these ideals have leanings. There are approaches that seem more reasonable (follow some kind of standard, documentation that actually describes the objects, fewer interruptions in navigation) but at some point the differences just become preferences.

Third, you can be a jerk about it. If you forget that being “right” isn’t about somebody else being wrong. If you fall into this trap then you’re no longer helpful to your team even if you do happen to be “right” because attitude precludes successful cooperation.

The last trap is particularly tricky because it’s self-conflicting. Sometimes you should call it “good enough.” If the cost to improve some code is $1000 but you’ll only get $900 of benefit out of it, then don’t waste the money. It might indeed be possible to make a more efficient version; but if doing so is a net loss then don’t do it. Another problem with this approach is that it might not be that easy to produce an accurate cost/benefit analysis. If you call it quits early, you may be missing an opportunity. If you pursue too long you may be wasting time, money and other resources. When I run into this gray area I talk to my manager or client or whomever is footing the bill and give them an honest status. “I think this is working well, but could probably be improved. I’m willing to continue working on it; but it’s up to you how much to spend before accepting the results.” If you can give an estimate of cost to implement and expectation of improvement that’s even better.

Some might dismiss the idea of need to be right; that it’s just an obnoxious way of saying “try to write good code.” Any programmer that cares about their results will try to be both correct and efficient with their results. Yes, that’s true. I agree with that. However, the differentiating factor between merely being conscientious and having the need to be right is the proving.

If you ask any developer “Are you trying to write good code or bad code?” They will, of course, answer “good code.” In my opinion though, a good programmer is willing to show why their code is good. This “need” has two important benefits. First, by proving their design qualities they demonstrate they really do care about the quality of their output, they aren’t simply giving the desired response. Second, the act of proving correctness and efficiency requires technical insight and skill.

My favorite developers to work with always demonstrate this need to be right by showing their work and asking for review.

I shied away from writing a philosophical article because it’s one of those areas without an absolute.  This is merely an opinion; but I thought an interesting and important piece of successful development.  I hope you enjoyed reading.




Why does my code break with no changes and no invalid objects?

Recently I encountered an interesting situation where a job has been running successfully for months and then one day it suddenly started failing every time with an ORA-06502: PL/SQL: numeric or value error.  The same error can also manifest itself in bulk operations with ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind.

The more confusing part is that no changes were made to the code and there were no invalid objects.  Digging into the problem some more, the job called a procedure which read from a view, selecting from remote tables via a database link.

So, next step, obviously, I check on the remote side: but again, I find no invalid objects there and looking at the LAST_DDL_TIME in DBA_OBJECTS I see none of the tables had been modified in a couple months either.

Those old remote changes seemed innocuous; but it had been even longer since I had changed anything in the view or procedure within the local db.  I recompiled the view in the local db and the job started working again.  So, what changed and why did it only fail now and not a couple months ago when the remote tables changed?

For this I had to go to SYS and query DBA_TAB_COLS with flashback query to check the columns of my view and sure enough, one of the VARCHAR2 columns had grown.  The reason nothing failed was no new data came in right away using the new, bigger limit.

Once I saw the problem, replicating it was fairly easy.  Another interesting quirk between SQL and PL/SQL is the query within the procedure would run without error when executed as SQL because it didn’t have precompiled size limitations.  So it would adapt on its own.

The example objects and data to replicate the problem are fairly simple but it can be a little tricky following along since the session alternates between the local and remote database.  I’ve left the connect statements and altered the SQL> prompt to include the database name to help highlight where the action is taking place.

sds@REMOTE_DB> connect sds/pa55w0rd@remote_db
sds@REMOTE_DB> create table sds.testtable(test_id integer, testtext varchar2(10));

Table created.

sds@REMOTE_DB> insert into sds.testtable(test_id,testtext) values (1,'abcd');

1 row created.

sds@REMOTE_DB> insert into sds.testtable(test_id,testtext) values (2,'efgh');

1 row created.

sds@REMOTE_DB> commit;

Commit complete.

sds@REMOTE_DB> connect sds/pa55w0rd@local_db
sds@LOCAL_DB> CREATE DATABASE LINK remote_db_link CONNECT TO sds IDENTIFIED BY "pa55w0rd" USING 'remote_db';

Database link created.

SELECT * FROM sds.testtable@remote_db_link; 2 3

View created.

sds@LOCAL_DB> SELECT * FROM test_view;

---------- ----------
1 abcd
2 efgh

FOR x IN ( SELECT test_id, testtext
FROM test_view
ORDER BY test_id)
DBMS_OUTPUT.put_line(x.test_id || ' ' || x.testtext);
END; 2 3 4 5 6 7 8 9 10
11 /

Procedure created.

sds@LOCAL_DB> set serveroutput on
sds@LOCAL_DB> exec show_remote_data;
1 abcd
2 efgh

PL/SQL procedure successfully completed.

Up to here everything is working normally, now we’ll make the text column larger.
The procedure will still work correctly even though the local and remote sizes don’t match. The error doesn’t occur until new data shows up that exceeds the prior limit.

sds@LOCAL_DB> connect sds/pa55w0rd@remote_db
sds@REMOTE_DB> alter table sds.testtable modify (testtext varchar2(30));

Table altered.

sds@REMOTE_DB> connect sds/pa55w0rd@local_db
sds@LOCAL_DB> exec show_remote_data;

PL/SQL procedure successfully completed.

sds@LOCAL_DB> set serveroutput on
sds@LOCAL_DB> exec show_remote_data;
1 abcd
2 efgh

PL/SQL procedure successfully completed.

sds@LOCAL_DB> connect sds/pa55w0rd@remote_db
sds@REMOTE_DB> insert into sds.testtable(test_id,testtext) values (3,'abcdefghijklmnopqrstuvwxyz');

1 row created.

sds@REMOTE_DB> commit;

Commit complete.

sds@REMOTE_DB> connect sds/pa55w0rd@local_db
sds@LOCAL_DB> set serveroutput on
sds@LOCAL_DB> exec show_remote_data;
1 abcd
2 efgh
BEGIN show_remote_data; END;

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SDS.SHOW_REMOTE_DATA", line 4
ORA-06512: at line 1


Missing peaks in ASH results

The ASH charts in OEM are great utilities for getting a quick summary of your system’s activity. However, these results can be misleading because of how the data is represented on screen. First, ASH is data is collected by sampling so it’s not a complete picture of everything that runs. Another thing to consider is that the charting in OEM doesn’t plot every ASH data point. Instead, it will average them across time slices. Within Top Activity and the ASH Analytics summary charts these points are then connected by curves or straight lines which then further dilutes the results.

Some example snapshots will help illustrate these issues.

The OEM Top Activity screen may produce a chart like this…
Top Activity

First, note the large spike around 1:30am on the 16th. This spike was largely comprised of RMAN backups and is a significant increase in overall activity on the server with approximately 9 active sessions at its peak and a sustained activity level of 8 for most of that period.

Next, let’s look at that same database using ASH Analytics and note how that spike is drawn as a pyramid of activity. While the slope of the sides is fairly steep, it’s still significantly more gradual than that illustrated by the Top Activity chart. The peak activity is still approximately 9 active sessions at its highest but it’s harder to determine when and where it tapers off because the charting simply draws straight lines between time slices.

ASH Analytics

But, ASH Analytics offers a zoom window feature and using that we can highlight the 1am-2am hour and we get a different picture that more closely reflects the story told in the Top Activity chart. Note the sharp increase at 1:30 as see in the Top Activity. Also, note the higher peaks approaching and exceeding 12 active sessions whereas each of the previous charts indicated a peak of 9. The last curiosity is when the activity declines it is more gradual than the Top Activity but steeper than the Analytics overall chart.

ASH Analytics wall

The charts above demonstrate some ambiguities in using any one visualization. In those examples though, the data was mostly consistent in magnitude, but differing on rate of change due to resolution of the time slices.

Another potential problem with the averaging is losing accuracy by dropping information. For instance, in the first chart above, note the brief IO spike around 9:30am with a peak of 6 active sessions. If you look on the ASH Analytics summary chart it has averaged the curve down to approximately 2 active sessions. If we now go to the ASH Analytics page and zoom in to only the 9am-10am hour, we see that spike was in fact much larger at 24! This is 4 to 12 times our previous values and more importantly, running at twice the number of available processors. It was a brief surge and the system recovered fine but if you were looking for potential trouble areas of resource contention, the first two charts could be misleading.

ASH Analytics peak

I definitely don’t want to discourage readers from using OEM’s ASH tools; but I also don’t want to suggest you need to zoom in on every single time range in order to get the most accurate picture. Instead I want readers to be aware of the limitations inherent in data averaging and if you do have reason to inspect activity at a narrow time range, then by all means zoom in with ASH Analytics to get the best picture. If you need larger scale summary views, consider querying the ASH data yourself to find extreme values that may have been hidden by the averaging.

The Curse of “Expertise”

Like everyone else, I make mistakes. While the results can sometimes be unfortunate, it’s also a truth that shouldn’t be ignored. A recurring problem though is that as a designated “expert” sometimes people don’t bother to test what I’ve given them. They just roll with it and then are surprised when their production installation goes awry.

I just ran into this situation again a few days ago. I was asked to help with a query that didn’t ever finish. I worked on it for a little while and came up with something that finished in a few seconds. Since the original didn’t finish, I didn’t have a predetermined set of results to test against. I manually walked through some sample data and my results seemed to tie out… so, it seemed like I was on the right track. I showed the client what I had and they were elated with the speed improvement.

I gave a brief description of what I had attempted to do and why it ran quickly. Then I asked them to test and contact me again if there were any questions.

The next day I got a message that they were very happy with the speed and were using it. I was glad to hear that but I also had been thinking that my query was extremely complicated, so even though it has apparently passed inspection I spent a few more minutes on it and came up with a simpler approach. This new method was almost as fast the other one but more significantly it returned more rows than my previous version. Clearly, at least one of them was incorrect.

With the simplified logic of the new version, it was much easier to verify that this second attempt was correct and the older more complicated version was wrong. I reached out to my client again and notified them of the change in query and problem I found. Then suggested they rerun more extensive tests anyway because I still could be wrong.

Fortunately, this second attempt did appear to be truly correct and the performance was still more than adequate.

Finding the name of an Oracle database

Oracle offers several methods for finding the name of a database.

More significantly, 12c introduces new functionality which may change the expected value from some of the old methods due to the multi-tenant feature.

Here are 11 methods for finding the name of a database.

SELECT ‘ora_database_name’ method, ora_database_name VALUE FROM DUAL
SELECT ‘SYS_CONTEXT(userenv,db_name)’, SYS_CONTEXT(‘userenv’, ‘db_name’) FROM DUAL
SELECT ‘SYS_CONTEXT(userenv,db_unique_name)’, SYS_CONTEXT(‘userenv’, ‘db_unique_name’) FROM DUAL
SELECT ‘SYS_CONTEXT(userenv,con_name)’, SYS_CONTEXT(‘userenv’, ‘con_name’) FROM DUAL
SELECT ‘SYS_CONTEXT(userenv,cdb_name)’, SYS_CONTEXT(‘userenv’, ‘cdb_name’) FROM DUAL
SELECT ‘V$DATABASE name’, name FROM v$database
FROM v$parameter
WHERE name = ‘db_name’
SELECT ‘V$PARAMETER db_unique_name’, VALUE
FROM v$parameter
WHERE name = ‘db_unique_name’
SELECT ‘GLOBAL_NAME global_name’, global_name FROM global_name
FROM database_properties
WHERE property_name = ‘GLOBAL_DB_NAME’
SELECT ‘DBMS_STANDARD.database_name’, DBMS_STANDARD.database_name FROM DUAL;

The results of these will vary by version, whether the db is a container or not,  and if its is a container, whether the query runs within a pluggable database or the container root database.
Note, the con_name and cdb_name options for the SYS_CONTEXT function do not exist in 11g or lower. So those queries in the union must be removed to execute in an 11g database. Within a pluggable database some of the methods recognize the PDB as the database, while others recognize the container as the database.

So, if you are using any of these methods in an 11g database and you upgrade to a 12c pluggable db, you may expect the PDB name to be returned, but instead you’ll get the CDB name instead.
Also note, some of the methods always return the name in capital letters, others will return the exact value used to create the database.







ora_database_name SDS12CR1 SDSCDB1 SDSPDB1 SDS11GR2
SYS_CONTEXT(userenv,db_name) sds12cr1 sdscdb1 sdscdb1 sds11gr2
SYS_CONTEXT(userenv,db_unique_name) sds12cr1 sdscdb1 sdscdb1 sds11gr2
SYS_CONTEXT(userenv,con_name) sds12cr1 CDB$ROOT SDSPDB1 n/a
SYS_CONTEXT(userenv,cdb_name) sdscdb1 sdscdb1 n/a
V$PARAMETER db_name sds12cr1 sdscdb1 sdscdb1 sds11gr2
V$PARAMETER db_unique_name sds12cr1 sdscdb1 sdscdb1 sds11gr2

On a related note, only the container of a multi-tenant database has instances. So, while PDBs can declare their own name for the database level with some methods above; there is no corresponding PDB-instance name functionality.

See you in Las Vegas!

I’m flying out tomorrow for Collaborate 16.
Looking forward to another great conference.

I’m presenting again this year.
I’ll be speaking on Tuesday, at 2:15
“Why Developers Need to Think like DBAs, Why DBAs Need to Think like Developers”
Session 1355 in Jasmine C

%d bloggers like this: