jump to navigation

Friday Philosophy – Work Inside Life August 24, 2012

Posted by mwidlake in Friday Philosophy, off-topic, Private Life.
Tags:
add a comment

I know, the usual phrase is “Life Outside Work” but I like to think that, no matter how much we may like our jobs, our overall life is the key thing.

I was prompted to write today’s Friday Philosophy when I was reading Neil Chandler’s blog a few days ago and saw that he had posted about his up-coming {and now in progress} Banger Rally. He and some mates are doing something very cool in their spare time, taking part in a rally from, basically, Calais in France to Naples in Italy, in a “banger”. This is a UK (and wider?) term for an old, tired, worn out car. The most they could spend on the car was £250 and it has to go over some pretty extreme mountain passes. I presume it also has to carry the set of 4 people involved too, so not a lot of weight there then, Neil :-). You can follow their progress at this blog. I was in a pub in Leeds with Neil when he was considering one car for this rally.

I can’t claim that I do anything as striking as Neil’s current jaunt when not working, but I do like to spend some time doing things that are nothing to do with IT at all. For me, this tends to be physical tasks like cutting down trees with my chainsaw. Or building structures in the garden with wood (OK, half-building them – I constructed the below platform and another out into our pond maybe 18 months ago, both still have no balustrade around them and there is a 45cm gap between the bank and the pond platform – NEXT weekend I’ll maybe finish one. Maybe.)


Another garden task recently was building our wood-fired, mud-constructed pizza oven. My wife and I went on a 1-day course to learn how to make a mud-based pizza oven and then spent, ohhhh, about 10 days over 8 weeks building one! We dug a big hole in the garden to get some clean clay (we live in an area that is on top of clay), stole some straw from the horse that lives at the bottom of our garden (long story), bought some sand (because nicking it from beaches is both illegal and bad form) and mixed up our first batch of clay-sand-straw. Add in some old bricks and we got started. It took about 30 lots of mixture, a few alterations to the ratios as we went, but we ended up with this monster.

If you are wondering what the white stuff on the oven is, some ants decided to build their nest in the oven, between the layers I guess (you build the inner oven first and then add several extra mud layers to give insulation and a larger thermal mass, so that the oven stays warm longer).

Like anything, if we did it a second time we would probably make a much better job of it as we learnt so much from the first attempt. One of them was to invite friends with young children over to see us during the build. Kids love the idea of treading the mud mix and you can get a suprising amount of work out of a single ten-year-old (and very insistent I get the age right) German girl if they are allowed to get very muddy.

I found the whole process very relaxing (but very tiring) as you do not need to use too much brain power, slapping the mud mixture on the outside is deeply cathartic and you end up with something useful. Well, slightly useful.


Having built it we then had to learn how to fire it. As an ex-boy-scout I thought this would be easy. Getting the fire going is not too hard, you need to keep it burning well for about an hour to heat up the oven and, one big tip, don’t use any damp wood or wood that “spits” (like willow). Having a burning log explode and shower your almost-ready pizza with charcoal embers can lead to angry garden scenes and considerable bad language.

We can just about get a 9″ pizza in there. The pizza in this shot is actually about 6″. The oven was designed to take a 12″ pizza but, errm, I forgot to allow enough space to one side to push the fire into as well.

Another major thing to keep in mind? That arch! You can’t put anything in the oven that is larger than that hole. More importantly, you can’t get anything out that won’t easily come back through that hole. Sticking your arm inside to jiggle things about is also tricky as it gets up to around 300C! {for US readers – about a millon F}

As you can see, the results are anything but professional! And, compared to nipping down to the shops to buy a pizza and just slamming it into the kitchen oven, it takes a lot, lot longer. But you can drink beer or enjoy a bottle of win and sit in the garden whilst getting the mud oven fired and hot. Last weekend we had friends over and the ladies made the pizzas, I cooked them and the other gentlemen helped us work through the beer collection and ate. As for taste? Fantastic.

My wife is far more artistic than I and she makes decorative cakes, sews and now even makes hats when not auditing IT systems (Have you ever been involved in an IT audit and the person knows how to make lists and check them – but can’t plug in a PC? Well, Sue was once a DBA and also a Unix Sys Admin, so when she audits you, she finds what you are hiding!). As such, maybe as an industry we should encourage her hat-making

So what, if anything is the point of this Friday Philosophy, other than to show off my pizza oven and my wife’s hats?

Well, I see this outside work activity as important in the workplace as well, especially if you are having to manage people. Firstly, it is important, I feel, for us all to do something we really enjoy to relax and re-energize (and this can be “computers” for IT people, but as I get older I notice more of my friends who were utterly technology-focused in their 20’s are now moving more towards non-IT hobbies).

Secondly, what people do in their spare time can tell you a lot about the person and what keeps them happy. If they have hobbies that are artistic and their IT job is very regimented, it could indicate they would be happier with some more less rigid task in the mix, say some design work. If they have NO outside hobbies, you are probably working the poor dears a little too hard!

Thirdly, and I have seen this for real, you may have a person or a team that is not performing well in the office – but it turns out a lot of them do interesting and challenging things at home. Someone who can motivate themselves to be a part-time fireman or has the dedication to train each day for a sport has skills and energy. Why, as their manager, am I not seeing much of these traits in the office? Because they are not happy and/or motivated. So I have an issue to sort out. I don’t manage people at present, but I still find it interesting what people do and achieve outside the office as it can indicate that they have talents and skills not being seen in the workplace.

I wonder how I can introduce my chain-saw skills into performance tuning?

Exclusion of Unioned SQL in Views – Followup August 20, 2012

Posted by mwidlake in database design, performance, SQL.
Tags: , ,
add a comment

Last week I put up a post about how Oracle can filter out sections of a union view..

Within the comments I put up another example where the CBO did not filter out all but one of the Union views despite my replicating the exact WHERE clause of one of the unioned statements. Jonathan Lewis posted a followup to say “Oracle can be very touchy about how it plays this game” and made a prediction of how the CBO would handle a slightly different scenario.

This was the extra scenario and I include brief details on creating the unioned view too. NB all on Oracle 11.2.0.2. {non-Exadata :-) }

  1  create table mdw_source
  2  tablespace users
  3  as select * from dba_objects

  1  create or replace view mdw_v1 as
  2  select
  3    '1'              src_type
  4   ,ms.OWNER ,ms.OBJECT_NAME,ms.SUBOBJECT_NAME ,ms.CREATED
  5  from mdw_source ms
  6  where ms.object_name like 'A%'
  7  union all
  8  select
  9    '2'              src_type
 10   ,ms.OWNER ,ms.OBJECT_NAME,ms.SUBOBJECT_NAME ,ms.CREATED
 11  from mdw_source ms
 12  where ms.object_name like 'B%'
 13  UNION ALL
 14  select
 15    '3'              src_type
 16   ,ms.OWNER ,ms.OBJECT_NAME,ms.SUBOBJECT_NAME ,ms.CREATED
 17  from mdw_source ms
 18  where ms.object_name like 'C%'

1 select count(created)
2 from mdw_v1 mv
3 where mv.object_name like ‘C%’

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 75 | 551 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 75 | | |
| 2 | VIEW | MDW_V1 | 824 | 61800 | 551 (1)| 00:00:07 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| MDW_SOURCE | 1 | 34 | 184 (1)| 00:00:03 |
|* 5 | TABLE ACCESS FULL| MDW_SOURCE | 2 | 68 | 184 (1)| 00:00:03 |
|* 6 | TABLE ACCESS FULL| MDW_SOURCE | 821 | 27914 | 184 (1)| 00:00:03 |
———————————————————————————–

Predicate Information (identified by operation id):
—————————————————
4 – filter(“MS”.”OBJECT_NAME” LIKE ‘A%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’)
5 – filter(“MS”.”OBJECT_NAME” LIKE ‘B%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’)
6 – filter(“MS”.”OBJECT_NAME” LIKE ‘C%’)

Statistics
———————————————————-
8 recursive calls
0 db block gets
2417 consistent gets 

So, as you can see, despite me providing the WHERE clause in my final select as “where mv.object_name like ‘C%’” and the WHERE clauses of the unioned view are all similar and one is “ms.object_name like ‘C%’” the CBO fails to realise it can exclude all but one of the unioned SQL statements – despite the mutally exclusive filter predicates:

4 – filter(“MS”.”OBJECT_NAME” LIKE ‘A%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’)
5 – filter(“MS”.”OBJECT_NAME” LIKE ‘B%’ AND “MS”.”OBJECT_NAME” LIKE ‘C%’)

I have to confess, in light of recent experience I would have wondered if these filter predicates would prevent the other two SQL statements being fired at execution time but the number of consistent gets confirms that it is not – 2417 compared to 805 or so for when the query exclusion occurs. You can check back to the prior post to verify that.

So, let’s do the tests Jonathan suggested. He suggested altering the view to use equality predicates so I have recreated the view as such:

TEST_DB> create or replace view mdw_v1 as
  2  select
  3    '1'              src_type
  4    ,ms.OWNER ,ms.OBJECT_NAME ,ms.SUBOBJECT_NAME ,ms.CREATED
  5  from mdw_source ms
  6  where substr(ms.object_name,1,1) ='A'
  7  union all
  8  select
  9    '2'              src_type
 10   ,ms.OWNER ,ms.OBJECT_NAME ,ms.SUBOBJECT_NAME ,ms.CREATED
 11  from mdw_source ms
 12  where substr(ms.object_name,1,1) ='B'
 13  UNION ALL
 14  select
 15    '3'              src_type
 16   ,ms.OWNER ,ms.OBJECT_NAME ,ms.SUBOBJECT_NAME ,ms.CREATED
 17  from mdw_source ms
 18  where substr(ms.object_name,1,1) ='C'

View created.

TEST_DB> set autotrace on
TEST_DB> --
TEST_DB> -- below will do a select of a non-indexed column across the view
TEST_DB> select count(created) from mdw_v1

COUNT(CREATED)
--------------
          1288

Execution Plan
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |     9 |   552   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE      |            |     1 |     9 |            |          |
|   2 |   VIEW               | MDW_V1     |  1677 | 15093 |   552   (1)| 00:00:07 |
|   3 |    UNION-ALL         |            |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
|*  6 |     TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='A')
   5 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='B')
   6 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='C')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       2417  consistent gets


TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via the src_type
TEST_DB> select count(created)
  2  from mdw_v1
  3  where src_type='3'

COUNT(CREATED)
--------------
           129

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    12 |   184   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE       |            |     1 |    12 |            |          |
|   2 |   VIEW                | MDW_V1     |   561 |  6732 |   184   (1)| 00:00:03 |
|   3 |    UNION-ALL          |            |       |       |            |          |
|*  4 |     FILTER            |            |       |       |            |          |
|*  5 |      TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
|*  6 |     FILTER            |            |       |       |            |          |
|*  7 |      TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
|*  8 |     TABLE ACCESS FULL | MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(NULL IS NOT NULL)
   5 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='A')
   6 - filter(NULL IS NOT NULL)
   7 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='B')
   8 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='C')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        807  consistent gets


TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via matching the where clause
TEST_DB> select count(created)
  2  from mdw_v1
  3  where substr(object_name,1,1) ='C'

COUNT(CREATED)
--------------
           129

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    75 |   184   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE       |            |     1 |    75 |            |          |
|   2 |   VIEW                | MDW_V1     |   561 | 42075 |   184   (1)| 00:00:03 |
|   3 |    UNION-ALL          |            |       |       |            |          |
|*  4 |     FILTER            |            |       |       |            |          |
|*  5 |      TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
|*  6 |     FILTER            |            |       |       |            |          |
|*  7 |      TABLE ACCESS FULL| MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
|*  8 |     TABLE ACCESS FULL | MDW_SOURCE |   559 | 19006 |   184   (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(NULL IS NOT NULL)
   5 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='A')
   6 - filter(NULL IS NOT NULL)
   7 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='B')
   8 - filter(SUBSTR("MS"."OBJECT_NAME",1,1)='C')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        807  consistent gets


TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via NOT matching the where clause
TEST_DB> -- but maybe logical exclusion can be managed
TEST_DB> select count(created)
  2  from mdw_v1
  3  where object_name ='C'

COUNT(CREATED)
--------------
             0

Execution Plan
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |    75 |   551   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE      |            |     1 |    75 |            |          |
|   2 |   VIEW               | MDW_V1     |     3 |   225 |   551   (1)| 00:00:07 |
|   3 |    UNION-ALL         |            |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| MDW_SOURCE |     1 |    34 |   184   (1)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| MDW_SOURCE |     1 |    34 |   184   (1)| 00:00:03 |
|*  6 |     TABLE ACCESS FULL| MDW_SOURCE |     1 |    34 |   184   (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("MS"."OBJECT_NAME"='C' AND
              SUBSTR("MS"."OBJECT_NAME",1,1)='A')
   5 - filter("MS"."OBJECT_NAME"='C' AND
              SUBSTR("MS"."OBJECT_NAME",1,1)='B')
   6 - filter("MS"."OBJECT_NAME"='C' AND
              SUBSTR("MS"."OBJECT_NAME",1,1)='C')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       2417  consistent gets

TEST_DB> -- below will do a select of a non-indexed column
TEST_DB> -- specific to one of the Union's in the view via NOT matching the where clause
TEST_DB> -- but logically it is the same.
TEST_DB> select count(created)
  2  from mdw_v1
  3  where object_name like 'C%'

COUNT(CREATED)
--------------
           129

Execution Plan
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |    75 |   551   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE      |            |     1 |    75 |            |          |
|   2 |   VIEW               | MDW_V1     |    24 |  1800 |   551   (1)| 00:00:07 |
|   3 |    UNION-ALL         |            |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| MDW_SOURCE |     8 |   272 |   184   (1)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| MDW_SOURCE |     8 |   272 |   184   (1)| 00:00:03 |
|*  6 |     TABLE ACCESS FULL| MDW_SOURCE |     8 |   272 |   184   (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("MS"."OBJECT_NAME" LIKE 'C%' AND
              SUBSTR("MS"."OBJECT_NAME",1,1)='A')
   5 - filter("MS"."OBJECT_NAME" LIKE 'C%' AND
              SUBSTR("MS"."OBJECT_NAME",1,1)='B')
   6 - filter("MS"."OBJECT_NAME" LIKE 'C%' AND
              SUBSTR("MS"."OBJECT_NAME",1,1)='C')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       2417  consistent gets

Above, I create the view, do a quick select count(*) to cause a full scan of all three section in the union view, then do selects using various WHERE predicates to see what sections of the unioned SQl statements are excluded.

The results are very intersting. The filtering on sub_type still works fine, but now the WHERE predicate “where substr(object_name,1,1) =’C’” allows the CBO to exclude sections of the union, the “Null is not null” filters appear and the consistent gets is 807. The WHERE predicate “where object_name =’C'” which at first glance you might think to be logically the same but is not (we selected object_names where only the first character is checked but the whole object_name is selected) acts does not do any exclusion and I thought it might. There are no OBJECT_NAMES of ‘C’ but logically as a human you can see that such an object name could not be found by the first two unioned SQL statements in any case.

The last test is most interesting. The WHERE clause of the SQL select over the view is “where object_name like ‘C%’” and is logically the same as the one WHERE clause in the unioned view “where substr(object_name,1,1) =’C’“. So no exclusion occurs and again we see 2417 consistent gets.

It would seem the CBO Pixies either have not coded for that or, as Jonathan says Oracle can be “touchy” about this feature. Bottom line, if you are expecting this sort of unioned view SQL exclusion to occur – TEST!

Friday Philosophy – New Game: Phone Zombies! (You Too can Play) August 17, 2012

Posted by mwidlake in Friday Philosophy, humour, off-topic, Perceptions.
Tags: , ,
8 comments

I’m spending a lot more time in Central London at the moment due to current work commitments. A few weeks ago I was having a quiet stroll through the streets and had what I can only describe as an odd moment:

I looked around and found I was being converged upon by 5 or 6 people walking slowly and aimlessly towards me – all from different angles, all only vaguely aware of their surroundings, all looking like they were making straight for me. I instantly thought of one of the scenes from “Shaun of the Dead” {A cracking film, go hire it tonight}.

They were all on their smart phones of course, intent on the little glowing screens of whatever it was they could not drag their eyes from – despite them also trying to navigate a busy city landscape. As some of you know, I have a bit of a “hate-mild dislike-grudging acceptance” relationships with Smart Phones.

I was so struck by this scene that I nearly did not move in time, but finally I did step to one side as I watched them do this quite wonderful, little, shuffling-dance around each other. I think only one of them actually looked up properly, the others all did that micro-glance; frown; direction shift; re-engage-with-screen procedure that is becoming so common. As a species we must be somehow pre-designed to cope with this as none of them actually bumped into each other – but it took several micro-glance manoeuvres for some of them to make it through.

This has resulted in a new game I can’t stop playing as I make my way through London:

Phone Zombies – How many people can I see at any time who are effectively lobotomised by their personal electronic device?

I have a few rules:

  • I has to be an electronic device – phones, smart phones, crackberries, tablets, electronic books etc.
  • Real books and papers do not count.
  • They must be upright (so no sitting).
  • If they are moving they count.
  • If they are stopped in the middle of the path they count.
  • If they have put themselves in a doorway or some other sensible place they do not count.
  • Unless, even though they have done that, they are still e.g. blocking ingress and egress from the doorway.
  • A bonus point if they micro-glance manoeuvre.
  • 5 bonus points if contact is made with another person in the time I am watching.
  • 10 bonus points for contact with something inanimate {only once to date}.
  • 20 points if they go “uuurrrgggghhh” and have blood on them. {no one has got 20 bonus points yet, but I live in hope}

I think my best so far is about 14, but that is because two phone-zombies both walked into each other. Classic.

Exclusion of Unioned SQL in Views? August 16, 2012

Posted by mwidlake in performance, SQL.
Tags: , , ,
13 comments

Question – you have a view definition of the following format:

select 1 as d_type, col_a,col_b,col_c
from TAB_X, TAB_Y, TAB_Z
where {your predicates}
UNION
select 2 as d_type, col_a,col_b,col_c
from TAB_P, TAB_Q, TAB_R
where {your predicates}
UNION
select 3 as d_type, col_a,col_b,col_c
from TAB_X X, TAB_Y, TAB_Z
where {your predicates}

You now select from the view and your code includes a filter predicate such as:

“WHERE D_TYPE = 1″

What will the CBO do? Is it intelligent enough to do what a human would do and exclude the parts of the query that could not possibly return data you want? Ie exclude the SQL statements where d_type is set to 2 and 3. Or is that funcitonality not built into the CBO? After all, it is only software and someone would have to decide that there could be benefit of looking into set of unioned SQL selects to see if any could be excluded and then code that into the CBO.

After all, the CBO is not smart enough to realise it could easily satisfy “select min(col_a), max(col_a) from tab x” via two fast lookups on a supporting index. It scans the index.

So – what is your guess?

I asked 3 people. Not a massive straw poll but I got one vote for it will not exclude, one for it will and one smart answer of “I would expect it to exclude- but as you asked the question I am thinking it does not”.

Let’s do the test.

DB_TEST> @demo_union_view_2
DB_TEST> drop table mdw_source purge;
Table dropped.

  1  create table mdw_source
  2  tablespace users
  3  as select * from dba_objects
  4  /
Table created.

  1  create index mdso_idx on mdw_source(owner,object_name)
  2  tablespace users
  3  /
Index created.

DB_TEST> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'MDW_SOURCE',method_opt=>'for all columns size 1')
PL/SQL procedure successfully completed.

  1  select table_name,num_rows from dba_tab_statistics
  2  where owner=USER and table_name='MDW_SOURCE';

TABLE_NAME      NUM_ROWS
--------------- ----------
MDW_SOURCE      55895

  1  create or replace view mdw_v1 as
  2  select
  3    '1'              src_type
  4   ,ms.OWNER
  5   ,ms.OBJECT_NAME
  6   ,ms.SUBOBJECT_NAME
  7   ,ms.CREATED
  8  from mdw_source ms
  9  where ms.object_name like 'A%'
 10  union all
 11  select
 12    '2'              src_type
 13   ,ms.OWNER
 14   ,ms.OBJECT_NAME
 15   ,ms.SUBOBJECT_NAME
 16   ,ms.CREATED
 17  from mdw_source ms
 18  where ms.object_name like 'B%'
 19  UNION ALL
 20  select
 21    '3'              src_type
 22   ,ms.OWNER
 23   ,ms.OBJECT_NAME
 24   ,ms.SUBOBJECT_NAME
 25   ,ms.CREATED
 26  from mdw_source ms
 27  where ms.object_name like 'C%'
 28  /
View created.

So, a simple table is created from DBA_OBJECTS and a view over the top of the table consisting on 3 simple selects unioned together, with a static value (1, 2 or 3) generated for each of the 3 parts as SRC_TYPE

Now let’s run some tests:

DB_TEST> set autotrace on
DB_TEST> --
DB_TEST> -- below will do a select of a non-indexed column across the view
DB_TEST> select count(created) from mdw_v1
  2  /

COUNT(CREATED)
--------------
          1284

Execution Plan
----------------------------------------------------------
Plan hash value: 4259489107
-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |     9 |   551   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE      |            |     1 |     9 |            |          |
|   2 |   VIEW               | MDW_V1     |  2463 | 22167 |   551   (1)| 00:00:07 |
|   3 |    UNION-ALL         |            |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
|*  6 |     TABLE ACCESS FULL| MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("MS"."OBJECT_NAME" LIKE 'A%')
   5 - filter("MS"."OBJECT_NAME" LIKE 'B%')
   6 - filter("MS"."OBJECT_NAME" LIKE 'C%')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       2417  consistent gets


DB_TEST> -- below will do a select of a non-indexed column across the view
DB_TEST> -- limiting it to "one" of the types
DB_TEST> select count(created)
  2  from mdw_v1
  3  where src_type='3'
  4  /

COUNT(CREATED)
--------------
           129


Execution Plan
----------------------------------------------------------
Plan hash value: 1188403912
------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    12 |   184   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE       |            |     1 |    12 |            |          |
|   2 |   VIEW                | MDW_V1     |   823 |  9876 |   184   (1)| 00:00:03 |
|   3 |    UNION-ALL          |            |       |       |            |          |
|*  4 |     FILTER            |            |       |       |            |          |
|*  5 |      TABLE ACCESS FULL| MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
|*  6 |     FILTER            |            |       |       |            |          |
|*  7 |      TABLE ACCESS FULL| MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
|*  8 |     TABLE ACCESS FULL | MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(NULL IS NOT NULL)
   5 - filter("MS"."OBJECT_NAME" LIKE 'A%')
   6 - filter(NULL IS NOT NULL)
   7 - filter("MS"."OBJECT_NAME" LIKE 'B%')
   8 - filter("MS"."OBJECT_NAME" LIKE 'C%')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        807  consistent gets


DB_TEST> -- below will do a select of an indexed column across the view
DB_TEST> select count(owner) from mdw_v1
  2  /

COUNT(OWNER)
------------
        1284


Execution Plan
----------------------------------------------------------
Plan hash value: 4219520050
------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |    17 |   221   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE         |          |     1 |    17 |            |          |
|   2 |   VIEW                  | MDW_V1   |  2463 | 41871 |   221   (1)| 00:00:03 |
|   3 |    UNION-ALL            |          |       |       |            |          |
|*  4 |     INDEX FAST FULL SCAN| MDSO_IDX |   821 | 26272 |    74   (2)| 00:00:01 |
|*  5 |     INDEX FAST FULL SCAN| MDSO_IDX |   821 | 26272 |    74   (2)| 00:00:01 |
|*  6 |     INDEX FAST FULL SCAN| MDSO_IDX |   821 | 26272 |    74   (2)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("MS"."OBJECT_NAME" LIKE 'A%')
   5 - filter("MS"."OBJECT_NAME" LIKE 'B%')
   6 - filter("MS"."OBJECT_NAME" LIKE 'C%')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        989  consistent gets


DB_TEST> --
DB_TEST> -- below will do a select of an indexed column across the view
DB_TEST> -- limiting it to "one" of the types
DB_TEST> select count(owner)
  2  from mdw_v1
  3  where src_type='3'
  4  /

COUNT(OWNER)
------------
         129


Execution Plan
----------------------------------------------------------
Plan hash value: 815942527
-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     1 |    20 |    74   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE          |          |     1 |    20 |            |          |
|   2 |   VIEW                   | MDW_V1   |   823 | 16460 |    74   (2)| 00:00:01 |
|   3 |    UNION-ALL             |          |       |       |            |          |
|*  4 |     FILTER               |          |       |       |            |          |
|*  5 |      INDEX FAST FULL SCAN| MDSO_IDX |   821 | 26272 |    74   (2)| 00:00:01 |
|*  6 |     FILTER               |          |       |       |            |          |
|*  7 |      INDEX FAST FULL SCAN| MDSO_IDX |   821 | 26272 |    74   (2)| 00:00:01 |
|*  8 |     INDEX FAST FULL SCAN | MDSO_IDX |   821 | 26272 |    74   (2)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(NULL IS NOT NULL)
   5 - filter("MS"."OBJECT_NAME" LIKE 'A%')
   6 - filter(NULL IS NOT NULL)
   7 - filter("MS"."OBJECT_NAME" LIKE 'B%')
   8 - filter("MS"."OBJECT_NAME" LIKE 'C%')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        331  consistent gets


DB_TEST> -- below will select against the undelying object we are interested in
DB_TEST> select count(created)
  2  from mdw_source ms
  3  where ms.object_name like 'C%'
  4  /

COUNT(CREATED)
--------------
           129


Execution Plan
----------------------------------------------------------
Plan hash value: 152094671

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    34 |   184   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |            |     1 |    34 |            |          |
|*  2 |   TABLE ACCESS FULL| MDW_SOURCE |   821 | 27914 |   184   (1)| 00:00:03 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MS"."OBJECT_NAME" LIKE 'C%')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        805  consistent gets

Look at the plans at the start.

When my query does not include SRC_TYPE,ie the first SQL statement, the plan is simple – it scans the table three times, estimates 821 rows per scan and a cost of 184 for each step. When I include the SRC_TYPE = ‘3’ in the next step we see that the plan still includes all three full table scans, each still estimated at a cost of 184.
When you provide a predicate that identifies the one sql statement that you want to run, it still runs all three. I tried a few further tests and could not force the exclusion of the “unwanted” queries from the plan.

I showed my friend above (the one who suggested it would not work as I had asked the question) the results and we agreed that it was a little unfortunate that the Oracle Optimizer Pixies had not coded in the ability to exclude unwanted Unioned statements, but we could understand it.

However, for those of you thinking “you are missing something” – you are right. {And, to be fair to my friend, my example was not quite so simplified as what I have posted here}.

I decided to quantify the level of the impact and so shifted my concentration to the buffer gets. Look at the first and second statements again. The cost of the total statement drops, from 551 to 184. The actual consistent gets resulting from executing the SQL is dropping from 2417 to 807. They are both dropping dramatically to approximately 1/3.

Now check out the filter predicates at step 4 and 6 – “NULL IS NOT NULL”. That will always be false. Everything “after” that statement in the plan will not be executed. The step in the plan is there and, as a step, has the same cost – but it is is not being executed.

The pattern is repeated when the simple SQL statement is on an indexed column and the index is being used (the next two statements) {and honesty forces me to admit I have included more output than I need for this example, as I wanted to confuse the issue slightly and maybe encourage you to not read the plans too carefully}.

So yes, the CBO IS excluding the individual unioned SQL statements based on the stated SRC_TYPE and the fixed value stated in each part of the union.

I’m quite impressed by that. Clever Oracle Optimizer Pixies.

To tie this back to reality, I initially looked at this as I am trying to tune some code that hides all the complexity of the application under one massive unioned uber-view over a set of 9 other complex, unioned views. The explain plan runs to close to 700 lines. I had utterly overlooked the filters and filter predicates in that – heck I missed it initially in my simple test :-) {which was more complex than in this post, in my defense}. Trying to work with a very slow statement based on nested, complex views that is used by pretty much the whole application is “fun”.

Also back in reality, the filter clause I see in my real code is along the lines of:

:B17 is null or is not null

This is as bind variable is being considerd – and that is actually one of the considerations back in this real world of the original issue. The level that the master query needs to run in is decided by this bind variable – otherwise the application developer would (should have?) just pointed at the sub-view they needed for that level. So the CBO does not know which chunk of the union’d code it can ignore until it sees that bind variable, so the plan shows what could be executed.

Of course, it could be argued that if you know the type at the time you query the database, rather than use the above union filtering trick to cut down your massively complex view to the part that is required, just query the sub view you need directly. A bit more code but a lot simpler system to look after.

It’s a nice SQL trick but I would keep it to simple examples!

Finaly, just for fun, here is the plan I’ve been trying to tune. As I said, close to 700 steps and only the highlighted grey section on the left is shown in more detail on the right.

Friday Philosophy – I Am An Exadata Expert August 10, 2012

Posted by mwidlake in Exadata, Friday Philosophy, Perceptions.
Tags: , , ,
5 comments

(Can I feel the angry fuming and dagger looks coming from certain quarters now?)

I am an Exadata Expert.

I must be! – I have logged onto an Exadata quarter rack and selected sysdate from Dual.

The pity is that, from some of the email threads and conversations I have had with people over the last 12 months, this is more real-world experience than some people I have heard of who are offering consultancy services. It’s also more experience than some people I have actually met, who have extolled their knowledge of Exadata – which is based solely on the presentations by Oracle sales people looking at the data sheets from 10,000 feet up and claiming it will solve world hunger.

Heck, hang the modesty – I am actually an Exadata Guru!

This must be true as I have presented on Exadata and it was a damned fine, technical presentation based on real-world experience and I have even debated, in public, the pros and cons of point releases of exadata. Touching base with reality once more, I did an intro talk “the first 5 things you need to know about Exadata” and the “debate” was asking Julian Dyke if he had considered the impact of serial direct IO on a performane issue he had seen and he had not only done so but looked into the issue far more than I – so he was able to correct me.

But joking aside –  I really am a true consulting demi-god when it comes to Exadata

I have years of experience across a wide range of Exadata platforms. That would be 0.5 years and I’ve worked intensively on just one system and am in a team now with some people who are proper experts. So a range of two. Yes, tongue is still firmly in cheek.

This situation always happens with the latest-greatest from Oracle (and obviously all other popular computing technologies). People feel the need to claim knowledge they do not have. Sometimes it is to try and get consultancy sales or employment, sometimes it is because they don’t want to be seen to be behind the times and sometimes it is because they are just deluded. The deluded have seen some presentations, a few blog posts and maybe even got the book and read the first few chapters and are honeslty convinced in their own minds that they now know enough to make effective use of the technology, teach {or, more usually, preach} others and so proclaim on it. {See Dunning Kruger effect, the certainty of idiots}. I’m certainly not arguing against going to presentations, reading blogs and books and learning, just don’t make the mistake of thinking theoretical, second-hand knowledge equates to expert.

With Exadata this situation is made worse as the kit is expensive and much of what makes it unusual cannot be replicated on a laptop, so you cannot as an individual set up a test system and play with it. Real world experince is required. This is growing but is still limited. So the bullshit to real skills quotient remains very, very high.

If you are looking for help or expertise with Exadata, how do you spot the people with real knowledge from the vocal but uninformed? Who do you turn to? {NB don’t call me – I’m busy for 6 months and I really am not an expert – as yet}. If your knowledge to date is based on sales presentations and tidbits from the net which may or may not be based on a depth of experience, it is going to be hard to spot. When I was still without real world experience I had an unfair advantage in that I saw email threads between my fellow OakTable members and of course some of those guys and gals really are experts. But I think I was still hoodwinked by the odd individual on the web or presenting and, I can tell you, though this background knowledge really helped – when I DID work on my first exadata system, I soon realised I did not understand a lot about the subtulties and not-so-subtulties of using a system where massively improved IO was available under key conditions. I had to put a lot of time and effort and testing to move from informed idoit to informed, partially experienced semi-idiot.

I know this issue of the non-expert proclaiming their skills really frustrates some people who do know their stuff for real and it is of course very annoying if you take someone’s advice (or even hire them) only to find their advice to be poor. Let’s face it, is is simple lying at best and potentially criminal mis-selling.

I guess the only way is for peopel needing help to seek the help of someone who has already proven themselves to be honest about their skills or can demonstrate a real-world level experience and success. I would suggest the real experts should do that most difficult task of pointing out the mistakes of the false prophets, but it is very tricky to do without looking like a smartarse or coming over as a big head or jealous.

I’ll finish on one thing. Last year I said how I thought maybe I should do more blog posts about things I did not know much about, and be honest about it and explore the process of learning. I did actualy draft out about 3 posts on such a topic but never pushed them out as I was way too busy to complete them… That and, being candid, I really did not want to look like an idiot. After all, this Oracle lark is what puts beer in my hand, hat fabric on my wife’s millinary worktop and food in my cat’s bowl. The topic was….? Correct, Exadata. Maybe I should dust them off and put them out for you all to laugh at.

Off Topic Rant – Olympics Athletics Annoyance August 3, 2012

Posted by mwidlake in humour, off-topic, rant.
Tags: ,
add a comment

I’m very much enjoying the current Olympics, the achievements and drama by so many competitors from all over the world in different events,  and the coverage provided by the BBC  – when they can keep narrative and vision together and they are not asking tired competitors who have just done their all daft questions like “how do you feel now” {I’d love some of them to reply “Knackered, just sod off OK!”}

However, a concern I have had for weeks seems to be panning out. For athletics I think we will see running. And more running. With running thrown in. Running, running, running. with a light scattering of everything else if there is UK interest. What vexes me is that most of this “running” is not even actual running!

I do not know what it is like in other countries but when an Athletics event is covered in the UK the broadcasters seem to regard anything that is not running as, well, Mhhh! There is a lot more going on, guys, and I would like to see a lot more of the throwing, chucking, jumping, swinging, vaulting and basically things other than running.  Don’t get me wrong, the races are thrilling and I am eager to watch them. But instead of showing the runners come out, showing you each one, watching them prepare, wait as they settle down, showing the race – then showing it you again. And agin in slow motion… Slow motion from the side. Slow motion from above. Last 30 meters in slow motion from the front… The start in slow motion… the whole race focused on the UK person… The competitors wandering about after…asking the person who came 6th if they enjoyed it and would they like to have done better….

All this for a heat! We get this for each heat for short races and meantime lots of other stuff is going on that is just as interesting as the actual race and way, way more interesting than the fluff around the race.

How about, cut 75 percent of that fluff and show us a good few minutes of the hammer? The long jump, the triple jump, the javelin, the pole vault. Not just the 2 or 3 favourites {and when no runners can be found by the camera), but you know, some of the other competitors and people achieving personal best or falling over.

I desperately hoped that with 20 plus channels they could use two on athletics. One on running and all the fluff around it and one on Everything Else that cuts to the running for the, you know, actual running bit along.. But it seems not, at least not yet. *sigh*.  I often wonder why the UK broadcasters call it athletics and not just Running.

I’m actually lucky enough to be going to the stadium this evening. I plan to really enjoy watch things as well as running.

Oh, can I tie this up to the world of Oracle? How about, Oracle tuning is like broadcasting Athletics. If you only concentrate on SQL tuning you are missing 75% of what you could achieve. So running is like SQL tuning. Sort of.

Follow

Get every new post delivered to your Inbox.

Join 163 other followers