Which Oracle Release are you using?

13/08/2018 9 Comments

Post Date: August 2018!

Recently an awesome Oracle Guru friend of mine questioned someone who was installing 11.2.0.4 with the word “seriously”, which is think shows that Oracle staff sometimes don’t live in the same technological world as the rest of business.

My response was:

11.2.0.4 is normal. In the real world:

– large corps mostly use old versions
– consultants look at current versions
– Oracle staff look at unreleased versions

I have known instances of Oracle staff blogging about how a feature works when, in the officially released versions, it didn’t. It only worked that way in a version which was released some months later. There was no reference to the release and the fact that there was a significant functional change between releases (but I suppose that’s a blog and not “official” documentation – the official documentation said nothing at all about how that particular feature worked. Nothing! So thank you mystery blogger.)

Anyway, the point of this post was I then did a small twitter poll to my most excellent and cosy band of followers to see what Oracle releases people were using. I asked 2 questions (because twitter is limited) and here’s the results:

oracle_version_highest

So more people have some form of 12 in the DB, but only 7% have 18 in Production. This at a time when most Oracle staff are thinking about Oracle 20 and 21, as Oracle 19 is done and just awaiting release. Think about that, Oracle… Whenever I am at a presentation by an Oracle PM, I think “wow – I might be able to use those new features in 2-5 years”.

oracle_version_lowest

So very few people have 12.x as their lowest version (which would include 18 as that’s really 12.2.0.2) and MORE have 9, 8 or 7 as their major headache! Yes – there are more on 9, 8 and 7 than are using 18 in Production. Lets say that once more. There are more on 9, 8 and 7 than are using 18 in Production

So why upgrade? Very few databases take advantage of all of the latest sexy features. I suspect that many of the applications still being produced could run on Oracle 7.3.4. – more so as the proliferation of ORM’s like Hibernate has left a generation of developers with little appreciation of the database and how to take advantage of it**. So why upgrade? These days? Security. Patches. Support. Without those 3 things, you are living on hope, hope that nothing goes wrong as you’ll struggle to find anyone to fix it – including Oracle. Hoping that nobody tries to hack your 8.1.7 database as it’s a Swiss Cheese of vulnerabilities, like all 7, 8, 8i, 9i, 10G DB’s. Not that we hear about systems being compromised every day on the news.

Anecdote : By coincidence I was talking to a client at about the same time and whilst they are a mostly 12.1 shop, they still had an old 8i database hanging around… as usual it was going toĀ  be “retired soon” (which in my experience means sometime in the next 15-20 years) and wasn’t worth the time and effort to be upgraded or even do a business case to upgrade it!

**Anecdote : At a client a few years ago, an excellent Java Developer asked me to put an index on a flag column. I pointed out that with only 3 values that an index wouldn’t help, and as this was OLTP a bitmap index wasn’t appropriate due to concurrency issues. He said that with 3 values indexed, his query would be 3 times faster! We sat down and I explained some database fundamentals to him, at which point he said “don’t put an index on there – that would be a stupid idea”. A few weeks later he came back over and asked about SQL queries “I’m trying to aggregate this data – can the database help?”. I spent 30 minutes showing him in-line views and windowing analytic functions and we wrote the code he needed for his output. “Wow! You have just saved me 3 days of Java coding…” – he was going to pull everything into Java and process it there, so as well as 3 days of coding, we also saved the SAN, the network and a whole bunch of CPU by dealing with data at the database layer – which is always the most efficient place to deal with it!

Filed under Administration, Management, oracle Tagged with , , , , , , , , , , , ,

UKOUG Tech13

07/12/2013 Leave a comment

I attended the UKOUG Tech13 conference this week, along with over 1,000 other Oracle Technical Professionals (and some less-professional too), to learn what I could about how everyone else is doing stuff with Oracle, and how it works. And to share some experiences.

I wasn’t disappointed.

As with everything these days, there was a lot of activity on Twitter about the 4 day conference, and one of the sponsors, RittmanMead, had knocked up a rather wonderful tweet visualisation (click here to see how – magical):

UKOUG13_Tweet_Feed

As you can see, there are some of the worlds best-known Oracle techies on the list of top speaker tweets, but I am as pleased as anything to have been in the top 10 most tweeted speakers at the end of the 1st day (although I suspect that’s because I knew where the pub was, rather than too much about my 1st presentation)

Get involved with the Oracle community, in whatever form. It leads to professional and personal development, real insights about what Oracle really does (rather than what the marketeers say), and some good friendships. And possibly liver disease judging by the amount of alcohol consumed by all. šŸ™‚

@chandlerdba

Filed under Management, UKOUG Tagged with , , ,

SYSMAN password change

29/08/2013 Leave a comment

Some days you just forget the dot all of the i’s.

I had just installed a new RAC cluster, got it all up and running and was using DBCONSOLE to check the system out – no access to the Production Grid Control for this cluster yet. I then made a few more configuration changes and restarted one of the nodes. I was rather surprised that the console could no longer access the system. It was claiming the instance was down, and asking for server logins to allow restart. I was quite sure the instance was available, mainly because I was connected using SQL Developer and executing queries.

So, what went wrong? What config had changed before I restarted the nodes? I checked my notes and… I was hardening passwords. One of the passwords I changed was the SYSMAN password. However, I had completely neglected to inform the EM agent for the console that I had changed the password! Idiot.

cd $ORACLE_HOME/<node_database>/sysman/config
vi emoms.properties
change:
 - oracle.sysman.eml.mntr.emdRepPwd=<clear-text-password>
 - oracle.sysman.eml.mntr.emdRepPwdEncrypted=FLASE

emctl stop dbconsole
emctl start dbconsole
…and all is well again

This blog entry was brought to you by Pierrot.

Filed under Administration, Management Tagged with , , , , , , ,

The Dunning-Kruger Effect – I don’t know

08/08/2013 2 Comments

Interviewing people is always a rich source of anecdotes, or should that be anec-dont’s? Whatever.

When interviewing for a technical specialist, such as a senior Oracle DBA, it seems important to ask a number of technical questions to ascertain the competency the individual has in relation to Oracle and the technologies related to Oracle. This is where the Dunning-Kruger effect comes into play beautifully.

Kruger and Dunning proposed that, for a given skill, incompetent people will:

  1. tend to overestimate their own level of skill;
  2. fail to recognize genuine skill in others;
  3. fail to recognize the extremity of their inadequacy;
  4. recognize and acknowledge their own previous lack of skill, if they are exposed to training for that skill;

So, lets ask a technical question of the candidate:

Under what circumstances will the optimizer decide not to use an index?

Here’s 4 extremely confident actual answers from 4 different candidates. This was the extent of their answers, although some (but not all) did elaborate further:

  • when you do a count(*)
  • when the columns you are selecting are not indexed
  • I query using a table and build a SQL Profile to bypass the index
  • it depends on the join condition

All candidates were OCP certified with at least 10 years worth of Oracle experience on their CV’s, applying for a senior position. To quote Darwin: “Ignorance more commonly begets confidence than does knowledge”. I like to think that it’s our brains protecting us from ourselves.

I mean… how hard is it to say “I don’t know” ? For some nationalities, that would appear to be impossible. A long time ago I spent several weeks teaching programmers in a country whose native/official language was not English. Given I am from the North of England, there are some who would say that this is also the case for me, given my notable regional accent. The combination of my idiosyncratic dialect, combined with the unnamed-county’s locals’ inability to lose face by saying “What you talkin’ about Neil?”, like Gary Coleman might have, meant that the second week of training was spent going through exactly the same materials as the first week, as it took me that long to realise they didn’t have a clue what I was saying half of the time. If only one of them had been brave enough to say (or I had been a little smarter and realised) then it would have save a whole lot of pain. However, the locals might never come understand what happens when you get “your knickers in a twist.”

NOTE: I do appreciate that I could be a Dunning-Kruger “victim” and these blogs merely indicative of immense incompetence. I’ll let you decide. šŸ˜

Filed under Management, Recruitment Tagged with ,

Why ITIL CAB’s do not work as expected

29/03/2012 7 Comments

ITIL (Information Technology Infrastructure Library) does work. It works very well and is becoming more and more widely adopted, but does not always work the way that management thinks it works.

The basic premise of ITIL is to put structure and process around the business of running IT. It is a collection of guiding principles which, if appropriately adopted into a company will help run the IT function in a reasonable process-driven fashion. It’s largely common sense, which helps.

However, after lots of experience with all of the different aspects of ITIL I have decided that it doesn’t really work the way management think it does. This is especially relevant to Change Management.

The premise behind Change Management is that technical staff are prevented from making non-Standard changes to the IT systems without prior authorisation from all relevant stakeholders.

Where this falls down is 2 fold.

– The definition of Standard change needs to be very carefully identified, documented and locked-down. It never is, leading to techies making personal decision about what constitutes standard change. This definition is always enthusiastically elastic, until something snaps when it will become frustratingly draconian (for a while).

– The stakeholders rarely understand the nature and true impact and risk of the changes taking place, so the CAB must accept the opinion of the techie (see above). The net result of a Change Advisory Board (CAB) is to authorise the technical staff to make change whilst putting the burden of responsibility for the change onto the management. Not the techie. If the changes goes wrong, then it is fault of the approving manager for not assessing the risk correctly, or for approving the change to take place at the wrong time or in the wrong way. However, the techie gets off scott free in all of this.

As long as the process is followed, we can get away with murder.

Filed under Management Tagged with , , , , ,

Exposing the Oracle Alert Log to SQL

26/03/2012 Leave a comment

I’ve been spending some time working in Apex recently, building a small app to draw together the monitoring of application and infrastructure components into a single easy-to-visualise tool. As part of that, I wanted to be able to read and report on the alert log. Traditionally, that would have meant creating an external table to point to the alert log and reading it that way, with lots of string manipulation and regular expressions to try to pull out useful bits of information. However, Oracle 11G had made that a lot easier. Step forward X$DBGALERTEXT. This is the decoded version of the XML version of the Alert log, and as such provides lots of lovely columns to filter by, rather than a single line of text to decode. Particularly useful (for me) is the MESSAGE_LEVEL. Is this line of text informational (16), or critical (1), or something in between? Of course, each “normal” line of text is still available in the MESSAGE_TEXT column.

SQL> desc x$dbgalertext;
 NameĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā   Type
 ------------------------------ --------------------------------------------------------
 ADDRĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  RAW(4)
 INDXĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 INST_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 ORIGINATING_TIMESTAMPĀ Ā Ā Ā Ā Ā Ā Ā Ā  TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMPĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā  TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 COMPONENT_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 HOST_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 HOST_ADDRESSĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(46)
 MESSAGE_TYPEĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 MESSAGE_LEVELĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 MESSAGE_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 MESSAGE_GROUPĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 CLIENT_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 MODULE_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 PROCESS_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(32)
 THREAD_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 USER_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 INSTANCE_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 DETAILED_LOCATIONĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(160)
 PROBLEM_KEYĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(64)
 UPSTREAM_COMP_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(100)
 DOWNSTREAM_COMP_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(100)
 EXECUTION_CONTEXT_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCEĀ Ā Ā Ā  NUMBER
 ERROR_INSTANCE_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 ERROR_INSTANCE_SEQUENCEĀ Ā Ā Ā Ā Ā Ā  NUMBER
 VERSIONĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 MESSAGE_TEXTĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(2048)
 MESSAGE_ARGUMENTSĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTESĀ Ā Ā Ā Ā Ā Ā  VARCHAR2(128)
 SUPPLEMENTAL_DETAILSĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā  VARCHAR2(128)
 PARTITIONĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER
 RECORD_IDĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  NUMBER

Very handy. Just add your own view, synonym and permissions to read the view, and you’re away…

create view v_$alert_log as select * from x$dbgalertext;
create public synonym v$alert_log for sys.v_$alert_log;
grant select on v$alert_log to whomever...

Ā  1* select message_text from v$alert_log where ...;

MESSAGE_TEXT
-----------------------------------------------------------------
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side pfile /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora
System parameters with non-default values:
Ā  processesĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = 200
Ā  sessionsĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = 322
Ā  sga_max_sizeĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = 2G
Ā  pre_page_sgaĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = TRUE
Ā  nls_languageĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = "ENGLISH"
Ā  nls_territoryĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = "UNITED KINGDOM"
Ā  filesystemio_optionsĀ Ā Ā Ā  = "SetAll"
Ā  sga_targetĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = 2G
Ā  control_filesĀ Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā  = "/u02/oradata/orcl/control01.ctl"
.
[snip]
.
Ā  aq_tm_processesĀ Ā Ā Ā Ā Ā Ā Ā Ā  = 1
Ā  diagnostic_destĀ Ā Ā Ā Ā Ā Ā Ā Ā  = "/u20/apps/oracle"
PMON started with pid=2, OS id=2492
PSP0 started with pid=3, OS id=2494
VKTM started with pid=4, OS id=2512 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=5, OS id=2520
DIAG started with pid=6, OS id=2522
...etc...

Filed under Administration, Management, Programming Tagged with , , , , ,

SYSTIMESTAMP INTERVAL Processing

12/12/2011 4 Comments

…or how to calculate dates in Oracle.

This is just a quick post to try to encourage the use of the INTERVAL function when adjusting (sys)timestamps (or dates). I thought this would be better expressed through a quick script with comments to show how using the traditional Oracle method of calculating fractions of a day can cause problems and make you have to think more than necessary.

There are 2 main issues with using the traditional Oracle method of calculating date/time changes. Firstly, it’s strange. You have to calculate fractions of a day. 1 second is 1/86400, 1 minute is 1/1440 [maybe 1/(24*60) expresses it better]. Secondly, with timestamps it causes an implicit conversion to a date type, with all of the unintended consequences that implicit conversion carries.

Lets run the script and see what happens:

Script Output


> -- Lets just set the date format explicitly before we start.
> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
session SET altered.


> -- Lets start simple and add a day.
> select systimestamp, systimestamp + 1 from dual


SYSTIMESTAMP  SYSTIMESTAMP+1            
------------- ------------------------- 
09-DEC-11 18.39.53.026000000 +00:00 10-DEC-2011 18:39:53  


> -- Hang on, doing this the traditional Oracle way has implicitly cast
> -- the timestamp into a sysdate format. We need to convert it back.
> -- Lets try that again, but add 3 hours instead
> select systimestamp, to_timestamp(
>        systimestamp + (3/24),
>       'DD-MON-YYYY HH24:MI:SS' ) from dual


SYSTIMESTAMP  TO_TIMESTAMP(SYSTIMESTAMP+(3/24),'DD-MON-YYYYHH24:MI:SS') 
------------- ------------------------- 
09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.39.53.000000000 


> -- So we have an implicit conversion, and we've lost the timestamp precision.
> -- Now lets add 3 hours, 5 minutes 10.5 seconds.
>select systimestamp, to_timestamp(
>       systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60),
>      'DD-MON-YYYY HH24:MI:SS') from dual 


 SYSTIMESTAMP  TO_TIMESTAMP(SYSTIMESTAMP+(3/24)+(5/(24*60))+(10.5/24/60/60),'DD-MON-YYYYHH24:MI:SS') 
------------- ------------------------- 
09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.45.04.000000000 


> -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate.
> -- It was rounded up. You might also have noticed that I have used some
> -- different date processing to calculate fractions of a day.
> -- There are many different way to calculate time in Oracle:
> -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc,
> -- It's all tricky to understand and standards vary from company to company,
> -- if the company actually has a standard.
> -- There is a better way, using the INTERVAL function. It goes like this:
> -- Lets start simple and add a day.


> select systimestamp, systimestamp + INTERVAL '1' day from dual


SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'1'DAY 
------------- --------------------------- 
09-DEC-11 18.39.53.042000000 +00:00 10-DEC-11 18.39.53.042000000 +00:00 


> -- So far so good. And we haven't lost the data type either!
> -- No implicit conversion to break our code in the future.
> -- Now lets try to add 3 hours


> select systimestamp, systimestamp + INTERVAL '3' hour from dual


SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'3'HOUR 
------------- ---------------------------- 
09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.39.53.042000000 +00:00 


> -- Note the indicator is always singular.
> -- Now lets add 3 hours, 5 minutes 10.5 seconds


> select systimestamp, 
>        systimestamp + INTERVAL '3' hour
>                     + INTERVAL '5' minute
>                     + INTERVAL '10.5' second
>        from dual 


SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'3'HOUR+INTERVAL'5'MINUTE+INTERVAL'10.5'SECOND
------------- ------------------------------------------------------------------- 
09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.45.03.542000000 +00:00                                 


> -- Spot on - and the precision is correct too
> -- My mam/mum/mom* could read it and work out what was going on.
> -- But she is an OCA** (* delete as applicable) (**not really)
> -- There you go. Get your company to use this nomenclature and you're home free.
> -- No more strange time-base calculations or implicit conversion errors.
> -- It also works with DATE types too.




Original Script
set echo on 


-- Lets just set the date format explicitly before we start.
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

-- Lets start simple and add a day.
select systimestamp, systimestamp + 1 from dual;

-- Hang on, doing this the traditional Oracle way has implicitly cast
-- the timestamp into a sysdate format. We need to convert it back.
-- Lets try that again, but add 3 hours instead
select systimestamp, to_timestamp(
       systimestamp + (3/24),
                                   'DD-MON-YYYY HH24:MI:SS' ) from dual; 

-- So we have an implicit conversion, and we've lost the timestamp precision.

-- Now lets add 3 hours, 5 minutes 10.5 seconds.
select systimestamp, to_timestamp(
       systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60),
                                   'DD-MON-YYYY HH24:MI:SS') from dual;

-- And we completely lost the .5 in the 10.5 as we've converted to a sysdate.
-- It was rounded up. You might also have noticed that I have used some
-- different date processing to calculate fractions of a day.
-- There are many different way to calculate time in Oracle:
-- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc,
-- It's all tricky to understand and standards vary from company to company,
-- if the company actually has a standard.

-- There is a better way, using the INTERVAL function. It goes like this:
-- Lets start simple and add a day.
select systimestamp, systimestamp + INTERVAL '1' day from dual; 

-- So far so good. And we haven't lost the data type either!
-- No implicit conversion to break our code in the future.
-- Now let's try to add 3 hours
select systimestamp, systimestamp + INTERVAL '3' hour from dual;

-- Note the indicator is always singular.
-- Now lets add 3 hours, 5 minutes 10.5 seconds
select systimestamp, systimestamp + INTERVAL '3' hour
                                  + INTERVAL '5' minute
                                  + INTERVAL '10.5' second
                     from dual;

-- Spot on - and the precision is correct too
-- My mam/mum/mom* could read it and work out what was going on.
-- But she is an OCA** (* delete as applicable) (**not really)
-- There you go. Get your company to use this nomenclature and you're home free.
-- No more strange time-base calculations or implicit conversion errors.
-- It also works with DATE types too.

Filed under Management, Problem Solving, Programming Tagged with , , , , ,

Industry Experience

01/12/2011 14 Comments

I don’t get it. Why do so many jobs and contracts seem to insist upon having experience in a particular industry when, in the overwhelming majority of cases, the specific industry in which we work has no bearing upon the nature of our work.

I have worked across many industries, but each time I talk to a recruitment agent I get similar questions: “Have you worked in X industry?”, “I won’t put you forward for Y unless you have worked for Z”.

It’s the wrong question. Have I worked in Media? Investment Banking? Accountancy? Property? Logistics? It doesn’t matter. No, really. It doesn’t. I have worked in all of those industries and a few more besides, and the nature of the industry was largely irrelevant. A friend recently suggested that you need Investment Banking experience so you understand the inordinateĀ bureaucracy and dreadful boredom that come with working for an Investment Bank. A little unkind, but I know where he’s coming from.

What is relevant is the type and nature of systems with which you are working. Are they mission critical? Zero downtime? Very High Transaction rate? Enormous Data Warehouses? Hundreds or Thousands of databases? These questions have relevance. A high transaction rate OLTP in a Bank is very similar to a high transaction rate OLTP Web Retailer.Ā  The challenge with these systems is a different to that of an enormous data warehouse, but it’s still fundamentally an RDBMS. Data is data is data. We don’t need industry experience – it doesn’t help us in the same way as it helps Business Analysts or Project Managers or even Developers.

The recruitment problem for DBA’s is that recruiters don’t know the difference between OLTP and Data Warehousing; a large proportion simply keyword match (the great ones don’t! – and there are genuinely great recruiters out there, in small numbers) so you need to ensure you have all of the relevant keywords on your CV – I have even been asked to amend my CV to put Word and Excel on there! WTF? Unfortunately you also need to be careful, otherwise you’re probably getting job adverts sent through for Cobol Programmers, Websphere Guru’s and all manner of support and helpdesk staff. I removed IBM Assembler Programmer from my CV about 10 years ago, although I suspect there are not too many jobs left for that skill set now.

Filed under Management, Recruitment Tagged with , ,

UKOUG Management & Infrastructure SIG – New Date

13/09/2011 Leave a comment

To blatantly steal this post from Martin Widlake, as I’m Deputy Chairman of the SIG, and I’m also presenting:

I ought to just mention that the UKOUG Management and Infrastructure SIG has moved from Tuesday September 20th to Tuesday September 27th (so two weeks from today). It had to be moved as we had a bit of a problem with the room booking. It will be in the usual venue of the Oracle City Office in London and is, of course, free to members of the UK Oracle User Group. {If you are not a member, you can come along for a fee ā€“ but if you are interested in coming along to see what a UKOUG Special Interest Group meeting is all about, send me a mail}.

So, if you fancy some free information about:

  • Getting the best out of your intel hardware (and BIOS in general) {Steve Shaw from Intel}
  • The latest on Oracle GRID and OEM {both presentations by customers not Oracle, one by Niall Litchfield and one by ā€˜Morrisonsā€™,though Oracle supported us very well by finding one of the customers!)}
  • AĀ presentation and discussion on Outsourcing by Piet de Visser
  • Ā A consideration of how deep into the technology real-world DBAs need to go to solve issues (Martin Widlake and myself)
  • An Oracle support update

Well, register for the event and Iā€™ll see you in two weeks!

Filed under Management Tagged with , , , ,

A little rant about DBA’s

05/05/2011 17 Comments

Well, a while ago I was doing some interviews for a client for a Production support DBA. This was for a short term contract to look after a few systems while the incumbent was off doing more interesting project work. The thing I discovered was the absolutely dire level of knowledge displayed by the interviewees about Oracle. Things that I regard as fundamental to the understanding of how Oracle works were simply unanswered.

Q: What’s the difference between and instance and a database?
Q: What does “nested loop” mean in an execution plan?
Q: Name the memory areas within an Oracle instance.

Several candidates answered these either very badly, or not at all. Not at all? Any you say you’ve been working with Oracle since Oracle 7 and you can’t answer these questions? You have been working with Oracle for 10 years and you can’t name the PGA? or ANY components within the SGA?

If you are reading this blog, I suspect that you know the answers to the above questions. You’re the sort of person who spends a little of their own time doing research. I don’t think the questions are demanding (or are they? please tell me they are not.) Who actually hires these people? Are these chancers and charletans deceiving everyone, or just deceiving themselves.

And they were all OCP certified. How? And how little does that certification mean in reality. A tick on a CV / resume to get it past the box tickers in HR and agents.

You know, I’m not angry, I’m just disappointed in the lack of professional standards that you sometimes come across in our industry.

(Note: this post has been delayed to protect the guilty)

Filed under Management