Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? D-Level Masters Thesis Report Muhammad Ahmad Javed Qureshi [email protected] Peter Taban [email protected] November 15th, 2007

ABB Power Technologies AB Vasterås, Sweden Supervisor: Lars-Ola Österlund [email protected] Supervisor: Kjell Bystedt [email protected]

Mälardalen University Vasterås, Sweden Supervisor: Frank Lüders [email protected] Examiner: Rikard Land [email protected]

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Abstract Supervisory Control and Data Acquisition (SCADA) systems are used for operation of many real world systems like power systems, telecommunication systems, etc. Real Time Database System (RTDBS) is an important constituent of SCADA system. There are many factors in RTDBS that affects the performance and operation, like transaction scheduling, concurrency control, I/O, buffer management and recovery. Network Manager™ is a system for Power process control manufactured by ABB. Network Manager™ uses AVANTI as RTDBS, AVANTI is developed by ABB for network management in power systems. Oracle TimesTen In-memory database is designed to provide real time performance. Network Management unit of ABB examines the possibility to substitute TimesTen in place of AVANTI. In order to inspect the performance several steps were followed: the schema of AVANTI was translated into TimesTen and data was transferred from AVANTI to TimesTen both steps were automated. Four Data Manipulation Language services of AVANTI were translated for TimesTen in order to measure the performance of TimesTen and compare it with AVANTI. The performance of TimesTen is analyzed in different modes i.e. standalone TimesTen, TimesTen to TimesTen replication and TimesTen in cache connect with Oracle mode. The results are compared with the performance of AVANTI. The results have shown that TimesTen is capable of providing real-time performance. Data retrieval in TimesTen is comparable with AVANTI. Data modification in TimesTen is slower than AVANTI.

-2-

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Acknowledgements We would like to avail this opportunity to thank ABB Power Technologies AB for granting us a chance to work on an industrial thesis. Thanks to Lars-Ola Österlund and Kjell Bystedt for guiding us throughout the thesis work. Thanks to Frank Lüders and Rikard Land for providing us academic material and guidance. Thanks to Joel Byren for setting up virtual machines for our project work. Thanks to Jimmy M Bjorklund for helping us out at different stages in project work, it was of really great worth. Last but not the least we would like to thank for cooperation of all people at Network Management unit of ABB Power Technologies AB.

3

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Contents 1

Introduction .................................................................................................. 9 1.1 1.2

2

Problem Description................................................................................................. 11 Outline of Thesis ...................................................................................................... 12

Background ................................................................................................. 14 2.1 Database Management System................................................................................. 14 2.2 Real-time Concepts .................................................................................................. 15 2.2.1 Task .................................................................................................................. 15 2.2.2 Real Time Operating System ........................................................................... 16 2.3 Real Time Databases ................................................................................................ 16 2.3.1 Air Traffic Control (ATC) System (An example of RTDBS) ......................... 17 2.3.2 Transactions ..................................................................................................... 18 2.3.2.1 Transaction Scheduling Algorithms............................................................. 18 2.3.3 Managing Concurrency in RTDBS .................................................................. 19 2.3.4 Buffer Management in RTDBS........................................................................ 19 2.3.5 Recovery Issues................................................................................................ 20 2.4 Related Work............................................................................................................ 20

3

Investigated Databases............................................................................... 22 3.1 AVANTI................................................................................................................... 22 3.1.1 Patterns in AVANTI......................................................................................... 22 3.1.2 Data Binding .................................................................................................... 23 3.1.3 Interpretation of data type in AVANTI............................................................ 24 3.1.4 Manipulation of data object.............................................................................. 24 3.1.5 Managing Error in AVANTI............................................................................ 26 3.1.6 Distributed Access............................................................................................ 26 3.1.7 Data Replication in AVANTI .......................................................................... 26 3.1.7.1 Types of Subscribers in AVANTI................................................................ 27 3.1.8 Triggers in AVANTI........................................................................................ 28 3.1.9 Message Flow in AVANTI .............................................................................. 28 3.1.9.1 Message Passing........................................................................................... 28 3.1.9.2 Handling Message Queue by a Container .................................................... 29 3.1.10 Meta Data ......................................................................................................... 29 3.1.11 Redundancy...................................................................................................... 29 3.2 TimesTen.................................................................................................................. 29 3.2.1 Architecture of TimesTen ................................................................................ 29 3.2.1.1 Shared Libraries ........................................................................................... 30 3.2.1.2 Memory Resident Data Structure ................................................................. 30 3.2.1.3 Database Process .......................................................................................... 31 3.2.1.4 Administrative Program ............................................................................... 31 3.2.1.5 Checkpoint and Log files ............................................................................. 31 3.2.1.6 Cache Connect and Replication Architecture .............................................. 32 3.2.2 Patterns in TimesTen........................................................................................ 32 3.2.2.1 READONLY cache groups .......................................................................... 33 3.2.2.2 SYNCHRONOUS WRITETHROUGH (SWT) cache groups..................... 33 3.2.2.3 ASYNCHRONOUS WRITETHROUGH (AWT) cache groups ................. 34 3.2.2.4 USERMANAGED cache groups ................................................................. 34 3.2.2.5 Aging in Cache groups ................................................................................. 34 3.2.3 Triggers compatibility in TimesTen................................................................. 35

4

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

3.2.4 Replication in TimesTen .................................................................................. 37 3.2.5 Access Management in TimesTen ................................................................... 39 3.3 Summary .................................................................................................................. 39

4

Solution ........................................................................................................ 40 4.1 Requirements for Translation of AVANTI DML for TimesTen ............................. 41 4.2 Schema Translation and Data Transfer from AVANTI into TimesTen................... 41 4.2.1 Schema Translation .......................................................................................... 41 4.2.2 Data Transfer.................................................................................................... 44 4.3 Translation of services of AVANTI DML for TimesTen ........................................ 45 4.3.1 GETLIT and XGETLIT ................................................................................... 45 4.3.2 MODLIT and XMODLIT ................................................................................ 48 4.3.3 GETFILE and XGETFILE............................................................................... 49 4.3.4 MODFILE and XMODFILE............................................................................ 51 4.4 Outcome ................................................................................................................... 52

5

Implementation........................................................................................... 54 5.1 Conducted Tests ....................................................................................................... 54 5.1.1 Execution Time Measurement ......................................................................... 54 5.1.2 Test System ...................................................................................................... 55 5.1.3 Tests Performed on GETLIT and MODLIT .................................................... 55 5.1.4 Tests Performed on GETFILE and MODFILE................................................ 58 5.1.5 Tests Performed on Insert and Delete .............................................................. 58 5.2 Results ...................................................................................................................... 58 5.2.1 TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT......................... 58 5.2.2 TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT...................... 59 5.2.3 GETFILE and MODFILE with sub file RTBUSA_ND (TimesTen and AVANTI) ......................................................................................................................... 60

6

Conclusions and Future Work .................................................................. 62 6.1 Conclusions .............................................................................................................. 62 6.1.1 Performance of TimesTen compared to AVANTI........................................... 62 6.1.2 Performance of data retrieval from TimesTen ................................................. 62 6.1.3 Performance of data modification in TimesTen............................................... 62 6.2 Future Work ............................................................................................................. 63

7

Appendix A.................................................................................................. 64 7.1.1 7.1.2 7.1.3 7.1.4 7.1.5 7.1.6 7.1.7 7.1.8 7.1.9 7.1.10 7.1.11 7.1.12 7.1.13 7.1.14 7.1.15 7.1.16 7.1.17

TimesTen-GETLIT-MEASURAND................................................................ 64 AVANTI-GETLIT ........................................................................................... 64 TimesTen-GETLIT-MEASUARND2.............................................................. 65 TimesTen-Replication-GETLIT....................................................................... 65 AVANTI-REP-GETLIT................................................................................... 66 TimesTen-Cache-Connect-GETLIT ................................................................ 67 TimesTen-MODLIT-MEASURAND .............................................................. 67 AVANTI-MODLIT.......................................................................................... 68 TimesTen-MODLIT-MEASURAND2 ............................................................ 69 TimesTen-Replication-MODLIT ..................................................................... 70 AVANTI-REP-MODLIT ................................................................................. 70 TimesTen-Cache-Connect-MODLIT............................................................... 71 GETFILE and MODFILE with RTBUSA_ND ............................................... 71 TimesTen-INSERT-MEASURAND................................................................ 72 TimesTen-INSERT-MEASUARND2.............................................................. 73 TimesTen-DELETE-MEASURAND............................................................... 73 TimesTen-DELETE-MEASURAND2............................................................. 74 5

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

7.1.18 7.1.19

8

Muhammad Ahmad Javed Qureshi Peter Taban

TimesTen-Concurrent-GETLIT ....................................................................... 75 TimesTen-Concurrent-MODLIT ..................................................................... 75

References.................................................................................................... 76

6

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

List of Figures Figure 1: Supervisory System, reprinted from [43] ................................................................... 9 Figure 2: Control Center Configuration, [17] .......................................................................... 10 Figure 3: Hierarchical database design, [17]............................................................................ 11 Figure 4: Data models in DBMS, [30] ..................................................................................... 14 Figure 5: Event triggered RTOS, reprinted from [22].............................................................. 16 Figure 6: ATC Database, [5] .................................................................................................... 17 Figure 7: Three phases of transactions under OCC, [3]........................................................... 19 Figure 8: “j” number of Records is allocated and “I” number of records are used, [7] ........... 23 Figure 9: Index Number Binding ............................................................................................. 24 Figure 10: Data type in AVANTI, [7]...................................................................................... 24 Figure 11: Distributed Access [7] ............................................................................................ 26 Figure 12: Online Standby replication, [7]............................................................................... 27 Figure 13: Subscription example [7]........................................................................................ 27 Figure 14: TimesTen Basic Architecture, reprinted from [8] .................................................. 30 Figure 15: TimesTen database, reprinted from [8] .................................................................. 31 Figure 16: TimesTen with cache connect and replication, reprinted from [8]......................... 32 Figure 17: Cache groups, reprinted from [8]............................................................................ 33 Figure 18: READONLY cache groups, reprinted from [13] ................................................... 33 Figure 19: Triggers in a Power Process Control System ......................................................... 35 Figure 20: XLA Architecture, reprinted from [8] .................................................................... 35 Figure 21: Code Event Capturing using XLA, reprinted from [41]......................................... 36 Figure 22: XLA Application Instead of Triggers..................................................................... 37 Figure 23: Master data sore and Subscriber data store, reprinted from [14]............................ 37 Figure 24: Return Receipt Replication, reprinted from [14] .................................................... 38 Figure 25: Return Twosafe Replication, reprinted from [14] .................................................. 39 Figure 26: ODBC Architecture [28]......................................................................................... 40 Figure 27: Proposed Model ...................................................................................................... 41 Figure 28: Schema Transfer from AVANTI to TimesTen....................................................... 42 Figure 29: MapDataType Method............................................................................................ 43 Figure 30: Create Cache groups in TimesTen.......................................................................... 43 Figure 31: Data Transfer .......................................................................................................... 44 Figure 32: AVANTI DATA Buffer ......................................................................................... 47 Figure 33: Sequence and Functionality of XGETLIT.............................................................. 48 Figure 34: XMODLIT Sequence and Functionality ................................................................ 49 Figure 35: Functionality of XGETFILE................................................................................... 51 Figure 36: Functionality of XMODFILE ................................................................................. 52 Figure 37: (a) Sequence of GETLIT (b) Sequence of MODIFICATION / INSERT / DELETE .................................................................................................................................................. 54 Figure 38: Replication Scheme ................................................................................................ 56 Figure 39: TimesTen deployed with Oracle............................................................................. 57 Figure 40: Results for TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT............. 59 Figure 41: Time required retrieving one record, TimesTen vs. AVANTI ............................... 59 Figure 42: Results for TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT.......... 60 Figure 43: Average Time Required by MODLIT to update One record in TimeTen and AVANTI................................................................................................................................... 60 Figure 44: Summary of results for TimesTen-GETLIT-MEASURAND ................................ 64 Figure 45: Summary of Results for AVANTI-GETLIT .......................................................... 65 Figure 46: Summary of Results for TimesTen-GETLIT-MEASURAND2............................. 65

7

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 47: Summary of Results for TimesTen-Replication-GETLIT...................................... 66 Figure 48: Results for AVANTI-REP-GETLIT....................................................................... 67 Figure 49: Summary of Results for TimesTen-Cache-Connect-GETLIT ............................... 67 Figure 50: Summary of Results for TimesTen-MODLIT-MEASURAND ............................. 68 Figure 51: Summary of Results for AVANTI-MODLIT......................................................... 69 Figure 52: Summary of Results for TimesTen-MODLIT-MEASURAND2 ........................... 69 Figure 53: Summary of Results for TimesTen-Replication-MODLIT .................................... 70 Figure 54: Summary of Results for AVANTI-REP-MODLIT ................................................ 71 Figure 55: Summary of Results for TimesTen-Cache-Connect-MODLIT .............................. 71 Figure 56: Summary of Results for TimesTen-Insert-MEASURAND.................................... 72 Figure 57: Summary of results for TimesTen-INSERT-MEASURAND2 .............................. 73 Figure 58: Summary of Results for TimesTen-DELETE-MEASURAND.............................. 74 Figure 59: Summary of Results for TimesTen-DELETE-MEASURAND2............................ 74

List of Tables Table 1: Equivalent Data Types in AVANTI and TimesTen/Oracle....................................... 43 Table 2: Schema of table MAPNAMETONUM...................................................................... 45 Table 3: Item name, their data-type and code .......................................................................... 47 Table 4: Test Cases Identified .................................................................................................. 55 Table 5: Tests Cases for Insert and Delete ............................................................................... 58 Table 6: Results for TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT ................ 58 Table 7: Results for TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT ............. 59 Table 8: Results for GETFILE with RTBUSA_ND ................................................................ 61 Table 9: Results for MODFILE with RTBUSA_ND............................................................... 61 Table 10: of results for TimesTen-GETLIT-MEASURAND .................................................. 64 Table 11: Summary of Results for AVANTI-GETLIT............................................................ 64 Table 12: Summary of results for TimesTen-GETLIT-MEASURAND2................................ 65 Table 13: Summary of results for TimesTen-Replication-GETLIT ........................................ 66 Table 14: AVANTI-REP-GETLIT .......................................................................................... 66 Table 15: Summary of Results for TimesTen-Cache-Connect-GETLIT................................. 67 Table 16: Summary of Results for TimesTen-MODLIT-MEASURAND............................... 68 Table 17: Summary of results for AVANTI-MODLIT............................................................ 68 Table 18: Summary of Results for TimesTen-MODLIT-MEASURAND2............................. 69 Table 19: Summary of Results for TimesTen-Replication-MODLIT...................................... 70 Table 20: Summary of results for AVANTI-REP-MODLIT................................................... 70 Table 21: Summary of Results for TimesTen-Cache-Connect-MODLIT ............................... 71 Table 22: Results for GETFILE with RTBUSA_ND .............................................................. 72 Table 23: Results for MODFILE with RTBUSA_ND............................................................. 72 Table 24: Summary of Results for TimesTen-Insert-MEASURAND ..................................... 73 Table 25: Summary of results for TimesTen-INSERT-MEASURAND2 ............................... 73 Table 26: Summary of Results for TimesTen-DELETE-MEASURAND ............................... 74 Table 27: Summary of Results for TimesTen-DELETE-MEASURAND2 ............................. 75 Table 28: Results for TimesTen-Concurrent-GETLIT ............................................................ 75 Table 29: Results for TimesTen-Concurrent-MODLIT........................................................... 75

8

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

1 Introduction Data management in real time applications is usually carried out within the application. It is suitable to manage small size of data inside application but as the requirement increases the demand for applications to manage large size of data increases, data management cannot be accomplished with in the application [1, pg 1]. Instead Real-time Database System (RTDBS) is used to manage data in a logical and structured manner [1, pg 1] [16, pg 1]. Apart from offering efficient storage and retrieval of information, RTDBS assures timing attributes such as deadline are fulfilled. [1, pg 1] [16, pg 1] An important application of RTDBS is in Supervisory Systems. Supervisory systems are used for monitoring and operating many real world systems like water supply systems, power systems, telecommunication systems, manufacturing systems etc. SCADA is most commonly used Supervisory System. [43] Supervisory system is used to achieve control over a particular device and obtain the desired performance. The supervisory system is commonly referred as SCADA system. SCADA is used in many industries like telecommunication, power systems, etc. SCADA system is used to provide data gathering and control functions. [43] Figure 1 illustrates the architecture of SCADA system. It can be divided into three parts as listed below: • Remote Terminal Units • Master Station • User Interface

Figure 1: Supervisory System, reprinted from [43]

Remote Terminal Units (RTUs) are linked directly with the devices and RTUs provide the data acquisition and control functions physically. Master Station stores the data acquired and changes in the system with the help of sophisticated software. The User Interface provides the

9

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

man-machine-interface; it is also referred as control center. As the size of supervisory system increases the number of RTUs, Master Station also increases. EMS (Energy Management System) [43] includes the functionality of SCADA along with software programs for contingency analysis [43], load flows [43], scheduling [43] and optimal load flows [43] calculation. DMS (Distribution Management System) provides topology analysis in a power system in order to analyze problems. [43] The modern power systems take advantage from SCADA [17] [18] / EMS (Energy management System) [17] [18] / DMS (Distribution management System) to achieve goals like: [17] [18] • Less cost for operation, maintenance and production • Better Efficiency • Increased utilization of resources Control center configuration of the Norsk Hydro is illustrated in Figure 2 [17]; it is extracted from [17]. It can be divided into three portions: • Control Center • Central and Front End (FE) computers • RTU, Substations and Power Plants RTUs acquire the data from Substations and Power Plants and transfer it to Central and FE computers. The central and FE computers transfers the data to control center to displayed on VDUs and perform appropriate control functions based on the data acquired from RTUs.

Figure 2: Control Center Configuration, [17]

Today, databases have gained very high importance in power systems. There are some significant benefits of databases in power systems [17]: • •

Extension of power system becomes very easy. System becomes dynamic; which helps in introducing new substations, RTUs, etc in the system. [17]. Recovery becomes easy after failure; data can be stored with redundancy. 10

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

• • •

Muhammad Ahmad Javed Qureshi Peter Taban

Databases can also help in load sharing in control system; clusters or replica of database can be used. History of data can be maintained by taking backups of databases. Data is organized in logical manner, in the form of entities and attributes this can be file or items, tables or columns.

With all these significant advantages, as demand lies within real-time system domain, there are some requirements of such systems [17]: • There must be some standard and logical way of storing and retrieving data from the database. • The database should avoid malfunction in order to give consistent service. • The most important, quick and predictable database access. The logical presentation of database structures can be classified into three categories: • Hierarchical [17] • Relational [17] • Network [17] Hierarchical: the data is organized in the form of levels and there is some logical link between data in different levels for example if we consider in domain of power system, on the top level we can have Substation, on one level down we can have RTUs, some calculations of RTUs on one more level down as illustrated in Figure 3.

Figure 3: Hierarchical database design, [17]

Relational: the data is organized in the form of tables and columns. There can be relations between columns of different tables. Such databases are commonly in use. Network: the data organized in the form of network is most suitable for applications in power systems and representation of power networks becomes very convenient. Such databases are rarely used.

1.1 Problem Description Network Manager TM [6] is a power process control system [6] manufactured by ABB. Network Manager TM uses AVANTI [6] as database management system (DBMS). AVANTI is a real-time database management system. AVANTI is a very important constituent of Network Manager TM. AVANTI organizes data in the form of Network and Hierarchical structure [6]; such structure is suitable for power process control system [6].

11

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Oracle TimesTen In-Memory Database [8] is a real-time database. It has all functionalities of modern DBMS like ACID (Atomicity, Consistency, Isolation and Durability) properties of transactions [12], SQL (Structured Query Language) as DML (Data Manipulation Language) and DDL (Data Definition Language), and organizes the data in relational structure. Network Management unit of ABB wants to analyze the possibility to use TimesTen in place of AVANTI. Therefore in this thesis, performance of data retrieval and modification in TimesTen is measured with actual data of AVANTI, which is compared with the performance of AVANTI. The performance should be examined in different conditions like TimesTen in Standalone mode, Replication mode, Cache connect with Oracle and concurrent data retrieval and modification. Beside this the aim is not only to achieve less response time, but also to look for predictability of execution time in order to inspect real-time characteristics of TimesTen (using statistical methods like average and standard deviation in order to find the range of variation in execution time). Certain steps were followed to analyze the performance of TimesTen: • • • •

Translate the schema of AVANTI into TimesTen. Transfer data from AVANTI to TimesTen. Translate services of AVANTI DML for TimesTen. Identify the Test Cases and perform tests on TimesTen in different scenarios using the translated services of AVANTI DML for TimesTen and analyze the results in order to examine the real-time performance.

The requirements for translating AVANTI DML for TimesTen and solution are discussed in detail in Chapter 4. The execution time measurement is discussed in Chapter 5. This thesis lies in the area of empirical research [19]. There are three techniques in empirical research i.e. experiments, case studies and surveys [19]. Our conclusion relies on the results gathered from the experiments conducted under different conditions on TimesTen (Explained in detail in section 5.1).

1.2 Outline of Thesis The division of this thesis is done in three parts: Part I (Introduction and Background) Chapter one contains the introduction and the problem description. Chapter two describes the database management system, real-time system and real time database. Chapter three explains the databases that are studied i.e. AVANTI and TimesTen. Part II (Contribution) Chapter four contains the proposed solution for schema translation, data transfer and AVANTI DML translation for TimesTen.

12

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Part III (Results & Conclusion) Chapter five describes the identified test cases and their implementation with the results obtained. Chapter six discusses the conclusion and future work.

13

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

2 Background In this chapter we have presented the database system, real-time concepts and real-time database.

2.1 Database Management System There are some motivations for using Database Management System (DBMS): • Data Abstraction [30] • Standard way of data manipulation [29] • Provide better performance e.g. concurrent data access and data recovery [29] Data abstraction helps to model the data in logical and structured form. DBMS provides DML (Data manipulation Language), DML provides a set of operation by which data can be manipulated in a standard way. DBMS also handles issues like concurrent data access and modification. DBMS also provides services for data recovery and backups. We can divide the data model of a DBMS into three separate portions as shown in Figure 4.

Figure 4: Data models in DBMS, [30]

The High-level/Conceptual model [30] can also be referred as database schema or the design of a database for a particular application. The implementation data model [30] is provided by the DBMS. The physical Data model [30] is the actual existence of DBMS in the form of files. [30] DBMS supports a Data Definition Language (DDL), DDL is used to define, alter and destroy database schema. DBMS also supports Data Manipulation Language (DML) in order to perform operations on the data stored in a database like insert, modify, retrieve and remove. Structured Query Language [30] is one of the most common example of DDL and DML, SQL is widely used by DBMS for DDL and DML. A transaction is a set of database operations. [36, pg1] it enables an application program to achieve ACID properties. [36, pg 1] ACID is acronym for Atomicity, Consistency, Isolation, and Durability. [36, pg1] Atomicity is achieved when whole transaction is executed successfully or nothing happens if there is a failure in even a single operation of transaction. [34, pg 13] The after effect of atomicity is that either all operations are executed or no operation is executed. [34, pg 13]

14

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Consistency is attained when operation of a transaction fails to meet integrity constraints (for example unique primary key), in the result whole transaction should be terminated and must rollback [34, pg 13] [35, pg 1] [36, pg 11]. Isolation states that execution of a transactions should not affect others transactions. In concurrent transactions where multiple transaction access common data at the same time, a lock system is addressed to synchronize transactions in order to avoid conflict in accessing common data. [34, pg 14] [35, pg 1] [36, pg 2] Durability property of transaction states the outcome of transaction should be permanent. [34, pg 14] This contradicts in database systems that monitor safety critical system because it relies on up to date data and expired data are not worthy. The duration of data in this type of database system is short. [34, pg 14]

2.2 Real-time Concepts Predictability of execution time is the most significant difference between real-time system and general purpose system. [20] It is usually hard to define the time in which task should be executed but there is always an expected time frame in which system should perform a certain task. Given that a system produces correct output but fails to meet execution deadline, will be considered to have failed because meeting timing constraints is a useful property to measure correctness of a real-time system.[20] There are two types of real-times system, hard and soft real-time system. [20, pg, 1] HARD REAL-TIME SYSTEM

Hard real-time systems are those systems in which failure to meet execution deadline may result into loss of lives or damage of properties, are referred to as safety critical system. [20, pg 1] [23, pg 12] SOFT REAL-TIME SYSTEM

Soft real-time systems are those systems in which failure to meet execution deadline is not so significant, what matters, is to produce correct result [20, pg 1] [23, pg 12].

2.2.1 Task A task is sequential execution program which perform similar activities and associates with other tasks. Three types of tasks addressed in real-time system: periodic, aperiodic, and sporadic task. [23, pg 12] [22, pg 1] • Periodic tasks are form of series of similar instances and in a given time interval. For example in every given period of time interval a new task is released for execution. [24, pg 4] [23, pg 12] [22, pg 1] • Aperiodic task are also known as event triggered tasks and are called when an event occur in the system. For example an interrupt or a program with higher priority precedes lower priority program in execution time. [24, pg 4] [23, pg 12] [22, pg 1] • Sporadic tasks are tasks we do not know their inter-arrival time. [24, pg 4] [23, pg 12] [22, pg 1].

15

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

2.2.2 Real Time Operating System A real-time operating system (RTOS) is a platform which supports multiple activities to run on it. [22] Tasks are activities which performs on RTOS and are assigned time to utilize the services of the CPU (central processing unit). [22] Assigning execution time to task can be archived by the help of scheduling algorithms which can either be online or offline. There are two types of RTOS, event triggered and time triggered. [22] An event triggered RTOS [22] uses plan known as “priority based pre-emptive scheduling” [22] to assign priorities to tasks when utilizing the services of CPU, where a task with higher priority will pre-empt lower priority tasks in execution time. The concept of event triggered RTOS has been elaborated with the help of Figure 5 below. The diagram comprises of three tasks A, B, and C where task C has higher priority than task B and task B has higher priority than task A in the execution time. Task A with low priority requests services of the CPU at time t1 and executes up to time t2. At time t2, task B interrupt task A since it has higher priority and executes up to time t3. At time t3, task C pre-empt task B because it has higher priority and execute up to time t4. At time t4, task B resumes its execution since there is no request from higher priority task than task C and executes up to time t5. At time t5, task A resumes its execution as there is no request from higher priority task and completes its execution. The organization of tasks activities in RTOS is conducted by the help of priority based scheduler. [22, pg 4]

Figure 5: Event triggered RTOS, reprinted from [22]

Time triggered RTOS [22] uses a type of scheduling in which tasks are assigned execution time before execution. For example a bus is expected to reach a bus stop, as defined in the buss table. [22, pg 5]

2.3 Real Time Databases RTDBS is distinguished from conventional database in many ways; RTDBS supports real-time transaction and must perform within specified deadline. In real-time system, a transaction which fails to meet the execution deadline will be referred as late transaction. [1, pg 2] There are numbers of reasons which hinders RTDBS from meeting transaction deadline. A database system uses a lot of system resource (i.e. I/O, memory) during transactions execution. This seems to be problem because using many system resources such as I/O, memory, and CPU will causes a RTDBS to not complete its execution within specified deadline which is also a problem for high system load. [1, pg 3][16, pg 12]

16

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Secondly, transaction support requires many database protocols. Running many database protocols at the same time will result into blocking and resuming of transaction. It will lead to unpredictable timing [1, pg 3]. Thirdly, the disk-based database tends to work with I/O subsystem. This was seen a problem because it creates variation in execution time, and buffer management which will eventually leads to unpredictable execution time [1, pg 3]. On the other hand the RTDBS must be predictable in order to provide real-time behaviour but there is a lot possibility of unpredictability addressed above. [16, pg 12]

2.3.1 Air Traffic Control (ATC) System (An example of RTDBS) Let us consider a real world example of RTDBS in ATC system. A model for ATC system is illustrated in Figure 6. There are two databases involved in ATC system: • •

Target/Track DB (TDB) [5] Display DB (DDB) [5]

Figure 6: ATC Database, [5]

TDB is involved with external environment that keeps track of position of aircrafts acquired from radar. DDB is involved in displaying the target aircrafts on visual display and alert detection in-order to prevent aircrafts from colliding. Transactions involved in ATC system: • TRK: Update TDB (Updates TDB on basis of external data input from radar device) • DIS: Display target (This transaction reads the TDB and it displays the target along with it also updates objects in DDB) • ALE: Alert Detection (It acquires data from both databases TDB and DDB, finds alert on basis of two databases, in case alert is detected ) DIS and ALE are not periodic but TRK is a periodic task so the transaction scheduling policy should be Earliest Deadline First (EDF) [5, pg83]. EDF is the most suitable for ATC system

17

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

because TRK is a periodic and a very critical transaction. TRK has firm deadline while DIS and ALE has soft deadline. [5, pg 83] Conflicts can occur in transaction during read/write to TDB. A conflict can occur when previous DIS or ALE is unfinished and its deadline has past, DIS and ALE have soft deadline so unfinished DIS or ALE cannot be aborted. In TRK this conflict does not exists because TRK has firm deadline, previous TRK is automatically terminated if it has missed deadline. [5, pg 84] In-order to get more detailed overview of RTDBS we have discussed following topics: • Transactions [1] [16] • Managing Concurrency in RTDBS [1][16] • Buffer Management [1][16] • Recovery Issues[16]

2.3.2 Transactions In a real-time system, scheduling is performed when execution time is assigned to tasks in order to utilize services of the CPU. A transaction scheduling is performed quite different to task scheduling because transaction uses extensive resource like memory and I/O in addition to a CPU due to extensive data processing in the database system. [1, pg 7]

2.3.2.1 Transaction Scheduling Algorithms There are two types of scheduling algorithms used in RTDBS. These are on-line and off-line scheduling algorithms. On-line scheduling which is known as dynamic scheduling and is applied to transaction on the runtime. And off-line scheduling which is known as static scheduling is performed offline before transactions start their execution. [5, pg 5] Comparing offline and online scheduling algorithms, off-line scheduling is more preferable to online because of its flexibility, lack of overload management problem, and ability to meet transaction deadline. Overload management [1] is one of the major problems which hinder transactions to meet deadline. We can solve overload management in the system by removing extra tasks from the system during the execution time or by simply excluding excess tasks from the system if overload management is to occur. [1, pg 9] [5, pg 5] In a situation where multiple transactions competes over system resources (memory, CPU, I/O, data access) there is a tendency for conflict to occur, due to access in shared resource by multiple transaction. We can alleviate conflict in concurrent transactions by introducing a conflict resolution scheduler which will enable single transaction to access data object at the moment. [1, pg 9] Pre-empting and resuming a transaction is considered not suitable for RTDBS because it enables transactions with high priority values to perform before low priority transaction, such as, high priority transaction pre-empts low priority transaction during transaction execution, this causes a low priority transaction to enter dormant stage and wait until the high priority transaction has completed execution. This is seen a problem because if a transaction with a lock holds the lock for a long period of time, could result into a queue of transaction waiting to use the lock system. A solution to this problem is achieved by introducing a robin-based

18

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

round priority CPU scheduling [1] that will read priorities in transactions by simply looking at the length of CPU slice. [1][2]

2.3.3 Managing Concurrency in RTDBS In database system, transaction is achieved through reads and writes in data objects. Thus, by introducing concurrent transactions in RTDBS, RTDBS will be able to attain flexibility and integrity. [1, pg 10] Serializability [1] states that if a number of database operations are serializable, the impact of scheduling will affect each individual transaction in the concurrent system. Applying Serializability in database system has not been successfully because Serializability [1] in database system does not support multiprogramming since it has a tendency of blocking and terminated transaction from resuming execution. On the other hand, Serializability [1] has been praised for improving performance in RTDBS because the life time of data in RTDBS is very short, therefore by applying Serializability [1] in RTDBS, will help to prevent irregularities produced by concurrent transactions. [1] Conflict in concurrency control is resolved by two mechanisms i.e. pessimistic [1] [3] and optimistic [1] [3] concurrency control. A pessimistic concurrency control [1] [3] is a conflict detection strategy used for detecting conflict in concurrent transactions when multiple transactions access data object at the same time, i.e. a pessimistic concurrency control strategy is used to prevent multiple transactions from accessing data object at the same time. Usually, this is done like this, when a transaction requests access to data objects, the pessimistic control manager examines transactions requests and it is then determined whether a transactions should be granted or denied the access to data object. [1, pg 10][3, pg 5] Optimistic concurrency control [1] [3] (OCC) is another type of concurrent control mechanism used for enabling concurrent transactions makes access to the database without creating a conflict. Transactions under OCC are divided into three phases read phase, validation phase and write phase as shown in Figure 7. In the read phase of OCC, a data buffer is read in the memory and updates are not performed to the database until the write phase of the transaction is invoked then updates are written to the database [1] [3], in validation phase [3], a system performs synchronization to avoid data object written by two transactions simultaneously.

Figure 7: Three phases of transactions under OCC, [3]

2.3.4 Buffer Management in RTDBS Buffer management is useful features for the performance of RTDBS, using this feature we can make our database memory resident. Execution of concurrent transaction requires large resource as discussed before, the memory residence database system (MRDBS) will reduce the data access time as compared to disk based databases. [1, pg 16]

19

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Availability of memory resource is one of the major issues in transaction execution because the size of buffer, memory in the execution of concurrent control will have positive or negative effect on transaction execution. [1, pg 17] Storing large amount of data in MRDBS has helped to shorten the response time from fetching data from the disk on other side the cost of MRDBS is higher than the disk memory if amount of data is large. In addition to that, if there is a lot of data to store, requires that we store data independently. [1, pg 19] Another problem encountered in storing large size of data in MRDBS is the power or CPU failure problem. This is a major problem faced in storing large amount of data in MRDBS because in case of power or CPU failure all the data in MRDBS will be lost. Thus, in order to protect our data object from getting lost, we need to use a disk-based memory to perform as a backup for our data. The backup on disk based memory, usually written asynchronous to disk but still it provides some overhead. [1, pg 19] [5, pg 7]

2.3.5 Recovery Issues Recovery has been always complex in every type of system. Recovery is needed when some malfunction or some function of abnormal course has occurred that leads the system to failure state. RTDBS should perform recovery without affecting ongoing transactions i.e. RTDBS must minimize the recovery overheads. One of the scenario in which recovery can affect ongoing transaction i.e. consider deadline for a transaction is missed and system is recovering from missed deadline, the system needs extra processing to terminate the missed deadline transaction and undo the data updates performed by the transaction in order to provide consistency. Recovery is an important essence of RTDBS but it provides a lot of overheads. [16, pg 24]

2.4 Related Work In the last decade a lot of work has been done on performance analysis of real time databases some of the work is discussed in [12, 37, 38 and 39]. BeeHive [39] is an Object Oriented RTDBS. In [39] experimental performance analysis of three types of transaction scheduling policies is performed on BeeHive. The three transaction scheduling policies used in BeeHive are EDF [39] (Earliest Deadline First), EDDF [39] (Earliest Data Deadline First) and EDF-DC [39] (Earliest Deadline First with Data Validity Check). •

•

•

EDF defines the priority of a transaction based on its deadline, if a transaction misses its deadline then the data becomes outdated, in this case the transaction has to be cancelled. [39] EDDF, in this policy two values are involved for defining the priority of transaction i.e. data deadline and transaction dead line. The data deadline is always dependent on transaction deadline. [39] In EDF-DC, a transaction is cancelled if any of the one i.e. data deadline or transaction deadline is missed. [39]

The results of experiments have shown that optimized performance is achieved by using EDDF. [39] In [38] Kang proposes a model for RTDBS, QMF [38] (QoS management architecture for Miss ratio and Freshness guarantees) [38]. STAR [38] (Security and Timeliness Assurance in Real-time databases) architecture was also developed in order to provide secure transactions 20

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

in timely manner. A simulation model was created to analyze the performance of QMF and various experiments were performed. [12] is a white paper from Oracle, to some extent it discusses the scalability of transactions processing in TimesTen with different number of processors. The response time is also discussed for a single transaction performed in TimesTen.

21

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

3 Investigated Databases In this section discusses the databases that we have studied in detail.

3.1 AVANTI As we have discussed before in Chapter 1 that AVANTI is a database system used in Network Manager TM [6]. The database consists of items and files which help in definition and maintenance of the database system. AVANTI DBMS favours Network and hierarchy structure and inter- process communication. [6, pg 5] The AVANTI database started in 1970’s and the database was purposely designed to manage a control system which performs in mini computers. AVANTI is a real-time DBMS developed intentionally to enable fast response requirements. The fast response time requirement in the database is achieved by the help of high speed disk rotation, availability of in-memory data, and short accessed path to AVANTI database system. [6, pg 5] The database is seen to offer services which supports access in random files through relative index or key format conversion, easily tuned services of the database, transparent storage media in transparent database file, offer efficient communication within processes, favours multiple databases transaction to run at the same time, provides support for many users by the help of multiple CPU, provides efficient data transfer among the database and the users of the database which is achieved by the help of high speed disk I/O with excellent caching, favours redundant configuration for example extra configuration, and supports access to distributed objects. [6, pg 6]

3.1.1 Patterns in AVANTI AVANTI database is composed of files. Files are managed by the operating system. Database files can be made primary memory resident and they can be unloaded from primary memory eventually. AVANTI caches the files on secondary memory into primary memory for immediate access. The changes made in the primary memory resident files are periodically written to secondary memory. [7, pg 8] A record is collection of similar or different variables grouped in one set and the variables in a record may vary from one to many. By grouping similar or different variable in one set records will enable us to handle variables as single unit rather than separate unit in AVANTI DBMS. The records represents file in AVANTI and can only be accessed by keys [7, pg 9]. Files in the AVANTI database are organized in array of records where each array of files represents a position index or unique key in database definition. Directly access (DIR) files are files we accessed with position index and the record file are files with fixed boundary where the boundary of records starts from one up to the logical end of file records. By numbering records in this manner will enable us to modify record file with the help of delete or store operations; however numbering records at this manner will not permit us to extend the size of record file as it is done in Database Definition Utility. During deletion of file record, parts of the logical records files are usually not affected only parts specified will be affected. The Figure 8 below is extracted from [7, pg 8] which will illustrate a file record in AVANTI. [7, pg 8]

22

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 8: “j” number of Records is allocated and “I” number of records are used, [7]

RAN stands for randomly accessed file and it is a file accessed by the key similar to DIR file. The number of records in RAN file varies from one to another, as such; there is no logical relationship in record files. RAN files supports delete and store DIR file, the only weakness with RAN file is because of its slow performance. [7] As discussed before files are composed of records and records are composed of items. An item can be classified into four types [7, pg9]: • Data-item (basic unit of data) • Pointer-item (refers to a data-item in a file) • Subrecord (set of data-items) • Group (Array of data-items) Data-items are presented with a string, flag, indication, integers, ASCII string, and float data types [7, pg 9]. With the help of groups one-to-many relations can be maintained in files, it helps to provide Hierarchical structure. With help of pointer items many-to-many relation can be managed, in-order to provide network structure. [7, pg 9]

3.1.2 Data Binding The symbolic names of database entities are defined in Acronym files. To achieve good performance these names are translated into index numbers at program compile time as shown in Figure 9. It can be clarified from the symbolic names syntax below: [7, pg 10] defines the file name defines the message file defines items name in the file. ACRONYN defines the used file name ACRONYN describe the use of DBMS system parameter. define parameter The names in this context are used for definition of global constant where as the items parameter refers to system parameter: [7, pg 10]

23

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 9: Index Number Binding

3.1.3 Interpretation of data type in AVANTI The data-types in AVANTI are also referred by Acronyms. Each Acronym has a unique integer value. Following Figure 10 shows the data-types in AVANTI:

Figure 10: Data type in AVANTI, [7]

3.1.4 Manipulation of data object AVANTI database supports four different operations for manipulation of data objects. These operations are achieved by the help of AVANTI Database Manipulation Language (DML) to enable database to successfully perform its operation. The operations listed bellow are the most used operations in AVANTI database: [6, pg 23] • STORE is an operation used for inserting data objects in the database • MODIFY used for modifying existing data objects • GET is an operation used for fetching data object from the database • DELETE is an operation used for removing data item from the database. During a query to AVANTI DBMS, the DML commands are included in a set of argument buffers with different operations before commands of DML are performed. DML command of AVANTI is not like Standard Query Language, it’s like a routine. Commands are referred as calls in AVANTI. The items and file in DML are not referred by name rather they are referred

24

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

by an index number. The buffers used as argument in the set of DBMS commands during query to AVANTI DBMS are: [6, pg 15] • General buffer used for error handling and for lock • Path buffer create connection to the database object • Records buffer creates database objects at the end of the path • Item buffer creates data object in a record buffer The parameters in AVANTI DML commands are defined by different operations before DML commands are initialized. Some of DML services for buffer initialization are listed below: [6, pg 16] • IADAM AVANTI library initialization. [6, pg 16] • IGARG General Arguments buffer initialization GARG. • IPATH Path buffer initialization, PATH. • IRECS Record identifier buffer initialization, RECS. • IITEMS Item identifier buffer initialization, ITEMS. In order to update and retrieve simplified items, DML services for this purpose are listed as below: • GETLIT Get top level items. [6, pg 16] • MODLIT Modify top level items. [6, pg 16] Except GARG no argument buffer is required. We will discuss MODLIT and GETLIT in detail. DML services for groups and items: • STOLI Store items in group. [6, pg 16] • GETLI Get items from record. [6, pg 16] • MODLI Update items in a record. [6, pg 16] • DELLI Remove items from a group. [6, pg 16] • GETIGS Retrieve item group size. [6, pg 16] DML services for records and sub-records: • STOLR Insert new records in file. [6, pg 16] • GETLR Retrieve records from file. [6, pg 16] • MODLR Update records in file. [6, pg 16] • DELLR Remove records from file. [6, pg 16] • GETRGS Retrieve sub-record group length. [6, pg 16] DML services for file: • •

DELLF LOCLF

Remove all records from file. Lock a file with the specified password. [6, pg 16]

A user of AVANTI database perform create or delete operations for a file in the database while the system executes it with the help of ESTF [6, pg 31] (File Establish) and DEESTF [6, pg 31] (File Deestablish) services. The ESTF service transforms a file from disk memory to subdirectory of a primary memory whereas DEESTF service transforms a file from primary memory to subdirectory of the disk memory. This is achieved by the help of PATH and GARG buffer. The PATH buffer specifies the name of the file and it’s created with help of IPATH service whereas the GARG buffer will consists of status of error reported from the file. [7, pg 31] 25

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

3.1.5 Managing Error in AVANTI During error handling in DML, the status of errors is sent to GARG where operations for error handling are found. Once errors are discovered in the DML will be reported in different variables with solution to manage errors. The variables in which errors are reported consists of input values where variable with the value zero implies the status of error will be sent to all variables and a variable with non zero instruct AVANTI to terminate the set up. In AVANTI database only the user has privilege to process error handling, however, there are also other option like for example AVANTI DML provides effective error processing at which the status of error is reported to the queue and will consists of names of images, subroutine, sequence number, file number, status number, and record number as well. [7, pg 14]

3.1.6 Distributed Access One of the powerful features of AVANTI Database is distributed access. AVANTI DBMS provides distributed access files and messages queue by the help of AVANTI assessors. The functions provide access to local files, remote files and as well as message queue across the network. By providing access to remote files will enable us to modify or move remote files without requiring us to modify the program [7, pg 39] [6, pg 16]. AVANTI DML services that support distributed access (STOLR, GETLR, MODLR, DELLR, GETRGS, STOLI, GETLI, MODLI, DELLI, GETIGS, GETLFD, MODLFD, GETLID, MODLID, LOCLF, UNLLF and DELLF) [6, pg39]. A model of distributed access is illustrated in Figure 11.

NODE #1

DML CALLS

LOCAL ACCESS DBMS

DB

REMOTE ACCESS

NODE #2

DML CALLS

LOCAL ACCESS DBMS

N E T W O R K

DB

REMOTE ACCESS

Figure 11: Distributed Access [7]

3.1.7 Data Replication in AVANTI High availability is a very critical issue in Power Control system. Replication is an essential need of a High available system. In case the online database goes down due to some reason

26

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

there must be some standby database which serves until online database is not recovered. Replication service in AVANTI is performed by a subscriber to enable successively update in AVANTI database files. A model of replication is illustrated in Figure 12. Subscription of file is performed in two ways in which a client subscribe one to many files and many clients can also subscribe updates to a single file and the update will be copied to a subscriber with maximum delay of five seconds. [7, pg 43] ON-LINE COMPUTER

HOT STANDBY COMPUTER

ADDITIONAL COMPUTERS

Figure 12: Online Standby replication, [7]

Replication service in AVANTI database supports client to use automatic subscription when subscribing AVANTI files. During automatic subscription in AVANTI files, a subscriber uses hot standby to sign in the Network Manage system while automatic subscription handles file subscription within database configuration and file creation statement [7, pg 43]. The code in Figure 13 illustrates the concept for automatic subscription in AVANTI where the requests and synchronized files are sent a client during the start of the server. The word EXPORT in subscription code implies a subscriber to AVANTI files will be granted access, whereas a subscription code without the word EXPORT will be denied access to the file. The SUBSCRIBE function will determine how executed program code will be handled. A SUBSCRIBE function with HOT STANDBY implies a client requests will be sent online to the interface RDB and a SUBSCRIBE operation with EMERGENCY STANDBY implies a client request will be sent to the interface RDB with MAIN_RDB [7, pg 43].

Figure 13: Subscription example [7]

3.1.7.1 Types of Subscribers in AVANTI A subscription class which consists of eight requests values and each request value is assigned numbers from one to eight values. For example value one is assigned online to HOT STANDBY subscription. When a client performs requests in the subscription class it is advisable that the request be made in the same class because performing requests in different class files will overload AVANTI LAN server since much data will be sent over it. [7, pg 44] A persistent subscriber is identified by the server before it receives the requests. Usually, a server allocates space in the memory to a persistent subscriber so that it can easily be identified next time it perform request to the file. When persistence subscriber requests file from the database server only sections it request will be returned. However, if request is made by non persistence subscriber the whole database will be returned to a subscriber. For this 27

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

reason makes non persistence subscriber ineffective since the server performs unnecessary data which are not requested by the subscriber. As a result, it leads to longer response time. [7, pg 44]

3.1.8 Triggers in AVANTI Trigger in AVANTI are implemented when there is a presence of an item in isolation or in a group of item as a whole and not to item in isolation. During the set of trigger in local or remote AVANTI files, AVANTI service SetTrigger is invoked to implement trigger in a single item or in group of items. When we update records in AVANTI files, a trigger is invoked and the status of the trigger and item updated are sent to AVANTI message queue where the trigger was initiated. Suppose error is discovered when message is sent to the trigger, no entry will be allowed, instead the value of error counter will be raised. Incase the status of event trigger states successfully, the value of counter will be included in message queue and the value of counter will be reset to zero by the call of AVANTI service ResetTrigger [7, pg 46].

3.1.9 Message Flow in AVANTI AVANTI DBMS is responsible for handling message services where messages of different sizes are sent to a queue and handled with FIFO (first in first out) policy. Each message in a queue has its own server process but can also receive message from DBMS application program. During the definition of message queue and message queue server by the QM utility, the message queue, message queue server files, the states of the process accessed through DML, and the lists of messages in the database are completed by and [7, pg 32].

3.1.9.1 Message Passing During message transmission in AVANTI, the process invokes SNDMSG (the service responsible for sending messages to the queue) services in AVANTI DBMS. The sent messages are included at the end of the queue in asynchronously manner. By doing this will enable message requester to wait until the server process has successfully completed before it is granted access to perform request. However, synchronously message processing uses different strategy when passing message to AVANTI. During message passing by synchronously approach, wait mode option is implemented by the server to handle message request and the server defines a wait mode option for DBMS to wait for server to complete processing the queue messages before is granted access to perform request. An empty message is sent in the start of synchronously message processing to start the server process and it is done by assigning the value of message length to zero to call SNDMSG service. By sending empty message requester will enable the server to start the process and will invoke the SNDMSG service which will cause a copy of message to be send to requester at the top of the queue [2, pg 34].

28

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

3.1.9.2 Handling Message Queue by a Container A container provides other options for handling message queue in AVANTI database. During the process of message queue by a container, the message is linked to a message queue cluster according to their priorities and the server will invoke the RCVMSG service to scan the content of the message queues before the process starts. [7, pg 34] There are three ways for passing message function in AVANTI DBMS and these are open, close, and hold state. In open state of message passing, access to AVANTI DBMS is done through closed or hold state by invoking MSOPEN service to empty the queue messages. A close state of message passing function is done by calling MSCLOSE service to stop the message queue server. While held state message passing function is performed by invoking MSHOLD service to terminate the activities of the database in organized manner and return a server state to idle stage when message queue is successfully completed. [7, pg 35]

3.1.10

Meta Data

Files in AVANTI can be classified into two types, user files and Meta files. The files we have discussed in previous sections are user files that are used to store data. Meta files describe the database structure. GETLFD and MODLFD are AVANTI assessors that are used to retrieve and modify database structure [6, pg 29].

3.1.11

Redundancy

The section 3.1.7 explains the data replication briefly, as the focus of this report is on performance evaluation, the redundancy and replication is not discussed in much detail as it requires a detailed study of replication and redundancy in AVANTI.

3.2 TimesTen Oracle TimesTen Database is relational and in-memory database which resides in the primary memory. [8, pg 7] It supports JDBC and ODBC interfaces for applications. The functionality of SQL-92 is also supported by TimesTen. TimesTen by default supports all data types that are present in Oracle. Some of the areas where Oracle TimesTen Database is used are in telecommunication, defence, and stock market field. Caching data in primary memory, aging, optimized data structures and data access algorithms are some features that promote TimesTen to achieve real-time performance [8, pg 7]. While maintaining the real time performance TimesTen also provides ACID (Atomicity, Consistency, Isolation and Durability) attributes for data management [12]. More information on ACID attributes for data management can be found in [33].

3.2.1 Architecture of TimesTen TimesTen can be used in three modes i.e. standalone [11] or in cache connect with Oracle Database and we can also use TimesTen in replication mode i.e. replicate TimesTen to TimesTen. First of all we will discuss the standalone TimesTen architecture. The architecture of TimesTen comprises of components which forms a complete system. Figure 14 below describes a structure of TimesTen and the components in TimesTen. TimesTen In-Memory

29

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Database consists of five components (After discussing these five components we will discuss cache connect and replication architecture): • Shared libraries • Memory residence data structure • Database process • Administrative programs • Check points and log files

Figure 14: TimesTen Basic Architecture, reprinted from [8]

3.2.1.1 Shared Libraries With help of shared libraries application programs can communicate with the TimesTen datastores. Shared libraries are interface for application programs to TimesTen data-store. The shared libraries component consists of routines that perform SQL operations and functions that connect to application program with data-source as part of application process. Comparing TimesTen shared library with conventional RTDBS, it is a set of executable program to which application will connect like for example client and Server. [8, pg 26] [12, pg 7]

3.2.1.2 Memory Resident Data Structure TimesTen log buffer, system catalogue, indexes, temp space, lock tables, and user data resides in operating system shared memory [12, pg 8]. An application can maintain multiple connections to Multiple TimesTen Data Stores and a TimesTen Data Store allows multiple connections with different application [8, pg 27] [12, pg 8]. Following Figure 15 illustrates how TimesTen database resides in memory. Every Data Store in TimesTen is defined by ODBC DSN more details on Data Store can be found in [15, pg 9].

30

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 15: TimesTen database, reprinted from [8]

3.2.1.3 Database Process TimesTen is a memory resident database, the processes are quite complex as compared to traditional DBMS. The data process component provides services like loading the database from log files into the memory, recovering database using log files, conduct check pointing at every interval at the background of active database, perform locking at database level or table level or row level, and conduct data aging [12, pg 8] [8, pg 27]. A daemon process serves whole TimesTen instance, there are sub-daemon process which serve every individual datastores. [12, pg 8]

3.2.1.4 Administrative Program The administrative components contains utility programs which the user, application program, and script call when performing interactive SQL, copying and restoring large size of data, shift of database from one location to another, and during restoring of the system. [12, pg 8][8, pg 27]

3.2.1.5 Checkpoint and Log files In order to provide durable data and transactions, TimesTen uses checkpoint file to provide storage of database image in disk. And it’s through dual checkpoint where TimesTen performs system safety in case a failure occur in the system while checkpoint is running will enable updates made in the database to be written in the transaction log which in turn be written periodically in the disk, along with safety check pointing and log files maintenance provide a lot of overhead. The maximum size of log file allowed is 1024 Mega bytes. When ever log file is full or after every 600 seconds by default, checkpoint is made. During recovery of the database, TimesTen performs transaction recovery in the database by merging database checkpoint in the disk and transaction log files which are successively completed. [8, pg 27] [12, pg 8]

31

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

3.2.1.6 Cache Connect and Replication Architecture TimesTen can also be used in Cache connect with Oracle database. Figure 16 illustrates the architecture of TimesTen with cache connect and replication.

Figure 16: TimesTen with cache connect and replication, reprinted from [8]

We can observe two new components in TimesTen i.e. • Replication Agents [14] • Cache agents [13] Cache agent loads the data into TimesTen data-source and Cache groups [13]. Cache groups are discussed in next section. In order to keep redundancy of databases so that in case of failure there must be some backup updated database to serve until the primary or online database is recovered, replication agents are responsible for providing such services.

3.2.2 Patterns in TimesTen TimesTen can be used in three modes standalone [11] or in cache connect with Oracle Database or replication mode. When TimesTen is used in standalone mode then it works as an independent memory resident relational database (Triggers and Stored Procedures are not supported in TimesTen, Triggers and Stored Procedures are expected in next major release of TimesTen). If we use TimesTen in cache connect with Oracle database then we have to create cache groups [13] that load the data from Oracle database along with relational database functionalities. A cache group is a group of tables arranged in logical hierarchy with the help of primary and foreign keys relationship and it can only be created by cache administrator using SQL operations. [13] Figure 17 illustrates how TimesTen maintains cache groups.

32

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 17: Cache groups, reprinted from [8]

There are four types of Cache groups [13]: • READONLY cache groups [13] • SYNCHRONOUS WRITETHROUGH (SWT) cache groups [13] • ASYNCHRONOUS WRITETHROUGH (AWT) cache groups [13] • USERMANAGED cache groups [13]

3.2.2.1 READONLY cache groups A READONLY cache group addresses a caching technique in which update to cache group is performed automatically by the help of AUTOREFRESH mechanism and it apply in both cache group and oracle database with the help of cache agent. During AUTOREFRESH mode, entire cache group is copied from the oracle database, READONLY cache group can not be updated [13, pg 33 and 48]. Syntax for creating READONLY cache group can be found in [13, pg 34]. Figure 18 shows READONLY cache group. [13]

Figure 18: READONLY cache groups, reprinted from [13]

3.2.2.2 SYNCHRONOUS WRITETHROUGH (SWT) cache groups Synchronous WRITETHROUGH cache group introduces caching technique in which updates in the cache groups are copied to the oracle database. When application program performs update in the cache group, update will first be performed in oracle database and then copied to the cache group later. Usually a locking system is applied in the cache groups to hinder multiple applications in accessing common data in the cache group until one application successfully successively conducts update before another can application access the cache group.[13] Syntax for creating synchronous WRITETHROUGH cache group can be found in [13, pg 36]. [13]

33

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

3.2.2.3 ASYNCHRONOUS WRITETHROUGH (AWT) cache groups In AWT cache groups changes done to cache group are written to oracle database asynchronously, it is more efficient then SWT cache groups. In AWT cache group replication agent is responsible for writing updates to oracle database. [13] The updated row is first written in TimesTen and after that it is written in Oracle database asynchronously. [13] More information related to syntax and setting up AWT cache groups can be found in [13, pg 39]. [13]

3.2.2.4 USERMANAGED cache groups USERMANAGED cached group is a cache group we apply when system managed cache group (read-only, synchronous, and asynchronous) have not fulfilled our requirement. During creation of USERMANAGED cache group, AUTOREFRESH and PROPAGATE attributes are defined in the root table of a cache group to enable cache group perform automatic refresh and data updates when data propagates from TimesTen cache group to oracle database and vice versa. [13] The AUTOREFRESH and PROPAGATE are useful attributes as it helps data update to propagate in both direction between cache group and oracle database. TimesTen introduces different ways at which data propagates within cache group and oracle database and can only be achieved with the help of SQL statement. [13, pg 43 and pg110] More information related to syntax and setting up USERMANAGED cache groups can be found in [13, pg 43].

3.2.2.5 Aging in Cache groups After the creation of cache group in the TimesTen, a cache instance is loaded in the cache group. We can also define policy for data aging in order to utilize the memory effectively. Two types of aging can be defined; these are Time Based Aging and Used Based Aging. [8, pg 73] [13, pg 72] Usage Based Aging helps us to maintain cache memory by deleting or removing Least Recently Used (LRU) data from the cache group memory. By removing LRU data will increase space in the cache memory and allow us to store new data in the memory. [8][13] We perform usage based aging by including LRU aging clause during the creation of the cache group. Applying aging in the cache group will depend on how it is implemented, when LRU aging policy is defined in the root table, will make root table proceed before other table to perform aging. Or else, aging in the root table starts at the same time as in the cache group and by the help of aging policy. [8, pg 73] [13, pg 64] More information can be found in [8] [13]. Time Based Aging removes or delete data from the cache group memory given it has lasted for a given period of time and a number of times aging occurs. . Time Based Aging is defined at the root table by including Aging USE clause for the cache group statement. Usually, Aging USE clause consists of a column name where the column name is the name of the timestamp in the time-based aging. During implementation of aging in the cache group, a TIMESTAMP is specified as a data type and NOT NULL because application will update values defined by TIMESTAMP. Suppose the rows fails to identify TIMESTAMP yet we do

34

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

not want to apply aging in a given rows we could instead use default value to avoid rows from being deleted. [13, pg 66]

3.2.3 Triggers compatibility in TimesTen Trigger is a database component that notifies the changes occurred in the database and take specified action on the basis of these changes. [42] Triggers are very important component for power process control system. Consider a scenario illustrated in Figure 19.

Figure 19: Triggers in a Power Process Control System

In this scenario, data acquisition program gathers the data from substations and external devices, and updates the most recent values in database DB_1, as the values are updated in DB_1 a trigger is activated that updates the values in Data Server. The clients subscribe to Data Server in order to retrieve the most recent values. TimesTen logs every change performed on the data store in log files as discussed in section 3.2.1 Architecture of TimesTen, sub section Checkpoint and Log Files. TimesTen provides a Transaction Log API (XLA) [41]. Its interface is provided in C language, as well as C++ and Java wrappers are also provided. There are some purposes of using XLA: [41] • • •

Keep track of updates in the data store [41] An important purpose is to provide a substitute of triggers [41] Develop custom Replication solutions with other databases i.e. except TimesTen [41]

The XLA architecture is illustrated in following Figure 20:

Figure 20: XLA Architecture, reprinted from [8]

There are two modes in which XLA works: [41] 35

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

• •

Muhammad Ahmad Javed Qureshi Peter Taban

Persistent: acquires the updated records from Log files directly. [41] Nonpersistent: acquires the updated records from a staging buffer [41].

In persistent mode the XLA application maintains a bookmark to keep positional track in the log buffer. [41] Every bookmark has two pointer values Initial Read LSN (Log Sequence Number) [41] and Current Read LSN. [41] Following snippet of code in Figure 21 is taken from [41]. It clarifies how XLA application can notify most recent committed Transactions.

Figure 21: Code Event Capturing using XLA, reprinted from [41]

The above snippet of code clarifies how we can provide an alternative of triggers in an application. In the above snippet of code the method ttXlaNextUpdateWait tracks the changes in the Data Store and returns the changes occurred in an array of ttXlaUpdateDesc_t. ttXlaAcknowledge method updates the bookmark position. The Figure 21 illustrates how an XLA Application can replace triggers in Figure 22.

36

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 22: XLA Application Instead of Triggers

In the Figure 19 the triggers have been replaced by XLA Application, it keeps track of changes, and updates the Data Server consequently. In Figure 19 the changes in DB_1 are captured in the database tier on the other hand in Figure 21 the changes in DB_1 are captured from application tier.

3.2.4 Replication in TimesTen Replication facilitates us to manage copy of similar data in different data stores with the aim to recover data in case failure occur in one of data store. Usually, replication is performed to data used for mission critical application where failure in data may result into lose of lives or damage of properties. A mission critical application is a real-time system which performs within hard-real time deadline in which meeting execution deadline is most significant if we are to attain reliability, safety, and availability of the system. Simple replication is illustrated in Figure 23. [14]

Figure 23: Master data sore and Subscriber data store, reprinted from [14]

There are many advantages we acquire by performing replication to manage copy of several data store. •

•

Firstly, replication helps us to recover lost data, in the sense that, by maintaining similar data store in several computer servers, failure in one server will enable us recover similar data in another server [14, pg 10]. Secondly, replication is useful in performing online upgrade because, since we maintain copies of similar data in stored in several database, will enable us perform online upgrade in one server while providing application access to similar data in another server [14,pg 10].

37

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

•

Muhammad Ahmad Javed Qureshi Peter Taban

Thirdly, replication helps to distribute workload among several servers, thus reduce overloading server with much work [14, pg 10].

We can configure replication in three different ways: [14] • Asynchronous • Return Receipt • Return Twosafe Asynchronous Replication [14] of TimesTen is a type of replication in which update in master data store are copied to subscriber data store asynchronously. When user application performs update in the data store, a replication agent in master data store instructs data manager to flush data updates in the disk rather than flushing updates directly to subscriber data store, this implies the master data store does not have to wait for subscriber data store to commit instead an internal message in form of control is sent to replication agent in master data store to confirm that updates in data store are successively committed and during updates user application is disconnected to master data store. Comparing performance of default replication with return two safe replication and return receipt replication, default replication is favoured most among the three replication types because of its high performance. [14] Return Receipt Replication [14] is a type of replication which performs similar to default replication when conducting updates in master data store. The difference lies when performing commit in subscriber data store. When replication agent in master data store flush transactions update from master data store to subscriber data store with return receipt replication, a replication agent in subscriber data store sends a message to master data store in form of acknowledgement that updates are successively received, however it does not assure master data store that updates in data store are committed. Figure 24 illustrates how Return Receipt Replication works. [14]

Figure 24: Return Receipt Replication, reprinted from [14]

Return Twosafe Replication [14] performs differently compared to both default replication and return receipt replication because with return twosafe replication subscriber data store commits before master data store. Usually, when user application updates master data store, replication agent in master data store instructs data manager to store updates in transaction log records and inserts a unique precommit log records to occupy the space before commit is performed in transaction records. The replication agent in subscriber data store receives transaction updates and delivers updates in the subscriber data store. After subscriber has successively commit in the data store, a replication agent in subscriber data store sends confirmation message to replication agent in master data store informing that transaction has successively been committed in the subscriber data store then transaction will be committed in the master data store. [14] The Figure 25 illustrates Return Twosafe Replication. [14] 38

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 25: Return Twosafe Replication, reprinted from [14]

3.2.5 Access Management in TimesTen Access control in TimesTen applies to TimesTen instance and not to tables in TimesTen. This will grant a user access to TimesTen database and will be achieved with the help of CREATE USER, GRANT USER, REVOKE USER, and DROP USER to create connection to TimesTen. The access control in TimesTen is enabled by instance Administrator who has authority to files and is the only user who has authority to start and stop TimesTen daemons and other process like for example replication and cache agent. During access to TimesTen database, a TimesTen client will be required to enable authentication attributes in the DSN (Data Source) and will be required to provide user name and password so as to be granted access to TimesTen. [8, pg 11 and 77]

3.3 Summary In this chapter we discussed AVANTI and TimesTen. AVANTI is a high performance database that is used in Power Process Control System. AVANTI is not a relational database while TimesTen is a relational database but AVANTI supports Hierarchical and Network structures which is suitable for Power Process Control System. Some of the feature comparison between TimesTen and AVANTI is described in table below: Feature DML DDL Triggers Replication Access Control Message Passing Distributed Access Transaction Processing

AVANTI YES YES YES YES YES YES YES NO

TimesTen YES YES NO YES YES NO YES YES

TimesTen does not support triggers but by using XLA we can implement the functionality of triggers in application layer. [41]

39

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

4 Solution Database Schema Translation and Data Transfer from AVANTI to TimesTen database and translation of services of AVANTI for TimesTen were very important and critical steps for this project work, in order to evaluate the performance of TimesTen, both databases should have same schema and data (i.e. TimesTen and AVANTI) and services as well. This work will provide environment to execute Test Cases discussed in next chapter. Microsoft ODBC [25] (Open Database Connectivity) was used for Schema Translation and Data Transfer from AVANTI to TimesTen. Open Database Connectivity ODBC is designed for high performance, making possible to access different DBMS (i.e. provide standard). X/Open and ISO/IEC Database APIs specifications provide basis for ODBC API and database access language used in ODBC is SQL (Structured Query Language). Interface for ODBC is C programming language [25], routines of ODBC API can be found in [26]. Interoperability is one of the important motivations behind ODBC. There are four components in architecture of ODBC [27]: • • • •

Application Driver Manager Driver Data source

Figure 26: ODBC Architecture [28]

Architecture of ODBC is illustrated in Figure 26. On the top there is application that uses ODBC API to communicate with the driver manager that keeps track of all DSNs, Driver Manager uses ODBC API to communicate with the Drivers of Data-source, drivers provide link to database. All ODBC Driver implement same methods and if we change the underlying driver in the DSN then the application does not need to be recompiled but we can access different database using this architecture, in this way ODBC provides interoperability.

40

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

4.1 Requirements for Translation of AVANTI DML for TimesTen Following are requirements for Translating AVANTI DML for TimesTen: 1. Translate four services of AVANTI DML for TimesTen i.e. GETLIT, MODLIT, GETFILE and MODFILE. (We name GETLIT as XGETLIT for TimesTen in order to differentiate between two services for AVANTI and TimesTen, in the same way MODLIT as XMODLIT, GETFILE as XGETFILE and MODFILE as XMODFILE). 2. The input and output of above mentioned four services should be same for both databases i.e. AVANTI and TimesTen. 3. As discussed before in previous chapter, AVANTI refers items and files with index numbers while TimesTen refers with name in the form of SQL query, a translator is needed to overcome this difference. 4. Schema and Data of AVANTI should be transferred into TimesTen in order to execute these services successfully on TimesTen. Based on the requirements described above, a model is proposed for Translation of services of AVANTI DML for TimesTen, illustrated in Figure 27. Purpose of translation of AVANTI DML for TimesTen is to evaluate the performance of TimesTen.

Figure 27: Proposed Model

4.2 Schema Translation and Data Transfer from AVANTI into TimesTen In this section we will discuss how we have used ODBC for Schema Translation and Data Transfer from AVANTI into TimesTen.

4.2.1 Schema Translation Three routines were written in C programming language for schema translation from AVANTI to TimesTen (According to Requirement Number 4 in section 4.1). These three routines are listed below: • • •

createAVANTITablesInOracle MapDataType TTCacheGroupScript

41

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

CreateAVANTITablesInOracle This routine enumerates files of AVANTI, then it enumerates the items in every file and creates them as tables in TimesTen (if we are using standalone TimesTen) or Oracle (if we are using TimesTen in Cache connect with Oracle) using ODBC interface of both Databases, Figure 28 shows how this routine works. In C programming language prototype of this routine looks like this: int createAVANTITablesInOracle(char* oraConnStr , char* AVANTIConnStr) Return: This routine returns 0 if the operation is successful else it returns -1. Parameters: • oraConnStr: Target Database, connection string for Oracle or TimesTen as ODBC provides transparency we can translate schema from AVANTI to Oracle Database or TimesTen, we used Oracle10g with this routine. • AVANTIConnSTR: Source database, connection string for AVANTI.

Figure 28: Schema Transfer from AVANTI to TimesTen

MapDataType Data-types in AVANTI were discussed in previous chapter in section 3.1.3. The data-types in TimesTen and AVANTI are different but data-types in TimesTen and Oracle are same. This routine is needed to provide TimesTen equivalent Data Type to AVANTI Data Type. The Figure 29 illustrates how MapDataType method works. The Table 1 illustrates the equivalent data types in AVANTI and TimesTen/Oracle. Prototype of this routine looks like this in C programming Language: char* MapDataType(char* COLUMN_NAME, char* DATA_TYPE,char* TYPE_NAME, char* LENGTH,char* SCALE, char* NULLABLE) Return: This routine returns the data-type in form of char string based on parameters provided. For example IDENTIFICATIONTEXT item in AVANTI is of type A-An and its length is 180, the returned character string from MapDataType would be “IDENTIFICATIONTEXT VARCHAR2 (180)”. Parameters: • COLUMN_NAME: mapped. • DATA_TYPE: • TYPE_NAME: • LENGTH: • SCALE:

Name of the column whose data-type is being Code of column’s data-type in AVANTI. Name of column data-type name in AVANTI. Size of the column. Scale of the data if its float else it should be 0.

42

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

•

NULLABLE:

Muhammad Ahmad Javed Qureshi Peter Taban

Whether the column can be null or not.

Figure 29: MapDataType Method

AVANTI Data Type A-F A-IND A-I A-I*2 A-I*4 A-R*4 A-R*8 A-An A-Bn A-L*2 A-A8

Equivalent TimesTen/Oracle Data Type NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2(LENGTH) VARCHAR2(LENGTH*2) NUMBER VARCHAR2(8)

Table 1: Equivalent Data Types in AVANTI and TimesTen/Oracle

TTCacheGroupScript This routine is needed when we want to use TimesTen in Cache connect to Oracle Database. It generates the SQL script for Cache Groups based on Tables in Oracle Database and creates Cache Group in TimesTen. Figure 30 shows how this routine works. If we are using TimesTen in Standalone mode i.e. not in Cache connect to Oracle Database then we do not need this routine. In C programming Language the prototype of this routine looks like: int TTCacheGroupScript(char* connStringOracle,char* connStringTT,char* TableName,char* CacheGroupName,char* UserName,char* primarykey)

Figure 30: Create Cache groups in TimesTen

43

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Return: This routine returns 0 if successful and -1 if failed. Parameters • connStringOracle: connection string of source Oracle Database. • connStringTT: connection string of Target TimesTen Database. • TableName: Table for which we are creating Cache Group. • CacheGroupName: Name of Cache group we desire to create. • UserName: The owner of the table. • Primary Key: The column that is primary key in the table.

4.2.2 Data Transfer A routine was needed to Transfer Data from AVANTI to TimesTen/Oracle. In order to fulfil this need, a routine capable of transferring data from AVANTI to Oracle or TimesTen was written, it uses ODBC interfaces of both databases for transferring the data, illustrated in Figure 31:

Figure 31: Data Transfer

DataTransfer routine which looks like this in C Programming Language: int dataTransfer(char* AVANTIConnStr, char* OraConnStr,char* dataTable) Return: This routine returns 0 on successful data transfer and -1 on failed data transfer. Parameters • AVANTIConnStr: Source Database connection string. • OraConnStr: Target Database connection string. • dataTable: Table name whose data we want to transfer CreateTranslationTable According to Requirement Number 3 in section 4.1, AVANTI refers items and files with index numbers while TimesTen refers columns and tables with name. This difference creates a need for a routine and a new table in TimesTen, this table will serve the purpose of translator. Another routine CreateTranslationTable was written. This routine creates a relational table MAPNAMETONUM the schema of this table is described below in Table 2. After creating table, it retrieves identity or index number for every FILE and ITEM from AVANTI and populates them in MAPNAMETONUM table in TimesTen for referring

44

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

TABLE and COLUMN according to index numbers. The prototype of this method is described below: int CreateTranslationTable(char* TTConnStr) Column Name AVANTI_RECNO AVANTI_ITEMNAME AVANTI_IDENTITY ISTABLE PARENT

Data Type NUMBER VARCHAR2(30) NUMBER NUMBER NUMBER

Constraint NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL

PRIMARY KEY

Table 2: Schema of table MAPNAMETONUM

Return This routine returns 0 when it’s completed successfully else it returns -1. Parameters • TTConnStr:

Connection string for TimesTen.

4.3 Translation of services of AVANTI DML for TimesTen Four simple services of AVANTI DML are translated for TimesTen i.e. GETLIT, MODLIT, GETFILE and MODFILE. As discussed in previous chapter GETLIT and MODLIT are used for retrieving and modifying top level items in a specified file. GETFILE and MODFILE are used for retrieving and modifying top level sub files. We name GETLIT as XGETLIT and MODLIT as XMODLIT for TimesTen in order to differentiate between services for two databases i.e. TimesTen and AVANTI. In the same way we name GETFILE as XGETFILE and MODFILE as XMODFILE for TimesTen. As discussed before AVANTI refers items and files in form of numbers, an improvement can be made by replacing these index numbers as names because TimesTen refers columns and tables with names.

4.3.1 GETLIT and XGETLIT GETLIT GETLIT is explained in detail. The prototype of GETLIT described below is taken from [6, pg 74]. CALL GETLIT(GARG , FILE , NOR , LENGTH , RECBUF , NOI , ITEBUF , FMTBUF , DATA , DATASIZE , NOWPR [, SEQNO , [, *ERRRET]]) GRAG: It is an array of A-I*2 type, if it contains 0 for success on return, greater than 0 for warning and less than 0 for error. It is input/output parameter. FILE: parameter.

Its type is A-I*2. Identity of file from which records will be retrieved. It is input

NOR:

Its type is A-I*2. This parameter tells the number of records to be read.

45

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

LENGTH:

Muhammad Ahmad Javed Qureshi Peter Taban

Its type is A-I*2. It’s the length of the record buffer array.

RECBUF: It is an array of A-I*2 (RAN Files) or A-I*4 (In case of DIR Files). If second the second index of this array contains -1 or then we have to fetch records starting from first index of RECBUF to RECBUF plus NOR (parameter of Getlit) consecutive. It is input parameter. NOI: It is of type A-I*2. It contains the number of items (columns) in a record. It is input parameter. ITEBUF: parameter.

It is an array of A-I*2. It contains the identity of items to be retrieved. It is input

FMTBUF: It is an array of A-I*2. It contains the data type in which we want to retrieve the items. It is input parameter. DATA: It is a buffer of type A-I*2 which contains the retrieved data. It is an input/output parameter. DATASIZE: It is the size of the DATA buffer in words (two bytes). It is only input parameter. NOWPR: parameter

It is of type A-I*2. It contains the number of words per record. It is out put

SEQNO: This is an optional parameter. It helps in identifying the error. It is used when is passed in GARG Buffer. [6, pg 76] It is an input parameter. *ERRRET: It is also an optional argument. It is required when programming language is FORTRAN [6, pg 76]. Data-types compatibility with C programming language listed as follows: • • • • • • • • •

A-I*2 is equivalent to short int A-I*4 is equivalent to int A-F is equivalent to short int A-IND is equivalent to short int A-I is equivalent to short int A-R*4 is equivalent to float A-R*8 is equivalent to double A-An is equivalent to char array A-Bn is equivalent to short int array

For example, if we retrieve 1000 records of file MEASURAND with four items listed below in Table 3: Item Name VALUE CURRENT IMPLEMENTED UNACK ALARM

Item Index Number 3 5 11 46

Data-Type A-R*4 A-F A-F

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

UPDATED

1

Muhammad Ahmad Javed Qureshi Peter Taban

A-F

Table 3: Item name, their data-type and code

The retrieved Data Buffer can be visualized in Figure 32.

Figure 32: AVANTI DATA Buffer

XGETLIT The prototype of XGETLIT in C programming Language is described as follows and purpose of every parameter is same as described above for GETLIT. int XGETLIT(short int *GARG, short int *file, short int *NOR, short int *length, int *recBuf, short int *NOI, short int *itemBuf, short int *fmtBuf, short int *data , short int *dataSize, short int *NOWPR, short int *SEQNO, short int *ERRRET) Our purpose of making XGETLIT is to provide same input and output as GETLIT but the challenge here is that TimesTen retrieves data via ODBC, it is quite different from AVANTI, we have to provide transparency to programs using AVANTI so that those programs can use TimesTen without any modification, and some of differences are listed below: • •

AVANTI refers Files and items by number but TimesTen refers columns and tables by name. AVANTI retrieves data in a buffer but in TimesTen ODBC returns row-sets, we can traverse in row-sets using methods of ODBC (for example: ODBC method SQLFetch).

Figure 33 shows how XGETLIT works.

47

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 33: Sequence and Functionality of XGETLIT

In the step 10 we Log time consumed for execution of GETLIT, the time logged is Wall Clock and CPU time as well in order to figure out the bottle neck.

4.3.2 MODLIT and XMODLIT MODLIT The prototype for MODLIT is described below [6, pg 101]: CALL MODLIT( GARG, FILE, NOR, LENGTH, RECBUF, NOI, ITEBUF, FMTBUF, DATA, DATASIZE, NOWPR [, SEQNO [, *ERRRET]])

All the parameters in MODLIT and GETLIT are same except that DATA and NOWPR is input instead of output as the operation of MODLIT is to update the data in file according to provided DATA in buffer. XMODLIT The prototype of XMODLIT is described below in C Programming language: int XModLit(short int *GARG, short int *file, short int *NOR, short int *length, int *recBuf, short int *NOI, short int *itemBuf, short int *fmtBuf, short int *data, short int *dataSize, short int *NOWPR, short int *SEQNO, short int *ERRRET); 48

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

The functionality of XMODLIT is clearly explained in Figure 34.

Figure 34: XMODLIT Sequence and Functionality

In the step 10 we Log time consumed for execution of MODLIT, the time logged is Wall Clock and CPU time as well in order to figure out the bottle neck.

4.3.3 GETFILE and XGETFILE GETFILE GETFILE retrieves whole (i.e. all records) single column disk file. The prototype of GETFILE is described below [6, pg 63] CALL GETFILE (STATUS, FILE, ITEM, DATA, DATASIZE, NOR, NOW) Description of parameters: STATUS: It is of type A-I*4, it is an output parameter, it contains the success status for GETFILE after completion. [6]

49

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

FILE: It is of type A-I*4, it is an input parameter, it contains the integer identity of sub file which we want to retrieve. [6] ITEM: It is of type A-I*4, it is an input parameter, it contains the integer identity of ITEM to be retrieved. [6] DATA: It is an array of type A-I*2, it is buffer for retrieved data, it an output parameter, the buffer must be sufficient to hold the data. [6] DATASIZE: It is of type A-I*4, it is the size of buffer in words. [6] NOR: It is of type A-I*4, it is an output parameter. It contains the number of records in the file. [6] NOW: It is of type A-I*4, it is an output parameter. It contains the number of words acquired from the sub file. [6] XGETFILE The output and parameters for XGETFILE are the same as for GETFILE. The prototype for XGETFILE is described below in C Programming Language (The source code for XGETFILE is presented in APPENDIX NNN): void XGETFILE ( int *STATUS, int *FILE, int *ITEM, short int *DATA, int *DATASIZE , int *NOR , int *NOW ) The description of parameters for XGETFILE is same as for GETFILE. The functionality for XGETFILE is explained in Figure 35.

50

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Figure 35: Functionality of XGETFILE

4.3.4 MODFILE and XMODFILE MODFILE MODFILE modifies the whole single column disk file. The prototype for MODFILE is described below. [6] CALL MODFILE (STATUS, FILE, ITEM, DATA, DATASIZE, NOR) STATUS: It is of type A-I*4, it is an output parameter. It contains the success status for MODFILE after completion. [6] FILE: It is of type A-I*4, it is an input parameter, it contains the integer identity of sub file which we want to modify. [6] ITEM: It is of type A-I*4, it is an input parameter, it contains the integer identity of ITEM to be retrieved. [6] DATA: It is an array of type A-I*2, it is buffer which contains the data we have to modify in the sub file, it is an input parameter. [6] 51

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

DATASIZE: It is of type A-I*4, it is the size of buffer in words. [6] NOR: It is of type A-I*4, it is an input parameter. It contains the number of records for the updated sub file. [6] XMODFILE The prototype for XMODFILE is presented in C Programming Language below: void XModFile ( int *STATUS, int *FILE, int *ITEM, short int *DATA, int *DATASIZE, int *NOR ) The description of these parameters is same as for MODFILE. The Figure 36 illustrates the functionality of XMODFILE. The first step in XMODLIT is to remove all records from the table. For removing all records from the table we have used truncate [40] command of TimesTen. Truncate [40] is much more efficient than delete command. After removing all records from the table the next step is to retrieve records from Data Buffer and insert into the table.

Figure 36: Functionality of XMODFILE

4.4 Outcome ODBC is a strong technology for database connectivity for data applications. By using ODBC we are able to accomplish schema translation and data transfer from AVANTI into TimesTen. The translated Schema consists of eleven tables listed below: • ACCUMULATOR (number of columns 256) • EVENT STORAGE (number of columns 59) • INDICATION (number of columns 256) • MEASURAND (number of columns 256) 52

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

• • • • • • •

Muhammad Ahmad Javed Qureshi Peter Taban

PROCESS VALUE (number of columns 113) SET POINT VALUE (number of columns 131) SYSTEM X-REF (number of columns 5) RTBUSA_ND (number of columns 2) SEPARMA_TMW (number of columns 2) DBBD_IBRTOPOL (number of columns 8) DBBD_BRY (number of columns 8)

The table MAPNAMETONUM is created in TimesTen only, it contains the identity in integers of all files and items in AVANTI, it is used for query building in XGETLIT and XMODLIT on the basis of file number and item numbers provided. Schema translation and data transfer were preliminary steps for conducting performance evaluation on GETLIT and MODLIT. AVANTI and TimesTen are quite different databases but still it is possible to provide same assessor for both databases. An improvement can be suggested that for TimesTen the index numbers for columns and tables can be replaced by names, as TimesTen refers columns and tables with names.

53

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

5 Implementation The ultimate goal is to measure the performance of TimesTen, in order to achieve this goal a number of tests were conducted on TimesTen. As the source code of TimesTen is not available, we can perform black box tests to measure the performance. The tests which were conducted also evaluated the aspects that affect the predictability and response time of TimesTen.

5.1 Conducted Tests The performance of a DBMS can be measured through DML, there are four operations in DML select, update delete and insert. For select and update we have two routines GETLIT and MODLIT as discussed in previous chapter, we will use GETLIT and MODLIT instead of select and update for measuring performance. The Figure 37 (a) illustrates the sequence of events for GETLIT. The Figure 37 (b) illustrates the sequence for MODLIT, MODFILE, insert and delete operations respectively.

(a)

(b) Figure 37: (a) Sequence of GETLIT (b) Sequence of MODIFICATION / INSERT / DELETE

5.1.1 Execution Time Measurement For the measurement of execution time we used two methods i.e. CPU time and Wall clock time. For calculating CPU time we have used clock() method of C library time.h [31]. For calculating wall clock time we have used gettimeofday(struct timeval *, void *) method of sys/time.h [32]. The purpose calculating the execution time by two methods is to find the I/O overhead in different operations of DML in TimesTen. The clock() method does not gives the precision more than 10 milliseconds, but the method gettimeofday gives precision of even one microsecond. To overcome the limitation of clock method, we have used large number of records with GETLIT and MODLIT e.g. 1 million records to retrieve with GETLIT and 10 54

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

thousand records to modify with MODLIT. 20 measurements were taken for every test case. We calculated the timing values described as follows for every test case (mentioned in the results of every test case in the Results section): •

CPU Time Minimum: Minimum CPU Time in the set of measurements taken for specific test case.

•

CPU Time Maximum: Maximum CPU Time in the set of measurements taken for specific test case.

•

Wall Clock Time Minimum: Minimum Wall Clock Time in the set of measurements taken for specific test case.

•

Wall Clock Time Maximum: Maximum Wall Clock Time in the set of measurements taken for specific test case.

•

CPU Time Average: Average CPU Time of set of measurements taken for specific test case.

•

Wall Clock Time Average: Average Wall Clock Time of set of measurements taken for specific test case.

•

Standard Deviation for CPU Time: Standard deviation for the set of measurements taken for CPU Time, in order to examine the variation for real-time performance.

•

Standard Deviation for Wall Clock Time: Standard deviation for the set of measurements taken for Wall Clock Time, in order to examine the variation of execution time for real-time performance.

5.1.2 Test System The performance tests were performed on 64 bit virtual machine of Red Hat Enterprise Linux 4.0 (RHEL 4) with Update 4. The hardware used was Intel® Xeon™ CPU 3.00 GHz and 1 GB of RAM.

5.1.3 Tests Performed on GETLIT and MODLIT MEASURAND is the most frequently used table in Network Manager TM, all tests are conducted on table MEASURAND. Table MEASURAND is having 255 columns, we made another version MEASURAND with 20 columns (in order to observe the performance effect) and we call that table as MEASURAND2. The table 4 presents the test cases for GETLIT and MODLIT. Condition TimesTen Standalone, table with many columns (MEASURAND with 255 columns) AVANTI standalone TimesTen Standalone, table with few columns (MEASURAND2 with 20 columns) TimesTen-TimesTen Replication (MEASURAND with 255 Columns) AVANTI Replication TimesTen in Cache connect with Oracle (MEASURAND with 255 Columns) Concurrent Data Handling

GETLIT TimesTen-GETLITMEASURAND AVANTI-GETLIT TimesTen-GETLITMEASUARND2 TimesTen-ReplicationGETLIT AVANTI-REP-GETLIT TimesTen-Cache-ConnectGETLIT TimesTen-ConcurrentGETLIT Table 4: Test Cases Identified

The conditions mentioned in Table 4 are explained below:

55

MODLIT TimesTen-MODLITMEASURAND AVANTI-MODLIT TimesTen-MODLITMEASURAND2 TimesTen-ReplicationMODLIT AVANTI-REP-MODLIT TimesTen-CacheConnect-MODLIT TimesTen-ConcurrentMODLIT

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

TimesTen Standalone, table with many columns In this condition we used standalone TimesTen with table MEASURAND. This is the simplest scenario. AVANTI Standalone In this condition we performed tests on standalone AVANTI. TimesTen Standalone, table with few columns In this condition the TimesTen was deployed standalone and we created another version of table MEASURAND, which was having only 20 columns and we call that table as MEASURAND2. This condition will help us to identify the whether tables with large number of columns create any overhead or not. TimesTen - TimesTen Replication In this condition we deployed TimesTen on two different machines named “spdt01” and “dev4u4ex”. The master data store was deployed on “spdt01” and the schema of AVANTI from master data store on spdt01 was replicated on “dev4u4ex”. We used unidirectional replication. The strongest reason to use unidirectional directional replication is the performance. It is asynchronous replication so the performance is very high in Unidirectional Replication configuration. The Figure 38 illustrates the replication scheme.

Figure 38: Replication Scheme

AVANTI Replication In this scenario we had replication of AVANTI with AVANTI. TimesTen in Cache Connect with Oracle In order to deploy TimesTen in cache connect with Oracle we have to create cache groups in TimesTen based on the table schema in Oracle. In this condition first we transferred the schema of AVANTI into Oracle 10g and then created Cache groups in TimesTen on the basis

56

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

of schema in Oracle, it was discussed in Chapter 4.2 and it is illustrated in Figure 39. We have used Asynchronous Cache Groups because the performance of the asynchronous cache groups is better than other type of cache groups. Figure 38 illustrates the deployment of TimesTen with Oracle10g.

Figure 39: TimesTen deployed with Oracle

Concurrent Data Handling In this condition we created 10 POSIX threads in an application simultaneously, every thread calls GETLIT and MODLIT in respective test case. In test case TimesTen-ConcurrentGETLIT every thread retrieves 100 thousand records. In the test case TimesTen-ConcurrentMODLIT every thread modifies 1000 records. Every test case of GETLIT and MODLIT have three sub-test cases (except Concurrent Data Handling) in every test case mentioned above, the three sub-test cases are classified as Test Case 1, Test Case 2 and Test Case 3. Test Case 1 In this sub-test case we perform data retrieval and data update using GETLIT and MODLIT respectively on one column of table MEASURAND i.e. VALUECURRENT and it is of type REAL. Test Case 2 In this sub-test case we perform data retrieval and data update using GETLIT and MODLIT respectively on eight columns of table MEASURAND, presented below: Column Name VALUE CURRENT IMPLEMENTED UNACK ALARM MANUAL ENTRY INVALID UPDATED PERSISTENT ALARM DEACTIVATED

Type REAL INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER

57

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Test Case 3 In this sub-test case we perform data retrieval and data update using GETLIT and MODLIT respectively on one column of table MEASURAND i.e. IDENTIFICATIONTEXT and it is of type CHAR.

5.1.4 Tests Performed on GETFILE and MODFILE There are four single column disk files in the schema that we transferred into TimesTen i.e. RTBUSA_ND, SEPARMA_TMW, DBBD_IBRTOPOL and DBBD_BRY. GETFILE and MODFILE are tested with all single column disk files mentioned.

5.1.5 Tests Performed on Insert and Delete The tests of insert and delete operation are also performed on table MEASURAND and MEASURAND2. The test cases identified for Insert and Delete are illustrated in Table 4. The conditions in Table 4 were explained in section 5.1.3 Condition INSERT TimesTen-INSERTTimesTen Standalone, table with many MEASURAND columns (MEASURAND with 255 columns) TimesTen-INSERTTimesTen Standalone, table with few columns MEASUARND2 (MEASURAND2 with 20 columns) Table 5: Tests Cases for Insert and Delete

DELETE TimesTen-DELETEMEASURAND TimesTen-DELETEMEASURAND2

5.2 Results This section will present the results of following test cases (the results of all test cases are presented in detail in Appendix A): • TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT • TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT • GETFILE and MODFILE with sub file RTBUSA_ND (TimesTen and AVANTI) We are considering the replication test cases results because the actual systems are deployed in replication mode

5.2.1 TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT In these test cases we retrieve 1 million records from TimesTen and AVANTI from MEASURAND table, 20 measurements were taken for this test case. The results for TimesTen-Replication-GETLIT and AVANTI-REP-GETLIT are presented in table 6 and illustrated in Figure 40. Time in milliseconds for 1 million records retrieval using GETLIT (each record is having eight items) CPU Time Minimum CPU Time Maximum Wall Clock Time Minimum Wall Clock Time Maximum CPU Time Average Wall Clock Time Average Standard Deviation for CPU Time Standard Deviation for Wall Clock Time

TimesTen-ReplicationGETLIT

AVANTI-REP-GETLIT

4330 4880 4473.564 5422.05 4591.904 4800.434 166.931 245.372

Table 6: Results for TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT

58

4440 4870 4541.422 5194.331 4615.238 4748.595 131.172 179.111

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT

Time in milli-seconds

6000 CPU Time Minimum 5000

CPU Time Maximum

4000

Wall Clock Time Minimum

3000

Wall Clock Time Maximum

2000

CPU Time Average Wall Clock Time Average

1000

Standard Deviation for CPU Time

0 TimesTen-Replication-GETLIT

AVANTI-REP-GETLIT

Standard Deviation for Wall Clock Time

Figure 40: Results for TimesTen-Replication-GETLIT vs. AVANTI-REP-GETLIT

Average the time required by TimesTen and AVANTI to retrieve one record using GETLIT is illustrated in Figure 41.

Time in Micro-seconds

Time Required to retrieve one record, TimesTen vs. AVANTI 4,9 4,8 4,7 4,6 4,5 4,4 4,3 4,2 4,1 4 Average Time required to retrieve one record from Average Time required to retrieve one record from TimesTen AVANTI

Figure 41: Time required retrieving one record, TimesTen vs. AVANTI

5.2.2 TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT In these test cases we modify 10 thousand records in TimesTen and AVANTI respectively, 20 measurements were taken for this test case. The table 7 presents the results for both test cases. The Figure 42 illustrates the results for both test cases. Time in milliseconds for 10 thousand TimesTen-ReplicationAVANTI-REP-MODLIT records Update using MODLIT (eight MODLIT items modification on every record) 1050 182 CPU Time Minimum 1530 351 CPU Time Maximum 1240.931 177.17 Wall Clock Time Minimum 2502.24 623.617 Wall Clock Time Maximum 1164.761 271.142 CPU Time Average 1623.351 314.896 Wall Clock Time Average 111.023 53.959 Standard Deviation for CPU Time 293.633 94.803 Standard Deviation for Wall Clock Time Table 7: Results for TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT

59

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT

Time in milli-seconds

3000 CPU Time Minimum 2500

CPU Time Maximum

2000

Wall Clock Time Minimum

1500

Wall Clock Time Maximum

1000

CPU Time Average Wall Clock Time Average

500

Standard Deviation for CPU Time

0 TimesTen-Replication-MODLIT

AVANTI-REP-MODLIT

Standard Deviation for Wall Clock Time

Figure 42: Results for TimesTen-Replication-MODLIT vs. AVANTI-REP-MODLIT

Time required by TimesTen and AVANTI to update one record using MODLIT is illustrated in Figure 43.

Time in micro-seconds

Time Required to Update One Record, TimesTen-ReplicationMODLIT vs. AVANTI-REP-MODLIT 200 150 100 50 0 Average Time Required to Update one Record in TimesTen

Average Time Required to Update One Record in AVANTI

Figure 43: Average Time Required by MODLIT to update One record in TimeTen and AVANTI

5.2.3 GETFILE and MODFILE with sub file RTBUSA_ND (TimesTen and AVANTI) In this test case GETFILE and MODFILE are tested with sub file RTBUSA_ND, 20 measurements were taken for these test cases. GETFILE Table 8 presents the results for GETFILE with sub file RTBUSA_ND.

1000 Iteration of GETFILE with RTBUSA_ND containing 343 records (Total number of records retrieved 343000)

GETFILE with TimesTen

690 810 700.863 648.582 744.5 772.637

Min CPU Time Max CPU Time Min Wall Clock Time Max Wall Clock Time Average CPU Time Average Wall Clock Time

60

GETFILE with AVANTI 0 10 1.899 47.399 1.5 13.776

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

33.478 Standard Deviation CPU Time 63.007 Standard Deviation Wall Clock Time Table 8: Results for GETFILE with RTBUSA_ND

3.663 9.904

MODFILE Table 9 presents the results for MODFILE with sub file RTBUSA_ND. 1000 Iteration of GETFILE with RTBUSA_ND MODFILE with MODFILE with containing 343 records (Total number of records TimesTen AVANTI Modified 343000) 7310 0 Min CPU Time 8780 20 Max CPU Time 7868.072 3.015 Min Wall Clock Time 10467.527 22.377 Max Wall Clock Time 7737 4 Average CPU Time 8625.299 9.24 Average Wall Clock Time 329.131 5.982 Standard Deviation CPU Time 713.219 5.648 Standard Deviation Wall Clock Time Table 9: Results for MODFILE with RTBUSA_ND

61

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

6 Conclusions and Future Work 6.1 Conclusions Several observations were made from the results in the previous chapter. The following list presents some important observations: •

Performance of TimesTen compared to AVANTI

•

Performance of data retrieval in TimesTen

•

Performance of data modification in TimesTen

6.1.1 Performance of TimesTen compared to AVANTI We have compared the performance of TimesTen with AVANTI, the performance of GETLIT of TimesTen was almost equivalent to AVANTI, the results in Table 6 and Figure 41. The performance of MODLIT of TimesTen was about 6 times slower than AVANTI, the results in Table 7 and Figure 43. GETFILE of TimesTen was about 60 times slower than GETFILE of AVANTI (Calculated on Average Wall Clock Time results in table 8) and MODFILE of TimesTen was about 933 times slower than MODFILE of AVANTI (Calculated on Average Wall Clock Time) results in table 8 and 9.

6.1.2 Performance of data retrieval from TimesTen The results of data retrieval have been presented in results of Test Case TimesTen-GETLITMEASURAND, TimesTen-GETLIT-MEASURAND2, TimesTen-Replication-GETLIT and TimesTen-Cache-Connect-GETLIT in Appendix A. In the results of all the test cases the data retrieval time was predictable, in the Table 6 standard deviation is presented in order to find the variation in the execution time for data retrieval.

6.1.3 Performance of data modification in TimesTen The results of data modification have been presented in Results of Test Case TimesTenMODLIT-MEASURAND, TimesTen-MODLIT-MEASURAND2, TimesTen-ReplicationMODLIT and TimesTen-Cache-Connect-MODLIT in Appendix A. In the results of all test cases for data modification in TimesTen we observed that the difference between the CPU time and Wall clock time is too much except TimesTen-MODLIT-MEASURAND2. The update operation performs logging on log file for every row in order to provide ACID data management [12]. In the results of all the test cases the data modification time was predictable, in the Table 7 standard deviation is presented in order to find the variation in the execution time for data modification. Many Columns compared to Few Columns We compared the results of test case TimesTen-MODLIT-MEASURAND with TimesTenMODLIT-MEASURAND2, the difference between the results of two test cases was quite significant. TimesTen performance becomes better if we use tables with few columns because the data written to log files was quite less than compared to many columns. On the basis of results discussed, the data retrieval and modification in TimesTen is predictable (Standard deviation calculated in the results in Table 6, 7 and Tables in Appendix

62

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

A, the variation is less), TimesTen can provide real-time performance. It is interesting to continue the study of real-time performance of TimesTen and its comparison with AVANTI.

6.2 Future Work There is always a room for future work. Following are some important observations for future work: •

Based on the results TimesTen is capable of providing real-time performance, enough DML services of AVANTI should be translated for TimesTen that a complete application of Power System can be executed on TimesTen, the performance of TimesTen can be examined in the real environment.

•

Performance analysis of TimesTen should be conducted with multiple processors as it is mentioned in [12] that the performance of TimesTen becomes better with increasing the number of processors.

•

Conduct the Test Cases mentioned in Chapter 5 with Oracle Call Interface (OCI) of TimesTen which is going to be part of TimesTen in next major release of TimesTen.

•

The performance of XLA (Transaction Log API) should be analyzed, as mentioned before XLA is used in implementing triggers and customized replication [8].

•

Performance analysis of TimesTen should be performed on a WAN.

63

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

7 Appendix A The results of all test cases presented in chapter 5 are mentioned here.

7.1.1 TimesTen-GETLIT-MEASURAND In this test case TimesTen is deployed in standalone mode, we retrieved 1 million records from table MEASURAND. The summary of results of three sub-test cases is illustrated in Table 10 and Figure 44. Time in milliseconds to retrieve 1 million records Test Case 1 Test Case 2 Test Case 3 with GETLIT 2220 4330 2550 CPU Time Minimum 2480 4780 2970 CPU Time Maximum 2302.657 4478.545 2613.917 Wall Clock Time Minimum 2671.807 6463.12 3408.189 Wall Clock Time Maximum 2335.238 4541.904 2752.38 CPU Time Average 4877.785 2870.358 2434.373 Wall Clock Time Average 136.221 117.724 75.473 Standard Deviation for CPU Time 520.623 171.964 104.113 Standard Deviation for Wall Clock Time Table 10: of results for TimesTen-GETLIT-MEASURAND TimesTen-GETLIT-MEASURAND

Times in milliseconds

7000

CPU Time Minimum

6000

CPU Time Maximum

5000

Wall Clock Time Minimum

4000

Wall Clock Time Maximum

3000

CPU Time Average

2000

Wall Clock Time Average

1000

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 44: Summary of results for TimesTen-GETLIT-MEASURAND

7.1.2 AVANTI-GETLIT In this test case AVANTI is deployed in standalone mode, we retrieved 1 million records from file MEASURAND, the summary of results of three sub-test cases is illustrated in Table 11 and Figure 45. Time in milliseconds for 1 million records retrieval from Test Case 1 Test Case 2 Test Case 3 AVANTI 1230 4370 1450 CPU Time Minimum 1430 4880 1670 CPU Time Maximum 1276.597 4501.398 1461.742 Wall Clock Time Minimum 1481.174 5128.284 1834.646 Wall Clock Time Maximum 1319.523 4647.619 1529.047 CPU Time Average 1355.008 4803.269 1578.606 Wall Clock Time Average 52.485 146.864 60.737 Standard Deviation for CPU Time 57.913 190.486 89.898 Standard Deviation for Wall Clock Time Table 11: Summary of Results for AVANTI-GETLIT

64

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

AVANTI-GETLIT

Time in milliseconds

6000 CPU Time Minimum

5000

CPU Time Maximum

4000

Wall Clock Time Minimum

3000

Wall Clock Time Maximum

2000

CPU Time Average Wall Clock Time Average

1000

Standard Deviation for CPU Time

0

Standard Deviation for Wall Clock Time

Test Case 1

Test Case 2

Test Case 3

Figure 45: Summary of Results for AVANTI-GETLIT

7.1.3 TimesTen-GETLIT-MEASUARND2 In this test case TimesTen is deployed in standalone mode, we retrieved 1 million records from table MEASURAND2, the summary of results of three sub-test cases is illustrated in Table 12 and Figure 46. Time in milliseconds for 1 million records retrieval CPU Time Minimum CPU Time Maximum

Test Case 1 2030 2470

Test Case 2 Test Case 3 3890 2320 4290 2610

Wall Clock Time Minimum

2068.822

4026.249

2417.298

Wall Clock Time Maximum

2604.551

4435.536

2695.012

4044.5 2207 CPU Time Average 2291.706 4188.156 Wall Clock Time Average 115.353 110.809 Standard Deviation for CPU Time 149.625 117.504 Standard Deviation for Wall Clock Time Table 12: Summary of results for TimesTen-GETLIT-MEASURAND2

2469.5 2566.576 94.394 101.695

TimesTen-GETLIT-MEASUARND2

Time in milliseconds

5000 CPU Time Minimum 4000

CPU Time Maximum

3000

Wall Clock Time Minimum Wall Clock Time Maximum

2000

CPU Time Average

1000

Wall Clock Time Average Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 46: Summary of Results for TimesTen-GETLIT-MEASURAND2

7.1.4 TimesTen-Replication-GETLIT In this test case TimesTen is deployed in replication mode, we retrieved 1 million records from table MEASURAND, the summary of results of three sub-test cases is illustrated in Table 13 and Figure 47. Time in milliseconds for 1 million records

Test Case 1

65

Test Case 2

Test Case 3

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

retrieval 2200 CPU Time Minimum 4330 2480 CPU Time Maximum 4880 2298.675 Wall Clock Time Minimum 4473.564 2570.573 Wall Clock Time Maximum 5422.05 2328.095 CPU Time Average 4591.904 2396.782 Wall Clock Time Average 4800.434 69.758 Standard Deviation for CPU Time 166.931 76.091 Standard Deviation for Wall Clock Time 245.372 Table 13: Summary of results for TimesTen-Replication-GETLIT

TimesTen-Replication-GETLIT Time in milliseconds

6000

2610 2900 2689.424 3323.114 2755.238 2887.627 84.12 153.246

CPU Time Minimum CPU Time Maximum

5000

Wall Clock Time Minimum

4000

Wall Clock Time Maximum

3000

CPU Time Average

2000

Wall Clock Time Average

1000

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 47: Summary of Results for TimesTen-Replication-GETLIT

7.1.5 AVANTI-REP-GETLIT In this test case AVANTI was deployed in replication mode and retrieval of 1 million records was done from table MEASURAND, the summary of results is illustrated in Table 14 and Figure 48. Time in milliseconds to retrieve 1 million records with Test Case 1 Test Case 2 Test Case 3 GETLIT 1240 CPU Time Minimum 4440 1440 1410 CPU Time Maximum 4870 1650 1264.012 Wall Clock Time Minimum 4541.422 1483.281 1838.845 Wall Clock Time Maximum 5194.331 1871.236 1306.667 CPU Time Average 4615.238 1533.333 1369.662 Wall Clock Time Average 4748.595 1588.457 45.971 Standard Deviation for CPU Time 131.172 65.979 122.03 Standard Deviation for Wall Clock Time 179.111 93.223 Table 14: AVANTI-REP-GETLIT

66

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

AVANTI-REP-GETLIT

Time in milli-seconds

6000 CPU Time Minimum 5000

CPU Time Maximum

4000

Wall Clock Time Minimum

3000

Wall Clock Time Maximum

2000

CPU Time Average Wall Clock Time Average

1000

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 48: Results for AVANTI-REP-GETLIT

7.1.6 TimesTen-Cache-Connect-GETLIT In this test case TimesTen is deployed in cache connect with Oracle. We retrieved 1 million records from table MEASURAND, the summary of results of three sub-test cases is illustrated in Table 15 and Figure 49 in the form of Graph. Time in milliseconds for 1 million records retrieval

Test Case 1

Test Case 2

Test Case 3

2230 CPU Time Minimum 4380 2510 CPU Time Maximum 5000 2299.731 Wall Clock Time Minimum 4478.923 3013.343 Wall Clock Time Maximum 5227.415 2355.714 CPU Time Average 4622.857 2476.78 Wall Clock Time Average 4814.824 74.335 Standard Deviation for CPU Time 165.714 168.411 191.315 Standard Deviation for Wall Clock Time Table 15: Summary of Results for TimesTen-Cache-Connect-GETLIT

2610 2870 2653.011 2998.627 2751.904 2841.122 75.803 89.951

TimesTen-Cache-Connect-GETLIT

Time in milliseconds

6000 CPU Time Minimum 5000

CPU Time Maximum

4000

Wall Clock Time Minimum

3000

Wall Clock Time Maximum

2000

CPU Time Average Wall Clock Time Average

1000

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 49: Summary of Results for TimesTen-Cache-Connect-GETLIT

7.1.7 TimesTen-MODLIT-MEASURAND In this test case TimesTen is deployed in Standalone mode. We modified 10 thousand records of table MEASURAND, the summary of results of three sub-test cases is illustrated in Table 16 and Figure 50.

67

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Time in milliseconds for 10000 records update Test Case 1 Test Case2 Test Case 3 970 1070 960 CPU Time Minimum 1570 1460 1540 CPU Time Maximum 1618.827 1797.568 1669.573 Wall Clock Time Minimum 3054.648 3004.46 2815.136 Wall Clock Time Maximum 1141.428 1220 1090.476 CPU Time Average 2057.094 2154.274 2003.156 Wall Clock Time Average 136.575 90 143.194 Standard Deviation for CPU Time 342.399 272.713 310.462 Standard Deviation for Wall Clock Time Table 16: Summary of Results for TimesTen-MODLIT-MEASURAND TimesTen-MODLIT-MEASURAND

Time in milliseconds

3500 CPU Time Minimum

3000

CPU Time Maximum

2500

Wall Clock Time Minimum

2000

Wall Clock Time Maximum

1500

CPU Time Average

1000

Wall Clock Time Average

500

Standard Deviation for CPU Time

0 Test Case 1

Test Case2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 50: Summary of Results for TimesTen-MODLIT-MEASURAND

7.1.8 AVANTI-MODLIT In this test case AVANTI is deployed in standalone mode, we updated 10 thousand records in file MEASURAND, the summary of results of three sub-test cases is illustrated in Table 17 and Figure 51. Time in milliseconds for 10000 records update

Test Case 1

Test Case 2 Test Case 3

80 130 CPU Time Minimum 170 240 CPU Time Maximum 138.853 78.553 Wall Clock Time Minimum 192.199 247.484 Wall Clock Time Maximum 112.761 177.619 CPU Time Average 122.175 191.676 Wall Clock Time Average 20.98 30.48 Standard Deviation for CPU Time 28.989 32.217 Standard Deviation for Wall Clock Time Table 17: Summary of results for AVANTI-MODLIT

68

90 140 88.198 351.029 113.809 130.473 17.457 53.023

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Time in milliseconds

AVANTI-MODLIT 400 350 300 250

CPU Time Minimum CPU Time Maximum Wall Clock Time Minimum Wall Clock Time Maximum

200 150 100 50 0

CPU Time Average Wall Clock Time Average Standard Deviation for CPU Time Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 51: Summary of Results for AVANTI-MODLIT

7.1.9 TimesTen-MODLIT-MEASURAND2 In this test case TimesTen is deployed in standalone mode, we modified 10 thousand records in table MEASURAND2, the summary of results of three sub-test cases is illustrated in Table 18 and Figure 52. Time in milliseconds for 1 million records retrieval

Test Case 1

Test Case 2

Test Case 3

500 600 CPU Time Minimum 700 730 CPU Time Maximum 621.59 737.433 Wall Clock Time Minimum 944.538 1087.245 Wall Clock Time Maximum 581.904 664.285 CPU Time Average 882.563 821.251 Wall Clock Time Average 69.829 39.695 Standard Deviation for CPU Time 77.62 80.455 Standard Deviation for Wall Clock Time Table 18: Summary of Results for TimesTen-MODLIT-MEASURAND2

390 550 498.631 680.936 455.238 581.194 40.449 50.853

TimesTen-MODLIT-MEASURAND2

Time in milliseconds

1200 CPU Time Minimum 1000

CPU Time Maximum

800

Wall Clock Time Minimum

600

Wall Clock Time Maximum

400

CPU Time Average Wall Clock Time Average

200

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 52: Summary of Results for TimesTen-MODLIT-MEASURAND2

69

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

7.1.10

Muhammad Ahmad Javed Qureshi Peter Taban

TimesTen-Replication-MODLIT

In this test case TimesTen is deployed in replication mode, we modified 10 thousand records in table MEASURAND, the summary of results of three sub-test cases is illustrated in Table 19 and Figure 53. Time in milliseconds for 10000 records Test Case 1 Test Case 2 Test Case 3 update 970 1050 870 CPU Time Minimum 1530 1200 1760 CPU Time Maximum 1240.931 1127.589 1090.99 Wall Clock Time Minimum 2502.24 1651.303 1987.663 Wall Clock Time Maximum 1113.809 1164.761 1013.333 CPU Time Average 1491.093 1623.351 1378.078 Wall Clock Time Average 175.512 111.023 75.255 Standard Deviation for CPU Time 227.197 293.633 154.288 Standard Deviation for Wall Clock Time Table 19: Summary of Results for TimesTen-Replication-MODLIT

TimesTen-Replication-MODLIT

Time in milliseconds

3000 CPU Time Minimum 2500

CPU Time Maximum

2000

Wall Clock Time Minimum

1500

Wall Clock Time Maximum

1000

CPU Time Average Wall Clock Time Average

500

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 53: Summary of Results for TimesTen-Replication-MODLIT

7.1.11

AVANTI-REP-MODLIT

In this test case AVANTI was deployed in replication mode, we updated 10 thousand records with MODLIT in AVANTI. The results are illustrated in Table 20 and Figure 54. Time in milliseconds to update 10 thousand records with Test Case 1 Test Case 2 Test Case MODLIT 3 104 182 104 CPU Time Minimum 234 351 208 CPU Time Maximum 104.228 177.17 102.219 Wall Clock Time Minimum 207.864 623.617 266.347 Wall Clock Time Maximum 147 271.142 154.142 CPU Time Average 152.001 314.896 171.227 Wall Clock Time Average 34.327 53.959 26.092 Standard Deviation for CPU Time 30.969 94.803 39.075 Standard Deviation for Wall Clock Time Table 20: Summary of results for AVANTI-REP-MODLIT

70

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

AVANTI-REP-MODLIT

Time in milli-seconds

700 CPU Time Minimum

600

CPU Time Maximum

500

Wall Clock Time Minimum

400

Wall Clock Time Maximum

300

CPU Time Average

200

Wall Clock Time Average

100

Standard Deviation for CPU Time

0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 54: Summary of Results for AVANTI-REP-MODLIT

7.1.12

TimesTen-Cache-Connect-MODLIT

In this test case TimesTen is deployed in cache connect with oracle. We modified 10 thousand records in table MEASURAND, the summary of results of three sub-test cases is illustrated in Table 21 and Figure 55. Time in milliseconds for 10000 records Test Case 1 Test Case 2 Test Case 3 update CPU Time Minimum 1010 1090 860 CPU Time Maximum 1250 1350 1110 Wall Clock Time Minimum 1535.271 1272.522 1141.702 Wall Clock Time Maximum 2278.256 2146.921 2039.056 CPU Time Average 1069.047 1156.666 990.476 Wall Clock Time Average 1859.777 1834.571 1725.871 Standard Deviation for CPU Time 51.078 58.594 59.705 Standard Deviation for Wall Clock Time 219.942 233.111 211.876 Table 21: Summary of Results for TimesTen-Cache-Connect-MODLIT TimesTen-Cache-Connect-MODLIT

Time in milliseconds

2500 CPU Time Minimum 2000

CPU Time Maximum

1500

Wall Clock Time Minimum Wall Clock Time Maximum

1000

CPU Time Average Wall Clock Time Average

500

Standard Deviation for CPU Time 0 Test Case 1

Test Case 2

Test Case 3

Standard Deviation for Wall Clock Time

Figure 55: Summary of Results for TimesTen-Cache-Connect-MODLIT

7.1.13

GETFILE and MODFILE with RTBUSA_ND

In this test case GETFILE and MODFILE are tested with sub file RTBUSA_ND. GETFILE Table 22 presents the results for GETFILE with sub file RTBUSA_ND.

71

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

1000 Iteration of GETFILE with RTBUSA_ND containing 343 records (Total number of records retrieved 343000)

Muhammad Ahmad Javed Qureshi Peter Taban

GETFILE with TimesTen

GETFILE with AVANTI

690 Min CPU Time 810 Max CPU Time 700.863 Min Wall Clock Time 648.582 Max Wall Clock Time 744.5 Average CPU Time 772.637 Average Wall Clock Time 33.478 Standard Deviation CPU Time 63.007 Standard Deviation Wall Clock Time Table 22: Results for GETFILE with RTBUSA_ND

0 10 1.899 47.399 1.5 13.776 3.663 9.904

MODFILE Table 23 presents the results for MODFILE with sub file RTBUSA_ND. 1000 Iteration of GETFILE with RTBUSA_ND MODFILE with MODFILE with containing 343 records (Total number of records TimesTen AVANTI Modified 343000) 7310 0 Min CPU Time 8780 20 Max CPU Time 7868.072 3.015 Min Wall Clock Time 10467.527 22.377 Max Wall Clock Time 7737 4 Average CPU Time 8625.299 9.24 Average Wall Clock Time 329.131 5.982 Standard Deviation CPU Time 713.219 5.648 Standard Deviation Wall Clock Time Table 23: Results for MODFILE with RTBUSA_ND

7.1.14

TimesTen-INSERT-MEASURAND

In this test case we insert 1, 100, 1000 and 10000 rows in table MEASURAND. TimesTen was deployed in standalone mode in this test case. The results of this test case are presented in Figure 56 in the form of graph. TimesTen-Insert-MEASURAND

Time in milliseconds

6000 CPU Time Minimum 5000

CPU Time Maximum

4000

Wall Clock Time Minimum

3000

Wall Clock Time Maximum

2000

CPU Time Average Wall Clock Time Average

1000

Standard Deviation for CPU Time

0 1 row

100 rows

1000 rows

10000 rows

Standard Deviation for Wall Clock Time

Figure 56: Summary of Results for TimesTen-Insert-MEASURAND

The summary of results in numerical values is presented in the Table 24. Time in milliseconds to Insert CPU Time Minimum CPU Time Maximum

1 row 0 40

72

100 rows 1000 rows 10000 rows 20 150 1600 60 310 2520

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

28.838 194.112 6.423 Wall Clock Time Minimum 30.891 66.628 384.617 Wall Clock Time Maximum 34.738 195.263 12.631 CPU Time Average 11.435 37.972 285.581 Wall Clock Time Average 10.457 12.635 40.738 Standard Deviation for CPU Time 7.556 11.707 64.309 Standard Deviation for Wall Clock Time Table 24: Summary of Results for TimesTen-Insert-MEASURAND

7.1.15

2203.59 4819.649 1737.638 2560.311 200.106 559.172

TimesTen-INSERT-MEASUARND2

In this test case we insert 1, 100, 1000 and 10000 rows in table MEASURAND2. TimesTen was deployed in standalone mode in this test case. The results of this test case are presented in Figure 57 in the form of graph.

Time in milliseconds

TimesTen-INSERT-MEASURAND2 900 800 700 600 500 400 300 200 100 0

CPU Time Minimum CPU Time Maximum Wall Clock Time Minimum Wall Clock Time Maximum CPU Time Average Wall Clock Time Average Standard Deviation for CPU Time 1 row

100 rows

1000 rows

10000 rows

Standard Deviation for Wall Clock Time

Figure 57: Summary of results for TimesTen-INSERT-MEASURAND2

The summary of results in numerical values is presented in the Table 25. Time in milliseconds to Insert 1 row 100 rows 1000 rows 10000 rows CPU Time Minimum 0 0 50 470 CPU Time Maximum 10 20 100 670 Wall Clock Time Minimum 2.896 7.321 57.219 581.819 Wall Clock Time Maximum 12.796 11.649 135.495 813.135 CPU Time Average 4.21 7.894 60 556.842 Wall Clock Time Average 4.528 8.834 69.263 697.074 Standard Deviation for CPU Time 5.072 5.353 15.634 50.558 Standard Deviation for Wall Clock Time 2.705 1.172 20.928 64.365 Table 25: Summary of results for TimesTen-INSERT-MEASURAND2

7.1.16

TimesTen-DELETE-MEASURAND

In this test case we delete 1, 100, 1000 and 10000 rows in table MEASURAND2. TimesTen was deployed in standalone mode in this test case. The results of this test case are presented in Figure 58 in the form of graph.

73

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

Time in milliseconds

TimesTen-DELETE-MEASURAND

CPU Time Minimum

2500

CPU Time Maximum

2000

Wall Clock Time Minimum

1500

Wall Clock Time Maximum

1000

CPU Time Average Wall Clock Time Average

500

Standard Deviation for CPU Time

0 1 row

100 rows

1000 rows

10000 rows

Standard Deviation for Wall Clock Time

Figure 58: Summary of Results for TimesTen-DELETE-MEASURAND

The summary of results in numerical values is presented in the Table 26. Time in milliseconds to Delete 1 row 100 rows 1000 rows 10000 rows CPU Time Minimum 0 0 10 120 CPU Time Maximum 10 20 40 200 Wall Clock Time Minimum 0.951 2.159 15.455 160.943 Wall Clock Time Maximum 8.062 14.796 54.871 2067.369 CPU Time Average 2.105 3.157 21.052 151.578 Wall Clock Time Average 2.032 6.011 28.63 433.323 Standard Deviation for CPU Time 4.188 5.823 9.365 21.67 Standard Deviation for Wall Clock Time 3.952 12.254 560.106 1.776 Table 26: Summary of Results for TimesTen-DELETE-MEASURAND

7.1.17

TimesTen-DELETE-MEASURAND2

In this test case we delete 1, 100, 1000 and 10000 rows in table MEASURAND2. TimesTen was deployed in standalone mode in this test case. The results of this test case are presented in Figure 59 in the form of graph. TimesTen-DELETE-MEASURAND2

Time in milliseconds

1000

CPU Time Minimum

800

CPU Time Maximum

600

Wall Clock Time Minimum Wall Clock Time Maximum

400

CPU Time Average

200

Wall Clock Time Average Standard Deviation for CPU Time

0 1 row

100 rows

1000 rows

10000 rows

Standard Deviation for Wall Clock Time

Figure 59: Summary of Results for TimesTen-DELETE-MEASURAND2

The summary of results in numerical values is presented in the Table 27. Time in millisecond to Delete CPU Time Minimum CPU Time Maximum Wall Clock Time Minimum Wall Clock Time Maximum

1 row 0 10 0.932 9.51

74

100 rows 0 20 2.292 30.618

1000 rows 10 60 19.638 64.282

10000 rows 130 240 174.253 913.441

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

CPU Time Average 6.842 28.947 1.052 Wall Clock Time Average 2.248 8.54 33.614 Standard Deviation for CPU Time 3.153 8.2 14.867 Standard Deviation for Wall Clock Time 2.113 7.857 16.922 Table 27: Summary of Results for TimesTen-DELETE-MEASURAND2

7.1.18

164.21 256.616 24.109 161.174

TimesTen-Concurrent-GETLIT

The results for this test case are illustrated in Table 28. Each thread retrieves 100000 records with one item. Time is presented in seconds Thread 1 Thread 2 Thread 3 Thread 4 Thread 5 Thread 6 Thread 7 Thread 8 Thread 9 Thread 10

Start Time CPU

End Time CPU

Start Time Wall Clock

End Time Wall Clock

0.29 0.98 197.365036 1.54 197.691413 0.59 0.75 2.12 197.87758 2.25 198.066373 0.92 0.16 2.54 197.236276 0.03 2.52 197.098043 2.71 198.312295 1.11 1.27 2.78 198.495248 1.48 2.81 198.710501 0.44 2.94 197.537256 Table 28: Results for TimesTen-Concurrent-GETLIT

198.149805 198.771397 199.395258 199.840148 199.874702 199.840148 200.072764 200.219843 200.278275 200.453227

Total CPU Time Consumed by all Threads: 2910 milliseconds Total Wall Clock Time Consumed by all Threads: 3355.184 milliseconds

7.1.19

TimesTen-Concurrent-MODLIT

The results for this test case are illustrated in Table 29. Each thread Modifies 1000 Records, Time is presented in seconds Thread 1 Thread 2 Thread 3 Thread 4 Thread 5 Thread 6 Thread 7 Thread 8 Thread 9 Thread 10

Start Time CPU

End Time CPU

Start Time Wall Clock

0.06 0.16 988.495016 0.16 0.24 989.168594 0.24 0.33 989.978775 0.33 0.44 990.676036 0.44 0.51 991.864979 0.52 0.59 993.1432 0.6 0.68 994.25501 0.68 0.76 995.081657 0.76 0.88 995.795524 0.89 0.97 997.007472 Table 29: Results for TimesTen-Concurrent-MODLIT

Total CPU Time Consumed by all Threads: 910 Total Wall Clock Time Consumed by all Threads: 2448.900

75

End Time Wall Clock 988.732016 989.398081 990.213911 990.949465 992.056243 993.320035 994.453069 995.320757 996.087361 997.208872

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

8 References [1] Ben Kao and Hector Garcia-Molina - AN OVERVIEW OF A REAL-TIME DATABASE MANAGED SYSTEM. Princeton University and Stanford University, (1995), URL: http://citeseer.ist.psu.edu/414630.html, accessed on 21-05-2007 [2] M.J.Carey, R. Jauhari and M. Livny - Priority in DBMS Resource Scheduling. Proceedings of the 15th VLDB conference (1989) 397-410, URL: http://www.vldb.org/conf/1989/P397.PDF, accessed on 21-05-2007 [3] Amer Abu Ali - On Optimistic Concurrency Control for Real-Time Database Systems, Faculty of Information Technology, Philadelphia University, Jordan, 2006 URL: http://www.scipub.org/fulltext/ajas/ajas321706-1710.pdf, accessed on 19-06-2007 [4] Prasun Dewan - Optimistic Concurrency Control, 1999 URL: http://www.cs.unc.edu/~dewan/242/s99/notes/trans/node7.html, accessed on 25-06-2007 [5] Sten F Andlers and Jörgen Hans - Lecture Notes in Computer Science, (Eds). Active, Real-Time, and Temporal Database System. Second International Workshop, ARTDB-97 Como, Italy, September 1997 Proceedings. [6] Sven-Erik Ängerfors and Erik Hedenblad - Network Manager, AVANTI Data management, © Copyright 2007 ABB All Rights Reserved [7] Sven-Erik Ängerfors and Erik Hedenblad - Network Manager, AVANTI Reference Manual General Introduction, © Copyright 2006 ABB All Rights Reserved [8] Oracle TimesTen In-Memory Database Introduction, http://downloaduk.oracle.com/otn_hosted_doc/timesten/702/TimesTen-Documentation/intro.pdf, accessed on 03-07-2007 [9] Introducing ODBC Drivers, URL: http://www.caspur.it/risorse/softappl/doc/sas_docs/odbc/z1010988.htm, accessed on 10-072007 [10] What is ODBC, URL: http://www.gordano.com/kb.htm?q=267, accessed on 12-07-2007 [11] Jonathan Gennick - When Microseconds Count, URL: http://www.oracle.com/technology/oramag/oracle/06-nov/o66timesten.html, accessed on 1307-2007 [12] Oracle TimesTen Product and Technologies - an Oracle White Paper, February 2007, URL: http://www.oracle.com/technology/products/timesten/pdf/wp/wp_timesten_tech.pdf, Accessed on 15-07-2007 [13] TimesTen cache connect to Oracle guide, URL: http://downloaduk.oracle.com/otn_hosted_doc/timesten/702/TimesTen-Documentation/cacheconnect.pdf [14] TimesTen to TimesTen Replication Guide, URL: http://downloaduk.oracle.com/otn_hosted_doc/timesten/702/TimesTen-Documentation/replication.pdf 76

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

[16] Krithi Ramamritham - Real Time Databases, Dept of Computer Science, University of Massachusetts, 1996, URL: http://citeseer.ist.psu.edu/cache/papers/cs/1455/http:zSzzSzwwwccs.cs.umass.eduzSz~krithizSzrtdbzSzjpdd.pdf/ramamritham93realtime.pdf (Accessed on 1807-2007) [17] Torsten Cegrel - Power System Control Technology, ISBN 0-13-688433-4 [18] ENMAC Network Solutions, URL: www.gepower.com/prod_serv/products/scada_software/en/downloads/enmac_overview.pdf, accessed on 29-07-2007 [19] Frank Luders - An Evolutionary Approach to Software Components in Embedded RealTime Systems, Mälardalen University, 2006 [20] Damir Isovic - Basic Concepts, Lecture notes, Department of Computer Science Mälardalen University, Västerås, Sweden [21] What exactly is meant by real-time? URL: http://www.omimo.be/encyc/publications/faq/rtfaq.htm#realtime_definition, accessed on 1407-2007 [22] Damir Isovic - Real-time Operating Systems, Lecture notes, Department of Computer Science, Mälardalen University, Västerås, Sweden [23] Andreas Harnesk and David Tenser - Real-Time Performance of Windows XP Embedded, April 30, 2006, URL: http://www.idt.mdh.se/utbildning/exjobb/files/TR0470.pdf [24] Damir Isovic - Real-time scheduling - part 1, Lecture notes, Department of Computer Science, Mälardalen University, Västerås, Sweden [25] Microsoft Open Database Connectivity (ODBC), URL: http://msdn2.microsoft.com/enus/library/ms710252.aspx, accessed on 2007-06-27 [26] ODBC API Reference, URL: http://msdn2.microsoft.com/en-us/library/ms714562.aspx, accessed on 2007-07-20 [27] ODBC Architecture, URL: http://msdn2.microsoft.com/en-us/library/ms710238.aspx, accessed on 2007-07-20 [28] ODBC Architecture, URL: http://www.canaimasoft.com/f90SQL/OnlineManual/Chapter02/ODBC%20architecture.htm, accessed on 2007-07-20 [29] Raghu Ramakrishnan - Introduction to Database Systems, Module 1, Lecture 1, Instructor: [email protected], UW-Madison, URL: http://pages.cs.wisc.edu/~dbbook/openAccess/firstEdition/slides/pdfslides/mod1l1.pdf, accessed on 01-08-2007

77

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

[30] Introduction to database management systems, URL: http://www.doc.ic.ac.uk/~amiri/DB05/Lecture1.pdf, accessed on 01-08-2007 [31] The Open Group Base Specifications Issue 6, IEEE Std 1003.1, 2004 Edition, Copyright © 2001-2004 The IEEE and The Open Group, All Rights reserved, time.h description, URL: http://www.opengroup.org/onlinepubs/009695399/basedefs/time.h.html, accessed on 02-082007 [32] The Single UNIX ® Specification, Version 2, sys/time.h description, Copyright © 1997 The Open Group URL: http://www.opengroup.org/onlinepubs/007908799/xsh/systime.h.html, accessed on 02-08-2007 [33] Information Management Lecture 7b, http://www.dcs.gla.ac.uk/~hcp/im2/lectures/lec7bself-study.pdf, Accessed on 20-08-2007 [34] Lise Cingiser, DippiPo and Victor Fay Wolfer - Real-Time Database, 1995, http://rtdoc.cs.uri.edu/downloads/rtdb_book_chapter.pdf, accessed on 22-08-2007 [35] Raul Barbosa - An Essay on Real-Time Databases, Department of Computer Science and Engineering, Chalmers University of Technology, SE-412 96 Goteborg, Sweden, [email protected], http://www.ce.chalmers.se/~rbarbosa/RBarbosa_AnEssayOnRealTimeDatabases.pdf, accessed on 22-08-2007 [36] Yuetang Deng, Phyllis Frankl and Zhongqiang Chen {ytdeng, phyllis, zchen}@cis.poly.edu - Testing Database Transaction Concurrency: Department of Computer and Information Science, Polytechnic University, Brooklyn, NY 11201 USA, http://cis.poly.edu/~ytdeng/paper/ASE03_DENG_PF_CHEN.pdf, accessed on 22-08-2007 [37] Ricardo M. Fricks, Antonio Puliafito and Kishor S. Trivedi - Performance Analysis of Distributed Real-Time Databases, URL: http://citeseer.ist.psu.edu/cache/papers/cs/28635/http:zSzzSzsun195.iit.unict.itzSzpublications zSz..zSzPaperszSzipds98.pdf/fricks98performance.pdf accessed on 25-08-2007 [38] Kyoung-Don Kang - QoS-Aware Real-Time Data Management, Ph.D. Dissertation University of Virginia, May 2003 [39] Suhee Kim, Sang H. Son and John A. Stankovic - Performance Evaluation on a RealTime Database, Proceedings of the Eighth IEEE Real-Time and Embedded Technology and Applications Symposium (RTAS’02) [40] Oracle TimesTen In-Memory Database, SQL Reference Guide, http://download.oracle.com/otn_hosted_doc/timesten/702/TimesTen-Documentation/sql.pdf, accessed on 27-08-2007 [41] Oracle TimesTen In-Memory Database, C-Dev Guide, http://download.oracle.com/otn_hosted_doc/timesten/702/TimesTenDocumentation/intro.pdf, accessed on 27-08-2007

78

Is It Possible To Use Oracle TimesTen While Maintaining Real-Time Performance? ABB Power Technologies

Muhammad Ahmad Javed Qureshi Peter Taban

[42] Oracle 9i Concepts Release 2, Triggers, http://downloaduk.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm, accessed on 27-08-2007 [43] William J. Ackerman and Wayne R. Block - Understanding Supervisory Systems, ©1992 IEEE

79