Skip navigation.

Development

ODTUG is my favorite Oracle User Group!

FeuerThoughts - Fri, 2014-02-21 05:33
Each June, ODTUG hold its highly respected and enthusiastically attended Kaleidoscope conference. this year it will take place in Seattle. Lots more details here.

And several years ago, ODTUG added a community service day (CSD) on the Saturday before the conference, to give attendees an opportunity to "do good" while they enjoy the perks of a software conference (they are, after all, very perky!).

Yesterday ODTUG announced their CSD for 2014:

Nature Consortium in Seattle, Washington, strives to combine community, art, and nature. Be a part of this quest by joining ODTUG on Saturday, June 21, for our seventh annual Community Service Day. ODTUGgers will be working to eradicate several invasive plant species in the West Duwamish Greenbelt, one of the crucial wild spaces in Seattle providing a home to much of the wildlife thriving in this tech-savvy city. In keeping with the values of Nature Consortium, Seattle itself, and Kscope14, you will realize the culmination of community, art, and nature as musicians play in the forest while you work to better the environment.








This choice makes me both excited to, for the first time, attend a Kscope CSD and proud to be a member of ODTUG.
I happen to believe that fighting invasives is just about the most important thing and best thing a human can do in this world to compensate for all the awful damage we have caused to our very own beautiful planet/habitat. 
I look forward to joining what I hope is a group of hundreds of fellow-Kscopians to make a big impact in June!
Thanks so much, ODTUG!
Categories: Development

Inlist iterator again

XTended Oracle SQL - Thu, 2014-02-20 17:30

Several months ago I wrote about avoiding inlist iterator, but this post about how to force inlist iterator in queries like that:

select *
from xt_test1 t1
    ,xt_test2 t2
where 
     t1.a = :a 
 and t2.id in (a,b)

i.e. when we need to get rows from big table using index by list of values from another table.

Test tables:
Spoiler:: ddl SelectShow

drop table xt_test1 purge;
drop table xt_test2 purge;

create table xt_test1(id, a not null, b not null, constraint xt_test1_pk primary key (id))
as
 select level, level*2-1, level*2
 from dual
 connect by level <=10000
/

create table xt_test2(id, pad,constraint xt_test2_pk primary key(id))
as
 select level,lpad(level,20)
 from dual
 connect by level <=10000
/
begin
   dbms_stats.gather_table_stats(user,'XT_TEST1');
   dbms_stats.gather_table_stats(user,'XT_TEST2');
end;
/

By default we can get only bad plan with concatenation and 2 full table scans for this query:

Plan hash value: 667870028

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |    22   (0)| 00:00:01 |
|   1 |  CONCATENATION                |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
|   7 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   8 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|  11 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - filter("T1"."A"=TO_NUMBER(:A))
   5 - access("T2"."ID"="B")
   9 - filter("T1"."A"=TO_NUMBER(:A))
  10 - access("T2"."ID"="A")
       filter(LNNVL("T2"."ID"="B"))

27 rows selected.

I have tried several different options and found one interesting thing:

SQL> explain plan for
  2  select *
  3  from xt_test1 t1
  4      ,xt_test2 t2
  5  where
  6       t1.a = :a
  7   and t2.rowid = (select x.rowid rid from xt_test2 x where x.id in (t1.a,t1.b))
  8  /

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 951366071

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    37 | 14061   (1)| 00:02:49 |
|   1 |  NESTED LOOPS               |             | 10000 |   361K|    11   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY USER ROWID| XT_TEST2    | 10000 |   244K|     1   (0)| 00:00:01 |
|   4 |    INLIST ITERATOR          |             |       |       |            |          |
|*  5 |     INDEX UNIQUE SCAN       | XT_TEST2_PK |     2 |    32 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - filter("T1"."A"=TO_NUMBER(:A))
   5 - access("X"."ID"=:B1 OR "X"."ID"=:B2)

As you can see it’s very good plan, but only for cases when you have one row only for any set of (t1.a, t2.b).
Look what will be if we replace “=” to “in”:
(strictly speaking we have to use “in (select distinct rowid“, but it’s not so important now)

SQL> explain plan for
  2  select *
  3  from xt_test1 t1
  4      ,xt_test2 t2
  5  where
  6       t1.a = :a
  7   and t2.rowid in (select x.rowid rid from xt_test2 x where x.id in (t1.a,t1.b))
  8  /

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |    22   (0)| 00:00:01 |
|   1 |  CONCATENATION                |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
|   7 |   NESTED LOOPS                |             |     1 |    37 |    11   (0)| 00:00:01 |
|   8 |    NESTED LOOPS               |             |     1 |    37 |    11   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL         | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     0   (0)| 00:00:01 |
|  11 |    TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$62C663B8
   4 - SEL$62C663B8_1 / T1@SEL$1
   5 - SEL$62C663B8_1 / X@SEL$2
   6 - SEL$62C663B8_1 / X@SEL$2
   9 - SEL$62C663B8_2 / T1@SEL$62C663B8_2
  10 - SEL$62C663B8_2 / X@SEL$62C663B8_2
  11 - SEL$62C663B8_2 / X@SEL$62C663B8_2

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

   4 - filter("T1"."A"=TO_NUMBER(:A))
   5 - access("X"."ID"="T1"."B")
   9 - filter("T1"."A"=TO_NUMBER(:A))
  10 - access("X"."ID"="T1"."A")
       filter(LNNVL("X"."ID"="T1"."B"))

Notice that join with “t2″ was eliminated(also we can get join elimination of X).
So let’s try with outlines from plan with “=”

SQL> explain plan for
  2  select
  3    /*+
  4        BEGIN_OUTLINE_DATA
  5        INDEX(@"SEL$2" "X"@"SEL$2" ("XT_TEST2"."ID"))
  6        PUSH_SUBQ(@"SEL$2")
  7        USE_NL(@"SEL$1" "T2"@"SEL$1")
  8        LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
  9        ROWID(@"SEL$1" "T2"@"SEL$1")
 10        FULL(@"SEL$1" "T1"@"SEL$1")
 11        OUTLINE_LEAF(@"SEL$1")
 12        OUTLINE_LEAF(@"SEL$2")
 13        ALL_ROWS
 14        DB_VERSION('11.2.0.4')
 15        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
 16        IGNORE_OPTIM_EMBEDDED_HINTS
 17        END_OUTLINE_DATA
 18    */
 19     *
 20  from xt_test1 t1
 21      ,xt_test2 t2
 22  where
 23       t1.a = :a
 24   and t2.rowid in (select x.rowid rid from xt_test2 x where x.id in (t1.a,t1.b))
 25  /

Explained.

SQL> @xplan +alias

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3405631984

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   500 | 18500 |    27   (4)| 00:00:01 |
|   1 |  NESTED LOOPS        |             |   500 | 18500 |    24   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | XT_TEST2    |   500 | 12500 |    14   (0)| 00:00:01 |
|   4 |    INLIST ITERATOR   |             |       |       |            |          |
|*  5 |     INDEX UNIQUE SCAN| XT_TEST2_PK |     1 |    16 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$1 / T2@SEL$1
   4 - SEL$2
   5 - SEL$2 / X@SEL$2

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

   2 - filter("T1"."A"=TO_NUMBER(:A))
   3 - filter( EXISTS (SELECT /*+ PUSH_SUBQ INDEX ("X" "XT_TEST2_PK") */ 0
              FROM "XT_TEST2" "X" WHERE ("X"."ID"=:B1 OR "X"."ID"=:B2) AND "X".ROWID=:B3))
   5 - access("X"."ID"=:B1 OR "X"."ID"=:B2)
       filter("X".ROWID=:B1)

Unfortunately “TABLE ACCESS BY USER ROWID” was replaced by “TABLE ACCESS FULL”.
I will try to register SR later about this strange behavior, but I want to show now a couple workarounds for that.
First of all we can use collection iterators:

SQL> explain plan for
  2  select/*+ use_nl(t2) index(t2 (id)) cardinality(x 2)*/ *
  3  from xt_test1 t1
  4      ,table(sys.odcinumberlist(a,b)) x
  5      ,xt_test2 t2
  6  where
  7       t1.a = :a
  8   and t2.id=x.column_value
  9  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 651710375

---------------------------------------------------------------------------------------------
| Id  | Operation                                | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |             |     2 |    78 |    41   (0)|
|   1 |  NESTED LOOPS                            |             |     2 |    78 |    41   (0)|
|   2 |   NESTED LOOPS                           |             |     2 |    78 |    41   (0)|
|   3 |    NESTED LOOPS                          |             |     2 |    28 |    39   (0)|
|   4 |     TABLE ACCESS FULL                    | XT_TEST1    |     1 |    12 |    10   (0)|
|   5 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|             |     2 |     4 |    29   (0)|
|   6 |    INDEX UNIQUE SCAN                     | XT_TEST2_PK |     1 |       |     0   (0)|
|   7 |   TABLE ACCESS BY INDEX ROWID            | XT_TEST2    |     1 |    25 |     1   (0)|
---------------------------------------------------------------------------------------------

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

   4 - filter("T1"."A"=TO_NUMBER(:A))
   6 - access("T2"."ID"=VALUE(KOKBF$))

It’s quite good solution, but there is a big overhead because of many objects creations.
So let’s try another options:
Spoiler:: With generated tables
SelectShow

SQL> explain plan for
  2  with gen as (select level rn from dual connect by level<=2)
  3  select/*+ leading(t1 gen t2) use_nl(gen) */ *
  4  from xt_test1 t1
  5      ,gen
  6      ,xt_test2 t2
  7  where
  8       t1.a = :a
  9   and t2.id=decode(gen.rn,1,t1.a,t1.b)
 10  /

Explained.

-------------------------------------------------------------------------------------
Plan hash value: 2353516195

-------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     1 |    50 |    13   (0)|
|   1 |  NESTED LOOPS                    |             |     1 |    50 |    13   (0)|
|   2 |   NESTED LOOPS                   |             |     1 |    50 |    13   (0)|
|   3 |    NESTED LOOPS                  |             |     1 |    25 |    12   (0)|
|*  4 |     TABLE ACCESS FULL            | XT_TEST1    |     1 |    12 |    10   (0)|
|   5 |     VIEW                         |             |     1 |    13 |     2   (0)|
|*  6 |      CONNECT BY WITHOUT FILTERING|             |       |       |            |
|   7 |       FAST DUAL                  |             |     1 |       |     2   (0)|
|*  8 |    INDEX UNIQUE SCAN             | XT_TEST2_PK |     1 |       |     0   (0)|
|   9 |   TABLE ACCESS BY INDEX ROWID    | XT_TEST2    |     1 |    25 |     1   (0)|
-------------------------------------------------------------------------------------

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

   4 - filter("T1"."A"=TO_NUMBER(:A))
   6 - filter(LEVEL<=2)
   8 - access("T2"."ID"=DECODE("GEN"."RN",1,"T1"."A","T1"."B"))

Note that you have to avoid duplicates if t1.a = t2.b.
Also it’s quite good to get plans with concatenation only on second table, without rereading others tables:
Spoiler:: 12c Lateral with concatenation
SelectShow

SQL> explain plan for
  2  select/*+ leading(t1 t2) use_nl(t2)  */ *
  3  from xt_test1 t1
  4      ,lateral(
  5           select/*+ no_merge */ *
  6           from xt_test2
  7           where xt_test2.id in (t1.a,t1.b)
  8      ) t2
  9  where
 10       t1.a = :a
 11  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 53936152

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     2 |   134 |    14   (0)|
|   1 |  NESTED LOOPS                  |                 |     2 |   134 |    14   (0)|
|*  2 |   TABLE ACCESS FULL            | XT_TEST1        |     1 |    12 |    10   (0)|
|   3 |   VIEW                         | VW_LAT_A18161FF |     2 |   110 |     4   (0)|
|   4 |    CONCATENATION               |                 |       |       |            |
|   5 |     TABLE ACCESS BY INDEX ROWID| XT_TEST2        |     1 |    25 |     2   (0)|
|*  6 |      INDEX UNIQUE SCAN         | XT_TEST2_PK     |     1 |       |     1   (0)|
|   7 |     TABLE ACCESS BY INDEX ROWID| XT_TEST2        |     1 |    25 |     2   (0)|
|*  8 |      INDEX UNIQUE SCAN         | XT_TEST2_PK     |     1 |       |     1   (0)|
---------------------------------------------------------------------------------------

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

   2 - filter("T1"."A"=TO_NUMBER(:A))
   6 - access("XT_TEST2"."ID"="T1"."B")
   8 - access("XT_TEST2"."ID"="T1"."A")
       filter(LNNVL("XT_TEST2"."ID"="T1"."B"))


But it’s really funny, that do the same with simple ANSI LEFT OUTER join!
Spoiler:: Ansi left join with concatenation
SelectShow

SQL> explain plan for
  2  select/*+ leading(t1 t2) use_nl(t2) */ *
  3  from xt_test1 t1
  4       left join xt_test2 t2
  5            on t2.id in (t1.a,t1.b)
  6  where t1.a = :a
  7  and t2.id*0 is not null
  8  /
-----------------------------------------------------------------------------------------------
Plan hash value: 4273891864

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     2 |   134 |    14   (0)| 00:00:01 |
|*  1 |  FILTER                         |             |       |       |            |          |
|   2 |   NESTED LOOPS OUTER            |             |     2 |   134 |    14   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL            | XT_TEST1    |     1 |    12 |    10   (0)| 00:00:01 |
|   4 |    VIEW                         |             |     2 |   110 |     4   (0)| 00:00:01 |
|   5 |     CONCATENATION               |             |       |       |            |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     2   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     1   (0)| 00:00:01 |
|   8 |      TABLE ACCESS BY INDEX ROWID| XT_TEST2    |     1 |    25 |     2   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN         | XT_TEST2_PK |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter("T2"."ID"*0 IS NOT NULL)
   3 - filter("T1"."A"=TO_NUMBER(:A))
   7 - access("T2"."ID"="T1"."B")
   9 - access("T2"."ID"="T1"."A")
       filter(LNNVL("T2"."ID"="T1"."B"))

Useful Queries in OEM12c PART-2

Arun Bavera - Thu, 2014-02-20 16:15
SOA/AIA 10g

From Management Views:

Total BPEL Instances in Last 1 Hour
SELECT sum(value) as TOTAL_NUM_INSTANCES, to_char(sysdate -2/24,'dd-mm-yyyy HH:MI:ss') as START_TIME ,to_char(sysdate -1/24,'dd-mm-yyyy HH:MI:ss') AS END_TIME
FROM  MGMT$METRIC_CURRENT
WHERE
  (target_name ='bpeltarget.myfullhostname1_bpel' or target_name ='bpeltarget.myfullhostname2.net_bpel')
  and metric_column ='closedInstances.load'
  and collection_timestamp > sysdate -2/24
  and collection_timestamp

Closed_Instances_Since_Last_Collection

SELECT target_name,column_label,to_char(collection_timestamp,'dd-mm-yyyy HH:MI:ss') Collected,key_value Domain,key_value2 BPEL_Process,value ClosedInstances,value/3600 "Avg Time Taken(Seconds)"
FROM  MGMT$METRIC_CURRENT
WHERE 
  (target_name ='bpeltarget.myfullhostname1_bpel' or target_name ='bpeltarget.myfullhostname2_bpel')
  and metric_column ='closedInstances.load'
  and collection_timestamp > sysdate -2/24   
  and collection_timestamp
  and to_number(value) >0
 ORDER BY to_number(value) DESC



Directly From  Database using DBLink in OEM12c to SOA/AIA Database

Total BPEL Instances in Last 1 Hour
SELECT   COUNT(*) AS TOTAL_NUM_INSTANCES,((??EMIP_BIND_END_DATE??)-2/24)  Start_Time,  ((??EMIP_BIND_END_DATE??)-1/24)  End_Time
FROM     ORABPEL.CUBE_INSTANCE@mydblink
WHERE  ORABPEL.CUBE_INSTANCE.PROCESS_ID NOT IN
                      ('AIAAsyncErrorHandlingBPELProcess',  
                       'TaskActionHandler',  
                      'TaskManager',  
                      '__QuoteAccountHoldService',  
                      '__QuoteGlobalSalesTaxService',  
                      '__QuoteInputValidationService',  
                      '__QuotePricingService',  
                      '__QuotePromiseDatesService',  
                      '__QuoteStandardizeAddressesService',  
                      '__QuoteValidateShippingService')  
AND ORABPEL.CUBE_INSTANCE.CREATION_DATE >(??EMIP_BIND_END_DATE??)-2/24 
AND ORABPEL.CUBE_INSTANCE.CREATION_DATE <(??EMIP_BIND_END_DATE??)-1/24 

AND ORABPEL.CUBE_INSTANCE.state in (5,6)


SOA SERVICES PERFORMANCE MONITOR - PRD - HOURLY
SELECT PROCESS_NAME AS PROCESS,
       OPERATION,
/*         TO_CHAR (MIN (INSTANCE_START), 'MM/DD/YYYY HH24:MI')
            AS PROCESS_LOAD_START,
         TO_CHAR (MAX (ACTIVITY_END), 'MM/DD/YYYY HH24:MI') AS PROCESS_LOAD_END, */
         COUNT (*) AS NUM_INSTANCES,
         ROUND (
            COUNT (*) * 60
            / (EXTRACT (
                  SECOND FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
               + EXTRACT (
                    MINUTE FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 60
               + EXTRACT (
                    HOUR FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 3600
               + EXTRACT (
                    DAY FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 86400),
            2)
            AS THRUPUT_PER_MIN,
         ROUND (
            AVG (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_AVG,
         ROUND (
            MEDIAN (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MEDIAN,
         ROUND (
            STDDEV (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_STDDEV,
         ROUND (
            MIN (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MIN,
         ROUND (
            MAX (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MAX,
         ROUND (
                 EXTRACT (SECOND FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START))
               + EXTRACT (MINUTE FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 60
               + EXTRACT (HOUR FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 3600
               + EXTRACT (DAY FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 86400,
            2)
            AS LOAD_DURATION,
         SUM(GT_2SEC) AS GT2SEC,
         ROUND(100 - 100*SUM(GT_2SEC)/COUNT(*),2) AS PER_LT2SEC,
         SUM(GT_5SEC) AS GT5SEC,
         ROUND(100 - 100*SUM(GT_5SEC)/COUNT(*),2) AS PER_LT5SEC,
         SUM(GT_15SEC) AS GT15SEC,
         ROUND(100 - 100*SUM(GT_15SEC)/COUNT(*),3) AS PER_LT15SEC,
         SUM(GT_30SEC) AS GT30SEC,
         ROUND(100 - 100*SUM(GT_30SEC)/COUNT(*),3) AS PER_LT30SEC,
         ROUND(100*SUM(SUCCESS_STATE)/COUNT(*),2) AS PER_SUCCESS,
         ROUND(100*SUM(FAULTED_STATE)/COUNT(*),2) AS PER_FAULT
    FROM (  SELECT CUBE_INSTANCE.TITLE TITLE,
                   CASE CUBE_INSTANCE.PROCESS_ID 
                        WHEN 'RDLUCommandProcess' THEN SUBSTR(CUBE_INSTANCE.TITLE, 0, INSTR(CUBE_INSTANCE.TITLE, ' ', 1, 1) - 1)
                        WHEN 'RDLUResponseProcess' THEN SUBSTR(CUBE_INSTANCE.TITLE, 0, COALESCE(NULLIF(INSTR(CUBE_INSTANCE.TITLE, ':', 1, 1),0), INSTR(CUBE_INSTANCE.TITLE, ' ', 1, 1)) - 1) 
                        ELSE 'N/A' 
                    END
                        OPERATION,
                   CUBE_INSTANCE.CIKEY CIKEY,
                   CUBE_INSTANCE.PROCESS_ID PROCESS_NAME,
                   CUBE_INSTANCE.CREATION_DATE INSTANCE_START,
                   CUBE_INSTANCE.MODIFY_DATE ACTIVITY_END,
                   CASE WHEN CUBE_INSTANCE.STATE = 5 THEN 1 ELSE 0 END AS SUCCESS_STATE,
                   CASE WHEN CUBE_INSTANCE.STATE = 6 THEN 1 ELSE 0 END AS FAULTED_STATE,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:02.000' THEN 1 ELSE 0 END AS GT_2SEC,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:05.000' THEN 1 ELSE 0 END AS GT_5SEC,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:15.000' THEN 1 ELSE 0 END AS GT_15SEC,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:30.000' THEN 1 ELSE 0 END AS GT_30SEC
              FROM ORABPEL.CUBE_INSTANCE@mydblink
             WHERE CUBE_INSTANCE.PROCESS_ID NOT IN
                      ('AIAAsyncErrorHandlingBPELProcess',  
                       'TaskActionHandler',  
                      'TaskManager',  
                      '__QuoteAccountHoldService',  
                      '__QuoteGlobalSalesTaxService',  
                      '__QuoteInputValidationService',  
                      '__QuotePricingService',  
                      '__QuotePromiseDatesService',  
                      '__QuoteStandardizeAddressesService',  
                      '__QuoteValidateShippingService')
         AND CUBE_INSTANCE.CREATION_DATE >(??EMIP_BIND_END_DATE??)-2/24 
         AND CUBE_INSTANCE.CREATION_DATE< (??EMIP_BIND_END_DATE??)-1/24 
          AND CUBE_INSTANCE.state in (5,6)
          ORDER BY CUBE_INSTANCE.CREATION_DATE ASC)
GROUP BY PROCESS_NAME, OPERATION

ORDER BY NUM_INSTANCES desc



Other Queries:
select  state, count(*) Count from ORABPEL.dlv_message@dblink  where receive_date >= sysdate -1/24  and receive_date <= sysdate  group by state  order by Count

select count(*),process_id 
from ORABPEL.invoke_message@dblink
where 
receive_Date >= sysdate -1/24   and 
receive_Date <= sysdate   and 
state in (0,1) 
Group By process_id

SELECT  PROCESS_ID,
        COUNT(*) as "count",
        STATE
FROM ORABPEL.CUBE_INSTANCE@dblink
WHERE 
CREATION_DATE >=  sysdate -1/24 AND CREATION_DATE <= sysdate
AND MODIFY_DATE - CREATION_DATE > '0 0:5:00.000000000' GROUP BY PROCESS_ID, STATE ORDER BY PROCESS_ID


SELECT W.CIKEY,W.CLUSTER_NODE_ID, W.EXP_DATE, W.CREATION_DATE,W.MODIFY_DATE,W.COUNT_ID,W.STATE, (W.MODIFY_DATE - W.CREATION_DATE) AS PROCESSING_TIME, P.PROCESS_ID 
FROM ORABPEL.WORK_ITEM@dblink W,  ORABPEL.PROCESS@dblink P
WHERE P.PROCESS_GUID = W.PROCESS_GUID
AND W.STATE       NOT IN (6,1)
AND W.CREATION_DATE >= sysdate -1/24  
AND W.CREATION_DATE <=sysdate


SELECT 
(CASE state
 WHEN 0 THEN 'INITIATED' 
 WHEN 1 THEN 'OPEN and RUNNING' 
 WHEN 2 THEN 'OPEN and SUSPENDED'  
 WHEN 3 THEN 'OPEN and FAULTED'  
 WHEN 4 THEN 'CLOSED and PENDING_CANCEL'  
 WHEN 5 THEN 'CLOSED and COMPLETED' 
 WHEN 6 THEN 'CLOSED and FAULTED' 
 WHEN 7 THEN 'CLOSED and CANCELLED' 
 WHEN 8 THEN 'CLOSED and ABORTED' 
 WHEN 9 THEN 'CLOSED and STALE' 
 ELSE 'UNKNOWN State='||state
END) as BPELstate,
  COUNT(*) COUNT,
  process_id,
  TRUNC(MAX(extract(DAY FROM (modify_date-creation_date))*24*60*60 + extract(hour FROM (modify_date-creation_date))*60*60 + extract(minute FROM (modify_date-creation_date))*60 + extract(second FROM (modify_date-creation_date))),4) MaxTime,
  TRUNC(MIN(extract(DAY FROM (modify_date-creation_date))*24*60*60 + extract(hour FROM (modify_date-creation_date))*60*60 + extract(minute FROM (modify_date-creation_date))*60 + extract(second FROM (modify_date-creation_date))),4) MinTime,
  TRUNC(AVG(extract(DAY FROM (modify_date-creation_date))*24*60*60 + extract(hour FROM (modify_date-creation_date))*60*60 + extract(minute FROM (modify_date-creation_date))*60 + extract(second FROM (modify_date-creation_date))),4) AvgTime
FROM ORABPEL.CUBE_INSTANCE@dblink
WHERE creation_date >= sysdate-1/24
AND creation_date   <= sysdate
AND process_id NOT IN ('VehicleOneShotNonCallProcess')
GROUP BY state,
  process_id

ORDER BY MaxTime DESC



Total Active Sessions on MW DB Servers

select count(*) Total_DB_sessions 
from v$session@mydblink
where machine in ('myfullhostname1','myfullhostname2')

Total DB Sessions

select username,machine,to_char(sysdate,'MM-DD-RRRR HH:MI:SS') currentTime, count(*) Total_DB_sessions 
from v$session@mydblink   group by username,machine order by Total_DB_sessions desc





Categories: Development

Useful Queries in OEM12c PART-1

Arun Bavera - Thu, 2014-02-20 15:59
Weblogic Info
select "MGMT$WEBLOGIC_SERVER"."CM_TARGET_NAME" as "TARGET NAME",
"MGMT$WEBLOGIC_SERVER"."JAVAVMVENDOR" as "JVM VENDOR",
"MGMT$WEBLOGIC_SERVER"."JAVAVERSION" as "JAVA VERSION",
"MGMT$WEBLOGIC_SERVER"."BINARY_WEBLOGICHOME" as "WEBLOGIC HOME",
"MGMT$WEBLOGIC_SERVER"."BINARY_HOST" as "HOST"
 from "MGMT_VIEW"."MGMT$WEBLOGIC_SERVER" "MGMT$WEBLOGIC_SERVER"
 where "MGMT$WEBLOGIC_SERVER"."BINARY_WEBLOGICHOME" is not null
   and "MGMT$WEBLOGIC_SERVER"."BINARY_HOST" is not null

CPU Utilization Daily
select "MGMT$METRIC_DAILY"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_DAILY"."TARGET_TYPE" as "TARGET_TYPE",
"MGMT$METRIC_DAILY"."METRIC_NAME" as "METRIC_NAME",
"MGMT$METRIC_DAILY"."ROLLUP_TIMESTAMP" as "ROLLUP_TIMESTAMP",
"MGMT$METRIC_DAILY"."SAMPLE_COUNT" as "SAMPLE_COUNT",
"MGMT$METRIC_DAILY"."AVERAGE" as "AVERAGE",
"MGMT$METRIC_DAILY"."MINIMUM" as "MINIMUM",
"MGMT$METRIC_DAILY"."MAXIMUM" as "MAXIMUM",
"MGMT$METRIC_DAILY"."STANDARD_DEVIATION" as "STANDARD_DEVIATION"
 from "MGMT_VIEW"."MGMT$METRIC_DAILY" "MGMT$METRIC_DAILY"
where metric_name = 'Load'
 and column_label like '%CPU Utilization%'
 and rownum <= 20
 ORDER BY MGMT$METRIC_DAILY.MAXIMUM desc
 
CPU Utilization Current
select "MGMT$METRIC_CURRENT"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_CURRENT"."METRIC_NAME" as "METRIC_NAME",
"MGMT$METRIC_CURRENT".column_label,
   to_char("MGMT$METRIC_CURRENT".collection_timestamp,'dd-mm-yyyy HH:MI:ss') Collected,
   "MGMT$METRIC_CURRENT".key_value key_value,
   "MGMT$METRIC_CURRENT".key_value2 key_value2,
   ROUND("MGMT$METRIC_CURRENT".value,2) as "CPU%Load"
 from "MGMT_VIEW"."MGMT$METRIC_CURRENT" "MGMT$METRIC_CURRENT"
where metric_name = 'Load'
 and column_label like '%CPU Utilization%'
 and collection_timestamp > sysdate - 1/24
 and rownum <= 20

 --ORDER BY MGMT$METRIC_CURRENT.MAXIMUM desc

20 Most Common Alerts by Alert Count
       SELECT * from (
          SELECT
    metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,
             decode(alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
             count(alert_state) NEW_ID
          FROM mgmt$alert_history
          WHERE alert_state in('Critical','Warning')
            AND target_guid = ??EMIP_BIND_TARGET_GUID??
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
         GROUP BY metric_name,column_label,target_type,decode(ALERT_STATE,'Critical',25,'Warning',20)
         ORDER BY 5 DESC
       ) WHERE ROWNUM <21 p="">

Alerts History (Target)
Displays alerts for a target over the last 24 hours.

SELECT A.metric_label METRIC_GRP,A.column_label METRIC_ID,decode(A.alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID,
MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.alert_duration/24) END_TIME_ID,
(
ltrim(to_char(trunc(NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp ),'009'))||'d'
||':'||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24 ),24),'09'))||'h'
||':'||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60 ),60),'09'))||'m'
||':'||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60*60 ),60),'09'))||'s'
) DURATION_ID, A.message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B
       WHERE A.TARGET_GUID=B.TARGET_GUID AND A.target_guid = ??EMIP_BIND_TARGET_GUID??  AND A.alert_state in ('Critical','Warning')
       AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
            (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
              or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION))
            )
          )
       ORDER BY A.collection_timestamp      
     
   
Top 10 LUNs by Capacity

(SELECT nameT.value "Name", TO_NUMBER(sizeT.val) "Size" FROM
(SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name= 'LunDetails' AND metric_column= 'Name') nameT,
(SELECT key_value, TO_NUMBER(value) AS val FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='LunDetails' AND metric_column='AddressableCapacity' ORDER BY val DESC) sizeT
WHERE nameT.key_value = sizeT.key_value)

       PIE Chart:
       (SELECT nameT.value "Name", TO_NUMBER(sizeT.value) "Size" FROM (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name= 'LunDetails' AND metric_column= 'Name') nameT, (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='LunDetails' AND metric_column='AddressableCapacity') sizeT WHERE nameT.key_value = sizeT.key_value) UNION (SELECT remainingNameT.value "Name", TO_NUMBER(remainingSpaceT.value) "Size" FROM (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='SystemDetails' AND metric_column='SystemName') remainingNameT, (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='SystemDetails' AND metric_column='TotalRemainingSpace_GB') remainingSpaceT WHERE remainingNameT.key_value = remainingSpaceT.key_value)
     

Top 20 Heap Usage
(SELECT nameT.value "Name", TO_NUMBER(sizeT.val) "Size" FROM
(SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name like = 'Heap Usage%' AND metric_column= 'Name') nameT,
(SELECT key_value, TO_NUMBER(value) AS val FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name like = 'Heap Usage%' AND metric_column='AddressableCapacity' ORDER BY val DESC) sizeT
WHERE nameT.key_value = sizeT.key_value)


Select * from mgmt$metric_current where metric_name like  '%Heap%'

select distinct(metric_column),metric_name from mgmt$metric_current where metric_column like  '%Heap%'
and target_type='weblogic_j2eeserver'



Heap Usage
SELECT * from (
          SELECT
    target_name,metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,
             decode(alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
           alert_state,key_Value
          FROM mgmt$alert_history
          WHERE alert_state in('Critical','Warning')
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
            AND  target_type='weblogic_j2eeserver'
            AND  column_label like 'Heap Usage (%)'
         ORDER BY 5 DESC
       ) WHERE ROWNUM <100 p="">
SELECT * from (
          SELECT
    target_name,metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,round(Value,2) AS "HeapUsage%"
          FROM MGMT$METRIC_DETAILS
          WHERE target_type='weblogic_j2eeserver'
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
            AND  column_label like 'Heap Usage (%)'
         ORDER BY 5 DESC
       ) WHERE ROWNUM <100 p="">

metric_name=jvm_memory_usage
metric_column=Java Heap Used (MB)
--and target_name like '%EMGC_OMS1%'


Weblogic 100 Top Alerts:

SELECT * from (
          SELECT
    metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,
             decode(alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
             count(alert_state) NEW_ID
          FROM mgmt$alert_history
          WHERE alert_state in('Critical','Warning')
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
           AND  target_type='weblogic_j2eeserver'
         GROUP BY metric_name,column_label,target_type,decode(ALERT_STATE,'Critical',25,'Warning',20)
         ORDER BY 5 DESC
       ) WHERE ROWNUM <100 p="">
jvm Heap Usage (%) weblogic_j2eeserver 20 8




OutStanding Alerts: ALL
Summary
=======
SELECT 'METRIC_ALERTS_TXT_ID' ALERT_TYPE_ID,
        NVL(SUM(decode(ALERT_STATE,'Critical',1,0)),0) CRITICAL_ID,
NVL(SUM(decode(ALERT_STATE,'Warning',1,0)),0) WARNING_ID,
        'NA_TXT_ID' INFORMATIONAL_ID,
COUNT(UNIQUE target_guid) TARGETS_AFFECTED_ID from MGMT$ALERT_CURRENT B
where B.violation_type in('Resource','Threshold Violation')
AND B.ALERT_STATE in ('Critical','Warning')

Metric Alerts

SELECT A.TARGET_NAME TARGET_NAME_ID,A.TYPE_DISPLAY_NAME TARGET_TYPE_ID,
A.METRIC_LABEL METRIC_GRP, A.COLUMN_LABEL METRIC_ID,decode(A.ALERT_STATE,'Critical',25,'Warning',20) SEVERITY_ID,
MGMT_VIEW_UTIL.ADJUST_TZ(A.COLLECTION_TIMESTAMP,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
A.MESSAGE MESSAGE_ID from MGMT$ALERT_CURRENT A,MGMT$TARGET B where A.TARGET_GUID=B.TARGET_GUID AND
A.violation_type in('Resource','Threshold Violation') and A.ALERT_STATE in ('Critical','Warning')
order by collection_timestamp



Weblogic Metrics for BI-Pug Reports

select "MGMT$METRIC_DAILY"."AVERAGE" as "AVERAGE",
"MGMT$METRIC_DAILY"."METRIC_LABEL" as "METRIC_LABEL",
"MGMT$METRIC_DAILY"."COLUMN_LABEL" as "COLUMN_LABEL",
"MGMT$METRIC_DAILY"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_DAILY"."TARGET_TYPE" as "TARGET_TYPE",
"MGMT$METRIC_DAILY"."ROLLUP_TIMESTAMP" as "ROLLUP_TIMESTAMP"
 from "MGMT_VIEW"."MGMT$METRIC_DAILY" "MGMT$METRIC_DAILY"
 where "MGMT$METRIC_DAILY"."METRIC_LABEL" like '%Server Servlet%JSP Metrics%'
   and "MGMT$METRIC_DAILY"."COLUMN_LABEL" like '%Request Processing Time %'
   and "MGMT$METRIC_DAILY"."TARGET_NAME" like '%MDA%Managed%'
   and "MGMT$METRIC_DAILY"."TARGET_TYPE" like 'weblogic_j2eeserver'
   and "MGMT$METRIC_DAILY"."TARGET_GUID" =:list_of_wls_param
 
   list_of_wls_lov
   select "MGMT$TARGET"."TARGET_NAME" as "TARGET_NAME",
"MGMT$TARGET"."TARGET_GUID" as "TARGET_GUID"
 from "MGMT_VIEW"."MGMT$TARGET" "MGMT$TARGET"
 where "MGMT$TARGET"."TARGET_TYPE" like 'weblogic_j2eeserver'


 list_of_wls_param

 select "MGMT$METRIC_DAILY"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_DAILY"."TARGET_TYPE" as "TARGET_TYPE",
"MGMT$METRIC_DAILY"."METRIC_LABEL" as "METRIC_LABEL",
"MGMT$METRIC_DAILY"."COLUMN_LABEL" as "COLUMN_LABEL",
"MGMT$METRIC_DAILY"."AVERAGE" as "AVERAGE",
         "MGMT$METRIC_DAILY"."MINIMUM" as "MINIMUM",
"MGMT$METRIC_DAILY"."MAXIMUM" as "MAXIMUM",
"MGMT$METRIC_DAILY"."STANDARD_DEVIATION",
         TO_CHAR("MGMT$METRIC_DAILY"."ROLLUP_TIMESTAMP", 'DD-MON-YYYY') as "ROLLUP_TIMESTAMP"
 from "MGMT_VIEW"."MGMT$METRIC_DAILY" "MGMT$METRIC_DAILY"
 where "MGMT$METRIC_DAILY"."TARGET_TYPE" like 'weblogic_j2eeserver'
   and "MGMT$METRIC_DAILY"."METRIC_LABEL" like 'Server Servlet%JSP Metrics%'
   and "MGMT$METRIC_DAILY"."COLUMN_LABEL" like 'Request Processing Time%'
   and "MGMT$METRIC_DAILY"."TARGET_GUID" =:wls_server_param
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7
ORDER BY ROLLUP_TIMESTAMP


select "MGMT$TARGET"."TARGET_NAME" as "TARGET_NAME",
"MGMT$TARGET"."TARGET_GUID" as "TARGET_GUID"
 from "MGMT_VIEW"."MGMT$TARGET" "MGMT$TARGET"
 where "MGMT$TARGET"."TARGET_TYPE" like 'weblogic_j2eeserver'


 =================================================

 select "MGMT$WEBLOGIC_DOMAIN"."NAME" as "NAME",
"MGMT$J2EE_APPLICATION"."CM_TARGET_NAME" as "CM_TARGET_NAME",
"MGMT$J2EE_APPLICATION"."CM_TARGET_TYPE" as "CM_TARGET_TYPE"
 from "MGMT_VIEW"."MGMT$J2EE_APPLICATION" "MGMT$J2EE_APPLICATION",
"MGMT_VIEW"."MGMT$WEBLOGIC_DOMAIN" "MGMT$WEBLOGIC_DOMAIN"



select "MGMT$WEBLOGIC_DOMAIN"."NAME" as "NAME",
"MGMT$WEBLOGIC_DOMAIN"."CM_TARGET_GUID" as "CM_TARGET_GUID",
"MGMT$WEBLOGIC_DOMAIN"."CM_TARGET_NAME" as "CM_TARGET_NAME"
 from "MGMT_VIEW"."MGMT$WEBLOGIC_DOMAIN" "MGMT$WEBLOGIC_DOMAIN"


 ==========================================================

 OSB Error Metrics:
 SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


==================OSB METRICS=================================
OSB Proxy Service Response:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) AVG_RSP_TM,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_proxy_service'
AND METRIC_NAME              = 'ProxyServices_TC'
AND METRIC_COLUMN            = 'avgStat'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Proxy Service Err:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_proxy_service'
AND METRIC_NAME              = 'ProxyServices_TC'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

OSB Proxy Service Message:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) MSG_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_proxy_service'
AND METRIC_NAME              = 'ProxyServices_TC'
AND METRIC_COLUMN            = 'countStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Business Service Response:
SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) AVG_RESP_TM,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'avgStat'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Business Service Err:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

OSB Business Service Message:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) MSG_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'countStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Proxy Service:
select rawtohex(entity_guid) pxy_svc_guid, entity_name pxy_svc_nm, parent_me_name osb_name_pxy from mgmt$manageable_entities where entity_type = 'osb_proxy_service'

OSB Business Service:
select rawtohex(entity_guid) bus_svc_guid, entity_name bus_svc_nm, parent_me_name osb_name from mgmt$manageable_entities where entity_type = 'osb_business_service'

OSB Error Per Minute:
SELECT TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE = 'bea_alsb'
AND METRIC_NAME              = 'OSB_ProxyServices_rollup'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

OSB Req Per Min:
SELECT TARGET_NAME,
  ROUND(AVERAGE, 2) MSG_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE = 'bea_alsb'
AND METRIC_NAME              = 'OSB_ProxyServices_rollup'
AND METRIC_COLUMN            = 'countStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

Categories: Development

Snake handlers and their faith - why not go all the way?

FeuerThoughts - Mon, 2014-02-17 18:11
Just reading about another snake-handling pastor who died from a snake bite.

My feeling about these folks is: go for it. If you want to risk death for your beliefs, why not? It's not like there aren't enough people alive in the world to fill in any gaps you leave behind.

The CNN article mentions that people like Jamie Coots takes this passage from the Bible's Gospel of Mark literally:  “And these signs shall follow them that believe; In my name shall they cast out devils; they shall speak with new tongues; They shall take up serpents; and if they drink any deadly thing, it shall not hurt them; they shall lay hands on the sick, and they shall recover.”

Reading that, I just had to wonder: so if you really do take it literally, why do you only handle snakes? It seems like you should also drink bleach before you start your sermon. That is a deadly thing, so "it shall not hurt them," right?

Obviously the answer is: Wrong. It will kill these people each and every time. Without fail.

So why do they handle snakes but not drink bleach or give comfort to humans who are dying from a bacterial infection that cannot be stopped by antibiotics?

It seems pretty clear: snakes won't always bite them, and if they do, the bites are not always deadly. They are ready and willing to take a risk - and if they survive, they can attribute it to their spiritual purity, the hand of god, whatever.

The article also mentions that adherents to this selective faith 'say there are other spiritual reasons to handle serpents. Practitioners often describe it as a mental and emotional rush, as if they were touching the hand of God. "They almost always use drug metaphors, like 'higher than any high you can experience," said  Paul Williamson, a professor of psychology at Henderson State University in Arkansas who studies serpent handlers.'

Right and that, too: it gets them high.

Drinking bleach? I don't think you will get any sort of high from that.

I am agnostic when it comes to matters of religious faith and God. Maybe there's a God, maybe there isn't. So I don't really get religious people. But I do admire and respect people who stick to their faith and practice is without hypocrisy.

Categories: Development

"Are you a runner?"

FeuerThoughts - Mon, 2014-02-17 08:19
That's a question I get a lot.

I guess that's because I am not overweight and kind of tall....?

I can remember a few times when I really enjoyed running: both times they were more an adventure, in which running was the mode of transport. But generally, I have been bored by running so, no, I am not a runner.

In fact, over the past year, I have completely changed my views on exercise, and running is even less a part of what I do to stay healthy than ever before.

And for some reason I've decided to share my changed views with you. Maybe you will be interested. But at least when I am done, I can say: Good, I don't have to think about writing this anymore.

I'd been a member of Bally Fitness since 1988, when Oracle offered to subsidize membership in a fitness club (said subsidy rescinded two years later!).

But early in 2013 I decided that I had had enough of exercising on machines, inside sterile buildings, in order to stay fit. It's a pretty awful way to compensate for a sedentary life, when you think about. Moving your limbs through a series of limited, repetitive motions, while watching TV or listening to music or who knows what. That's not the same as living in the world, exercising our bodies as we evolved to live and thrive. Not even close.

So I quit Bally (by then acquired by LA Fitness, which promptly shut our closest club after the acquisition. Clever people) and now get my "exercise" by going outside for long walks, very occasionally a run, and most important of all (to me) fighting invasive species (buckthorn in Chicago, kudzu in Puerto Rico).

Cutting down trees, clearing brush, pulling out invasive sapling trees by their roots: great exercise! (so long as I do not injure myself, which I must confess has not been one of my strong points).

But I will admit that is not all I do. I am getting older each day, definitely well into middle aged now, and I am getting creaky. If I do not stretch, I am much more easily injured and I just don't feel good. Flexibility is a wonderful thing!

In addition, I have discovered the joy of a strong abdomen. I have found that as long as my "core" muscles are well maintained, I no longer have problems with lower back pain. In addition, I have noticed that I move around the world with greater confidence and (dare I say it?) grace - because my abdominals can easily support my torso as I move around.

So what does all that mean? That I do a set of abdominal exercises (crunches, situps, various things) each day.And a bunch of different stretches. Oh, and I've found that outdoor work involves lots of bicep activity, but not so much on the triceps. So I do some of that indoors, too (another key objective for me to stay in shape is to be able to play, hold, fly like airplanes, etc. the children dearest to me, and upper body strength is key for this).

Which brings me, really, to the basic point of this post (still with me?): I think that I am a fairly disciplined person, but I could never remember all the different stretches and exercises I want to do each day without a reminder. And I have found that unless I work from a checklist, it is all too easy to say "Aw, I don't feel like doing that one today."

So I put together a very simple grid that lists the things I want to do each day. I don't care about (for the most part) how many I do, just that I do it. And I don't beat myself up if I miss a day or a stretch. There's always tomorrow.





Categories: Development

Some help with the Oracle APEX Theming Competition 2014

Dimitri Gielis - Wed, 2014-02-12 15:07
ODTUG announced a few days ago the Oracle APEX Theming Competition.

Make an awesome APEX theme and you may win some cool prices!

In the Learning Resources on the ODTUG site you will see a reference to my presentation of last year "Advanced Responsive Design in APEX". In that presentation I showed a lot of live demo's from different aspects of Responsive Design: here's a quick overview of what I covered:


I made the RWD demo app available online now, so you can see it in action or you can download the app and install it in your environment (the zip includes the presentation too).

Hopefully it will help some people doing a responsive design theme.

Another tip that might help; make use of SASS or LESS to generate the CSS. There's a great SASS tutorial (video/material) on SASS from Hampton Catlin, the creator of Sass, which explains why and how to use it.

I look forward to the result of the competition. I'm always impressed with what people come up with.
Categories: Development

The evolution of APEX Evangelists

Dimitri Gielis - Tue, 2014-02-11 05:22
Every year we at APEX Evangelists come together to discuss the past and the future of the company.
In the past 7 years a lot has changed; the APEX market changed, the community changed, we personally changed, ...

We really like APEX, we are all passionate about it, we love to have technical discussions with each other and come together to share our thoughts, but we also like our independence and make our own trade-offs company wise.
So we decided to go a different route with "APEX Evangelists". And that’s why we’re now in the midst of transforming APEX Evangelists from a company to a network.
A network of APEX experts. A network without any financial incentive behind it. A network of independent people that are passionate about APEX. A network of recognised experts in the Oracle community. A network with members that can rely on each other and trust each other.


You can also become part of this network, but only by invitation and acceptance by the other members.
We love to compare us with the Oaktable network, which is a network of experts for the Oracle database. We hope that "APEX Evangelists network" will be the same for Oracle Application Express.
The first addition to the APEX Evangelists network is Martin Giffy D’Souza and we hope to grow the network further in the next months.

The new website of APEX Evangelists is live which reflects the network now, you find it at http://www.apex-evangelists.com

If you're wondering what else the future will bring for me, check out APEX R&D, the company I'll move forward with.
Categories: Development

Oracle 12c: scalar subqueries

XTended Oracle SQL - Mon, 2014-02-10 15:10

We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):
Spoiler:: Before 12.1 SelectShow

SQL> explain plan for
  2  select
  3  (select count(*) from XT_TEST) cnt
  4  from dual;

Explained.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2843533371

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
|   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

10 rows selected.


Spoiler:: 12.1
SelectShow

SQL> alter session set "_optimizer_unnest_scalar_sq"=false;

Session altered.

SQL> explain plan for
  2  select
  3  (select count(*) from XT_TEST) cnt
  4  from dual;

Explained.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2843533371

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    52   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
|   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

10 rows selected.


But it’s interesting that correlated subquery can reference now to a column from parent tables more
than one level above:
Spoiler:: Before 12.1
SelectShow

SQL> with t1 as (select/*+ materialize */ 1 a from dual)
  2      ,t2 as (select/*+ materialize */ 2 b from dual)
  3      ,t3 as (select/*+ materialize */ 3 c from dual)
  4  select
  5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
  6  from t1;
  (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
                                                       *
ERROR at line 5:
ORA-00904: "T1"."A": invalid identifier


Spoiler:: 12.1
SelectShow

SQL> with t1 as (select/*+ materialize */ 1 a from dual)
  2      ,t2 as (select/*+ materialize */ 2 b from dual)
  3      ,t3 as (select/*+ materialize */ 3 c from dual)
  4  select
  5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
  6  from t1;

         S
----------
         6

SYS_OP_MAP_NONNULL is in the documentation now

XTended Oracle SQL - Mon, 2014-02-10 14:24

Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views

Lazy tip: By the way, with length limitations, we can also use documented dump function:

SQL> with
  2    t(a,b) as (
  3               select *
  4               from table(ku$_vcnt(null,'FF','A'))
  5                   ,table(ku$_vcnt(null,'FF','B'))
  6              )
  7  select
  8      a,b
  9     ,case when sys_op_map_nonnull(a) = sys_op_map_nonnull(b) then '=' else '!=' end comp1
 10     ,case when dump(a,1017)          = dump(b,1017)          then '=' else '!=' end comp2
 11     ,sys_op_map_nonnull(a) s_o_m_n_a
 12     ,sys_op_map_nonnull(b) s_o_m_n_b
 13     ,dump(a,  17) dump_a
 14     ,dump(b,  17) dump_b -- it is preferably sometimes to use 1017 - for charset showing
 15  from t;

A     B     COMP1 COMP2 S_O_M_N_A  S_O_M_N_B  DUMP_A                DUMP_B
----- ----- ----- ----- ---------- ---------- --------------------- ---------------------
            =     =     FF         FF         NULL                  NULL
      FF    !=    !=    FF         464600     NULL                  Typ=1 Len=2: F,F
      B     !=    !=    FF         4200       NULL                  Typ=1 Len=1: B
FF          !=    !=    464600     FF         Typ=1 Len=2: F,F      NULL
FF    FF    =     =     464600     464600     Typ=1 Len=2: F,F      Typ=1 Len=2: F,F
FF    B     !=    !=    464600     4200       Typ=1 Len=2: F,F      Typ=1 Len=1: B
A           !=    !=    4100       FF         Typ=1 Len=1: A        NULL
A     FF    !=    !=    4100       464600     Typ=1 Len=1: A        Typ=1 Len=2: F,F
A     B     !=    !=    4100       4200       Typ=1 Len=1: A        Typ=1 Len=1: B

9 rows selected.
Categories: Development

OEM12c: Searching a Target without a LifeCycle status property

Arun Bavera - Mon, 2014-02-10 11:16
Steps:

Enterprise->Configurations->Search

Create->
Target Type->Choose Host
Click Properties to add a  property and choose
PropertyName  "Contains"  LifeCycleStatus

Click "advanced Options"  Choose "NOT EXISTS"

Save as " Host_LifeCycle_Null"

SQL for searching Production hosts with Null LifecycleStatus:
-------------------------------------------------------------------------------
SELECT
        s1.target_name
FROM
        MGMT$ECM_CURRENT_SNAPSHOTS s1gen1,
        MGMT$TARGET s1
WHERE
        (
                s1gen1.TARGET_GUID = s1.TARGET_GUID
            AND
                (
                        NOT EXISTS
                        (
                                SELECT
                                        s2.PROPERTY_NAME
                                FROM
                                        CM$EM_TPROPS_ECM_VIEW s2
                                WHERE
                                        (
                                                s1gen1.ECM_SNAPSHOT_ID = s2.ECM_SNAPSHOT_ID
                                            AND
                                                (
                                                        (
                                                                NLS_UPPER( s2.PROPERTY_NAME) LIKE NLS_UPPER('%'
                                                                ||'orcl_gtp_lifecycle_status'
                                                                ||'%')
                                                        )
                                                )
                                        )
                        )
                )
            AND s1.TARGET_TYPE       = 'host'
            AND s1gen1.SNAPSHOT_TYPE = 'orcl_tp_config'
            AND UPPER(s1.target_name) LIKE '%P.DOMAINNAME%'
        )
-------------------------------------------------------------------------------
Categories: Development

What programming languages do I use?

FeuerThoughts - Fri, 2014-02-07 11:04
This month's Roundtable discussion on the PL/SQL Challenge is:

If you are on this website, you almost certainly know PL/SQL and SQL. What other programming languages do you currently use? How do you find they compare to PL/SQL and SQL?

We have been playing quizzes at the PL/SQL Challenge for several years; some of you almost seem like old friends to me by now. It is always interesting to find out more about players. Let's start from the professional side (perhaps the next Roundtable can explore our personal lives. :-) ).

  • What language do you use most of all in your work? Do you consider this your primary language or just the one you have to spend the most time with?
  • What languages do you use now? Preferably this would mean you used the language on a "real" project in 2013.
  • What languages do you plan to learn? Why? Do you need it for your job or do you simply want to expand your horizons?
Here's the answer I posted there today:

What technologies to I use?

I suppose everyone on this site knows the answer to this question:

1. PL/SQL
2. SQL (in a rather ignorant fashion, relative to my level of expertise in PL/SQL)
3. HTML (I know, I know, it's not a programming language, but I can't have just two languages. Too embarrassing)

Years ago, I was a FORTRAN programmer.

I happened to get a part-time programming job in a University of Rochester research lab and lo and behold! Time to learn FORTRAN.

So I did, and used that knowledge to get jobs all the way through 1986. FORTRAN in a bank, Fortran in a pharmaceutical company, FORTRAN in an insurance company.

But, fortunately, as I moved through various FORTRAN jobs, I also started to work with these strange things named databases - on DEC "mainframes" like DEC10s and DEC20s.

Which then allowed me to think I might be able to work for Oracle with their even newer relational databases. That was a pre-sales job (standing up in front of groups of people and showing them how amazing SQL joins were!), but fortunately I arrived just in time to welcome SQL*Forms 3 and PL/SQL. Ah! A nice easy language that even I could "master"! (I only took three programming classes in college, all "101" courses on Algol, Lisp and something else....)

I suppose I should learn some new technologies. Ruby on Rails sounds very cool - the name, I mean. I don't know anything about the language itself. Python? How fun is that?

I should learn new stuff...but, heck, I am 55. I have spent a very large percentage of the last 35 years in front of a computer or talking to other people about how to work best in front of a computer.

I'd rather learn other new stuff, so for the past year I have been intensively studying evolution. How truly incredible and amazing! Now there's a "language" that blows my mind: The coding in DNA is mind-boggling. The irrefutable and compelling logic of evolution is astonishing.

If you have not read about evolution lately (and certainly almost anything you learned in school was both superficial and is now out of date), I strongly encourage you to check out:

Your Inner Fish, Neil Shubin
The Beak of the Finch, Jonathan Weiner
The Darwinian Tourist, Christopher Wills
Why Evolution is True, Jerry Coyne


Categories: Development

A Letter from "Oyster Shel"

FeuerThoughts - Fri, 2014-02-07 06:49
My father, Sheldon Feuerstein, was a large presence in our lives and the lives of many others. He was a big man, physically (and, sadly, overweight for too many of his later years, which contributed to the diseases that led to his death in 2010), but it was more than that. He was smart and honest and eloquent. He could also be very stern and, before he passed middle age, manifested quite the temper. But he was a deeply compassionate man, especially for members of the family. Many nieces, nephews and cousins remember him with a love and deep fondness that sometimes surprises me.

Who knew that the man who insisted I throw away and redo my math homework because it had an erasure, who sent me up to bed without dinner because I did or wouldn't do [whatever], who demanded that I mow the 2/3 acre of a lawn even though I was wracked by allergies, could be so warmly encouraging and loving to my cousins? Ah, humans - such complicated creatures! :-)

I visited my mom in Florida last week (oh, the green! the sun! the warmth!) and we looked through my dad's stamp collection and many old papers stored in the safety deposit box.

Most of it was the "usual" - birth, death and marriage certificates, honorable discharge papers from the military (Dad was always disappointed that all he did as a soldier was move around to different bases in the US, never fought, served overseas. That's probably why he so looked up to his great Irving Effron, who was a Jewish Marine (quite unusual) and served in the Pacific. Check out this great NY Times letter about him - subscription probably required), and so on.

But then inside a small, old envelope, we found a real gem: a letter Dad had written to his oldest sister, Belle, and her husband, Max, just before he was going to be Bar Mitzvahed:

 

I especially love the line: "where would you stick Jeffrey?" The idea of "sticking" Jeffrey (another very large human being in multiple senses, and just thirtenn years or so younger than my dad) anywhere is very amusing....

Love you, Dad! Miss you, Dad!




Categories: Development

APEX 5.0 (EA): CSS Calendar

Dimitri Gielis - Fri, 2014-02-07 04:54
APEX 5.0 allows you to create a new Calendar type, called CSS Calendar.
The CSS Calendar is based on Full Calendar 1.6.4, at the moment of writing, the current latest production release. The creator of this JQuery plugin, Adam Shaw, is currently working on version 2. A few days ago he released beta 2. It remains to be seen which version of Full Calendar will make it in the final release of APEX 5.0.
Following the wizard in APEX allows us to add a Source for the calendar.You can just enter a SQL query here.

In the next step you define which column contains your start date, the end date etc.


There are some nice features that come with Full Calendar, for example the integration with Google Calendar and JSON (APEX WebService URL). Another interesting feature is the export to different formats like iCal and CSV.

Finally running the page shows you the new CSS Calendar. You can change the color scheme of the calendar by changing the css.


This CSS calendar is also responsive... when you make your screen smaller the calendar automatically switches to the Week view.


The only thing that's missing with this calendar is a year view... there are people who created extensions for example here and here to offer the year view. Hopefully version 2 of FullCalendar will add the year view native. Wonder if the final release of APEX 5.0 will include the year view too...

Categories: Development

APEX 5.0 (EA): Feedback Page

Dimitri Gielis - Fri, 2014-02-07 00:23
One of the first things I do when creating a new application is enabling the feedback mechanism in combination with Team Development.
Click the Create Page button in Application Builder and select "Feedback Page":

The next step looks like this:


The Page Mode can now be set to Modal, so instead of a popup it will render as a modal.

Note that in the EA1 version there are some known issues with the Feedback Page, which will be resolved in one of the next iterations of EA.

One issue made me think that once I upgraded my applications to APEX 5.0 I probably want to start searching for "apex.navigation.popup.url" and change those to become the native APEX 5.0 modal windows. It's those "small" things like declarative modal windows which make a big difference in development effort and clean code. But more on modal windows in a dedicated post.

I love the Feedback mechanism and how it integrates with Team Development, but if it had one more feature, I would be thrilled. It's great to know session state, it's great people can add a comment, but sometimes a picture shows more than a 1000 words. So if we could click a button that took a screen capture and we could annotate that... wouldn't that be awesome? The result would be an image with the feedback in Team Development.

Some of us in the community build some custom solutions to do that. For example Martin and I "hacked something together" for the OSN-competition at OOW which did the screen capture and annotation, but having such a feature native in APEX and integrated with Team Development would be even better :)
Categories: Development

APEX 5.0 (EA): Creating a new application

Dimitri Gielis - Wed, 2014-02-05 04:12
Lets see how creating a new Database application works in APEX 5.0 and what was changed compared to APEX 4.2.

In the Application Builder you start by hitting the Create button.


There are a couple of improvements here:

  • You'll see APEX 5.0 includes a complete new set of icons which are flat and fresh and immediately give you an insight what the button is doing. This is throughout the entire release.
  • The icons are now driven by base64 encoded strings in css which is more performant than to call every icon as a separate image (what happened prior APEX 5.0).
  • There's a direct link to create a Mobile application

Next we will define the name and id of our application


The improvements here lay in:

  • The cleanup of the screen; some options are now moved to an advanced attributes section
  • You select the theme already at this stage instead of waiting till the end
  • The default theme is now "Blue Responsive (Navigation List)", which makes a lot of sense as its inline with the industry standards to make responsive applications. The "Navigation List" replaces the "Tabs" of before. Personally I already used Lists to do the navigation in 99% of the cases in APEX 4.x, as they are way more flexible than Tabs, and if you want you can give the List the look and feel of a Tab anyway.
  • New is the "Theme Style" which has currently two options: Default and Red. So the themes in APEX 5.0 will be easier to change based on a style. The main HTML in the templates stays the same, but with a different css you get another look and feel.

Moving on in the wizard gives the possibilities to add pages to the application.


Improvements lay in:

  • The look and feel
  • More intuitive icon in front of the page to edit the page (see next screenshot). In APEX 4.x you could edit the page to by clicking on the name of the page. That gave a popup window, where in APEX 5.0 it's implemented as a modal window. This is a good showcase of the new Modal window feature of APEX 5.0 which you can define declarative now (more on that in another post).


The final screen gives an overview of the application and what APEX will create.


As you can see, already in the creation of a database application in APEX 5.0 many new features and small enhancements are included.
Categories: Development

Configure Coherence HotCache

Edwin Biemond - Tue, 2014-02-04 22:29
Coherence can really accelerate and improve your application because it's fast, high available, easy to setup and it's scalable. But when you even use it together with the JCache framework of Java 8 or the new Coherence Adapter in Oracle SOA Suite and OSB 12c it will even be more easier to use Coherence as your main HA Cache.  Before Coherence 12.1.2 when you want to use Coherence together with

APEX 5.0 (EA): Overview

Dimitri Gielis - Tue, 2014-02-04 06:16
Now that APEX 5.0 EA is out, it's time to do a deep dive in the new features of this early adopter (EA) release.

Overall this new version of Oracle Application Express tries to put even more power and speed in the development. The UI is made simpler and cleaner with a fresher look and feel.
Not every part of APEX got an overhaul in EA1, it remains to be seen if in EA2 more pages are updated.

I'll update this post with links to the more detailed blog posts of the specific parts.




So stay tuned... this post is the index for the next series of APEX 5.0 (EA) posts.
Categories: Development

APEX 5.0 - New Features - Sub Region - Region Position

Denes Kubicek - Mon, 2014-02-03 01:45
One of the first things I checked in APEX 5.0 EA was the region positioning of the sub regions. I was positively surprised to see that I can now position the regions next to each other as well. In earlier versions the only option was underneath. Having this possibility it will be much easier to create complex forms where you need to take care of positioning multiple items in more than one column. At the moment this is doable but somewhat tricky.

Categories: Development