/
cellio.sql
55 lines (51 loc) · 1.53 KB
/
cellio.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
COL cell_cell_path HEAD CELL_PATH FOR A20
COL cell_event HEAD IO_TYPE FOR A35
COL disk_name HEAD DISK_NAME FOR A30
BREAK ON cell_event SKIP 1
--WITH cc AS (
-- SELECT /*+ MATERIALIZE */
-- CAST(extract(xmltype(confval), '/cli-output/cell/name/text()') AS VARCHAR2(20)) cell_name
-- FROM
-- v$cell_config
-- WHERE
--)
SELECT /*+ CARDINALITY(a 100000) */ /* LEADING(c cc d) USE_HASH(d) USE_HASH(cc) USE_HASH(a) */
a.event cell_event
, current_obj#
, CAST(extract(xmltype(confval), '/cli-output/cell/name/text()') AS VARCHAR2(20)) cell_name
, c.cell_path cell_cell_path
-- , sql_id
, nvl(substr(d.name,1,30),'-') disk_name
--, substr(d.path,1,30) disk_path
, c.cell_hashval
, COUNT(*)
FROM
v$cell c
, v$cell_config cc
, v$asm_disk d
, v$active_session_history a
WHERE
a.p1 = c.cell_hashval
AND c.cell_path = cc.cellname
--AND c.cell_path = replace(regexp_substr(d.path,'/(.*)/'),'/')
AND cc.conftype = 'CELL'
AND a.p2 = d.hash_value(+)
AND &1
--AND a.event LIKE 'cell%'
AND sample_time BETWEEN &2 AND &3
GROUP BY
a.event
, nvl(substr(d.name,1,30),'-')
--, substr(d.path,1,30)
, CAST(extract(xmltype(confval), '/cli-output/cell/name/text()') AS VARCHAR2(20))
, c.cell_path
, c.cell_hashval
, a.current_obj#
-- , sql_id
--HAVING COUNT(*) > 1000
ORDER BY
a.event
, COUNT(*) DESC
/