@@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