FilesExpand file tree

 master

/

onesysstat.sql

Copy path

BlameMore file actions

BlameMore file actions

Latest commit

 

History

History

History

51 lines (44 loc) · 1.21 KB

 master

/

onesysstat.sql

Top

File metadata and controls

• Code
• Blame

51 lines (44 loc) · 1.21 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

set linesize 32000

set pagesize 1000

set long 2000000000

set longchunksize 1000

set head off;

set verify off;

set termout off;

column u new_value us noprint;

column n new_value ns noprint;

select name n from v$database;

select user u from dual;

set sqlprompt &ns:&us>

set head on

set echo on

set termout on

set trimspool on

spool &ns.onesysstat.log

set define off

-- show one system statistic

-- for instance 1 in RAC

-- one year

-- "bytes received via SQL*Net from client" statistic

select

to_char(sn.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') "End snapshot time",

after.value-before.value value_difference

from

DBA_HIST_SYSSTAT before,

DBA_HIST_SYSSTAT after,

DBA_HIST_SNAPSHOT sn

where

before.STAT_NAME = 'bytes received via SQL*Net from client' and

before.STAT_NAME = after.STAT_NAME and

before.INSTANCE_NUMBER = 1 and

before.INSTANCE_NUMBER = sn.INSTANCE_NUMBER and

before.INSTANCE_NUMBER = after.INSTANCE_NUMBER and

before.SNAP_ID = sn.SNAP_ID and

before.SNAP_ID + 1 = after.SNAP_ID and

before.DBID = sn.DBID and

before.DBID = after.DBID and

after.value >= before.value and

sn.END_INTERVAL_TIME >= to_date('2018-11-01','YYYY-MM-DD') and

sn.END_INTERVAL_TIME <= to_date('2018-12-21','YYYY-MM-DD')

order by sn.SNAP_ID;

spool off