plsql-produrces.sql
SELECT /*+ ALL_ROWS */
dbms_ash_internal.get_plsql_name(ash.dbid,
ash.plsql_entry_object_id,
ash.plsql_entry_subprogram_id),
ash.dim1_percentage,
' ' ||
dbms_ash_internal.get_plsql_name(ash.dbid,
nvl(ash.plsql_object_id, -1),
ash.plsql_subprogram_id),
ash.dim12_percentage
FROM (SELECT d12aa_ash.*
FROM (SELECT d12gb_ash.*,
(dim1_count * 100 / :ash_num_rows) as dim1_percentage,
dense_rank() over(order by dim1_count desc, dbid, plsql_entry_object_id, plsql_entry_subprogram_id) as dim1_rank
FROM (SELECT dbid,
plsql_entry_object_id,
plsql_entry_subprogram_id,
plsql_object_id,
plsql_subprogram_id,
count(*) as dim12_count,
(count(*) * 100 / :ash_num_rows) as dim12_percentage,
rank() over(partition by dbid, plsql_entry_object_id, plsql_entry_subprogram_id order by count(*) desc, plsql_object_id, plsql_subprogram_id) as dim12_rank,
sum(count(*)) over(partition by dbid, plsql_entry_object_id, plsql_entry_subprogram_id) as dim1_count
FROM (SELECT unified_ash.*
FROM ((SELECT d.dbid,
inst.instance_number,
0 as snap_id,
a.sample_id,
a.sample_time,
a.session_id,
a.session_serial#,
a.session_type,
a.session_state,
a.flags,
a.user_id,
a.sql_id,
a.sql_opcode,
a.sql_opname,
a.top_level_sql_id,
a.sql_child_number,
a.sql_plan_hash_value,
a.sql_plan_line_id,
a.sql_plan_operation,
a.sql_plan_options,
a.sql_exec_id,
a.force_matching_signature,
a.plsql_entry_object_id,
a.plsql_entry_subprogram_id,
a.plsql_object_id,
a.plsql_subprogram_id,
a.service_hash,
a.qc_session_id,
a.qc_instance_id,
a.qc_session_serial#,
nvl(a.event,
'CPU + Wait for CPU') as event,
nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#,
a.p1,
a.p1text,
a.p2,
a.p2text,
a.p3,
a.p3text,
a.wait_time,
a.time_waited,
a.xid,
a.blocking_session,
a.blocking_session_serial#,
a.blocking_session_status,
a.blocking_inst_id,
a.current_obj#,
a.current_file#,
a.current_block#,
a.top_level_call#,
a.top_level_call_name,
a.program,
a.module,
a.action,
a.client_id,
a.remote_instance#,
a.in_connection_mgmt,
a.in_parse,
a.in_hard_parse,
a.in_sql_execution,
a.in_plsql_execution,
a.in_plsql_rpc,
a.in_plsql_compilation,
a.in_java_execution,
a.in_bind,
a.in_cursor_close,
a.in_sequence_load
FROM V$ACTIVE_SESSION_HISTORY a,
V$DATABASE d,
V$INSTANCE inst
WHERE 1 = 1
and :ash_enable_mem_view = 1
and :ash_enable_disk_view = 0
and a.sample_time between
:ash_mem_btime and
:ash_mem_etime) UNION ALL
(SELECT d.dbid,
inst.instance_number,
0 as snap_id,
a.sample_id,
a.sample_time,
a.session_id,
a.session_serial#,
a.session_type,
a.session_state,
a.flags,
a.user_id,
a.sql_id,
a.sql_opcode,
a.sql_opname,
a.top_level_sql_id,
a.sql_child_number,
a.sql_plan_hash_value,
a.sql_plan_line_id,
a.sql_plan_operation,
a.sql_plan_options,
a.sql_exec_id,
a.force_matching_signature,
a.plsql_entry_object_id,
a.plsql_entry_subprogram_id,
a.plsql_object_id,
a.plsql_subprogram_id,
a.service_hash,
a.qc_session_id,
a.qc_instance_id,
a.qc_session_serial#,
nvl(a.event,
'CPU + Wait for CPU') as event,
nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#,
a.p1,
a.p1text,
a.p2,
a.p2text,
a.p3,
a.p3text,
a.wait_time,
a.time_waited,
a.xid,
a.blocking_session,
a.blocking_session_serial#,
a.blocking_session_status,
a.blocking_inst_id,
a.current_obj#,
a.current_file#,
a.current_block#,
a.top_level_call#,
a.top_level_call_name,
a.program,
a.module,
a.action,
a.client_id,
a.remote_instance#,
a.in_connection_mgmt,
a.in_parse,
a.in_hard_parse,
a.in_sql_execution,
a.in_plsql_execution,
a.in_plsql_rpc,
a.in_plsql_compilation,
a.in_java_execution,
a.in_bind,
a.in_cursor_close,
a.in_sequence_load
FROM V$ACTIVE_SESSION_HISTORY a,
V$DATABASE d,
V$INSTANCE inst
WHERE 1 = 1
and :ash_enable_mem_view = 1
and :ash_enable_disk_view = 1
and a.is_awr_sample = 'Y'
and a.sample_time between
:ash_mem_btime and :ash_mem_etime)
UNION ALL
(SELECT a.dbid,
a.instance_number,
a.snap_id,
a.sample_id,
a.sample_time,
a.session_id,
a.session_serial#,
a.session_type,
a.session_state,
a.flags,
a.user_id,
a.sql_id,
a.sql_opcode,
a.sql_opname,
a.top_level_sql_id,
a.sql_child_number,
a.sql_plan_hash_value,
a.sql_plan_line_id,
a.sql_plan_operation,
a.sql_plan_options,
a.sql_exec_id,
a.force_matching_signature,
a.plsql_entry_object_id,
a.plsql_entry_subprogram_id,
a.plsql_object_id,
a.plsql_subprogram_id,
a.service_hash,
a.qc_session_id,
a.qc_instance_id,
a.qc_session_serial#,
nvl(a.event,'CPU + Wait for CPU') as event,
nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#,
a.p1,
a.p1text,
a.p2,
a.p2text,
a.p3,
a.p3text,
a.wait_time,
a.time_waited,
a.xid,
a.blocking_session,
a.blocking_session_serial#,
a.blocking_session_status,
a.blocking_inst_id,
a.current_obj#,
a.current_file#,
a.current_block#,
a.top_level_call#,
a.top_level_call_name,
a.program,
a.module,
a.action,
a.client_id,
a.remote_instance#,
a.in_connection_mgmt,
a.in_parse,
a.in_hard_parse,
a.in_sql_execution,
a.in_plsql_execution,
a.in_plsql_rpc,
a.in_plsql_compilation,
a.in_java_execution,
a.in_bind,
a.in_cursor_close,
a.in_sequence_load
FROM DBA_HIST_ACTIVE_SESS_HISTORY a
WHERE 1 = 1
and :ash_enable_disk_view = 1
and a.sample_time between
:ash_disk_btime and
:ash_disk_etime)) unified_ash
WHERE dbid = :dbid
AND (instance_number MEMBER OF :inst_num)
AND ((snap_id = 0) OR
(snap_id between :bid and :eid))
AND sample_time between :ash_begin_time and
:ash_end_time
AND (:ash_num_samples = :ash_num_samples)
AND (:ash_sid IS NULL OR
session_id = :ash_sid OR
(qc_session_id = :ash_sid AND
qc_instance_id MEMBER OF :inst_num))
AND (:ash_sql_id IS NULL OR
sql_id like :ash_sql_id)
AND (:ash_wait_class IS NULL OR
wait_class like :ash_wait_class)
AND (:ash_service_hash IS NULL OR
service_hash = :ash_service_hash)
AND (:ash_module IS NULL OR
module like :ash_module)
AND (:ash_action IS NULL OR
action like :ash_action)
AND (:ash_client_id IS NULL OR
client_id like :ash_client_id)
AND (:ash_plsql_entry IS NULL OR
((unified_ash.dbid,
unified_ash.plsql_entry_object_id,
unified_ash.plsql_entry_subprogram_id) IN
(select d.dbid,
object_id,
subprogram_id
from (select object_id,
subprogram_id,
object_type,
owner,
object_name,
procedure_name,
overload
from dba_procedures) plsname1,
v$database d
where dbms_ash_internal.format_plsql(plsname1.owner,
plsname1.object_name,
plsname1.object_type,
plsname1.procedure_name,
plsname1.overload) like
:ash_plsql_entry)))) a
WHERE 1 = 1
and plsql_entry_object_id IS NOT NULL
GROUP BY dbid,
plsql_entry_object_id,
plsql_entry_subprogram_id,
plsql_object_id,
plsql_subprogram_id) d12gb_ash) d12aa_ash
WHERE d12aa_ash.dim1_rank <= 5
AND d12aa_ash.dim1_percentage >= 1
AND d12aa_ash.dim12_rank <= 3
AND ((d12aa_ash.dim12_rank <= 1) OR
((d12aa_ash.dim12_rank > 1) AND
(d12aa_ash.dim12_percentage >= 1)))
ORDER BY d12aa_ash.dim1_rank, d12aa_ash.dim12_rank) ash
WHERE 1 = 1
ORDER BY ash.dim1_rank, ash.dim12_rank
