349 lines
9.0 KiB
SQL
349 lines
9.0 KiB
SQL
/*
|
|
Following objects need to be created to capture ZFSSA monitoring data
|
|
*/
|
|
|
|
|
|
|
|
/*
|
|
To capture ZFS pool space usage
|
|
*/
|
|
create table zfs_pool_space
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, pool_name VARCHAR2(20) NOT NULL
|
|
, used NUMBER NOT NULL
|
|
, avail NUMBER NOT NULL
|
|
, compression NUMBER
|
|
, dedup NUMBER
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-APR-2015','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_pool_space
|
|
add constraint zfs_pool_space_pk
|
|
primary key (capture_time, zfssa_nodename, pool_name)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
/*
|
|
To capture ZFS project space usage
|
|
*/
|
|
create table zfs_project_space
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, pool_name VARCHAR2(20) NOT NULL
|
|
, project_name VARCHAR2(30) NOT NULL
|
|
, space_total NUMBER NOT NULL
|
|
, space_data NUMBER NOT NULL
|
|
, space_available NUMBER NOT NULL
|
|
, space_snapshots NUMBER NOT NULL
|
|
, space_unused_res NUMBER NOT NULL
|
|
, space_unused_res_shares NUMBER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(7,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('06-APR-2015','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_project_space
|
|
add constraint zfs_project_space_pk
|
|
primary key (capture_time, zfssa_nodename, pool_name, project_name)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
/*
|
|
To capture ZFS snapshot space usage
|
|
*/
|
|
create table zfs_snapshot_space
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, pool_name VARCHAR2(20) NOT NULL
|
|
, project_name VARCHAR2(30) NOT NULL
|
|
, snapshot_name VARCHAR2(60) NOT NULL
|
|
, unique_space NUMBER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(7,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('06-APR-2015','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_snapshot_space
|
|
add constraint zfs_snapshot_space_pk
|
|
primary key (capture_time, zfssa_nodename, pool_name, project_name,snapshot_name)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
|
|
/*
|
|
To capture ZFS share space usage
|
|
*/
|
|
create table zfs_share_space
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, pool_name VARCHAR2(20) NOT NULL
|
|
, project_name VARCHAR2(30) NOT NULL
|
|
, share_name VARCHAR2(30) NOT NULL
|
|
, space_total NUMBER NOT NULL
|
|
, space_data NUMBER NOT NULL
|
|
, space_available NUMBER NOT NULL
|
|
, space_snapshots NUMBER NOT NULL
|
|
, space_unused_res NUMBER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(7,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('06-APR-2015','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_share_space
|
|
add constraint zfs_share_space_pk
|
|
primary key (capture_time, zfssa_nodename, pool_name, project_name, share_name)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = ip.bytes[hostname]
|
|
Description = Network: IP bytes per second broken down by hostname
|
|
*/
|
|
create table zfs_ipbytes_by_host
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, hostname VARCHAR2(60) NOT NULL
|
|
, bytes_per_sec INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_ipbytes_by_host
|
|
add constraint zfs_ipbytes_by_host_pk
|
|
primary key (capture_time, zfssa_nodename, hostname)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = nfs3.bytes[project]
|
|
Description = Protocol: NFSv3 bytes per second broken down by project
|
|
*/
|
|
create table zfs_nfsv3_bytes_by_project
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, project VARCHAR2(60) NOT NULL
|
|
, bytes_per_sec INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_nfsv3_bytes_by_project
|
|
add constraint zfs_nfsv3_bytes_by_project_pk
|
|
primary key (capture_time, zfssa_nodename, project)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = nfs3.ops[project]
|
|
Description = Network: IP bytes per second broken down by hostname
|
|
*/
|
|
create table zfs_nfsv3_ops_by_project
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, project VARCHAR2(60) NOT NULL
|
|
, ops_per_sec INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_nfsv3_ops_by_project
|
|
add constraint zfs_nfsv3_ops_by_project_pk
|
|
primary key (capture_time, zfssa_nodename, project)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = nfs3.ops[share]
|
|
Description = Network: IP bytes per second broken down by hostname
|
|
*/
|
|
create table zfs_nfsv3_ops_by_share
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, sharename VARCHAR2(60) NOT NULL
|
|
, ops_per_sec INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_nfsv3_ops_by_share
|
|
add constraint zfs_nfsv3_ops_by_share_pk
|
|
primary key (capture_time, zfssa_nodename, sharename)
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = cpu.utilization
|
|
Description = CPU: percent utilization
|
|
*/
|
|
create table zfs_cpu_utilization
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, util_percent INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_cpu_utilization
|
|
add constraint zfs_cpu_utilization_pk
|
|
primary key (capture_time, zfssa_nodename )
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics =
|
|
Description =
|
|
*/
|
|
|
|
create table zfs_nfsv3_operations
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, operations_persec INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_nfsv3_operations
|
|
add constraint zfs_nfsv3_operations_pk
|
|
primary key (capture_time, zfssa_nodename )
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = ndmp.diskkb
|
|
Description = Data Movement: NDMP bytes transferred to/from disk per second
|
|
*/
|
|
create table zfs_ndmp_diskio
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, ndmp_diskkb INTEGER NOT NULL
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_ndmp_diskio
|
|
add constraint zfs_ndmp_diskio_pk
|
|
primary key (capture_time, zfssa_nodename )
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|
|
|
|
|
|
|
|
/*
|
|
To capture following dataset
|
|
Statistics = nic.kilobytes[direction]
|
|
Description =
|
|
*/
|
|
create table zfs_network_stats
|
|
(
|
|
capture_time date NOT NULL
|
|
, zfssa_nodename VARCHAR2(20) NOT NULL
|
|
, in_kb INTEGER
|
|
, out_kb INTEGER
|
|
)
|
|
partition by RANGE (capture_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
|
|
(
|
|
PARTITION part_01 values LESS THAN (TO_DATE('01-SEP-2014','DD-MON-YYYY'))
|
|
)
|
|
PCTFREE 0
|
|
PCTUSED 99
|
|
;
|
|
|
|
alter table zfs_network_stats
|
|
add constraint zfs_network_stats_pk
|
|
primary key (capture_time, zfssa_nodename )
|
|
using index LOCAL
|
|
PCTFREE 0
|
|
;
|