Search

OakieTags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Oakies Blog Aggregator

EM13c- BI Publisher Reports

How much do you know about the big push to BI Publisher reports from Information Publisher reporting in Enterprise Manager 13c?  Be honest now, Pete Sharman is watching…. </p />
</p></div></div>

    	  	<div class=

Column Groups

Patrick Jolliffe alerted the Oracle-L list to a problem that appears when you combine fixed length character columns (i.e. char() or nchar())  with column group statistics. The underlying cause of the problem is the “blank padding” semantics that Oracle uses by default to compare varchar2 with char, so I’ll start with a little demo of that. First some sample data:


rem     Script:         col_group_char_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2016

execute dbms_random.seed(0)

create table t1
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        cast(chr(trunc(dbms_random.value(1,6))+64) as char(1))  c1,
        cast(chr(trunc(dbms_random.value(1,6))+64) as char(2))  c2,
        cast('X' as varchar2(2))                                v2
from
        generator       v1
where
        rownum <= 5 * 5 * 10
;

insert into t1(c1, c2, v2)
select  'X', 'X', 'X'
from    t1
;

update t1 set v2 = c2;
commit;


The little demos I’m going to report here don’t use all the data in this table – there are several other tests in the script that I won’t be reporting – so I’ll just point out that there are 500 rows in the table, half of them have ‘X’ in all three columns, and half of them have a uniform distribution of the letters ‘A’ to ‘E’ in every column.

  • Column c1 is declared as char(1) – so it will hold the data exactly as it was inserted by the script.
  • Column c2 is declared as char(2) – so even though the script apparently inserts a character string of length 1, this will be padded with a space to two characters before being stored.

Now we can create some stats – in particular a frequency histogram on the c2 column – and check the cardinality estimates for a couple of queries:

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          => 'T1',
                method_opt       => 'for all columns size 254'
        );
end;
/

set autotrace traceonly explain

prompt  ==================
prompt  c2 without padding
prompt  ==================

select  *
from    t1
where   c2 = 'X'
;

prompt  ================
prompt  c2 with padding
prompt  ================

select  *
from    t1
where   c2 = 'X '
;

set autotrace off

The first query compares c2 with the single character ‘X’, the second compares it with the two-character string ‘X ‘. But since the comparison is with a char(2) column the optimizer pads the first constant with spaces, and both queries end up predicting the same cardinality:


==================
c2 without padding
==================

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   250 |  2000 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   250 |  2000 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C2"='X')

================
c2 with padding
================

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   250 |  2000 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   250 |  2000 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C2"='X ')


Note that both queries predict the 250 rows where (we know) c2 = ‘X ‘; even though the predicate sections suggest the queries are looking for different data sets. This IS the expected behaviour.

Now let’s make things more complex – we’ll add the predicate “and c1 = ‘X'” to both queries but we’ll create a column group with histogram on (c1, c2) before checking the plans. Again we expect both versions of the new query to predict the same volume of data and (in fact) to produce a perfect prediction because we have so few rows and so few distinct combinations that we should get a perfect frequency histogram:


begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1 for columns (c1, c2) size 254'
        );
end;
/

prompt  ========================
prompt  (c1, c2) without padding
prompt  ========================

select  *
from    t1
where   c1 = 'X' and c2 = 'X'
;

prompt  =====================
prompt  (c1, c2) with padding
prompt  =====================

select  *
from    t1
where   c1 = 'X' and c2 = 'X '
;

And here are the execution plans:

========================
(c1, c2) without padding
========================

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    16 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    16 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C1"='X' AND "C2"='X')

=====================
(c1, c2) with padding
=====================

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   250 |  2000 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   250 |  2000 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C1"='X' AND "C2"='X ')


If we run the query where the literal is padded with spaces to the correct length (2nd query) then the prediction is correct. But if we haven’t padded the literal the prediction is wrong; the estimate is the one the optimizer would have used for “value not found in histogram”.

I think what’s happening is that the optimizer doesn’t “remember” that the literal is being compared with a char() when making the call to sys_op_combined_hash() that it uses for calculating column group stats so it doesn’t pad the column with spaces before calling the function and, as a consequence, the hashed value isn’t the one it should be using.

I’ve run this test on 11.2.0.4 and 12.1.0.2 – the effects are the same on both versions.

Bottom Line:

Be careful about how you use char() data types in your code, and be especially careful if you think you’re going to be creating column group stats involving char() columns – and then remember that 12c may generate column group stats automatically for you. If you use char() columns you will have to ensure that predicates using literal values should have those values padded to the correct number of spaces if you want to have the best possible chance of getting the correct execution plans.

 

Wednesday Philosophy – A Significant Day (but only to me)

Today is a significant day. Well, to me it is – to the rest of you it’s just a Wednesday in the latter half of April, in the mid 20-10’s. Because we count in 10s (probably due to the number of flexible pointy bits on our front limbs, but that is a much debated argument) we have “magic” numbers of 10, 100, 1000 and multiples thereof. As geeks we also have 2,4,8,16,32 etc. And as nerds (but nerds who appreciate certain literature) we have 42. But today is not significant to me for any of those magic numbers.

Today I have been classed as an adult for twice as long as I was classed a child. 2/3rds of my life ago I hit 16 (which means I hit 48 today) and I was legally responsible for my own crimes, allowed to have sex as I saw fit & get married (which suggests those 2 options were open to me at that point – but if you were a lady and met me back then, neither was likely!) And I was allowed to smoke cigarettes – though the age limit for that has since changed to 18 in the UK. And drink in a pub – so long as someone else bought the booze and I was having a meal.

I could also leave home, get a job, draw benefits or join a group that was legally allowed to shoot at people, or in turn be shot at (armed forces – and yes, I know they do a lot more than that). But, best of all, I could have ridden a moped, a lawn tractor (oh yes, yes, yes!) or flown a glider.

In reality, many of the above still needed parental consent and you truly become an adult in the UK at 18 (so I could write almost the same stuff as this in 6 years’ time too), but back then it felt like you were stepping out of shorts and into long trousers. Except for girls. They tended to step out of skirts and into shorter skirts, if memory serves. (If anyone thinks I’m being sexist, when I was 16 the girls were half a decade more mature than most of us boys and they *did* all start raising their hem lines). And I still wear short trousers when I can get away with it.

At age 16 I also chose what subjects to study for my “A” levels, the exams we do in the UK which help decide what college courses we can go for. I chose all sciences (biology, chemistry and physics) and threw in maths (not “math” mind you – though I’ve never been able to decide which contraction is more silly; we don’t do “Econ” or “Econs” ,”chem” or “Chemy”). I did the physics just so I did not have to do this waste-of-time subject called “general studies”, that no one could tell me was of any use for anything but seemed almost mandatory. No, I never did find out if “gens” ever helped anyone get a job, career, college course or anything. Anyway, it turns out it was a wise move as I was found to be useless at maths at “A” level but pretty good at physics. Who knew? All I knew was I was going to be a surgeon or a scientist. Or maybe a coroner, I quite fancied being a coroner. Well, that worked out as planned, eh? I’ve never put my hands on a living brain, never extracted a dead brain and never tried to work out how a brain works. I’ve just created a few small brain-replacement tools to allow people to use their brains for more interesting stuff.

A key thing about 16 for me was that most of the people who were not academic or decided they would rather try and earn an income rather than sit in school rooms anymore left school at that age, and that included a large swathe of the floor-knuckle-scraping thugs who had made the last couple of years at school such a deep, deep joy for me. A few of the goons stuck around as there was very little work around back then (thank you Margaret) but the worst of them went off to… oh, I don’t know what they did, but as I did not see them generally around I think a lot of them ended up in prison or in factories where they were kept out of society’s way for 8 or 10 hours a day or something happened to them to stop them being arseholes. For me, 16 was when I started to actually enjoy life more.

I’ve changed a lot since I was 16 and of course the world around me has too. The career I’ve ended up having is nothing like I expected I would back then – and has in fact been, to a large extent, using stuff that did not even exist back then. Computers were around, but they were not common. Relational databases were more theoretical than practical and as for the internet & smart phones, you had to look at Sci Fi to see anything like that. Maybe it is a good thing I never planned a career given how much things have changed. I wonder if we should be teaching today’s 16 year olds to not even think about a career but more think of how they can make the most of whatever comes along. ‘cos it’s all gong to change.

I wonder what the next 1/3rd will bring for me and what I’ll be up to when it has become 1/4th.

nVision Performance Tuning: Coalescing Tree Leaves

I have blogged recently about the effectiveness of the Tree Performance options in improving the performance of nVision reports.
However, a consequence of the Tree Performance Access Method suppress join; use literal values is that the resulting SQL in nVision will have a criteria for every leaf on each of the selected nodes on that tree.

nVision Tree Performance Options|

There will be an equality condition for each single value leaf. I normally set Selector Options to Ranges of values (BETWEEN), so I get a between condition for each ranged leaf. Behind the scenes, Oracle rewrites between as a pair of inequalities, so there is no difference, but the SQL generated by nVision is slightly shorter.
The following is typical of nVision SQL with these performance options set.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT A.ACCOUNT,SUM(A.POSTED_TOTAL_AMT) 
FROM
PS_LEDGER A WHERE A.LEDGER='ACTUALS' AND A.FISCAL_YEAR=2015 AND
A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND A.CURRENCY_CD='GBP' AND
A.STATISTICS_CODE=' ' AND (A.BUSINESS_UNIT=

) AND (
A.DEPTID='C500' OR A.DEPTID='C512' OR A.DEPTID='C117' OR A.DEPTID='C157' OR
A.DEPTID='C340' OR A.DEPTID='C457' OR A.DEPTID='C510' OR A.DEPTID='A758' OR
A.DEPTID='8220' OR A.DEPTID='A704' OR A.DEPTID='A121' OR A.DEPTID='A110' OR
A.DEPTID BETWEEN 'A153' AND 'A154' OR A.DEPTID BETWEEN 'A151' AND 'A152' OR
A.DEPTID='A724' OR A.DEPTID BETWEEN 'A131' AND 'A133' OR A.DEPTID='A733' OR
A.DEPTID='A217' OR A.DEPTID='A437' OR A.DEPTID='A130' OR A.DEPTID='A134' OR
A.DEPTID='A703' OR A.DEPTID='A714' OR A.DEPTID='A218' OR A.DEPTID='A226' OR
A.DEPTID BETWEEN 'A135' AND 'A138'

A consequence of all the criteria is that Oracle can take a long time to parse the SQL statement. It may only be a few seconds in the case of a single SQL statement, but an nVision report book can consist of thousands of SQL statements.
To produce the following performance profile, I enabled Oracle SQL trace for a report book and profiled the trace. SQL Parse accounted for nearly 8% of the total runtime of this report book, so it can be significant, and can vary widely.

#CCCC99; border-bottom: #555555; border-left: #EEEEBB; border-right: #555555; border-style: solid; border-top: #EEEEBB; border-width: 1.0pt; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">Event Name
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: solid #EEEEBB 1.0pt; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">% Time
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: solid #EEEEBB 1.0pt; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">Seconds
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: solid #EEEEBB 1.0pt; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.08%;" width="14%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">Calls
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: solid #EEEEBB 1.0pt; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 36.28%;" width="36%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">- Time per Call -
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: none; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-top-alt: solid #EEEEBB .75pt; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">Avg
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: none; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-top-alt: solid #EEEEBB .75pt; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">Min
#CCCC99; border-bottom: solid #555555 1.0pt; border-left: none; border-right: solid #555555 1.0pt; border-top: none; mso-border-bottom-alt: #555555; mso-border-left-alt: #EEEEBB; mso-border-left-alt: solid #EEEEBB .75pt; mso-border-right-alt: #555555; mso-border-style-alt: solid; mso-border-top-alt: #EEEEBB; mso-border-top-alt: solid #EEEEBB .75pt; mso-border-width-alt: .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.04%;" width="14%">
#336699; font-family: "tahoma" , sans-serif; font-size: 7.0pt;">Max
#FFFFEE; border-top: none; border: solid #CCCCAA 1.0pt; mso-border-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#333333; font-family: "courier new"; font-size: 7.0pt;">#aa3300;">FETCH calls [CPU]
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#333333; font-family: "courier new"; font-size: 7.0pt;">48.2%
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#333333; font-family: "courier new"; font-size: 7.0pt;">3,699.8440s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.08%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">16,068
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.2303s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.0000s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.04%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">178.0640s
#FFFFEE; border-top: none; border: solid #CCCCAA 1.0pt; mso-border-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#333333; font-family: "courier new"; font-size: 7.0pt;">#aa3300;">db file sequential read
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#333333; font-family: "courier new"; font-size: 7.0pt;">22.5%
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#333333; font-family: "courier new"; font-size: 7.0pt;">1,728.2101s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.08%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">4,413,352
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.0004s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.0002s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.04%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.1294s
#FFFFEE; border-top: none; border: solid #CCCCAA 1.0pt; mso-border-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#333333; font-family: "courier new"; font-size: 7.0pt;">#aa3300;">SQL*Net message from client [idle]
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#333333; font-family: "courier new"; font-size: 7.0pt;">8.0%
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#333333; font-family: "courier new"; font-size: 7.0pt;">617.7042s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.08%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">926
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.6671s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.0002s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.04%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">61.3147s
#FFFFEE; border-top: none; border: solid #CCCCAA 1.0pt; mso-border-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#333333; font-family: "courier new"; font-size: 7.0pt;">#aa3300;">PARSE calls [CPU]
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#333333; font-family: "courier new"; font-size: 7.0pt;">7.9%
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#333333; font-family: "courier new"; font-size: 7.0pt;">605.9340s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.08%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">5,383
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.1126s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#333333; font-family: "courier new"; font-size: 7.0pt;">0.0000s
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.04%;" width="14%">
#333333; font-family: "courier new"; font-size: 7.0pt;">11.0500s
#FFFFEE; border-top: none; border: solid #CCCCAA 1.0pt; mso-border-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#333333; font-family: "courier new"; font-size: 7.0pt;">…
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.08%;" width="14%">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 11.12%;" width="11%">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 14.04%;" width="14%">
#FFFFEE; border-top: none; border: solid #CCCCAA 1.0pt; mso-border-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 22.96%;" width="22%">
#333333; font-family: "courier new"; font-size: 7.0pt;">Total#333333; font-family: "courier new"; font-size: 7.0pt;">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 9.64%;" width="9%">
#333333; font-family: "courier new"; font-size: 7.0pt;">100.0%#333333; font-family: "courier new"; font-size: 7.0pt;">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 17.04%;" width="17%">
#333333; font-family: "courier new"; font-size: 7.0pt;">7,681.4428s#333333; font-family: "courier new"; font-size: 7.0pt;">
#FFFFEE; border-bottom: solid #CCCCAA 1.0pt; border-left: none; border-right: solid #CCCCAA 1.0pt; border-top: none; mso-border-alt: solid #CCCCAA .75pt; mso-border-left-alt: solid #CCCCAA .75pt; mso-border-top-alt: solid #CCCCAA .75pt; padding: 1.5pt 1.5pt 1.5pt 1.5pt; width: 50.36%;" width="50%">

Reducing the number of criteria in the SQL will reduce the parse time, but that is determined by the way the tree leaves are defined.
The leafcoal.sql script seeks to address this by repeatedly merging two consecutive leaves on the same tree node into a single ranged leaf where possible. It performs two checks before merging adjacent leaves on the same tree node:

  • There is not an intermediate value on the detail field defined in the tree structure record. So if the detail field was DEPT_TBL.DEPTID, the script checks that there are no values of DEPTID on PS_DEPT_TBL that are not currently selected by existing leaves that would be included in the merged leaf.
  • There is not another leaf on another node on the tree that would intersect with the merged leaf.

Instructions

leafcoal.sql was written as an anonymous PL/SQL block, so there is nothing to install. It should be run in SQL*Plus connected as the PeopleSoft owner ID (usually SYSADM). It is expected that there are some adjustments to the script that the user may need to make. As delivered, it runs in a test mode that does not update the database but reports on what it would do. Change k_testmode to FALSE to make it update the database.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">k_testmode     CONSTANT BOOLEAN := FALSE; /*set this false to perform update*/
The level of output emitted depends on the variable l_debug_variable
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">l_debug_level  INTEGER := 4;
  • 1. end of processing message 
  • 2. start of processing for tree 
  • 3. number of leaves in tree and number of leaves coalesced 
  • 4. details of leaves being compressed 
  • 5. start and end of each procedure 
  • 6. parameters passed to functions 
  • 7. number of rows updated/deleted during coalesce 
  • 8. dynamic SQL statement 
The script reports on the work it has done. It does not commit its updates. That is left for the user to either commit or rollback.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">.(3)Processing SHARE, ,XXX_ACCOUNT,151201                                       
.(4)634 nodes, 2636 leaves
.(4)1358 leaves coalesced (52%)

(1)Commit changes or rollback

The query at the end of the script determines which trees will be processed and may need to be changed as required. For example, you might choose to coalesce the leaves on

  • specific trees,
  • most recent effective dated trees,
  • trees with literal values performance option

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">  FOR i IN (
SELECT DISTINCT d.setid, d.setcntrlvalue, d.tree_name, d.effdt
FROM pstreedefn d
, pstreestrct s
, psrecfielddb f
WHERE d.tree_strct_id = s.tree_strct_id
AND s.node_fieldname = 'TREE_NODE'
-- AND d.TREE_ACC_METHOD = 'L' --literal values
AND s.dtl_recname = f.recname
AND s.dtl_fieldname = f.fieldname
-- AND tree_name = 'XXX_ACCOUNT'
) LOOP

Conclusion 

The number of leaves coalesced depends entirely on how the trees have been built. At one customer it has produced a reduction of over 50%, at another it was only 10%. The reduction in the number of leaves does produce a corresponding reduction in time spent on SQL parse time during nVision reports.

VirtualBox 5.0.18

VirtualBox 5.0.18 has been released.

The downloads and changelog are in the usual places.

So far I’ve only installed it on Windows 7, but I’ll no doubt be doing an install on OS X El Crapitan and Oracle Linux tonight. </p />
</p></div>

    	  	<div class=

Subtle variations in optimizer stats

Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care.

Let’s look at the following example


SQL> create table T (
  2    x varchar2(20) , y varchar2(100));

Table created.

SQL> insert into T
  2  select 'x' , rpad('z',100) from all_objects;

94117 rows created.

SQL> insert into T
  2  select 'X' , rpad('z',100) from all_objects;

94117 rows created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.

SQL> create index TX on T ( upper(x) ) ;

Index created.

So you can see that in reality, ALL of the rows have a single value for UPPER(X), namely “X”. So let’s look at an execution plan.


SQL> exec dbms_stats.gather_index_stats(user,'TX')

PL/SQL procedure successfully completed.

SQL> explain plan for select * from T where upper(x) = :b1
  2  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1501193905

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |  1882 |   189K|   354   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |  1882 |   189K|   354   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TX   |   753 |       |   342   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("X")=:B1)

14 rows selected.

That seems incorrect. Bind peeking etc aside (because we are just using standard EXPLAIN), we would expect to not use an index when the there is only 1 value across the whole table.

Let’s try that experiment again.


SQL> drop table T;

Table dropped.

SQL>
SQL> create table T (
  2    x varchar2(20) , y varchar2(100));

Table created.

SQL>
SQL> insert into T
  2  select 'x' , rpad('z',100) from all_objects;

94117 rows created.

SQL>
SQL> insert into T
  2  select 'X' , rpad('z',100) from all_objects;

94117 rows created.

SQL>
SQL> create index TX on T ( upper(x) ) ;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_index_stats(user,'TX')

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for select * from T where upper(x) = :b1
  2  /

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   188K|    18M|   786   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   188K|    18M|   786   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(UPPER("X")=:B1)

13 rows selected.

And this time it has got the estimate just right. So what did we do different ? If you look at the two scripts, in the first one, we did

“gather table stats, create index, gather index stats”

and in the second one, we did

“create index, gather table stats, gather index stats”

The subtle difference there is what statistics were calculated at column level on the hidden column (which got created as part of our function based index).

In the second (“correct”) example, statistics were gathered on ALL of the columns, including the hidden one


SQL> select COLUMN_NAME,NUM_DISTINCT,AVG_COL_LEN, num_nulls, density
  2  from user_tab_cols
  3  where  table_name = 'T'
  4  order by COLUMN_ID;

COLUMN_NAME                    NUM_DISTINCT AVG_COL_LEN  NUM_NULLS    DENSITY
------------------------------ ------------ ----------- ---------- ----------
X                                         2           2          0         .5
Y                                         1         101          0          1
SYS_NC00003$                              1           2          0          1

whereas in the first example, because the hidden column did not yet exist when we gathered the table stats, it got missed.


SQL> select COLUMN_NAME,NUM_DISTINCT,AVG_COL_LEN, num_nulls, density
  2  from user_tab_cols
  3  where  table_name = 'T'
  4  order by COLUMN_ID;

COLUMN_NAME                    NUM_DISTINCT AVG_COL_LEN  NUM_NULLS    DENSITY
------------------------------ ------------ ----------- ---------- ----------
X                                         2           2          0         .5
Y                                         1         101          0          1
SYS_NC00003$

So if you see execution plans that don’t seem right…double check your statistics – you might be missing some.

Quick tip on Function Based Indexes

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.


SQL> create table blah ( x varchar2(30));

Table created.

SQL> create index blah_ix on blah ( upper(x));

Index created.

SQL> select column_name from user_ind_columns
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_NAME
------------------------------
SYS_NC00002$

Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.


SQL> select column_expression from user_ind_expressions
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
UPPER("X")

Easy peasy

Video : Flashback Query

Today’s video is a quick demo of flashback query.

If you prefer to read articles, rather than watch videos, you might be interested in these articles.

The cameo for this video comes courtesy of Dina Blaschczok, a DBA based in South Africa and a friend of the family. When the wife goes down to SA, Dina takes care of her and occasionally introduces her to big cats. </p />
</p></div>

    	  	<div class=

A little known RI clause

Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int primary key, p int references PAR ( p ) );

Table created.

SQL>
SQL> insert into CHD
  2  select rownum, mod(rownum,4)+1
  3  from dual connect by level <= 12;

12 rows created.

SQL>
SQL> delete from PAR
  2  where p = 2;
delete from PAR
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.SYS_C0018225) violated - child record found

And most people are also aware that you can head to the other extreme, and wipe out the children when you wipe out the parent


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int primary key, p int references PAR ( p ) ON DELETE CASCADE);

Table created.

SQL>
SQL> insert into CHD
  2  select rownum, mod(rownum,4)+1
  3  from dual connect by level <= 12;

12 rows created.

SQL>
SQL> delete from PAR
  2  where p = 2;

1 row deleted.

SQL> select * from CHD;

         C          P
---------- ----------
         2          3
         3          4
         4          1
         6          3
         7          4
         8          1
        10          3
        11          4
        12          1

9 rows selected.

But don’t forget, there is also a third option that you can implement declaratively


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int primary key, p int references PAR ( p ) ON DELETE SET NULL);

Table created.

SQL>
SQL> insert into CHD
  2  select rownum, mod(rownum,4)+1
  3  from dual connect by level <= 12;

12 rows created.

SQL>
SQL> delete from PAR
  2  where p = 2;

1 row deleted.

SQL> select * from CHD;

         C          P
---------- ----------
         1
         2          3
         3          4
         4          1
         5
         6          3
         7          4
         8          1
         9
        10          3
        11          4
        12          1

12 rows selected.

SQL>
SQL>

I don’t think I’ve seen this used in a Production instance. If you’ve seen it, please add a comment – I’d be curious to see a use case.

Spot the Oracle Faces

My wife has been going through old photo’s from her mother today, trying to find a picture of Uncle Stan. In the box of photographs was also a magazine – an Oracle magazine!

Oracle Magazine award winners 2003!

Oracle Magazine award winners 2003!

As you can see from the front cover, it is the Oracle Magazine from the end of 2003, with the Oracle Magazine 2003 Award Winners on it. The tiny photograph on the bottom right is me:-). Sue’s mum, Di, seemed to be more interested in what I did at work than my own mum (but then Di was like that).

So how many of the people on the magazine do you recognize? If you’ve met them, you should be able to identify a few – even though we are all at least 13 years older than those pictures. If you click on the image, you will get a larger version which might help. It is SO long ago that I don’t think there is an electronic issue of the magazine online, not even in the Oracle Magazine archive. But then, who wants to know about enterprise grid computing in 10g now? I could not even find another copy of the front cover in my 90-second search.

I’m not really one for looking to the past but I do enjoy the odd reminisce. It’s good to see what happened in the past (be it good or bad) and where it has left you in the present. There seems to have been quite a bit of this going on around me this week. Some people on the OakTable have been sharing pictures with the group of a similar vintage (so way before my time), I was talking about how we got into presenting and the Oracle community with Brendan Tierney over the last couple of days and at home we have been looking back even further. The “Uncle Stan” I mentioned was a POW in WW2 in Singapore and he painted the Changi Murals when he was there – painted to help keep up the spirits of those in the infirmary at the time. We will visit The Changi Museum to see the replicas and read the history when we are out there in 2 weeks and, if we are lucky, we might even get to see the originals.

Getting the 2003 Oracle magazine “Beta tester of the year” award was my first real step into the Oracle community. I’d only just started presenting (I think once at UKOUG Tech conference & one SIG, a couple of Oracle Life Science conferences plus being the “friendly face of the end user” at an Open World in 2002 talk…maybe 2003. I never even got on the agenda for that one). I got the award more as the representative of the work done by people in my team, ie their work, as opposed to mine – and for a long while I felt a bit guilty about it. But as a good friend pointed out, it was a team that I had built, doing work I guided and, between myself and Shanthi Sivadasan, we had it all running well and we were doing stuff that no one else would own up to doing (and that HP offered to help us with – and we ended up helping them!).

So back to the magazine cover. Who can you spot? Some I am pretty sure are no longer anything to do with the Oracle scene, but some still are…:

Arup Nanda, DBA of the year (oTY)
Tim Sharick, CTO oTY
Ronan Miles, IT Manager oTY
Peter Charles Smith, PL/SQL developer oTY
TonyJambu, consultant oTY
Bob Magan, developer oTY
Jeroen Baltussen, web services developer oTY
Harvinder Singh Saluja, Jdeveloper oTY
Arno Van Der Klok Java developer oTY
Matt Rhoades, BI developer oTY
Arnaud Bontemps, Portal developer oTY
Tom Copeland, Open Source developer oTY
{how many “X develop of the years”? How many “DBA-types” of the year? Oh yes, 10g was supposed to be the death of the DBA – again}
Jamie Kinney & Grant McAlister, Linux Innovators oTY
Hoosh Asfar, Early Adopter oTY
Mogens Norgaard {who?}, Educator oTY
Tom Kyte {another obscure one}, Oracle Book Author oTY
Jason Hunter, Oracle Magazine Author oTY
Me, Beta Tester oTY
Andrew Clarke, OTN contributor oTY
Rick Hamilton, Architect oTY