Created
August 7, 2018 23:51
-
-
Save karlarao/9eb0d05fdb680db4bb6153e4a23c9bac to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with temp as | |
( | |
select 108 Name, 'test' Project, 'Err1,Err2,Err3' Error from dual | |
union all | |
select 109, 'test2', 'Err1' from dual | |
) | |
select distinct | |
t.name, t.project, | |
trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) as error | |
from | |
temp t, | |
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels | |
order by name | |
/ | |
-- working version | |
select | |
REGEXP_SUBSTR (trim(regexp_substr(t.tables_accessed, '[^,]+', 1, levels.column_value)) , '[^.]+', 1, 1) as owner , | |
REGEXP_SUBSTR (trim(regexp_substr(t.tables_accessed, '[^,]+', 1, levels.column_value)) , '[^.]+', 1, 2) as table_name, | |
test_name, | |
test_type, | |
test_category, | |
test_notes | |
from | |
( | |
select aa.*, cc.sqlid, bb.tables_accessed | |
from | |
( | |
select | |
test_name, | |
test_type, | |
test_category, | |
test_notes, | |
end_snap, | |
SUM(CASE WHEN name = 'secs - elapsed time' THEN round(delta,2) ELSE 0 END) elapsed, | |
SUM(CASE WHEN name = 'secs - CPU used by this session' THEN round(delta,2) ELSE 0 END) cpu_used | |
from | |
( | |
with q1 as (select * | |
from gluent_app.get_run_stats | |
where test_name in (select test_name from gluent_app.get_run_stats | |
where stat_class = 'tables accessed' | |
and name is not null and name not like 'SYS%' and name not like ' %')) | |
select substr(test_name,1,40) test_name, test_type, test_category, test_notes, begin_snap, to_char(end_snap,'MM/DD/YY HH24:MI:SS') end_snap, stat_class, name, delta from | |
( | |
select | |
test_name, test_type, test_category, test_notes, snap_type, stat_class, | |
'secs - ' || name as name, | |
lag(snap_time) over (order by snap_time) begin_snap, | |
snap_time end_snap, | |
(snap_time - (lag(snap_time) over (order by snap_time)))*86400 delta, | |
1 stat_order | |
from q1 | |
where name = 'elapsed time' | |
union all | |
select | |
test_name, test_type, test_category, test_notes, snap_type, stat_class, | |
'secs - ' || name as name, | |
lag(snap_time) over (order by snap_time) begin_snap, | |
snap_time end_snap, | |
(value-lag(value) over (order by snap_time))/100 delta, | |
2 stat_order | |
from q1 | |
where name = 'CPU used by this session' | |
union all | |
select | |
test_name, test_type, test_category, test_notes, snap_type, stat_class, | |
name as name, | |
lag(snap_time) over (order by snap_time) begin_snap, | |
snap_time end_snap, | |
(value-lag(value) over (order by snap_time)) delta, | |
4 stat_order | |
from q1 | |
where STAT_CLASS = 'sql_id' | |
union all | |
select | |
test_name, test_type, test_category, test_notes, snap_type, stat_class, | |
name as name, | |
lag(snap_time) over (order by snap_time) begin_snap, | |
snap_time end_snap, | |
(value-lag(value) over (order by snap_time)) delta, | |
5 stat_order | |
from q1 | |
where STAT_CLASS = 'tables accessed' ) | |
where snap_type = 'END' | |
order by end_snap asc, test_name asc, stat_order asc, delta desc | |
) | |
group by test_name, test_type, test_category, test_notes, end_snap | |
order by test_type asc, end_snap asc | |
) aa, | |
( | |
select * from | |
( | |
select | |
test_name, | |
test_type, | |
test_category, | |
test_notes, | |
end_snap, | |
(CASE WHEN stat_class = 'tables accessed' THEN name END) tables_accessed | |
from | |
( | |
with q1 as (select * | |
from gluent_app.get_run_stats | |
where test_name in (select test_name from gluent_app.get_run_stats | |
where stat_class = 'tables accessed' | |
and name is not null and name not like 'SYS%' and name not like ' %')) | |
select substr(test_name,1,40) test_name, test_type, test_category, test_notes, begin_snap, to_char(end_snap,'MM/DD/YY HH24:MI:SS') end_snap, stat_class, name, delta from | |
( | |
select | |
test_name, test_type, test_category, test_notes, snap_type, stat_class, | |
name as name, | |
lag(snap_time) over (order by snap_time) begin_snap, | |
snap_time end_snap, | |
(value-lag(value) over (order by snap_time)) delta, | |
5 stat_order | |
from q1 | |
where STAT_CLASS = 'tables accessed' ) | |
where snap_type = 'END' | |
order by end_snap asc, test_name asc, stat_order asc, delta desc | |
) | |
group by test_name, test_type, test_category, test_notes, end_snap,(CASE WHEN stat_class = 'tables accessed' THEN name END) | |
order by test_type asc, end_snap asc | |
) | |
where tables_accessed is not null) bb, | |
( | |
select * from | |
( | |
select | |
test_name, | |
test_type, | |
test_category, | |
test_notes, | |
end_snap, | |
(CASE WHEN stat_class = 'sql_id' THEN name END) sqlid | |
from | |
( | |
with q1 as (select * | |
from gluent_app.get_run_stats | |
where test_name in (select test_name from gluent_app.get_run_stats | |
where stat_class = 'tables accessed' | |
and name is not null and name not like 'SYS%' and name not like ' %')) | |
select substr(test_name,1,40) test_name, test_type, test_category, test_notes, begin_snap, to_char(end_snap,'MM/DD/YY HH24:MI:SS') end_snap, stat_class, name, delta from | |
( | |
select | |
test_name, test_type, test_category, test_notes, snap_type, stat_class, | |
name as name, | |
lag(snap_time) over (order by snap_time) begin_snap, | |
snap_time end_snap, | |
(value-lag(value) over (order by snap_time)) delta, | |
4 stat_order | |
from q1 | |
where STAT_CLASS = 'sql_id' ) | |
where snap_type = 'END' | |
order by end_snap asc, test_name asc, stat_order asc, delta desc | |
) | |
group by test_name, test_type, test_category, test_notes, end_snap, (CASE WHEN stat_class = 'sql_id' THEN name END), (CASE WHEN stat_class = 'tables accessed' THEN name END) | |
order by test_type asc, end_snap asc | |
) | |
) cc | |
where aa.test_name = bb.test_name | |
and aa.test_name = cc.test_name | |
and aa.test_type = bb.test_type | |
and aa.test_type = cc.test_type | |
and aa.end_snap = bb.end_snap | |
and aa.end_snap = cc.end_snap | |
) t, | |
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.tables_accessed, '[^,]+')) + 1) as sys.OdciNumberList)) levels | |
order by 2,3 asc | |
/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment