Stanford Linear Accelerator Center Oracle Storage for the

Stanford Linear Accelerator Center Oracle Storage for the Channel Archiver Managing Channel Archiver data with Oracle partitions Overview R. Hall/L. Yasukawa EPICS Collaboration Mtg 2 May 21, 200 1

Topics Goals Oracle table design for Channel Archiver data Partition management algorithms Partition compaction algorithms Oracle support tables Oracle views to retrieve data Status of implementation R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 2

Goals Increase data storage and retrieval performance Use a commercial RDB with its associated data management tools Support the current functionality of the Channel Archiver Allow flexibility for each site to manage their data their own way. R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 3

Table Structure Overview arch pvname pv id arch table data pv id stat timestamp sevr value/sample id ostat nanosecs arch data i arch data f (scalar integer) (scalar float) sample id value pv name pv starttime pv id pv endtime pv active pv id data start data table data end arch waveform arch data wi arch data wf (waveform integer) (waveform float) seq id R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 4

Partitioning Syntax CREATE TABLE ARCH DATA I ( timestamp pv id value nanosecs stat sevr ostat date, number(38), number(38), number(9), number(8), number(8), number(16) ) PARTITION BY RANGE (timestamp) ( partition MAY0702 0001 values less than (TO DATE('05/07/2002 00:10:00','mm/dd/yyyy hh24:mi:ss')), partition MAY0702 0002 values less than (TO DATE('05/07/2002 00:20:00','mm/dd/yyyy hh24:mi:ss')), partition bin values less than (MAXVALUE) ); R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 5

Partitioning of Oracle Tables The arch data f, arch data i and arch waveform tables will be partitioned into small ( 10 minutes but can be specified) time intervals for the day. These tables are NOT indexed. EX: 12:00 – 12:10 12:10 – 12:20 12:20 – 12:30 Oracle will track which partition to store the data in so no additional overhead is performed by the Archive Engine. R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 6

Daily Processing of Partitions (Scalar Data – similar processing for waveforms) arch data f arch data i temp data f temp data i daily partitions 10 min partitions All partitions for yesterday are copied to a temporary table R. Hall/L. Yasukawa archive data f archive data i The data is validated and indexes are created EPICS Collaboration Mtg 02 The temporary table, along with indexes, is exchanged with a partition in the archived data tables May 21, 20 7

Partition Compaction Algorithm Each night a partition compaction script will run which processes archive XXX tables and associated indexes. The compaction algorithm uses the arch part durations table to determine the way in which partitions will be compacted. Eventually, it will also be used to handle the “rolling out” of partitions from the current location into a near-storage device. R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 8

Oracle Partition Compaction EX: 05/05/2002 – 05/11/2002 1 week 05/12/2002 – 05/18/2002 05/19/2002 – 05/22/2002 05/23/2002 – 05/26/2002 4 day s 05/27/2002 R. Hall/L. Yasukawa 1 day 05/28/2002 archive data f archive data i archive waveform archive data wf archive data wi 05/29/2002 05/30/2002 EPICS Collaboration Mtg 02 May 21, 20 9

Oracle Views for Retrieval Since there will be two tables for each data type, an Oracle view will be created to retrieve data from both tables for each data type The retrieval SQL will query the view instead of querying the tables directly. The views will be created read-only Views allow flexibility as to what data the user has access to Views allow access to scalar and waveform data to be the same R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 10

Status of Implementation We have an Oracle machine available but we currently don’t have enough disk storage available for long term archiving. The OCI interface to Oracle is defined The support scripts are written but some of the partition management functions are still being worked out We have a limited number of licences for the Oracle partitioning option We are waiting on the changes to the Channel Archiver LibIO code for the integration and test phase to begin. R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 11

Additional Notes We have tried to keep most of the processing flexible so other labs can use it “out of the box”. Other labs may use bits and pieces of the Oracle table processing algorithms and are not required to handle their data the same way we plan on handling our data at SLAC. The only hard and fast requirement is for the initial table structure to be the same so the Channel Archiver knows where to store the data. We are open to any suggestions and ideas for improvement. R. Hall/L. Yasukawa EPICS Collaboration Mtg 02 May 21, 20 12