176 lines
7.5 KiB
SQL
176 lines
7.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display OS configuration
|
|
* Parameter : None
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 27-JUN-14 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
|
|
define _IF_INST1_EXISTS="--"
|
|
define _IF_INST2_EXISTS="--"
|
|
define _IF_INST3_EXISTS="--"
|
|
define _IF_INST4_EXISTS="--"
|
|
define _IF_INST5_EXISTS="--"
|
|
define _IF_INST6_EXISTS="--"
|
|
define _IF_INST7_EXISTS="--"
|
|
define _IF_INST8_EXISTS="--"
|
|
define _IF_INST9_EXISTS="--"
|
|
define _IF_INST10_EXISTS="--"
|
|
define _IF_INST11_EXISTS="--"
|
|
define _IF_INST12_EXISTS="--"
|
|
define _IF_INST13_EXISTS="--"
|
|
define _IF_INST14_EXISTS="--"
|
|
define _IF_INST15_EXISTS="--"
|
|
define _IF_INST16_EXISTS="--"
|
|
|
|
col INST1_EXISTS new_value _IF_INST1_EXISTS noprint
|
|
col INST2_EXISTS new_value _IF_INST2_EXISTS noprint
|
|
col INST3_EXISTS new_value _IF_INST3_EXISTS noprint
|
|
col INST4_EXISTS new_value _IF_INST4_EXISTS noprint
|
|
col INST5_EXISTS new_value _IF_INST5_EXISTS noprint
|
|
col INST6_EXISTS new_value _IF_INST6_EXISTS noprint
|
|
col INST7_EXISTS new_value _IF_INST7_EXISTS noprint
|
|
col INST8_EXISTS new_value _IF_INST8_EXISTS noprint
|
|
col INST9_EXISTS new_value _IF_INST9_EXISTS noprint
|
|
col INST10_EXISTS new_value _IF_INST10_EXISTS noprint
|
|
col INST11_EXISTS new_value _IF_INST11_EXISTS noprint
|
|
col INST12_EXISTS new_value _IF_INST12_EXISTS noprint
|
|
col INST13_EXISTS new_value _IF_INST13_EXISTS noprint
|
|
col INST14_EXISTS new_value _IF_INST14_EXISTS noprint
|
|
col INST15_EXISTS new_value _IF_INST15_EXISTS noprint
|
|
col INST16_EXISTS new_value _IF_INST16_EXISTS noprint
|
|
|
|
set term off
|
|
SELECT MIN(DECODE(inst_id,1,' ', '--')) INST1_EXISTS
|
|
, MIN(DECODE(inst_id,2,' ', '--')) INST2_EXISTS
|
|
, MIN(DECODE(inst_id,3,' ', '--')) INST3_EXISTS
|
|
, MIN(DECODE(inst_id,4,' ', '--')) INST4_EXISTS
|
|
, MIN(DECODE(inst_id,5,' ', '--')) INST5_EXISTS
|
|
, MIN(DECODE(inst_id,6,' ', '--')) INST6_EXISTS
|
|
, MIN(DECODE(inst_id,7,' ', '--')) INST7_EXISTS
|
|
, MIN(DECODE(inst_id,8,' ', '--')) INST8_EXISTS
|
|
, MIN(DECODE(inst_id,9,' ', '--')) INST9_EXISTS
|
|
, MIN(DECODE(inst_id,10,' ', '--')) INST10_EXISTS
|
|
, MIN(DECODE(inst_id,11,' ', '--')) INST11_EXISTS
|
|
, MIN(DECODE(inst_id,12,' ', '--')) INST12_EXISTS
|
|
, MIN(DECODE(inst_id,13,' ', '--')) INST13_EXISTS
|
|
, MIN(DECODE(inst_id,14,' ', '--')) INST14_EXISTS
|
|
, MIN(DECODE(inst_id,15,' ', '--')) INST15_EXISTS
|
|
, MIN(DECODE(inst_id,16,' ', '--')) INST16_EXISTS
|
|
FROM gv$instance
|
|
GROUP BY version
|
|
;
|
|
set term on
|
|
|
|
PROMPT
|
|
PROMPT #############################################################################
|
|
PROMPT # Operation System Configuratoin
|
|
PROMPT #############################################################################
|
|
|
|
COLUMN stat_name HEADING "Name" FORMAT A25
|
|
COLUMN comments HEADING "Description" FORMAT A55
|
|
COLUMN value1 HEADING "Inst1" FORMAT A10
|
|
COLUMN value2 HEADING "Inst2" FORMAT A10
|
|
COLUMN value3 HEADING "Inst3" FORMAT A10
|
|
COLUMN value4 HEADING "Inst4" FORMAT A10
|
|
COLUMN value5 HEADING "Inst5" FORMAT A10
|
|
COLUMN value6 HEADING "Inst6" FORMAT A10
|
|
COLUMN value7 HEADING "Inst7" FORMAT A10
|
|
COLUMN value8 HEADING "Inst8" FORMAT A10
|
|
|
|
SELECT s.stat_name
|
|
, s.comments
|
|
, MAX(DECODE(s.inst_id
|
|
, 1, DECODE(s.stat_name
|
|
,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
,TO_CHAR(ROUND(s.value,2)))
|
|
, ' ')
|
|
) value1
|
|
&&_IF_INST2_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST2_EXISTS , 2, DECODE(s.stat_name
|
|
&&_IF_INST2_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST2_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST2_EXISTS , ' ')
|
|
&&_IF_INST2_EXISTS ) value2
|
|
&&_IF_INST3_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST3_EXISTS , 3, DECODE(s.stat_name
|
|
&&_IF_INST3_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST3_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST3_EXISTS , ' ')
|
|
&&_IF_INST3_EXISTS ) value3
|
|
&&_IF_INST4_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST4_EXISTS , 4, DECODE(s.stat_name
|
|
&&_IF_INST4_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST4_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST4_EXISTS , ' ')
|
|
&&_IF_INST4_EXISTS ) value4
|
|
&&_IF_INST5_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST5_EXISTS , 5, DECODE(s.stat_name
|
|
&&_IF_INST5_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST5_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST5_EXISTS , ' ')
|
|
&&_IF_INST5_EXISTS ) value5
|
|
&&_IF_INST6_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST6_EXISTS , 6, DECODE(s.stat_name
|
|
&&_IF_INST6_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST6_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST6_EXISTS , ' ')
|
|
&&_IF_INST6_EXISTS ) value6
|
|
&&_IF_INST7_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST7_EXISTS , 7, DECODE(s.stat_name
|
|
&&_IF_INST7_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST7_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST7_EXISTS , ' ')
|
|
&&_IF_INST7_EXISTS ) value7
|
|
&&_IF_INST8_EXISTS , MAX(DECODE(s.inst_id
|
|
&&_IF_INST8_EXISTS , 8, DECODE(s.stat_name
|
|
&&_IF_INST8_EXISTS ,'PHYSICAL_MEMORY_BYTES',TO_CHAR(ROUND(s.value/power(1024,3))) || ' GB'
|
|
&&_IF_INST8_EXISTS ,TO_CHAR(ROUND(s.value,2)))
|
|
&&_IF_INST8_EXISTS , ' ')
|
|
&&_IF_INST8_EXISTS ) value8
|
|
FROM gv$osstat s
|
|
WHERE s.cumulative = 'NO'
|
|
GROUP BY s.stat_name, s.comments
|
|
ORDER BY DECODE(s.stat_name
|
|
,'NUM_CPU_SOCKETS' , 1
|
|
,'NUM_CPU_CORES' , 2
|
|
,'NUM_CPUS' , 3
|
|
,'PHYSICAL_MEMORY_BYTES' , 4
|
|
,'LOAD' , 5
|
|
,'GLOBAL_RECEIVE_SIZE_MAX' , 6
|
|
,'GLOBAL_SEND_SIZE_MAX' , 7
|
|
,99
|
|
)
|
|
, s.stat_name
|
|
;
|
|
|
|
/*
|
|
GLOBAL_RECEIVE_SIZE_MAX
|
|
GLOBAL_SEND_SIZE_MAX
|
|
LOAD
|
|
NUM_CPUS
|
|
NUM_CPU_CORES
|
|
NUM_CPU_SOCKETS
|
|
PHYSICAL_MEMORY_BYTES
|
|
TCP_RECEIVE_SIZE_DEFAULT
|
|
TCP_RECEIVE_SIZE_MAX
|
|
TCP_RECEIVE_SIZE_MIN
|
|
TCP_SEND_SIZE_DEFAULT
|
|
TCP_SEND_SIZE_MAX
|
|
TCP_SEND_SIZE_MIN
|
|
|
|
*/
|
|
@@footer
|