67 lines
1.4 KiB
SQL
67 lines
1.4 KiB
SQL
set linesize 150
|
|
|
|
column sharable_mem_kb format 99G999G999D00
|
|
column persistent_mem_kb format 99G999G999D00
|
|
column runtime_mem_kb format 99G999G999D00
|
|
column cursor_cnt format 999G999G990
|
|
column unique_cursor_cnt format 999G999G990
|
|
|
|
compute sum of sharable_mem_kb on report
|
|
compute sum of persistent_mem_kb on report
|
|
compute sum of runtime_mem_kb on report
|
|
compute sum of cursor_cnt on report
|
|
compute sum of unique_cursor_cnt on report
|
|
|
|
break on report
|
|
|
|
with
|
|
childs as
|
|
( select
|
|
parsing_schema_id,
|
|
sql_id,
|
|
count(*) child_cnt
|
|
from
|
|
v$sql
|
|
group by
|
|
parsing_schema_id,
|
|
sql_id
|
|
),
|
|
mchild as
|
|
( select
|
|
parsing_schema_id,
|
|
max(child_cnt) max_child_cnt
|
|
from
|
|
childs
|
|
group by
|
|
parsing_schema_id
|
|
),
|
|
vsql as
|
|
( select
|
|
parsing_schema_id,
|
|
count(*) cursor_cnt,
|
|
count(distinct sql_id) unique_cursor_cnt,
|
|
sum(sharable_mem)/1024 sharable_mem_kb,
|
|
sum(persistent_mem)/1024 persistent_mem_kb,
|
|
sum(runtime_mem)/1024 runtime_mem_kb
|
|
from
|
|
v$sql
|
|
group by
|
|
parsing_schema_id
|
|
)
|
|
select
|
|
username, cursor_cnt, unique_cursor_cnt, max_child_cnt,
|
|
sharable_mem_kb, persistent_mem_kb, runtime_mem_kb
|
|
from
|
|
mchild,
|
|
vsql,
|
|
dba_users usr
|
|
where
|
|
vsql.parsing_schema_id = mchild.parsing_schema_id
|
|
and vsql.parsing_schema_id = usr.user_id
|
|
order by
|
|
username
|
|
;
|
|
|
|
clear breaks
|
|
clear computes
|