Total

 Session  History  
Author:  Christian  A.  Craft,  Oracle  Corporation    
Date:  June  9th,  2014  
Version  1.3  
 

Overview  
Proving  performance  gains  of  Exadata  requires  statistical  analysis  of  the  total  
session  history,  including  charting  active  as  well  as  idle  sessions.    This  paper  
outlines  a  method  for  capturing  total  session  history  statistics  over  a  specific  time  
period.    Total  Session  History  can  be  used  in  conjunction  with  the  SQL  Replay  
feature  of  Real  Application  Testing  and  SQL  Tuning  sets  to  determine  exactly  how  
Exadata  will  impact  performance  of  a  particular  system.  
 

Installation  Overview  
The  installation  package  include  the  following  database  objects  
 
• Table  -­‐  TOTAL_SESSION_HISTORY  
• Table  –  TSH_STATUS  
• PL/SQL  Package  –  TSH_ADMIN  
• Login  Trigger  Sample  
• Logoff  Trigger  Sample  
 
These  objects  can  be  created  in  any  schema  using  the  installation  scripts  provided.    
The  default  schema  is  TSH.  

Administrative  Functions  
The  administrative  capabilities  include  3  functions  as  follows:  
 
• TSH_ADMIN.TSH_START  
• TSH_ADMIN.TSH_SNAP  
• TSH_ADMIN.TSH_STOP  
• TSH_ADMIN.IS_ACTIVE  
• TSH_ADMIN.SET_ALERT  
• TSH_ADMIN.ALERT_STATUS  
TSH_START  
The  START  Function  is  called  at  the  beginning  of  a  capture  session.  
TSH_SNAP  
The  SNAP  function  can  be  called  at  any  desired  interval  between  START  and  
STOP  to  capture  interim  statistics  for  all  active  sessions.  
 
 TSH_STOP  
The  STOP  function  is  called  to  terminate  a  capture  session.    The  STOP  function  
will  be  called  automatically  if  an  error  is  detected  by  the  LOGIN/LOGOFF  triggers.  
IS_ACTIVE  
The  IS_ACTIVE  function  returns  TRUE  if  TSH  is  currently  capturing  session  
statistics  upon  LOGON/LOGOFF.  
SET_ALERT  
The  SET_ALERT  function  accepts  one  argument  with  3  valid  values  that  affect  
behavior  of  the  LOGON  and  LOGOFF  triggers  as  follows:  
 
• SILENT  -­‐  suppresses  all  errors  from  TSH  
• ALERT_LOG  -­‐  propagates  TSH  errors  to  the  database  alert.log  
• APP_ALERT  -­‐  allows  error  messages  to  be  raised  to  the  application  
ALERT_STATUS  
The  ALERT_STATUS  function  returns  the  current  TSH  alert  setting.  

Session  Level  Functions  

The  following  functions  are  available  at  the  session  level.    Normally,  the  
SESSION_LOGIN  and  SESSION_LOGOFF  function  calls  should  be  implemented  as  
login/logoff  triggers  at  the  system  level  to  simplify  installation.    These  functions  can  
be  implemented  by  calling  from  within  application  code,  but  this  can  result  in  an  
incomplete  picture  of  session  activity  system-­‐wide.  
 
• TSH_ADMIN.SESSION_LOGON  
• TSH_ADMIN.SESSION_LOGOFF  
 
SESSION_LOGON  checks  the  TSH_STATUS  and  if  status  is  “ACTIVE”,  inserts  a  
record  into  the  TSH  table  with  current  stats  for  the  session,  with  “START”  in  the  
ACTION  column.    TSH_LOGON  can  be  called  multiple  times  with  the  same  result,  
but  should  only  be  called  once  within  a  session.  
 
SESSION_LOGOFF  checks  the  TSH_STATUS  and  if  status  is  “ACTIVE”,  inserts  a  
record  into  the  TSH  table  with  current  stats  for  the  session,  with  “STOP”  in  the  
ACTION  column.    LOGOFF  can  be  called  multiple  times  with  the  same  result,  but  
should  only  be  called  once  within  a  session.  
 
Because  the  LOGON/LOGOFF  functions  do  not  check  for  pre-­‐existing  records  for  a  
particular  session,  any  reports  against  the  TSH  table  should  query  for  first  &  last  
records  (oldest  timestamp  vs.  newest  timestamp)  belonging  to  a  session.    Any  
intervening  records  between  those  timestamps  provide  redundant  detail,  reflecting  
a  portion  of  the  overall  session  activity.    This  design  minimizes  overhead  for  the  
logon/logoff  functions.  
Data  Collection  Process  
The  Total  Session  History  utility  captures  session-­‐level  statistics  during  a  defined  
period  of  time.  
Existing  Active  Sessions  at  TSH_START  
The  TSH_START  function  call  captures  the  beginning  statistics  for  all  active  
sessions  in  the  system.  
Logon  Events  
The  LOGON  trigger  captures  the  time  of  login  and  other  session-­‐level  
information  that  identifies  the  session,  user,  program  executed,  etc.  
Logoff  Events  
The  LOGOFF  trigger  captures  the  time  of  logoff  and  statistical  information  for  
the  session  during  the  capture  period.    This  includes  idle-­‐time,  active  (on  
CPU)  time,  SQL  executions,  etc.  

Active  Sessions  at  TSH_SNAP  

When  the  SNAP  function  is  called,  session  level  statistics  will  be  captured  for  
all  active  sessions  in  the  system.  

Remaining  Active  Sessions  at  TSH_STOP  

When  the  STOP  function  is  called,  session  level  statistics  are  captured  for  all  
active  sessions  in  the  system.  

Exception  Handling  Options  

Exception  handling  within  TSH  can  be  set  to  use  one  of  3  different  mechanisms  to  
alert  for  any  errors  that  occur  as  follows:  

Session  Alert  
The  Session  Alert  option  will  allow  exceptions  in  TSH  to  propagate  to  the  
application  upon  invocation  of  the  SESSION_LOGON  and  SESSION_LOGOFF  
function  calls.    This  can  result  in  unhandled  exceptions  at  the  application  
level,  and  may  cause  unwanted  disruption  to  the  application.  

Alert  Log  
The  Alert  Log  option  allows  propagation  of  TSH  errors  into  the  database  alert  
log  for  capture  and  diagnosis.  

Suppress  All  Exceptions  

TSH  includes  an  option  to  suppress  ALL  exceptions  that  occur  during  
SESSION_LOGON  and  SESSION_LOGOFF  calls.    This  option  ensures  against  
any  disruption  to  the  system  and  applications,  but  TSH  typically  will  not  
capture  any  statistics  in  the  event  of  any  failure.    The  most  common  failure  is  
running  out  of  space  in  the  TSH  logging  table.  
TSH  Snapshot  Options  
The  TSH_SNAP  function  generates  interim  statistics  for  all  active  sessions  in  the  
system  at  the  time  TSH_SNAP  is  called.    For  long  running  jobs,  this  feature  enables  
analysis  of  job  progression  over  the  full  duration  of  a  job.  

Scheduling  via  DBMS_JOB  

The  Oracle  DBMS_JOB  facility  can  be  used  to  schedule  execution  of  TSH_SNAP  
at  the  desired  interval.    The  TSH  library  contains  examples  of  using  
DBMS_JOB  to  schedule  this  task.  

Scheduling  via  cron  or  other  O/S  Scheduler  

TSH_SNAP  can  be  scheduled  to  execute  at  the  desired  interval  using  an  O/S  
job  scheduler  such  as  the  Unix/Linux  “cron”  facility  or  other  O/S  job  
scheduler.  

Statistics  Collected  
The  following  statistics  are  collected  for  each  session.    The  statistics  reference  
underling  GV$  (or  V$)  statistics.    For  details  of  each  statistic,  please  refer  to  the  
standard  Oracle  documentation.    The  V$  views  are  used  for  session-­‐level  capture  (in  
LOGIN/LOGOFF  triggers)  because  a  particular  session  only  resides  on  the  local  
instance.    The  GV$  views  are  only  used  in  the  START/STOP  functions  calls  that  
capture  for  all  instances  system-­‐wide.  

V$SESSION  
The  following  values  are  collected  from  V$SESSION  for  each  session  that  is  active  
when  the  START  and  STOP  functions  are  called,  as  well  
 
• SID  
• SERIAL#  
• USERNAME  
• SERVER  
• OSUSER  
• PROCESS  
• MACHINE  
• PROGRAM  
• TYPE  
• CLIENT_INFO  
• LOGON_TIME  
• SERVICE_NAME  
 
All  identifying  session-­‐level  information  is  collected  as  shown  above  to  facilitate  
identifying  of  business  processes  in  the  system.  
 
 
 
 
V$SESSTAT  statistics  
The  following  statistics  are  collected  from  the  V$SESSTAT  view  for  each  active  
session.  
 
Statistic  Name   Column  Name  
user  commits   user_commits
user  rollbacks   user_rollbacks
user  calls   user_calls
session  logical  reads   session_logical_reads
CPU  used  by  this  session   cpu_used_by_this_session
DB  Time   db_time
Application  wait  time   application_wait_time
User  I/O  wait  time   user_io_wait_time
session  connect  time   session_connect_time
Messages  sent   messages_sent
Messages  received   messages_received
Physical  read  total  IO  requests   physical_read_total_io_req
Physical  read  multi  block  requests   physical_read_multi_block_req
Physical  read  requests  optimized   physical_read_requests_optim
physical  read  bytes   physical_read_bytes
Physical  write  total  IO  requests   physical_write_total_io_req
Physical  write  total  multi  block  requests   physical_write_total_multi_blk
Physical  write  total  bytes   physical_write_total_bytes
db  block  gets   db_block_gets
consistent  gets   consistent_gets
Physical  reads   physical_reads
Physical  read  flash  cache  hits   physical_read_flash_cache_hits
Physical  reads  direct   physical_reads_direct
Physical  read  IO  requests   physical_read_IO_requests
Db  block  changes   db_block_changes
Physical  writes   physical_writes
Physical  writes  direct   physical_writes_direct
Physical  writes  from  cache   physical_writes_from_cache
Physical  write  IO  reqests   physical_write_IO_reqests
Flash  cache  inserts   flash_cache_inserts
Physical  write  bytes   physical_write_bytes
DML  statements  parallelized   dml_statements_parallelized
DDL  Statements  parallelized   ddl_statements_parallelized
queries  parallelized   queries_parallelized
parse  count  (hard)   parse_count_hard
parse  count  (total)   parse_count_total
execute  count   execute_count
bytes  sent  via  SQL*Net  to  client   bytes_sent_via_sqlnet_to_client
bytes  received  via  SQL*Net  from  client   bytes_rec_via_sqlnet_from_cli
SQL*Net  roundtrips  to/from  client   sqlnet_roundtrips_tofrom_client
bytes  sent  via  SQL*Net  to  dblink   bytes_sent_via_sqlnet_to_dblink
bytes  received  via  SQL*Net  from  dblink   bytes_rec_via_sqlnet_from_dblink
SQL*Net  roundtrips  to/from  dblink   sqlnet_roundtrips_tofrom_dblink
Sorts  (memory)   sorts_memory
Sorts  (disk)   sorts_disk
Sorts  (rows)   sorts_rows
OS  User  time  used   os_user_time_used
OS  system  time  used   os_system_time_used