How to *really* send a script to the background

Let’s check this small script

foo.sh


#!/bin/sh
echo foo.1:`date` | tee $HOME/tmp/foo.txt
sleep 3
echo foo.2:`date` | tee -a $HOME/tmp/foo.txt


$ $HOME/tmp/foo.sh
foo.1:Thu Nov 27 17:34:53 CET 2014
foo.2:Thu Nov 27 17:34:56 CET 2014

Very obvious, I write to the console, wait three seconds, then write to the console.

Ok, let’s take another script that would call this script in the background using &

bar.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh &
echo bar.2:`date`


$ $HOME/tmp/bar.sh
bar.1:Thu Nov 27 17:36:32 CET 2014
bar.2:Thu Nov 27 17:36:32 CET 2014
$ 
foo.1:Thu Nov 27 17:36:32 CET 2014
foo.2:Thu Nov 27 17:36:35 CET 2014

bar is printing the date, calling foo in the background, then printing the date, then it returns to you, and foo is still running.

BUT this is only in a relative background …

Let’s try this


$ time $HOME/tmp/bar.sh > /dev/null

real    0m0.01s
user    0m0.00s
sys     0m0.00s

So it takes no time to run bar you believe ?

Let’s try, for instance, over ssh (or cron or whatever)


$ time ssh localhost $HOME/tmp/bar.sh > /dev/null
real    0m3.81s
user    0m0.01s
sys     0m0.01s

running bar suddenly waits 3 seconds for foo to finish.

To be sure the script is sent to the farest background, you need to close the file descriptors, stdin, stdout, stderr

I rewrote it as

baz.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh <&- >&- 2>&- &
echo bar.2:`date`


$ time ssh localhost $HOME/tmp/baz.sh >/dev/null
real    0m0.44s
user    0m0.00s
sys     0m0.00s

Now the script baz is immediately finished and does not wait for foo to complete

The mess that is fast-refresh join-only Materialized Views

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only materialized view?
  2. Show me it working
  3. What’s it done to my commit time?
  4. How can this be avoided this with _mv_refresh_use_stats?
  5. Is it any different if I use MLOG stats?
  6. Why might I not want to mess with _mv_use_refresh_stats?


First, why am I looking at a fast refresh, on-commit join-only materialized view.

I have got two datasets which only produce a small resultset when they are joined.
But neither is, on their own, highly selective.

The model below might not be 100% representative of my real-world situation.
I’ve tried to sanitise and simplify but as a result I’ve then had to bump up the volumes a bit to make the point.

drop table t1;
drop table t2;

create table t1
as
select rownum oid
,      case when rownum <= 1000000 then 'Y' else 'N' end flag
,      rownum oid_t2
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t1_1 on t1 (oid);
create index i_t1_2 on t1(flag);
create index i_t1_3 on t1(oid_t2);

create table t2
as
select rownum oid
,      case when rownum >= 999901 then 'Y' else 'N' end flag
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t2_1 on t2 (oid);
create index i_t2_2 on t2(flag);

Currently, I can crunch my data in a few seconds:

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Gives

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:15.91 |     606K|    303K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:15.91 |     606K|    303K|   238M|  7667K|  292M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.54 |     303K|      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |      0 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:09.78 |     303K|    303K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------- 

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But I want to query these tables quite frequently and preferably I want this driving logic to be subsecond.
And there will have reasonable inserts and updates incoming at various times.

So, one of the better options is a join-only materialized view.


So, let’s get going:

create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv_t1_t2
refresh fast on commit
enable query rewrite
as
select t1.rowid   t1_rowid
,      t1.oid     t1_oid
,      t1.flag    t1_flag
,      t1.oid_t2  t1_oid_t2
,      t1.padding t1_padding
,      t2.rowid   t2_rowid
,      t2.oid     t2_oid
,      t2.flag    t2_flag
,      t2.padding t2_padding
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

create index i_mv_t1_t2_1 on mv_t1_t2(t1_rowid);
create index i_mv_t1_t2_2 on mv_t1_t2(t2_rowid);

Tangent alert!
Now, if I wasn’t going to go off on this tangent because the article will be long enough anyway but when running up the article, a number of questions about
“hy was this not being rewritten?” came up so, we might as well cover more bases.

So, if we run our SELECT now, what happens?

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:08.22 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:08.22 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.69 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

Bother!

We could go digging around but let’s try the easy way out – DBMS_MVIEW.EXPLAIN_REWRITE

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message
  measure_in_msg        VARCHAR2(30),   -- Measure in current message
  join_back_tbl         VARCHAR2(30),   -- Join back table in message
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used
  reerved2              VARCHAR2(10))   -- currently not used;

DECLARE
l_sql CLOB :=
q'{select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y'}';
BEGIN
 DBMS_MVIEW.EXPLAIN_REWRITE(l_sql,'MV_T1_T2','mysql');
END;
/

select message from rewrite_table;
MESSAGE
-------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled

Yep – easy one.
Let’s set query_rewrite_enabled to true (would be system level if this was not just a test/demo) and repeat.

alter session set query_rewrite_enabled = true;
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    101 |00:00:00.01 |      16 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |      1 |    101 |    101 |00:00:00.01 |      16 |
--------------------------------------------------------------------------------------------------

Great – I have my subsecond response time.

But what about updates to the data?

First of all, for completeness, let’s observe what happens when we make an uncommitted change.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.27 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.27 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.63 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.65 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

I’ve lost my usage of the MV.
This is expected.
Why?
DBMS_MVIEW.EXPLAIN_REWRITE tells us:

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01279: query rewrite not possible because DML operation occurred on a table referenced by materialized view MV_T1_T2

We can’t use the materialized view because it’s stale.
To use it we have to consider whether we want to run with query_rewrite_integrity set to stale_tolerated.
In this case, not.

If I continue to leave the update uncommitted and check another session, the it continues to use the MV.

But, another tangent!!
In that other session, if I set statistics_level to all, then it won’t use the MV and DBMS_MVIEW.EXPLAIN_REWRITE can’t tell me why.
I’m going to conveniently leave that investigation for another time but just show the evidence:

alter session set statistics_level = all;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.10 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.10 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.49 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.72 |     303K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But

alter session set statistics_level = typical;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |    101 |
----------------------------------------------------------   

Note
-----
   - Warning: basic plan statistics not available.....

So… anyway… we have our fast-refresh on commit materialized view.


What happens when we commit my previous change?

set timing on
update t1 set flag = 'Y' where oid = 1000001;

1 rows updated.
Elapsed: 00:00:00.029

commit

committed.
Elapsed: 00:00:02.098

Gadzooks! My COMMIT now takes two seconds!

At this point, if you read a lot of technical articles, you might get lucky and have that vague sense of nagging familiarity about having read about this at some point…

Alberto Dell’Era has an excellent set of articles which helps understand what is going on here and what we might do about it.

If we trace my session, we can find out what is contributing to this unpleasant two seconds.
I’m going to cheat, flush the shared pool, do the update and commit and check v$sql
Here is a summary of the significant contributing sql snippets.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
dt1mmbxbp6uk7            1                          1                 1.9356 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE(
5qah9xjyhapg1            0       828077931          1               0.010237 /* MV_REFRESH (DEL) */ DELETE FROM "E668983_DBA"."MV_T1_T2" SNA$ WHERE "T1_ROWID
4agmycb29dd1p            0                          1               0.001747 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000589 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000518 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmlty

A couple of interesting observations.
First is that we can see the XID$$ mechanism which Alberto mentions in his articles linked to above.

Second is that the INSERT is the major contributor to our commit time, followed by the DELETE.
We shall focus on these.

These are the prettified statements:

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM   ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
         FROM "DOM"."T1" "MAS$"
         WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                         FROM "DOM"."MLOG$_T1" "MAS$"
                         WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE  "JV$"."FLAG"='Y'
AND    "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y';

With plan:

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL         |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   2 |   NESTED LOOPS                   |            |      1 |        |      1 |00:00:01.92 |     303K|       |       |          |
|   3 |    NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|   4 |     VIEW                         |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|*  5 |      HASH JOIN RIGHT SEMI        |            |      1 |      1 |      1 |00:00:01.92 |     303K|   832K|   832K|  387K (0)|
|   6 |       TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T1         |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T1_2     |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN            | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------ 

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

   5 - access(ROWID=CHARTOROWID("MAS$"."M_ROW$$"))
   7 - access("MAS$"."XID$$"=:1)
   9 - access("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE
FROM   "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ *
                      FROM   (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"
                              WHERE "MAS$".XID$$ = :1 )MAS$)
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|*  2 |   HASH JOIN SEMI              |              |      1 |    101 |      1 |00:00:00.01 |       3 |   963K|   963K| 1252K (0)|
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------  

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

   2 - access("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

If you have read Alberto’s blogs then you will notice the problematic HASH JOIN RIGHT SEMI in the INSERT
And you will also be familiar with a couple of solutions which now present themselves.

It’s great that there are known workarounds to this problem but, as I hope to demonstrate, the fact that there are at least three separate codepaths through the fast refresh mechanism worries me particularly when we see that
at least one of these alternative code paths causes bugs with other variations on the MV refresh mechanism. What a mess!!


First solution to the problem is paramter _mv_refresh_use_stats.
Repeat update & commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

set timing on
update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.035

commit;
committed.
Elapsed: 00:00:00.174

That’s better.
What do our INSERT and DELETE statements look like now?
Note the different SQL IDs – these are completely different statements.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
av53npjd112vx            1       374625119          1               0.013106 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
5a6ugwh5v1j5x            0       466548951          1               0.009088 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
gpk46p11kbp3d            0      1735585849          1               0.001665 SELECT OLD_NEW$$, COUNT(*)  FROM "DOM"."MLOG$_T1"   WHERE SNAPTIME$$ > :1 AND SN
4agmycb29dd1p            0                          1               0.001615 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000538 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000515 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$     

For both the INSERT and the DELETE, we’ve lost the HASH_SJ hint and we’ve gained a CARDINALITY hint and a NO_SEMIJOIN.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
       FROM   "DOM"."T1" "MAS$"
       WHERE  ROWID IN (SELECT /*+ CARDINALITY(MAS$ 0)  NO_SEMIJOIN */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                        FROM   "DOM"."MLOG$_T1" "MAS$"
                        WHERE  "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y'
AND   "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y'

Giving plan:

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL           |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                     |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                    |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                           |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       VIEW                         | VW_NSO_1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   7 |        SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   8 |         TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |       TABLE ACCESS BY USER ROWID   | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 11 |     INDEX UNIQUE SCAN              | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 12 |    TABLE ACCESS BY INDEX ROWID     | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

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

   9 - access("MAS$"."XID$$"=:1)
  10 - filter("MAS$"."FLAG"='Y')
  11 - access("JV$"."OID_T2"="MAS$0"."OID")
  12 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  NO_SEMIJOIN  */ *
                      FROM   (SELECT  /*+ CARDINALITY(MAS$ 0) */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)

with plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                         | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   2 |   NESTED LOOPS                  |              |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                         | VW_NSO_1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |     SORT UNIQUE                 |              |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|*  7 |    INDEX RANGE SCAN             | I_MV_T1_T2_1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("MAS$"."XID$$"=:1)
   7 - access("T1_ROWID"="RID$")

There’s no doubt that that is faster for our specific circumstances.

What about the second workaround available?

Let’s reset “_mv_refresh_use_stats” and look at locking stats on the MV logs.

alter session set "_mv_refresh_use_stats" = false;

session SET altered.

begin
  dbms_stats.gather_table_stats(USER,'MLOG$_T1');
  dbms_stats.gather_table_stats(USER,'MLOG$_T2');
  dbms_stats.lock_table_stats(USER,'MLOG$_T1');
  dbms_stats.lock_table_stats(USER,'MLOG$_T2');
end;
/

anonymous block completed

select table_name, num_rows from user_tables where table_name in ('MLOG$_T1','MLOG$_T2');

TABLE_NAME                     NUM_ROWS
------------------------------ --------
MLOG$_T1                              0
MLOG$_T2                              0

What happens then?

update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.026

commit;

committed.
Elapsed: 00:00:00.169

Very similar to the effects of “mv_refresh_use_stats”, but exactly the same? No.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
69xq38c4ts0j2            1       190791364          1               0.013053 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
dzzb43k6h3vhs            0                          1               0.009172 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
4agmycb29dd1p            0                          1               0.000924 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1                0.00064 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000632 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$

We’ve got completely different SQL IDs again.

In both INSERT and DELETE we’ve lost any hint to do or avoid a semi-join and there’s no CARDINALITY hint.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "DOM"."T1" "MAS$"
       WHERE ROWID IN (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                       FROM "DOM"."MLOG$_T1" "MAS$"
                       WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y' AND "JV$"."OID_T2"="MAS$0"."OID" AND "MAS$0"."FLAG"='Y'
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL          |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                    |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                   |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                          |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                 |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  9 |       TABLE ACCESS BY USER ROWID  | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN             | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   8 - access("MAS$"."XID$$"=:1)
   9 - filter("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  */ *
                      FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                            FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |      13 |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |      13 |
|   2 |   NESTED LOOPS SEMI           |              |      1 |    101 |      1 |00:00:00.01 |       5 |
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |    101 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |    101 |      1 |    101 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

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

   4 - filter("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

So, to briefly summarise the above, we have at least three clear codepaths through the fast refresh mechanism which result in completely different internal SQL being generated.
Multiple code paths are added complexity when it comes to combining other features, upgrades, testing, etc

It’s then no great surprise when such multiple codepaths lead to other bugs.

So which approach are we meant to do?

Should we just take the default longer commit on the chin?

I’m not sure.

But I can come up with an illustration of why we should think twice about doing down the “_mv_refresh_use_stats” approach.
Firstly it’s an underscore parameter so we should really get Oracle Support approval before setting it (which means a fair bit of back and forth until you get someone who knows what you’re going on about)


Secondly, we can quickly find some bugs around the usage of this parameter with the newer COMMIT SCN (also covered in Alberto’s blogs above)

drop materialized view log on t1;
drop materialized view log on t2;
create materialized view log on t1 with rowid, commit scn;
create materialized view log on t2 with rowid, commit scn;

drop materialized view mv_t1_t2;
create materialized view mv_t1_t2...

Then go back to our update and commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated

commit;

SQL Error: ORA-12008: error in materialized view refresh path
ORA-00904: "SNAPTIME$$": invalid identifier
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

We don’t actually need COMMIT SCN in our ON-COMMIT MV because we’re using the XID.
It’s a newer mechanism for FAST REFRESH ON DEMAND

I know that I don’t need COMMIT SCN in my case above but the fact that it’s so easy to find an issue means that I’m reluctant to do down the parameter route.

Note that there are quite a few bug notes around this in Oracle Support.
A lot of the advice is conflicting, not least because the behaviour of “_mv_refresh_use_stats” changes mid 10.1 something.

At the moment, I think I’m going to go with the locked stats on the MLOGs but it’s worrying.

What a mess!

References:
Lots of Oracle doc ids…


HOWTO: Create a Structured XMLIndex, using the In-Memory Column Store

In Oracle database version 12.1.0.2, Oracle introduced the Oracle In-Memory Database option. It is possible…

Agents Management Via EM12c Release 4 Console

I’m going to take a break from the OMS/OMR Performance pages and jump over to the Agents page in release 12.1.0.4.  You can access this page from the Setup menu drop down in EMCC:

agent10

Main Agent Management Page

The Agents Management page will default to show you all agents and looks very similar to the All Targets page when filtered by a target type, but this page focuses on Agents, something the All Targets page just doesn’t do well no matter how you filter.

agent1

A few things are going to stand out when you come to the Agents Management page-

  • The names of all the agents deployed.
  • The status of the agents, (which often is the first place you should look when you have an issue with a target.)
  • Counts of how many targets it monitors and if there are any broken targets.  (I haven’t taken into consideration how this count may be affected by composite targets, still looking into this…)
  • Status, incident and when the last successful upload from the agent occurred.

At the top of the graph are actions that can be used to manage the agents from the list.

  • Block and Unblock
  • Start up and Shut down
  • Restart
  • Secure and Unsecure
  • Properties
  • Query by Example
  • Detach

Depending on the current status of the agent, different actions will be available.  To choose an agent to perform an action on, you must move your cursor to the box to the very left of the agent or to a spot on the agent line that does not have a link.  If a link exists, it’s going to take you to the corresponding page, (i.e. Agent Home page, Incident Manager, etc.)

agent2

One of the cool things is you can perform actions on multiple agents by highlighting more than one agents in the list like you see above.  There’s a couple ways to do this-

  1. Bring your cursor to the left side box of the agent name and then left click hold and move your cursor down to to the last agent you wish to perform an action on.
  2. The standard known way of choosing multiple lines via office programs, hold down the ctrl key and left click on the individual agents or…
  3. Left click on the first, hold down the shift key and then click on the last one to select a succession of agents from the list.

Once you have chosen the agents you are interested in performing an action on, click on what you wish to do from the menu above the graph and follow through with any required information to complete the action.

Agents with a Down Status

To quickly see what agents are down is valuable as I’ve seen way too often where administrators have become accustomed to not having a 100% green status and give up and correcting the issue.  Focusing on your agents first is a solid path to resolving this.

agent6

You can quickly choose, (and I recommend doing this action one at a time to then see if anything is amiss, troubleshoot and proceed) and attempt to start the agent.  Once the agent is started, expect some “noise” in the form of EM notifications as Cloud Control gets a handle on what it’s been missing while the agents been down.

Agent Unreachable

An unreachable status has always bothered me-  It’s not just down, the OMS can’t reach it due to some issue and you should expect to have some type of troubleshooting to correct the problem.  Luckily, you can first hover your cursor over the status and the Agent Management page will show you the high level reason behind the agent being in an unreachable status.  The one we’ve highlighted below shows that it can’t write to the file system, so we are already way ahead in what will need to be corrected for this agent to resolve to an up, (green) status.

agent4

Agents that are blocked are also shown clearly in the status section, so keep in mind that these are “double” and also shown in their own filtering in the Blocked view, which we discuss more in depth below.  The rest of the agents you will need to double click on the agent name and investigate the logs to see what has caused the issue.  If data isn’t up to date enough to explain the issue, then the best course of action is to follow my instructions in this post to correct the problems.

Agent Blocked

agent3

Blocked agents are quite straight forward and if you hover your cursor over the Blocked Reason, the full description and how to correct it will even be shown.  I had to laugh when it said to have the EM administrator do this, but for many, the separation of duties is clear and this message will be very helpful.  For those of you that do need to perform this yourself, you can resync the agent by following this blog post from Adeesh Fulay.

Misconfigured Agents

Now I don’t have any misconfigured agents to show you in our environment:

agent11

What I can tell you, that the Misconfigured status, like the Agent Unreachable, will offer you high level information if you hover over the status section for the agent in question.  As this is a misconfigured agent, the best advice I will offer is to go to the host the agent resides and attempt the following command:

emctl status agent -details

This should give you information on why or what it believes is the OMS upload URL/port, where the agent believes the AGENT_HOME is, etc.  It’s a good place to start, but gather information from the emctl.log, the emctl*.out and other logs to find out what is causing the issue.

Advanced Performance Data for Agents

The default view is not the only data that can be displayed.  Note that there are a number of columns that could be added or removed from the graph display, allowing you to answer any number of questions regarding EM12c agents.

agent7

As you can see in the next screenshot, you can even change the Agents Management page to view performance data, which can help you if you are experiencing backlog or agent resource usage issues.  I’ve changed my view to display the following:

  • Agent Name, Incident information
  • Backlog
  • CPU Usage
  • Restarts

agent12

I can now sort by backlog data and display what  agents have a backlog of collections to upload and view the higher CPU usage, (but as you can see by this graph, it’s pretty light considering… :)) This gives me an opportunity to optimize these agents collections, upload intervals and the data its collecting to give it better performance.

Locating All Targets for an Agent

Last but not least, at the bottom left of the Agents Management page you’ll see the following link:  agent13This link will take you to the search page to view what agents support what targets to Cloud Control.

agent9

By typing in a filter for a target type, (or you can choose to leave this blank) you can then type in a name of an agent or partial agent and all targets and agents with that naming convention will be listed.  You will see each target and the agent that supports it.

As unimportant as some may think the Agents Management page is to the Manage Cloud Control offering in the console, I know how often the agent is the first place one should look to a solution for upload issues, incidents and collection errors.  I hope administrators will come to appreciate this valuable console page and how much time it should save them when issues arise.

 

 



Tags:  

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon


Copyright © DBA Kevlar [Agents Management Via EM12c Release 4 Console], All Right Reserved. 2014.

Happy Thanksgiving

Editor’s note: Here’s a first post from one of our new team members, Thao Nguyen (@thaobnguyen), who runs our Emerging Interactions team, the Research and Design part of the R, D & D.

That last D is Development if that’s unclear. Anyway, like Thao says, Happy Thanksgiving for those who celebrate it, and for those who don’t enjoy the silence in our absence. To Thao’s question, I’m going with Internet. Yes, it’s a gadget because it’s a series of tubes, not a big truck.

Find the comments to add the gadget for which you are most thankful.

Tomorrow is Thanksgiving and this seems like a good time to put my voice out on The AppsLab (@theappslab). I’m Thao, and my Twitter (@thaobnguyen) tagline is “geek mom.” I’m a person of few words and those two words pretty much summarize my work and home life. I manage The AppsLab researchers and designers. Jake welcomed us to the AppsLab months ago here, so I’m finally saying “Thank you for welcoming us!”

Photo by floodllama on Flickr used under Creative Commons

Photo by floodllama on Flickr used under Creative Commons

As we reflect on all the wonderful things in our lives, personal and professional, I sincerely want to say I am very thankful for having the best work family ever. I was deeply reminded of that early this week, when I had a little health scare at work and was surround by so much care and support from my co-workers. Enough of the emotional stuff, and onto the fun gadget stuff . . . .

My little health scare led me to a category of devices that hadn’t hit my radar before – potentially, life saving, personal medical apps. I’ve been looking at wearables, fitness devices, healthcare apps, and the like for a long time now but there is a class of medical-grade devices (at least recommended by my cardiologist) that is potentially so valuable in my life, as well as those dear to me . . . AliveCor. It is essentially turns your smartphone into an ECG device so you can monitor your heart health anytime and share it with your physician. Sounds so cool!

Back to giving thanks, I’m so thankful for all the technology and gadgets of today – from the iPhone and iPad that lets me have a peaceful dinner out with the kids to these medical devices that I’ll be exploring now. I want to leave you with a question, “What gadget are you most thankful for?”Possibly Related Posts:

Lunchtime quiz

There was a question on OTN a few days ago asking the following question:

Here’s a query that ran okay on 11g, but crashed with Oracle error “ORA-01843: not a valid month” after upgrade to 12c; why ?

The generically correct answer, of course, is that the OP had been lucky (or unlucky, depending on your point of view) on 11g – and I’ll explain that answer in another blog posting.

That isn’t the point of this posting, though. This posting is a test of observation and deduction. One of the respondants in the thread had conveniently supplied a little bit of SQL that I copied and fiddled about with to demonstrate a point regarding CPU costing, but as I did so I thought I’d show you the following and ask a simple question.’


drop table T;

Create Table T
As
with
periods as (
                  Select 'January' period, 1 cal  From Dual
        union all Select 'February' period , 2 cal From Dual
        union all Select 'March' period , 3 cal From Dual
        union all Select 'April' period , 4 cal From Dual
        union all Select 'May'  period, 5 cal From Dual
        union all Select 'June' period, 6 cal From Dual
        union all Select 'July' period, 7 cal From Dual
        union all Select 'August' period, 8 cal From Dual
        union all Select 'September' period, 9 cal  From Dual
        union all Select 'October' period, 10 cal From Dual
        union all Select 'November' period, 11 cal From Dual
        Union All Select 'December' Period, 12 Cal From Dual
        Union All Select '13 Series' Period, Null Cal  From Dual
)
Select  Period,Cal
from periods;

prompt  ==================================
prompt  When we invoke below SQL it works.
prompt  ==================================

set autotrace on explain

select *
from    (
        select
                Period,
                Cal,
                to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,
                to_date('November, 2014','Month, YYYY') col2
        From  T
        Where  Cal > 0
        )
;

prompt  ================================================
prompt  But when we add comparison operations , it fails
prompt  ================================================

select *
from    (
        select
                Period,
                Cal,
                to_date(Period || ', ' || 2014,'Month, YYYY')   col1,
                to_date('November, 2014','Month, YYYY')         col2
        From  T
        Where  Cal > 0
        )
where
        col1 >= col2
;

set autotrace off



All I’ve done is create a table then run and generate the execution plans for two queries – with a comment that if you try to run one query it will succeed but if you try to run the other it will fail (and raise ORA-01843). As far as the original supplier was concerned, both queries succeeded in 11g and the failure of the second one appeared only in 12c. In fact, for reasons that I won’t discuss here, it is POSSIBLE for the failure to appear in 11g as well, though not necessarily with this exact data set.

Here’s the COMPLETE output I got from running the code above on an 11.2.0.4 instance:



Table dropped.


Table created.

==================================
When we invoke below SQL it works.
==================================

PERIOD           CAL COL1      COL2
--------- ---------- --------- ---------
January            1 01-JAN-14 01-NOV-14
February           2 01-FEB-14 01-NOV-14
March              3 01-MAR-14 01-NOV-14
April              4 01-APR-14 01-NOV-14
May                5 01-MAY-14 01-NOV-14
June               6 01-JUN-14 01-NOV-14
July               7 01-JUL-14 01-NOV-14
August             8 01-AUG-14 01-NOV-14
September          9 01-SEP-14 01-NOV-14
October           10 01-OCT-14 01-NOV-14
November          11 01-NOV-14 01-NOV-14
December          12 01-DEC-14 01-NOV-14

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

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

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

   1 - filter("CAL">0)

Note
-----
   - dynamic sampling used for this statement (level=2)

================================================
But when we add comparison operations , it fails
================================================

PERIOD           CAL COL1      COL2
--------- ---------- --------- ---------
November          11 01-NOV-14 01-NOV-14
December          12 01-DEC-14 01-NOV-14

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

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

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

   1 - filter("CAL">0 AND TO_DATE("PERIOD"||', '||'2014','Month,
              YYYY')>=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


So this is the question. What’s the anomaly in this output ?

Bonus question: What’s the explanation for the anomaly ?


Oracle Midlands Event #6 : Summary

OraclePressBearLast night was the Oracle Midlands Event #6 with Bjoern Rost.

Let’s start with the important stuff! I am now the proud owner of an Oracle Press bear! I didn’t technically win it, but one of the people who did donated it to me. This was the right thing to do for two reasons. (1) I wanted it more than him and (2) it is his fault I am in my current job, rather than living the good life. Thanks Dave!

Bjoern’s first session was on SQL Plan Management, which I think is an awesome feature when used in the correct context. For most DBAs, consistency is the primary goal, not ultra performance. SQL Plan Management gives you the ability to lock down your execution plans to give you more consistent results. I’ve used this during upgrade and patching cycles and it really works well. I see even more need for this as we move into the new world order of everything being optimized 75,000 times before we get the final good plan. :)

After some food, Bjoern was back with 12c New Features for DBAs and Developers. This was a quick tour through some of the cool bits of 12c. I like the way Bjoern presents. He’s very honest about features he’s not used much yet, which makes a change from people acting like they know everything, only to crumble under questioning. It also makes the talks very amusing, as well as being informative. Having spent so long with him on the APAC Tour, it was surprising that I hadn’t seen him present either of these talks before.

After the event a few of us ventured over to the German market, which was actually closed by the time we got there. :) Instead, we went into a local pub and discussed life, the universe and everything until far too late.

When it was time to leave, I gave Bjoern a lift round Birmingham city centre, looking for a taxi rank. Eventually, he got out of my car at some traffic lights and got into the taxi behind me. :) In my defence, there is loads of building work around the city centre, so roads are closed and the taxi ranks have moved… I also gave Dave (my bear supplier) a lift home…

Thanks to RedGate for sponsoring the event, allowing it remain free.  Thanks to Mike Dirden for organising the event. Thanks to Bjoern Rost for coming to entertain us and to the Oracle ACE Program for getting him across to us. Thanks to Oracle Press for my bear. Thanks also to the attendees, without whom this would not happen. Please keep spreading the word. :)

Cheers

Tim…

PS. The next event is in January. I’ll write a post about it once it is up on the website…


Oracle Midlands Event #6 : Summary was first posted on November 26, 2014 at 10:38 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

The insanity that is Uber – a 100$B company?

So we've had taxis for years and we know that generally taxi drivers work hard, long hours and make small amounts of money. The cab companies make more, of course, but I don't think there are a whole lot of billionaires in the taxi business.

And now there is Uber. An earlier round of VC $ put its value at $17B. According to Fortune, Uber is now "raising new funding at a valuation of between $35 billion and $40 billion, according to a new report from Bloomberg. This would be one of the richest “venture capital” rounds in history (Facebook still holds the crown), and likely mean that investors expect Uber to eventually go public at a valuation of at least $100 billion."

How are to make any sense of this? Where would all the money come from to make all these investors (and shareholders) rich? 

By cutting out the "middleman" (regulation to ensure safe rides, primarily)? Maybe, but I can't imagine it will generate that much revenue?

By reducing the cost of a ride, compared to a taxi? That's true, apparently, some of the time with Uber, but often it is way MORE expensive - because prices are "market-driven."

By shifting more and more of the costs and risks to the drivers? That's pretty darn likely. Just look at the poor "contractors" who have to pay for their trucks and lease their gear from FedEx. 

By shifting riders from mass transit to Uber (in other greatly expanding the "pie" of pay-per-ride)? Again, that seems unlikely.

What am I missing? How could Uber replace an existing business that brings in nowhere near that much money and suddenly be printing the stuff?

Oh, and that's if they don't self-destruct due to their cavalier, arrogant attitudes and actions of their management.

The User Group Tour

Those of you that have been around Oracle for some time may already be aware of the various OTN tours. These tours bring well known speakers to some of the smaller (relatively speaking) regions that often don’t get to see some of the big name speakers, simply because of audience size. Over the past couple of years, I’ve been involved in the OTN APAC tour, and recently returned from the New Zealand leg of the tour for this year. I presented two workshops – one on Database Lifecycle Management and one on DBaaS – as well as standing in for a sick presenter and covering Snap Clone in Enterprise Manager 12c. For me, the best value for the conference was (as it so often is) the time spent interacting both with customers and other speakers / exhibitors. It was great to catch up with so many people I haven’t seen for a long time, both from within Oracle and outside of it.

In many ways, the New Zealand leg of the OTN APAC Tour is the start of my very own user group tour. Over the next few months, I’ll be presenting at UKOUG, RMOUG and possibly Collaborate 15 and Kscope 15. Should be a fun time, provided my back and knee hold out. I was on crutches at the NZOUG conference and will be for UKOUG as well.

UKOUG Tech14

This will be my first time at the UKOUG conference. I hear it’s one of the better user group conferences to get to. Of course, in my case getting there is going to be the problem since I’ll still be on crutches. I’m flying from Canberra to Manchester with two stops:

  • Canberra to Sydney – the shortest leg, just to prepare me for the next leg. :) That’s only a 55 minute flight, so most of the time will be getting to and from cruising height. I suspect this one will be a Dash 8, which means I’ll have to check my laptop bag as premium luggage (taken from and delivered to me at the steps into the plane) and have to carry on the laptop itself. All these things are relatively easy when you’re not on crutches, but adding the crutches adds a whole new dimension of complexity!
  • Sydney to Dubai – after a 2 hour layover in Sydney, this is the real ugly one – 15 hours, 28 minutes estimated travel time. UGH! :( At least this time I can carry the laptop bag on. I had to get a new one so I could carry it on my back since wheeling a bag around isn’t the easiest of options with crutches! :)
  • Dubai to Manchester – after a 3 hour 40 minute layover in Dubai, a short 7 hours, 32 minutes – well, short compared to the leg before it anyway!

After landing in Manchester, I’ll be met by a good friend who’s volunteered to drive me to Liverpool. I wonder what state I’ll be in by that time!

Once I’m at the conference, I’ll kick things off with Super Sunday, probably attending most of the OakTable stream. I’m looking forward to meeting a few of my colleagues on the Oak Table from Europe that I haven’t met as yet, and of course there’s the ACE Dinner that night that the organizers have kindly invited me to. During the Tech14 conference itself, I’ll be helping out with Enterprise Manager Round Table at 2:30 on Monday, as well as presenting on Snap Clone on Tuesday at 3:00. I’m also visiting Barclays on the Thursday to discuss EM and how it can help them in their environment, before the long haul home again.

RMOUG Training Days

The RMOUG Training Days conference is one of my personal favourites, and to me one of the best grass roots user group conferences out there. I’ve presented at it multiple times, and have always enjoyed meeting up with my good friends on the organizing committee. This time round, I have two hands-on workshops (one on Delivering Pluggable Database as a Service (PDBaaS) with Enterprise Manager and the other on Delivering Schema as a Service with Enterprise Manager, and doing the Snap Clone presentation as well.

The two workshops are really designed to show you Database as a Service (DBaaS) in two different versions of the Oracle database. Schema as a Service works with releases prior to Oracle Database 12c, and as we all know, there are a lot of people out there who haven’t made the switch to DB12c as yet. Of course, there can be multiple reasons for that, but if you can, I really do suggest you make the move to DB12c and then look at using PDBaaS instead of Schema as a Service. It’s a much more straightforward implementation at the application level, since it addresses the namespace collision issue you need to deal with in Schema as a Service (see my posts here and here on Schema as a Service if what I just said is double dutch to you. ;) )

Once the conference is over, I’m looking forward to at least seeing some snow close up. I doubt I’ll be of much use for skiing or similar with my back and knee problems, but I can always hope to see Carol Dacko disappear from sight as she attempts a snow angel again! :)

Collaborate 15

This is another conference that I haven’t attended before, which I’m hoping to get to this time round. At the time I’m writing this, they haven’t announced acceptances for the conference, so I may or may not get there. Again, it’s a conference I’ve been wanting to get to but just haven’t had the opportunity as yet. Hopefully this year I’ll get there. THe only downside for me is that it’s in Las Vegas, a city I visited plenty of times for work when I was based in the US. It’s one of those places I wanted to visit to tick off my bucket list, but it really doesn’t hold a lot of attraction for me now I’ve been there. I’m not a gambler so the gaming side doesn’t interest me at all, but the shows are usually worth investigating. :)

Kscope 15

Like Collaborate 15, this is another conference I haven’t been to. I submitted a couple of papers for it as some of my colleagues said this was a great conference to get to, so we’ll see what comes of those!

So there you have it – a bunch of travel coming up to present at a variety of user group conferences. That’s one of the parts of my job as a database architect in the DBaaS group within EM that really makes me happy. I love talking with customers about what EM and DBaaS in particular can do to help them, so if you see me hobbling around on crutches at any of these conferences, come up and say hi!

Pythian at UKOUG 14

Will you be joining us at the UKOUG Conference and Exhibition in Liverpool, UK? Over 200 world-class speakers and industry experts will be in attendance, including some of our very own.

Michael Abbey, Oracle ACE and Team Lead at Pythian notes that Pythian’s attendance is important, not only for the company and its employees, but for the Oracle community as a whole. “Pythian’s presence at UKOUG this year is the next chapter in an ongoing participation in database and EIS technical events around the world,” Michael explains. “We pride ourselves on presence on all seven continents and our appearances in Liverpool are strategic to the worldwide user community, as are all the locations we frequent every year.The user group community is one of the fundamental building blocks for technical resources as they hone their skills to better serve their clients and the masses in general. Since its founding, Pythian has been a strategic and financial support organization feeding talent to many of the world’s largest technical shows.”

“You will see a number of presenters from the Pythian suite of experts including Oracle ACEs, Oracle ACE Directors, and members of the OakTable Network. We look forward to catching up with new acquaintances and rekindling existing relationships.” In the meantime, you can find their speaking sessions below:

 

RMAN: The Necessary Basics
Presented by Michael Abbey | Monday December 8, 2014 — 9:00-9:50 AM

This session, best suited for attendees just getting started with RMAN, the basic skills to write consistent backups and perform recovery activities from day one. Highlighted in this masterclass will be the following: RMAN architecture, using a catalog, backups to disk, backups to tape, recovery (complete and incomplete), database duplication, and tips and tricks.

About Michael: Michael Abbey is a seasoned and experienced presenter on the Oracle Database CORE technology. He first cut his teeth on V3 in 1986 and it has been a whirlwind of Oracle since then. Michael co-authored the first work in the Oracle Press series in 1994.

 

Why Use OVM for Oracle Database?
Presented by Francisco Munoz Alvarez | Monday December 8, 2014 — 5:00-5:50 PM

Vibrant session about OVM, that will explain how, when and why use this product for Virtualization. It will also give an overview of how Revera is currently using this product in NZ (Biggest OVM Farm in the ANZ region) and show benchmark results between Bare Metal, OVM and ESX concluding with some tips and showing the scalability and break point of load of the Virtualization solutions.

Come and discover the answers for the following questions:

  • Does an Oracle Database perform well on a virtualized environment?
  • What virtualization technology is more stable and allows an Oracle database to perform faster?
  • What is the performance difference between using a bare metal and a virtualized guest?
  • Is it safe to run a production database in a virtualized environment?

About Francisco:  Working out of Pythian’s Australian office in Macquarie Park, Francisco Munoz Alvarez is Vice President and Managing Director of Service Delivery in Asia Pacific, overseeing its regional expansion effort. Francisco previously served as Chief Technology Officer at Database Integrated Solutions Ltd and has more than two decades’ experience in Oracle databases. As President of the Chilean Oracle Users Group and founder of the Oracle Technology Network (OTN) tours in Latin America and Asia Pacific, he is best known for his evangelist work with the Oracle community.

 

Big Data with Exadata
Presented by Christo Kutrovsky | Tuesday December 9, 2014 — 5:30-6:20 PM

In this presentation, Oracle ACE Christo Kutrovsky will discuss common big data use cases and how they can be implemented efficiently with Exadata. Attendees will learn how Exadata actually delivers most of the benefits touted by newer big data technologies, and can often be the right platform for data scalability.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Measuring Performance in Oracle Solaris and Oracle Linux
Presented by Christo Kutrovsky | Wednesday December 10, 2014 —9:00-9:50 AM

You can’t improve what you can’t measure. If you want to get the most value from your database, you need to start with the basics: are you using your hardware and operating systems efficiently? Attend this session to learn how to measure system utilization in the Linux and Oracle Solaris operating systems and how to use this information for tuning and capacity planning.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Lessons Learned in Implementing Oracle Access Manager 11g with Forms, Reports, and Discoverer
Presented by Sudeep Raj and Maris Elsins | Wednesday December 10, 2014 — 12:30-1:20 PM

Support for SSO has ended in December 2011. To take advantage of the latest security enhancements, it’s always recommended for customers to upgrade their system to the latest and the greatest version of the product i.e OAM/OID 11g, very important to stay on the supported configurations. This session will give you an opportunity to understand how OAM 11g can be configured with Forms/Reports/Discoverer 11g, integration with external directory service like Microsoft AD and discuss about the upgrade considerations for customers planning to upgrade from 10g SSO/OID to OAM 11g and OID 11g.

About Sudeep: Sudeep Raj is a Team Lead/Oracle Applications Database Consultant at Pythian, managing a group of expert DBAs spread across the globe. With nearly a decade of experience as an Apps DBA, he has been involved in and led multiple Oracle E-Business Suite 11i/R12 implementations, maintenance, migration and upgrade projects. Sudeep Raj is an OCP certified professorial and holds a Bachelor of Engineering degree in Computer Science.

About Maris: Recently awarded the Oracle ACE designation, Maris Elsins is a Lead Database Consultant at Pythian. He is a blogger and frequent speaker at many Oracle related conferences like Collaborate, UKOUG, and LVOUG where he is a board member. Maris is an exceptional trouble shooter and enjoys learning why things behave the way they do.

 

Optimizing and Simplifying Complex SQL with Advanced Grouping
Presented by Jared Still | Wednesday December 10, 2014 — 3:30-4:20 PM

This presentation will show how these features can be used to simplify SQL that was previously quite complex by reducing the amount of code needed and improving readability, and perhaps most importantly, greatly optimizing the performance of SQL statements.

About Jared: Jared Still is a Senior Database Consultant at Pythian. His experience includes working with Oracle databases beginning with version 7.0. While Oracle has expanded to encompass many aspects of the application environment, Jared’s focus has been on the database itself and related infrastructure.

 

Oracle RAC — Designing Applications for Scalability
Presented by Christo Kutrovsky | Wednesday December 10, 2014 — 3:30-4:20 PM

Oracle Real Application Clusters (RAC) promises 100% transparent active-active clustering technology – true horizontal scaling, but does it work in all cases? This presentation explores the challenges with Oracle’s active-active solution and how to solve them from both database side and application side. Both conceptual design and highly practical solutions are explored.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Database as a Service on the Oracle Database Appliance Platform
Presented by Marc Fielding and Maris Elsins | Wednesday December 10, 2014 — 3:30-4:20 PM

Oracle Database Appliance provides a robust, highly-available, cost-effective, and surprisingly scalable platform for database as a service environment. By leveraging Oracle Enterprise Manager’s self-service features, databases can be provisioned on a self-service basis to a cluster of Oracle Database Appliance machines. Discover how multiple ODA devices can be managed together to provide both high availability and incremental, cost-effective scalability. Hear real-world lessons learned from successful database consolidation implementations.

About Marc: Marc Fielding is a passionate and creative problem solver, drawing on deep understanding of the full enterprise application stack to identify the root cause of problems, and to implement effective and sustainable solutions. He has extensive experience implementing Oracle’s engineered system portfolio, including leading one of the first enterprise Oracle Exadata implementations. Marc has a strong background in performance tuning and high availability.

About Maris: Recently awarded the Oracle ACE designation, Maris Elsins is a Lead Database Consultant at Pythian. He is a blogger and frequent speaker at many Oracle related conferences like Collaborate, UKOUG, and LVOUG where he is a board member. Maris is an exceptional trouble shooter and enjoys learning why things behave the way they do.

 


Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Oracle expertise or read some of our Oracle-related blog posts.