#!/usr/bin/ksh
# whatsysstat_parallel ()
#------------------------------------------------------------------------------#
#    Module Name:  whatsysstat_parallel                                        #
#                                                                              #
#    Purpose:      Report on the SYSTEM Statistics -- Parallel.                #
#                  $ORACLE_HOME/dbs/init<SID>.ora                              #
#                     PARALLEL_MAX_SERVERS = ##                                #
#                                                                              #
#                  Note: PARALLEL_MAX_SERVERS is the Maximum Number of         #
#                        Parallel-Execution-Server-Processes.                  #
#                        Once the PARALLEL_MAX_SERVERS value is reached,       #
#                        no new PARALLEL servers processes are started.        #
#                                                                              #
#                        When a SQL-Statement is launched with DEGREE OF       #
#                        PARALLELISM the Query-Coordinator recruits the        #
#                        requested-number of PARALLEL-EXECUTION-SERVERS.       #
#                                                                              #
#                        When the SQL-Statement's DEGREE OF PARALLELISM is     #
#                        larger-than PARALLEL_MAX_SERVERS or larger-than the   #
#                        available PARALLEL-SERVERS, the SQL-Statement will    #
#                        execute in reduced PARALLELISM or in SERIAL.          #
#                                                                              #
#                        PARALLEL_MIN_SERVERS is the Minimum Number of         #
#                        Parallel-Execution-Server-Processes. (DEFAULT=0)      #
#                        PARALLEL_MIN_SERVERS > 0 will hold ALL SQL-Statements #
#                        until the MINIMUM-PARALLEL-EXECUTION-SERVERS are      #
#                        available.                                            #
#                                                                              #
#                        PARALLEL_MAX_SERVERS=8 will foster a Degree of        #
#                        Parallelism (DOP) equal 8.                            #
#                        DOP = CPU-Count * Parallel-Threads-Per-CPU            #
#                                                                              #
#                        Setting PARALLEL_MAX_SERVERS too large will generate  #
#                        Oracle-Error-Messages:                                #
#                          ORA-12801: Error signaled in Parallel Query.        #
#                          ORA-12853: Insufficient Memmory for PX Buffer.      #
#                                                                              #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------- #
#    08-Aug-2013   M.Swing       Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
        clear
        echo
        echo  " #--------------------------------------------------------#"
        echo  " # whatsysstat_parallel:         Oracle SYSTEM Statistics #"
        echo  " #--------------------------------------------------------#"
        echo
        echo  " Connecting as SYSDBA"
        sleep 2

        clear
        ORANAM=${ORASID}

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  /  as  SYSDBA

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        SPOOL /tmp/whatsysstat_parallel_$$.txt

        prompt #---------------------------------------------------------------#
        prompt #- What (whatsysstat_parallel)                                 -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  SY_name         FORMAT  A60     Heading 'VSYSSTAT Table Name'
        COLUMN  SY_value        FORMAT  9999999 Heading 'VSYSSTAT|Value'

        BREAK   ON PROC_username skip 1 ON SESS_username skip 0         -
                ON SESS_status   skip 0 ON SESS_sid      skip 0         -
                ON SESS_serial#  skip 0 ON PROC_spid     skip 1
        TTITLE  CENTER  '[(${HNAME}) V\$SYSSTAT Parallel-Execution Results]'-
                LEFT    DATEVAR                                     skip 1  -
                LEFT    TIMEVAR                                     skip 2

       SELECT   TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
                SY.name                 SY_name,
                SY.value                SY_value
          FROM  sys.v\$sysstat  SY
         WHERE  name                    LIKE  'Parallel%'
        ORDER BY SY_name;
EXIT;

END_FILE
print
print "Please see /tmp/whatsysstat_parallel_$$.txt"
print
}