S-ASH mods for ASHviewer

-- cpu_count parameter has to be in sash_param 

-- following view will let you to run ashviewer
-- looks like top activity tab is CPU only and not Avg Active Session.



create or replace view sash.v$active_session_history as
select
ash.dbid ,
ash.inst_id ,
ash.sample_time ,
ash.session_id ,
ash.session_state ,
ash.session_serial# ,
ash.user_id ,
ash.sql_address ,
ash.sql_id ,
ash.sql_plan_hash_value ,
ash.sql_opcode ,
decode(bitand(ash.session_type,19),17,'BACKGROUND',1,'FOREGROUND',2,'RECURSIVE','?') session_type, --9i
decode(session_state,'WAITING',ash.event#,null) event#,
decode(session_state,'WAITING',ash.event#,null) event_id,
ash.seq# ,
ash.p1 ,
ash.p2 ,
ash.p3 ,
ash.wait_time ,
ash.current_obj# ,
ash.current_file# ,
ash.current_block# ,
ash.program ,
ash.module ,
ash.action ,
ash.FIXED_TABLE_SEQUENCE ,
ash.sample_id ,
decode(session_state,'WAITING',e.name,null) event,
nvl(e.wait_class,'Other') wait_class,
null SQL_EXEC_START, --fix for ASHVIEWER
null FLAGS,
null SQL_CHILD_NUMBER,
null FORCE_MATCHING_SIGNATURE,
null TOP_LEVEL_SQL_ID,
null TOP_LEVEL_SQL_OPCODE,
null SQL_PLAN_LINE_ID,
null SQL_PLAN_OPERATION,
null SQL_PLAN_OPTIONS,
null SQL_EXEC_ID,
null PLSQL_ENTRY_OBJECT_ID,
null PLSQL_ENTRY_SUBPROGRAM_ID,
null PLSQL_OBJECT_ID,
null PLSQL_SUBPROGRAM_ID,
null QC_INSTANCE_ID,
null QC_SESSION_ID,
null QC_SESSION_SERIAL#,
ash.p1 p1text,
ash.p2 p2text,
ash.p3 p3text,
null wait_class_id,
null TIME_WAITED,
null BLOCKING_SESSION,
null BLOCKING_SESSION_STATUS,
null CURRENT_ROW#,
null BLOCKING_SESSION_SERIAL#,
null CONSUMER_GROUP_ID,
null XID,
null REMOTE_INSTANCE#,
null IN_CONNECTION_MGMT,
null IN_PARSE,
null IN_HARD_PARSE,
null IN_SQL_EXECUTION,
null IN_PLSQL_EXECUTION,
null IN_PLSQL_RPC,
null IN_PLSQL_COMPILATION,
null IN_JAVA_EXECUTION,
null IN_BIND,
null IN_CURSOR_CLOSE,
null SERVICE_HASH,
null CLIENT_ID
from
sash_all ash,
sash_event_names e
where
e.event# = ash.event# and
e.dbid = ( select dbid from sash_target) and
ash.dbid = ( select dbid from sash_target) ;

create or replace view v$instance as select
version version,
inst_num instance_number,
host host_name,
sid instance_name
from sash_targets
where dbid = ( select dbid from sash_target);

create or replace view v$database as select
dbid dbid,
host host_name,
sid instance_name
from sash_targets
where dbid = ( select dbid from sash_target);


create or replace view v$sql_plan as SELECT null address, null hash_value, sql_id, plan_hash_value, null child_number,
operation, options, object_node, null object#, object_owner, object_name, null object_alias,
object_type, optimizer, id, parent_id, depth, position, null search_columns, cost,
cardinality, bytes, other_tag, partition_start, partition_stop, partition_id,
other, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates,
null projection, null time, null qblock_name, null remarks
FROM sash_sqlplans;

create or replace view v$sql as select sql_id, 100 command_type,
 listagg( REGEXP_REPLACE(REGEXP_REPLACE(trim(sql_text),'\n',''),'\r',''),'')
within group (order by piece) sql_text from sash_sqltxt group by sql_id;


Comments