Development
Formspider Authentication and Authorization Repository for PL/SQL
We are very excited to announce the release of Formspider Desktop 1.7. FS Desktop 1.7 includes one of the most requested features by our customers.Built-In Security RepositoryFS Desktop 1.7 comes with an integrated authentication and authorization repository. Formspider developers are now able to secure both data and UI elements and grant access rights to users directly from the Formspider IDE.
Formspider Security Highlights:- Security both at data and UI layer.
Grant read and write access at the query, column and row level. Control editable, enable, visible attributes of every UI component. - Security aware UI components.
Formspider automatically detects the read&write privileges defined at the data layer and restricts access to data bound UI objects. - Incredibly easy to manage.
Build and maintain your security model quickly with the point and click graphical user interface. - Create reports on your security model easily with SQL.
No LDAP, no XML, no files. Formspider security model is implemented in relational database tables.
Oracle APEX Cookbook : Second Edition
Useful Queries in OEM12c PART-3
FROM OEM12c Views
Total BPEL Instances in Last 1 Hour
SELECT max(value) as TotalInvocations , sysdate-2/24 Start_Time,sysdate-1/24 End_Time --((??EMIP_BIND_END_DATE??)-2/24) Start_Time, ((??EMIP_BIND_END_DATE??)-1/24) End_Time
FROM mgmt$metric_details
WHERE target_name like '/PROD%'
AND collection_timestamp >= sysdate-2/24
AND collection_timestamp <= sysdate-1/24
AND metric_column = 'compositeInstances.delta'
SOA SERVICES PERFORMANCE MONITOR - PRD - HOURLY
select Process_Name,to_number(Invocations,'9999999999999') as NUM_INSTANCES,round(to_number(Invocations,'9999999999999')/60,4) as THRUPUT_PER_MIN from (
SELECT SUBSTR(target_name, INSTR(target_name,'/', -1, 1)+1) AS Process_Name,
max(value) as Invocations
FROM mgmt$metric_details
WHERE target_name like '/PROD%'
AND collection_timestamp >= sysdate-2/24 --TO_DATE('3/5/2014 1:00:03 PM','MM/DD/YYYY HH:MI:SS AM') --((??EMIP_BIND_END_DATE??)-2/24)
AND collection_timestamp <= sysdate-1/24 --TO_DATE('3/5/2014 2:00:03 PM','MM/DD/YYYY HH:MI:SS AM')--((??EMIP_BIND_END_DATE??)-1/24)
AND metric_column = 'compositeInstances.delta'
GROUP BY SUBSTR(target_name, INSTR(target_name,'/', -1, 1)+1)
ORDER BY to_number(Invocations,'9999999999999') desc
)
where to_number(Invocations,'9999999999999')>0
ORDER BY to_number(Invocations,'9999999999999') desc
FROM Direct Database link or Metric Extension:
Total SOA11g Composite Instances in Last 1 Hour
SELECT COUNT(*) AS TOTAL_NUM_INSTANCES,((sysdate)-2/24) Start_Time, ((sysdate)-1/24) End_Time
FROM MY_soainfra.CUBE_INSTANCE@MY_SOA_DBLINK
WHERE MY_soainfra.CUBE_INSTANCE.CREATION_DATE >= (sysdate)-2/24
AND MY_soainfra.CUBE_INSTANCE.CREATION_DATE <= (sysdate)-1/24
AND MY_soainfra.CUBE_INSTANCE.state in (5,6)
SOA11g-SERVICES PERFORMANCE MONITOR - PRD - HOURLYSELECT PROCESS_NAME,
DOMAIN_NAME,
/* 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,
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_ANOTHER,
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,
CUBE_INSTANCE.DOMAIN_NAME,
CUBE_INSTANCE.component_name PROCESS_NAME,
CUBE_INSTANCE.CIKEY CIKEY,
CUBE_INSTANCE.CREATION_DATE INSTANCE_START,
CUBE_INSTANCE.MODIFY_DATE ACTIVITY_END,
DECODE(CUBE_INSTANCE.STATE,5,1,0) AS SUCCESS_STATE,
CASE WHEN CUBE_INSTANCE.STATE = 6 THEN 1 ELSE 0 END AS FAULTED_STATE,
CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE,
CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 00:00:02.000000' THEN 1 ELSE 0 END AS GT_2SEC,
CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:05.000000' THEN 1 ELSE 0 END AS GT_5SEC,
CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:15.000000' THEN 1 ELSE 0 END AS GT_15SEC,
CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:30.000000' THEN 1 ELSE 0 END AS GT_30SEC
FROM MY_soainfra.CUBE_INSTANCE@MY_SOA_DBLINK
WHERE CUBE_INSTANCE.CREATION_DATE >= ((??EMIP_BIND_END_DATE??)-2/24)
AND CUBE_INSTANCE.CREATION_DATE <=((??EMIP_BIND_END_DATE??)-1/24)
-- and component_name='SalesOrderEBS'
AND CUBE_INSTANCE.state in (5,6)
ORDER BY CUBE_INSTANCE.CREATION_DATE ASC)
GROUP BY PROCESS_NAME, DOMAIN_NAME
ORDER BY NUM_INSTANCES desc
List all metric names for SOA Composite targets
select distinct metric_name from mgmt$metric_hourly where target_type='oracle_soa_composite';
List the names of all of the SOA Composite targets
select distinct target_name from mgmt$metric_hourly where target_type='oracle_soa_composite';
List the metric columns for the soainfra_composite_label_rollup_by_revision metric
select distinct metric_column from mgmt$metric_hourly where target_type='oracle_soa_composite' and metric_name='soainfra_composite_label_rollup_by_revision';
select distinct metric_column from mgmt$metric_hourly where target_type='soa_composite_component'
select distinct metric_column from mgmt$metric_details where target_type='soa_composite_component'
select distinct metric_column from mgmt$metric_hourly where target_type='soa_composite_service'
select distinct metric_column,target_type from mgmt$metric_hourly where target_type like '%soa%'; --and metric_name='soainfra_composite_label_rollup_by_revision';
select distinct target_type from mgmt$metric_hourly where target_type like '%soa%';
Single SQL vs SQL+PL/SQL
Everyone knows Tom Kyte’s mantra:
You should do it in a single SQL statement if at all possible.
But we all know that “Every rule has an exception”
There are many different cases when pl/sql with sql can be more efficient than only sql, and i dont want to catalog them. I just want to show a couple examples of such exceptions:
Simple example from forum:
select dt,
dim1,
dim2,
val,
sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum,
sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum,
sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum
from mg_t
order by dt;
This query will be very hard for big data sets, so we can do it efficiently with pl/sql:
create or replace function xf_to_drop return xt2_to_drop pipelined
is
type tt is table of number index by pls_integer;
type tt2 is table of tt index by pls_integer;
dim1_c tt;
dim2_c tt;
dim12_c tt2;
begin
for r in (
select dt,
dim1,
dim2,
val
from mg_t
order by dt
)
loop
dim1_c(r.dim1):=case when dim1_c.exists(r.dim1) then dim1_c(r.dim1) else 0 end + r.val;
dim2_c(r.dim1):=case when dim2_c.exists(r.dim1) then dim2_c(r.dim1) else 0 end + r.val;
dim12_c(r.dim1)(r.dim2):=case
when dim12_c.exists(r.dim1)
and dim12_c(r.dim1).exists(r.dim2)
then dim12_c(r.dim1)(r.dim2)
else 0
end + r.val;
pipe row (xt1_to_drop( r.dt
,r.dim1
,r.dim2
,r.val
,dim1_c(r.dim1)
,dim1_c(r.dim1)
,dim12_c(r.dim1)(r.dim2)
));
end loop;
end;
/
Spoiler:: Full example
SelectShow
create table mg_t as
select trunc(sysdate) + level/1440 dt,
trunc(3 * dbms_random.value()) dim1,
trunc(3 * dbms_random.value()) dim2,
trunc(100 * dbms_random.value()) val
from dual
connect by level <= 3e6;
create type xt1_to_drop is object(
dt date
,dim1 number
,dim2 number
,val number
,dim1_cumulative_sum number
,dim2_cumulative_sum number
,dim1_dim2_cumulative_sum number
);
create type xt2_to_drop as table of xt1_to_drop;
create or replace function xf_to_drop return xt2_to_drop pipelined
is
type tt is table of number index by pls_integer;
type tt2 is table of tt index by pls_integer;
dim1_c tt;
dim2_c tt;
dim12_c tt2;
begin
for r in (
select dt,
dim1,
dim2,
val
from mg_t
order by dt
)
loop
dim1_c(r.dim1):=case when dim1_c.exists(r.dim1) then dim1_c(r.dim1) else 0 end + r.val;
dim2_c(r.dim1):=case when dim2_c.exists(r.dim1) then dim2_c(r.dim1) else 0 end + r.val;
dim12_c(r.dim1)(r.dim2):=case
when dim12_c.exists(r.dim1)
and dim12_c(r.dim1).exists(r.dim2)
then dim12_c(r.dim1)(r.dim2)
else 0
end + r.val;
pipe row (xt1_to_drop( r.dt,r.dim1,r.dim2,r.val,dim1_c(r.dim1),dim1_c(r.dim1),dim12_c(r.dim1)(r.dim2)));
end loop;
end;
/
exec for r in (select * from table(xf_to_drop)) loop null; end loop;
2. Finding connected components
Assume that we have big table with many-to-many relationship:
create table test (clientid NUMBER(10), accountid NUMBER(10));
How we can find all connected groups?
This example also taken from our russian forum and there was very good and simple sql-only solution, but it’s not efficient on big data sets:
select min(group_member_id) as group_max_id, accountid, clientid
from (select clientid as group_member_id
, connect_by_root accountid as accountid
, connect_by_root clientid as clientid
from test
connect by nocycle decode(accountid, prior accountid, 1, 0)
+ decode(clientid, prior clientid, 1, 0)
= 1
) a
group by accountid, clientid
order by group_max_id, accountid
/
We can try to remember algorithms courses and adopt one of the several algorithms for connected components:
Spoiler:: Based on weighted quick-union algorithm
SelectShow
declare
type int_array is table of pls_integer index by pls_integer;
type arr_elems is table of sys.ku$_objnumset index by pls_integer;
root int_array;
root_elems arr_elems;
n int;
clients int_array;
accounts int_array;
l integer:=dbms_utility.get_time();
procedure print(v in varchar2) is
begin
dbms_output.put_line(to_char((dbms_utility.get_time-l)/100,'0999.99')||' '||v);
l:=dbms_utility.get_time();
end;
function get_root(n int) return pls_integer is
begin
if root.exists(n) then
return root(n);
else
return null;
end if;
end;
procedure update_root(old_root pls_integer,new_root pls_integer) is
i pls_integer;
elem pls_integer;
cnt_old pls_integer;
cnt_new pls_integer;
begin
if old_root!=new_root then
--root_elems(new_root):=root_elems(new_root) multiset union all root_elems(old_root);
cnt_old:=root_elems(old_root).count;
cnt_new:=root_elems(new_root).count;
root_elems(new_root).extend(cnt_old);
for i in 1..cnt_old
loop
elem := root_elems(old_root)(i);
root(elem):=new_root;
root_elems(new_root)(cnt_new+i):=elem;
end loop;
root_elems(old_root).delete;
end if;
end;
procedure add_elem(p_root pls_integer, p_elem pls_integer) is
begin
if not root_elems.exists(p_root) then
root_elems(p_root):=sys.ku$_objnumset(p_elem);
else
root_elems(p_root).extend();
root_elems(p_root)(root_elems(p_root).count):=p_elem;
end if;
end;
procedure add_link(clientid pls_integer,accountid pls_integer) is
r1 pls_integer;
r2 pls_integer;
new_root pls_integer;
begin
r1:=get_root(clientid);
r2:=get_root(accountid);
if r1 is null or r2 is null then
new_root := coalesce(r1,r2,clientid);
if r1 is null then add_elem(new_root,clientid ); root(clientid) :=new_root; end if;
if r2 is null then add_elem(new_root,accountid); root(accountid):=new_root; end if;
else
new_root := least(r1,r2);
root(clientid) :=new_root;
root(accountid):=new_root;
update_root(greatest(r1,r2),new_root);
end if;
end;
function str_format(p int) return varchar2 is
begin
return utl_lms.format_message('(%d, %d) = group #%d'
,clients(p)
,accounts(p)
,get_root(clients(p))
);
end;
begin
print('start');
select clientid,accountid
bulk collect into clients,accounts
from test
-- where rownum<=1000
;
print('fetched');
n:=clients.count;
dbms_output.put_line('count='||n);
for i in 1..n loop
add_link(clients(i),accounts(i));
end loop;
print('processed');
---
/*
for i in 1..n loop
dbms_output.put_line(str_format(i));
end loop;
-- */
end;
We can also try even more interesting special algorithms for parallel processing: CONNECTED COMPONENTS ALGORITHMS
FOR MESH-CONNECTED PARALLEL COMPUTERS
APEX Listener => Oracle REST Data Services
Yesterday a new version of the Oracle APEX Listener was released. The new version is 2.0.6... and the biggest change in this release is the change in name. From now on we won't speak about the "APEX Listener" anymore, but we speak about "Oracle REST Data Services".
The name explains more what the piece of software is doing and it will be less confusing for non-APEX people. Many people thought about the Database Listener when talking about the APEX Listener. Or others just wanting REST, don't question the use of APEX anymore.
The "Oracle REST Data Services" still carries the same purpose for us as APEX developers:
- serve as a replacement for the (older) mod_plsql
- enable the RESTful Services in APEX (found in SQL Workshop and Application wizard)
Is Evolution Irrefutable and Compelling?
The current discussion (well, sharing, really) asks players to share the programming languages with which they work.
In part of my answer, I wrote:
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
To which one player responded:
==> [The irrefutable and compelling logic of evolution is astonishing.] <==
Is religious zeal allowed on this site? If it is, I am very happy to hear. I have tons of it. Irrefutable huh? Huh. Sounds like religious zeal to me. Please let me know!
I responded in part as follows:
The irrefutable and compelling logic of evolution is astonishing to those of us who use and celebrate science to understand the world and live within that world. Evolution is accepted as fact within the scientific community (which is not to say there aren't a few scientists here and there who reject it, I suppose) and is demonstrated in virtually every branch of science active today.
and offered to start a discussion on my personal blog, where it would be more appropriate to delve into our different opinions about evolution.
So here it is! I look forward to at least a response from Mike to get this going, and I would ask those who submit a post to tell me what books or articles by scientists that document the evolutionary process have you read. I am not asking if you believed any of it, but simply whether (and which) you have exposed yourself to this information.
Friday prank: select from join join join
Valid funny queries 
select the join from join join join using(the,some)
/
select some join from left join join right using(some,the)
/
select 1 join from join join join join join using(the) on 1=1
/
select the some from join
where the=some( the(select some from join)
,the(select the from join)
)
/
Spoiler:: tables
SelectShow
:) Little addition Another one ….
create table join as select 1 some,1 the from dual;
create table left as select 1 some,1 the from dual;
create table right as select 1 some,1 the from dual;
update two tables set join=2;
select join from two tables;
select first, second, random from two tables join three tables on 1=1;
select first
, case when the=some(some,some) then join end true
from two tables join three tables using(random);
and…select random columns from two tables join four tables on the=some(some,some);
ODTUG is my favorite Oracle User Group!
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!
Inlist iterator again
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.
By default we can get only bad plan with concatenation and 2 full table scans for this query: I have tried several different options and found one interesting thing: 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). Notice that join with “t2″ was eliminated(also we can get join elimination of X). Unfortunately “TABLE ACCESS BY USER ROWID” was replaced by “TABLE ACCESS FULL”. It’s quite good solution, but there is a big overhead because of many objects creations.
Note that you have to avoid duplicates if t1.a = t2.b.
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;
/
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.
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)
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"))
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)
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$))
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"))
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
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
Useful Queries in OEM12c PART-1
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
Snake handlers and their faith - why not go all the way?
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.
"Are you a runner?"
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.
Some help with the Oracle APEX Theming Competition 2014
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.
The evolution of APEX Evangelists
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.
Oracle 12c: scalar subqueries
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
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.
OEM12c: Searching a Target without a LifeCycle status property
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%'
)
-------------------------------------------------------------------------------
What programming languages do I use?
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?
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
A Letter from "Oyster Shel"
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!