124 lines
2.9 KiB
MySQL
124 lines
2.9 KiB
MySQL
@@header
|
|
|
|
PROMPT ****** NOT FINISHED ***********
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display session from ASH
|
|
* Version :
|
|
* Parameters :
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 27-Sep-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE sql_id
|
|
UNDEFINE plan_hash_value
|
|
UNDEFINE days
|
|
|
|
DEFINE sql_id="&&1"
|
|
DEFINE plan_hash_value="&&2"
|
|
DEFINE days="&&3"
|
|
|
|
|
|
COLUMN _plan_hash_value NEW_VALUE plan_hash_value NOPRINT
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
|
|
set term on
|
|
SELECT DECODE('&&plan_hash_value','','%','&&plan_hash_value') "_plan_hash_value"
|
|
, DECODE('&&days','','7','&&days') "_days"
|
|
FROM DUAL;
|
|
set term off
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE COUNT_SMALL_FORMAT=9,999
|
|
DEFINE COUNT_SMALL_DIVIDER="1"
|
|
DEFINE COUNT_SMALL_HEADING="#"
|
|
--DEFINE COUNT_DIVIDER="1000"
|
|
--DEFINE COUNT_HEADING="#1000"
|
|
|
|
|
|
|
|
DEFINE COUNT_FORMAT=9,999,999,999
|
|
DEFINE COUNT_DIVIDER="1"
|
|
DEFINE COUNT_HEADING="#"
|
|
--DEFINE COUNT_DIVIDER="1000"
|
|
--DEFINE COUNT_HEADING="#1000"
|
|
|
|
DEFINE BYTES_FORMAT="999,999"
|
|
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
DEFINE BYTES_DIVIDER="1024/1024"
|
|
DEFINE BYTES_HEADING="MB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
--DEFINE BYTES_HEADING="GB"
|
|
|
|
DEFINE TIME_FORMAT=9,999
|
|
|
|
--DEFINE TIME_DIVIDER="0.001"
|
|
--DEFINE TIME_HEADING="msec"
|
|
DEFINE TIME_DIVIDER="1"
|
|
DEFINE TIME_HEADING="sec"
|
|
--DEFINE TIME_DIVIDER="60"
|
|
--DEFINE TIME_HEADING="min"
|
|
|
|
|
|
Prompt
|
|
Prompt ******************************************************************
|
|
Prompt * A S H I N F O
|
|
Prompt *
|
|
PROMPT * Input Parameters
|
|
PROMPT * -
|
|
Prompt ******************************************************************
|
|
|
|
|
|
SELECT ash.sample_time
|
|
, ash.inst_id
|
|
, u.username
|
|
, sn.name service_name
|
|
, ash.machine
|
|
, ash.program
|
|
, ash.sql_opname
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_exec_start
|
|
, ash.top_sql_id
|
|
, ash.module
|
|
, ash.action
|
|
, ash.event
|
|
FROM gv$active_session_history ash
|
|
, dba_users u
|
|
, dba_services sn
|
|
WHERE ash.service_hash = sn.name_hash
|
|
AND ash.user_id = u.user_id
|
|
AND ash.sample_time > systimestamp - 5/24
|
|
-- and ash.service_hash = '1859414386'
|
|
-- and ash.sql_id like '9m4qqj5uzzn3k'
|
|
-- AND ash.top_level_sql_id LIKE '%'
|
|
and u.username like '%ATTRIB%'
|
|
-- and upper(ash.machine) like upper('%beta%')
|
|
-- AND upper(sn.name) like upper('SYS$USERS') -- service_name
|
|
-- and upper(ash.program) like upper('%SQL%')
|
|
-- and upper(ash.module) like upper('%PiNT%')
|
|
-- ORDER BY s.end_interval_time
|
|
ORDER BY ash.sample_time
|
|
|
|
@@footer
|