X

TimesTen In-Memory Database
for Extreme Performance

FEATURED POST

Platforms and Clouds for Oracle TimesTen Scaleout

Watch a short #Video where @SQLMaria and @ScalableDBDoug discuss the platforms and #Clouds that Oracle TimesTen Scaleout runs on. Disclaimer: These are my...

Recent Posts

Performance

SQLMaria on Best Hardware for Oracle TimesTen Scaleout

Watch the video where SQLMaria and ScaleableDBDoug discuss the best hardware for Oracle TimesTen Scaleout, the World's Fastest OLTP Database. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Watch the video where SQLMaria and ScaleableDBDoug discuss the best hardware for Oracle TimesTen Scaleout, the World's Fastest OLTP Database. Disclaimer: These are my personal thoughts and do not...

Introduction

Webcast for Oracle TimesTen Scaleout

See the webcast for Oracle TimesTen Scaleout by Tirthankar Lahiri, VP Data and In-Memory Technologies, Oracle Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

See the webcast for Oracle TimesTen Scaleout by Tirthankar Lahiri, VP Data and In-Memory Technologies, Oracle Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint...

SQLMaria Architecture Video of Oracle TimesTen Scaleout

Oracle guru @SQLMaria and @ScaleableDBDoug discuss the architecture of Oracle TimesTen Scaleout in this short video. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Oracle guru @SQLMaria and @ScaleableDBDoug discuss the architecture of Oracle TimesTen Scaleout in this short video. Disclaimer: These are my personal thoughts and do not represent Oracle's official...

General

Oracle TimesTen Scaleout and Oracle TimesTen Velocity Scale - one and the same

Today Oracle announced Oracle TimesTen Scaleout, the world's fastest Online Transaction Processing (OLTP) database. Some of you may have heard of Oracle TimesTen Velocity Scale. TimesTen Velocity Scale was re-named TimesTen Scaleout, they are the same thing! The following blog and video gives a brief overview of TimesTen Scaleout. See the TimesTen Scaleout product page for more information. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Today Oracle announced Oracle TimesTen Scaleout, the world's fastest Online Transaction Processing (OLTP) database. Some of you may have heard of Oracle TimesTen Velocity Scale. TimesTen Velocity Scale...

Performance

Deep Dive on TimesTen Scaleout Distribution Clauses

Overview TimesTen Scaleout is a distributed database which has a shared nothing architecture suitable for low latency, high throughput OLTP, IoT and analytic workloads. TimesTen Scaleout supports high availability, ACID transactions, SQL & PL/SQL and does not require database de-normalization. Oracle TimesTen Scaleout supports SQL operations independent of the number of hosts. However, as the database is spread over multiple hosts, network messages are required to complete most SQL operations. A fundamental performance tuning technique is to minimize the number of network messages given common use cases. TimesTen Scaleout uses table distribution clauses to enable these optimizations. TimesTen Scaleout supports three data distribution methods (Distribute by Hash, Distribute by Reference and Duplicate). The following picture is an example of a master-detail-detail hierarchy using all three distribution methods. This is not the only way to support this master-detail-detail hierarchy. Figure 1 In figure 1, the 'customers' table rows are evenly spread across all hosts in the database. The 'orders' rows are not guaranteed to have the same number of rows per host as some customers have more orders than others. Given a large number of orders, it is expected that the number of orders per host will average out. The products table is duplicated across all hosts so each host will have exactly the same number of rows per host. If an application wants to query all orders and products for a given customer, then no network messages are required for the join as all of those rows will be present on the host that contains the customer row.     Distribute by Hash For large tables that are the 'master' table [eg customers], you want to be able to evenly spread the data across all of the hosts in your database. You also want to be able to add and remove hosts from the database without having to make dramatic changes to the distribution of data for that table. The TimesTen Scaleout distribute by hash mechanism uses a consistent hash algorithm to achieve these goals. Figure 2. In figure 2, the distribution key is explicitly set to be the same as the primary key. If you do not specify a distribution clause for a table, TimesTen Scaleout will by default use the PK columns for the hash distribution clause. If there is no PK then TimesTen Scaleout will randomly distribute by hash using a hidden internal column.  You can also explicitly distribute by a set of columns which are different than the primary key. In figure 1, the customer rows are evenly distributed across the four nodes in the database. The consistent hash algorithm used is not a perfect hash algorithm so there will tend to be a small variation of the number of rows per table across. Where there are more than about ten thousand rows in a table with distribute by hash, we expect that there will be no more than 2% variation in the number of rows. Figure 3 Figure 3 shows that all of the nodes (elements) have about the same number of rows [in replica set 1] for the customers table and that there is no more than 2% variation in the number of rows for the replica set.   Distribute by Reference When you have tables that you expect to be joined often via PK/FK, you can use the distribute by reference table clause for the detail table. When the distribute by reference syntax is used, you guarantee that the child rows in the detail table will be in the same element as the corresponding parent row. This means that a join of those PK/FK will occur on the same element. Joins on the same node will occur in memory in TimesTen Scaleout, so those joins can occur very quickly. As shown in figure 1, the customers table rows are spread evenly over all nodes, but the orders rows for the customers table PKs are always on the same node. For distribute by reference to work, the parent table has to have a PK and the child table has to have one or more FKs. When there is more than one FK, you choose the FK that will be the most used for your workload.. Figure 4 In figure 4, the FK_CUSTOMER foreign key was chosen over the FK_ACCT_TYPE and FK_ACCT_STATUS foreign keys. This picture also shows that the table is distributed by a set of columns [ie CUST_ID] which is different than the PK columns for the table [ie ACCOUNT_ID]. Figure 5. In figure 5, with a million rows in the accounts table, there is a fairly even distribution of rows across all of the nodes with less than 1% variation.   Duplicate Distribution For tables that are often joined to other tables and are 'read mostly', the duplicate distribution clause can be very effective. The contents of these tables are copied/duplicated across all elements in the database. The advantage of duplicate distribution is that these tables can always enable efficient in-memory joins. The disadvantage of duplicate tables is that inserts, updates and deletes to these tables require two phase commit transactions to all other nodes in the database. Figure 6. In figure 6, the account_type table uses the duplicate table distribution clause. Figure 7. Figure 7 shows that duplicate tables always have the same number of rows.  The account_type table only has six rows, but duplicate tables could have billions of rows as long as they are read mostly.   Explicit table distribution clauses are optional performance optimizations for TimesTen Scaleout. Although indexes and materialized views can also help tune data access, always tune your table distribution clauses first.   Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Overview TimesTen Scaleout is a distributed database which has a shared nothing architecture suitable for low latency, high throughput OLTP, IoT and analytic workloads. TimesTen Scaleout supports high...

General

TimesTen Scaleout Terminology

Introduction Oracle TimesTen Scaleout is a distributed database that has its own terminology. This blog discusses those terms and common variations.   Host A TimesTen Scaleout host is a physical machine, a virtual machine or a container. As TimesTen Scaleout has a shared nothing architecture, each host logically has its own CPU, memory, persistent storage and networking. For optimal performance and availability, physical [bare metal] machines should be used as hosts. Virtual machines can have significant network and disk IO overhead compared to bare metal machines. Also virtual machines and containers need to share their resources (CPU, memory, persistent storage and networking).   Database A database is a set of users, tables and other schema objects that applications can connect to. Every database is distributed across all the data instances.       Instance A TimesTen Scaleout instance is a running copy of the TimesTen software. The instance includes the processes and files that exist on a host. A host may contain one or more instances. Each instance contains a single element of the database. For optimal high availability, you should configure one instance per host. For development and test environments, it can be convenient and cheaper to configure multiple instances per host. If you configure multiple instances per host, you need to make sure that there is sufficient memory and disk space for each instance. Having multiple instances per hosts means that each instance needs to share the hosts CPU, network, disks and memory.   Element Each instance contains a single element. Elements correspond to the traditional TimesTen In-Memory Database components (shared memory, daemon and sub-daemon processes, checkpoint files and transaction log files). Each element can persistently store its own data and be recovered independently.   Replica Sets and Data Space Groups A TimesTen Scaleout database will usually consist of many hosts.To enable high availability and avoid single points of failure, there can be multiple copies of an element. The copies of an element are called relica sets.  TimesTen Scaleout uses the two phase commit protocol which means that the copies of an element are relicas of each other. The number of copies of an instance corresponds to the number of data space groups. A TimesTen Scaleout database with a single data space means that there is no data redundancy and that each instance is a single point of failure. A TimesTen Scaleout database with two data spaces means that there is data redundancy and that each element is not single point of failure. The picture on the left shows that the intersection of a replica set and the data space group is an instance. In the picture on the right, if a host contains a single instance then the intersection of replica set and a data space group is a host. Having a single instance per host provides the best availability. The above picture shows how SQL Developer displays data space groups and replica sets.   K-Safety TimesTen Scaleout uses the term K-Safety to define the number of copies of an element. K copies of an element corresponds to the number of data space groups. A database with K=2 will have two data spaces. A database with K=1 will have one data space.   NxK Database To simply describe a configuration, the NxK term to describes the number of replica sets (N) and the K Factor (K) for a database. The above is an 8x1 grid as there are 8 replica sets and only a single data space (K=1). The above is an 8x2 grid as there are 8 replica sets and two data spaces (K=2). The above shows two different representations of a 3x2 grid as there are 3 replica sets and two data spaces (K=2).    Installation TimesTen Scaleout defines an installation as the read only copy of the files in the TimesTen software distribution.     Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Introduction Oracle TimesTen Scaleout is a distributed database that has its own terminology. This blog discusses those terms and common variations.   Host A TimesTen Scaleout host is a physical machine,...

Performance

What is the best hardware for TimesTen Scaleout

Introduction TimesTen Scaleout is Linux software that runs on x8664 hardware. The performance of TimesTen Scaleout is dependent on the performance of the hardware components - you get what you pay for. Given well balanced hardware, you can expect the performance of TimesTen Scaleout to be proportional to the performance of the hardware. For example even though TimesTen Scaleout performs well on older hardware or small VMs, it will tend to perform much better on the latest hardware, larger VMs or bare metal machines. Well balanced hardware means that the critical components (ie CPU, network cards, persistent storage and DRAM) are equivalent. There is no point having really fast disk and a slow network or a really fast network and slow CPUs.     CPU TimesTen Scaleout is an In-Memory database which ideally will be CPU bound, so the faster your CPU the better: CPUs with higher core counts, faster clock speeds and large L3 caches perform the best The number of CPU cores per machine and the number of machines determines the effective concurrency and throughout   Persistent Storage TimesTen Scaleout databases still need to persist their data, so storage devices with high bandwidth and low latency are best: Storage devices with non volatile memory are significantly faster than traditional spinning disks The capacity of the storage devices needs to be approximately three times that of the DRAM used by the database   Network Cards TimesTen Scaleout is a shared nothing database that passes messages over a TCP/IP Ethernet network The lower the network latency and higher the bandwidth the better Although 1G Ethernet works with TimesTen Scaleout, it is only suitable for development or test environments For production environments you should use 10G Ethernet network cards For improved performance, you can use two 10G Ethernet network cards as a single network card can become a bottleneck when the database is performing millions of transactions per second Although 25G, 40G and 100G Ethernet network cards are 'better' than 10G Ethernet cards, they are not required based on current benchmarks Under extreme load, multiple 10G Ether network cards are still better than one 25, 40 or 100G Ethernet card as the bottleneck tends to be context switches rather than bandwidth     DRAM As TimesTen Scaleout is an in-memory database, you need to have sufficient DRAM to store all of your data The capacity of a TimesTen Scaleout database is determined by the sum of the DRAM of each of the hosts The more hosts that you use in your database, the larger the database that you can support Currently DDR4 DRAM gives the best memory density and bandwidth   On Premise Hardware You can choose whatever x8664 hardware that you want on premise. The following vendors all make good server class x8664 hardware:   In the Cloud In the Oracle Cloud, dedicated Bare Metal DenseIO systems give the best performance, eg BM.DenseIO1.36 or BM.DenseIO2.52 For other clouds, you will tend to get optimal performance based on dedicated shapes with a good balance of memory density, CPU cores, disk and network performance. You can run TimesTen Scaleout using multipe small VMs in your favorite cloud (eg VM.Standard1.1), however you will be limited by the CPU, DRAM, disk IO and networking of those VMs. You get what you pay for!     Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Introduction TimesTen Scaleout is Linux software that runs on x8664 hardware. The performance of TimesTen Scaleout is dependent on the performance of the hardware components - you get what you pay for. G...

Introduction

An Introduction to Oracle TimesTen Scaleout

Introduction Finally there is a scale out database where you don't need to trade data consistency for scalability. A database where you don't need to de-normalize to get extreme performance. An OLTP SQL database that is faster than NoSQL databases. This database is called TimesTen Scaleout. Oracle TimesTen In-Memory Database has been a leading relational in-memory database for mission critical applications for over 20 years. Oracle TimesTen In-Memory Database has an exciting new scalability feature called TimesTen Scaleout. TimesTen Scaleout is a distributed database which has a shared nothing architecture suitable for low latency, high throughput OLTP, IoT and analytic workloads. TimesTen Scaleout supports high availability, ACID transactions, SQL & PL/SQL and does not require database de-normalization.   Scale Out To enable linear scalability, TimesTen Scaleout uses a shared nothing architecture. This means that the database can scale horizontally over a set of machines. Adding more machines to a TimesTen Scaleout database enables more connections, greater throughput, larger capacity and higher availability. Adding more machines also enables SQL operations to perform in parallel. In general, the more machines the more effective the parallel SQL processing.   High Availability A TimesTen Scaleout database will usually span several machines. Multiple copies of the data can be configured so that there are no single points of failure. Having multiple copies of the data means that transient hardware and software failures do not affect the availability of the database. If all copies of the data become permanently un-available, then the database can be recovered from backup.   ACID Transactions Despite being an ‘In-Memory Database’, TimesTen databases are persistent across system or power failures. TimesTen persists data to a disk or flash filesystem.TimesTen Scaleout uses ACID (Atomic, Consistent, Isolated & Durable) transactions, just like Oracle databases. TimesTen Scaleout achieves strong consistency via an optimized Two Phase Commit Protocol [2PC]. Unlike various 'eventual consistency' mechanisms, the 2PC protocol ensures that all changes are atomic. This strong consistency means that the transactional model is the same as if a single instance RDBMS is being used.  This means that developers do not need to worry about how transactions work, they just need to commit or rollback their units of work.   Single Database Image Although the database can consist of many physical or virtual machines, it still appears to be a single logical database to developers and applications. Developers just see a single database with Schemas, Tables, Primary Keys, Foreign Keys, Indexes, Views, Materialized Views and Sequences. Applications can modify any number of rows stored on any combination of machines in a single transaction.   Table Distribution Clauses As TimesTen is an In-Memory Database, it can execute extremely fast SQL Select/Insert/Update/Delete and Join operations within a single machine. With other scale-out databases, as you spread your database across multiple machines you will tend to have performance problems. TimesTen Scaleout solves this by allowing you to co-locate commonly accessed data on the same machine. Depending on the SQL operation, data co-location can minimize or even eliminate network messaging and has a dramatic effect on performance and scalability. Data co-location is configured via table distribution clauses. By default, rows in a table are evenly distributed across the machines in the TimesTen Scaleout database via consistent hash. For detail tables that are often joined, the foreign keys can be co-located with their parent table via the Distribute By Reference syntax. Reference tables which are 'read mostly' can be duplicated across all machines in a TimesTen Scaleout database via the Duplicate syntax.   Benchmarks As TimesTen Scaleout uses a shared nothing architecture, it is possible to achieve linear scaleability for well tuned SQL workloads. At Oracle Open World 2017, a beta version of TimesTen Scaleout achieved 1.2 Billion SQL selects per second for 100% reads with the TPTBM workload. This benchmark used 64 BM.HighIO1.36 compute nodes in the Oracle Cloud. By adding more Oracle Cloud compute nodes to the TimesTen Scaleout database the throughput continued to increase and this benchmark showed linear scaleability up to 64 machines.   The same TPTBM workload also showed linear scalability with 80% reads and 20% writes achieving 144 million transactions per second using 64 database elements on 32 BM.HighIO1.36 compute nodes in the Oracle Cloud. The Oracle Cloud Analytic Workload is a good example of a complex analytic workload [it uses the TCP-H schema and data but uses more complex queries] that scales as more machines are added to the TimesTen Scaleout database. This chart shows that as more machines are added to the TimesTen Scaleout database, the time to run all of the analytic queries is reduced. Specifically as the number of machines doubles, the latency halves. This dramatic reduction in latency is constrained by Amdahl's law.   SQL APIs TimesTen Scaleout supports SQL and PL/SQL via JDBC, ODBC, OCI, Pro*C, ttClasses and ODP.NET. TimesTen Scaleout also works with open source languages with SQL APIs that use an Oracle OCI driver or ODPI.   Developing Applications for TimesTen Scaleout Existing TimesTen applications should work on TimesTen Scaleout with little or no change. Chongqing Mobile, [a subsidiary of China Mobile] recently went into production using TimesTen Scaleout by deploying an existing TimesTen application with only two changes. They changed the connect string to point to TimesTen Scaleout and they added distribution clauses to the tables to optimize the queries. Also as TimesTen Scaleout uses a subset of Oracle Database compatible APIs, SQL, PL/SQL and data types, Oracle Database application developers can easily target their applications at TimesTen. Details on how to develop applications for TimesTen Scaleout can be found here.   SQL Developer Support for TimesTen Scaleout SQL Developer 18.2 supports full life cycle management for TimesTen Scaleout 18.1. A SQL Developer window showing that the accounts table uses the distribute by reference table distribution clause. Some SQL Developer windows showing a data model and the hash distribution of rows for the customers table.   Platforms TimesTen Scaleout is officially supported on these platforms and has been tested on the following Linux distributions:   TimesTen Scaleout is officially supported on Oracle VM and has been tested on the following VMs:   Hardware TimesTen Scaleout is software that runs on Linux x8664 hardware. The performance of TimesTen Scaleout is dependent on the performance of the hardware components - you get what you pay for. Dedicated bare metal machines will tend to give better performance than Virtual Machines.   Downloads Oracle TimesTen 18.1 Scaleout can be downloaded from here Oracle SQL Developer 18.2 can be downloaded from here A VirtualBox VM with a Hands On Lab for TimesTen 18.1 Scaleout can be downloaded from here Sample code for TimesTen 18.1 Scaleout can be downloaded from here   Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Introduction Finally there is a scale out database where you don't need to trade data consistency for scalability. A database where you don't need to de-normalize to get extreme performance. An OLTP...

Performance

Scaling SQL to millions of transactions per second with a single database

Introduction Oracle TimesTen In-Memory Database has always had very low latency, now it also has extreme scalability.  TimesTen 18.1.1.2.0 has a new scale-out feature called TimesTen Scaleout that enables linear scalability via a shared nothing architecture. TimesTen Scaleout is a SQL RDBMS that supports ACID transactions and persists its data to disk. The above chart shows a benchmark that achieved 144 million transactions per second using the TPTBM workload with 80% SQL selects and 20% SQL updates. The above chart shows a benchmark that achieved 1.2 billion transactions per second using the TPTBM workload with 100% SQL selects. The rest of this blog goes into the details of what this means and how it was achieved. These benchmarks were run for several hours to obtain the 'steady state' behavior and avoid any short term 'caching effects'.   TPTBM Workload The Telecom Provider Transaction Bench Mark [TPTBM] workload models a simple transactional workload used by some telecommunications providers. The TPTBM workload uses a single table: CREATE TABLE vpn_users( vpn_id TT_INT NOT NULL, vpn_nb TT_INT NOT NULL, directory_nb CHAR(10) NOT NULL, last_calling_party CHAR(10) NOT NULL, descr CHAR(100) NOT NULL, PRIMARY KEY (vpn_id, vpn_nb) ); This table was populated with 640 million rows. The SQL select statement used a primary key lookup with randomly generated values for the bind variables : select directory_nb, last_calling_party, descr from vpn_users where vpn_id = ? and vpn_nb = ?; The update statement updates the 'last_calling_party' column via the primary key with randomly generated values for the bind variables : update /*+ TT_CommitDMLOnSuccess(1) */ vpn_users set last_calling_party = ? where vpn_id = ? and vpn_nb = ?; The TPTBM workload is one of the Quickstart sample programs that TimesTen has made available for over ten years. The source code for TPTBM is available in C / ODBC and Java / JDBC.   Hardware Configuration These two benchmarks were run on the same hardware in Oracle Cloud Infrastructure. 64 BM.HighIO1.36 bare metal compute instances were used: The BM.HighIO1.36 compute instances were Oracle X5-2 servers with NVMe storage and a single 10G Ethernet network card using TCP/IP. TimesTen Scaleout is a distributed SQL RDBMS database with a shared nothing architecture. A TimesTen Scaleout database has a single database image. That means that each user sees a single database schema with tables, indexes, sequences, views and materialized views. As the benchmarks use 64 compute servers in the Oracle Cloud, logically the TimesTen Scaleout database looked like this: The single VPN_USERS table was accessible on each of the 64 machines. The data was automatically distributed across the 64 machines using a consistent hash on the primary key (the VPN_ID and VPN_NB columns). Each machine contained about 10 million rows and there were 640 million unique rows in the VPN_USERS table. As the SQL Select and Update statements used random values for the primary key bind values, this means that each transaction could occur on any of the 64 machines.   Software The BM.HighIO1.36 compute instances used the standard Oracle Linux 7.4 operating system image [Red Hat 7.4 compatible] that is available for Oracle Cloud Infrastructure instances. The Oracle TimesTen Scaleout 18.1.1.2.0 software was used with the following database configuration: [tptbm] PermSize=40000 TempSize=8192 CkptLogVolume=4096 LogBufMB=4096 LogFileSize=4096 LogBufParallelism=16 Connections=10000 LockWait=3600 DatabaseCharacterSet=US7ASCII ConnectionCharacterSet=US7ASCII Datastore=/u10/instances/tptbm/tptbm LogDir=/u10/instances/logs PrivateCommands=1 PLSQL_MEMORY_SIZE=1024     How to make it Scale The following techniques were used to make the TPTBM workload scale to millions of transactions per second: Use the Oracle TimesTen Scaleout feature to scale horizontally using many machines The database was spread over 64 elements using BM.HighIO1.36 servers in Oracle Cloud Infrastructure. Use the TT_CommitDMLOnSuccess SQL directive This SQL hint avoids an extra round trip for DML operations that you know will be followed by a commit operation. Use the C Routing API : The routing API is a way to enable simple transactions to determine which machine that a row exists on. The routing API imports the hash distribution map to the client, so that transactions can be executed on the element where the data resides avoiding a network hop.   Note The BM.HighIO1.36 shape has been phased out in Oracle Cloud and has been replaced by the BM.DenseIO1.36 shape. BM.DenseIO1.36 compute instances are also Oracle X5-2 servers with more NVMe storage and a single 10G Ethernet network card. While testing these TPTBM workloads, we did not see a performance difference between the BM.HighIO1.36 and BM.DenseIO1.36 shapes.   Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.      

Introduction Oracle TimesTen In-Memory Database has always had very low latency, now it also has extreme scalability.  TimesTen 18.1.1.2.0 has a new scale-out feature called TimesTen Scaleout that...

HowTo

Easy Steps to Configure a TimesTen Scaleout Environment for Experimentation

An easy way to get started with TimesTen Scaleout is to set it up on a single host. While normally TimesTen Scaleout would be deployed across more than one, installing everything on just one host allows you to learn about the product and perform experiments with minimal hardware requirements.  Naturally such a configuration should not be used for production use or performance testing. To set up TimesTen Scaleout on a single host your hardware should meet the following requirements: Run a supported 64-bit Linux distribution such as Oracle Linux 6 or 7, Red Hat Enterprise Linux 6 or 7, or SUSE Enterprise Server 12 12 GB of RAM  Has Java release 1.8 (JDK 8) or greater installed This procedure will configure 5 TimesTen instances: 1 management instance and 4 data instances. It will also create a single database which spans the 4 data instances. Each row of data in this database will be stored in at least two data instances. To get started, download TimesTen 18.1 from the Oracle Technology Network. Then follow the steps in the linked pages below. Step 1:  Install the TimesTen Scaleout Software Step 2:  Install Zookeeper and setup memberships Step 3:  Create a database definition file Step 4: Rollout a 2x2 grid on your local machine using ttGridRollout utility Optional: Step 5: How to Stop a TimesTen Scaleout Database Optional: Step 6: How to Start/Restart a TimesTen Scaleout Database   For more information about TimesTen Scaleout, see the TimesTen 18.1 documentation library or visit Oracle Technology Network for TimesTen. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

An easy way to get started with TimesTen Scaleout is to set it up on a single host. While normally TimesTen Scaleout would be deployed across more than one, installing everything on just one host...

HowTo

How to deploy TimesTen Scaleout for experimentation using ttGridRollout

  To use TimesTen Scaleout you must create a grid. A grid is a set of TimesTen instances; each instance is a running copy of the TimesTen software. There are several ways to create a grid. In this example we will use the ttGridRollout utility. The ttGridRollout utility reads a configuration file that you create and it then uses ttGridAdmin commands behind the scenes to create your desired grid.  1. Create a ttGridRollout configuration file. Here are the attributes used for this local demo ttGridRollout configuration file. This file is called localdemo.conf.  Replace "ttgridvm" with your host's host name. grid_name = localdemo zoo_conf = /grid/membership/membership.conf instance_location = /grid/inst dbdef_file = /grid/config/sampledb.dbdef mgmt_instances = [ { "host":"mgmthost1", "address": "ttgridvm", "instance": "mgmtinstance1", "daemonport":5624, "csport":5625, "mgmtport":3754} ] data_instances = [ { "host":"datahost1", "address": "ttgridvm", "dataspacegroup": 1, "instance":"instance1", "daemonport":6624, "csport":6625}, { "host":"datahost2", "address": "ttgridvm", "dataspacegroup": 1, "instance":"instance2", "daemonport":7624, "csport":7625}, { "host":"datahost3", "address": "ttgridvm", "dataspacegroup": 2, "instance":"instance3", "daemonport":8624, "csport":8625}, { "host":"datahost4", "address": "ttgridvm", "dataspacegroup": 2, "instance":"instance4", "daemonport":9624, "csport":9625} ]  2. Execute ttGridRollout. Note the location of the ttGridRollout utility is in  the directory where the TimesTen software package was unzipped to. $/grid/sw/tt18.1.1.2.0/bin/ttGridRollout localdemo.conf ... ========================================================== 4-instance (2x2) grid successfully created. Management Instance Location ----------------------------  ttgridvm:/grid/inst/mgmtinstance1 Please source ttenv script under Management Instance for grid management via "ttGridAdmin" commands.   On ttgridvm:   sh:  . /grid/inst/mgmtinstance1/bin/ttenv.sh   csh: source /grid/inst/mgmtinstance1/bin/ttenv.csh Data Instance Locations -----------------------  ttgridvm.instance1 ==> ttgridvm:/grid/inst/instance1  ttgridvm.instance2 ==> ttgridvm:/grid/inst/instance2   ttgridvm_2.instance3 ==> ttgridvm:/grid/inst/instance3   ttgridvm_2.instance4 ==> ttgridvm:/grid/inst/instance4 Please source ttenv script under Data Instances for database operations.   For example, to use instance1, on ttgridvm:   sh:  . /grid/inst/instance1/bin/ttenv.sh   csh: source /grid/inst/instance1/bin/ttenv.csh 3. Using ttIsql to connect to the sampledb database in this 2x2 grid. Connection can be done from one of the data instances. $ /grid/inst/instance1/bin/ttenv ttisql sampledb Copyright (c) 1996, 2018, Oracle and/or its affiliates. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=sampledb"; Connection successful: DSN=sampledb;Durability=0;UID=oracle;DataStore=/grid/db/instance1/sampledb;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;LogFileSize=64;MemoryLock=4;LogBufMB=64;PermSize=128;TempSize=128;Connections=100; (Default setting AutoCommit=1) Command> exit; Disconnecting... Done. At this point, you have created a 2x2 grid on your local machine with a sample database called sampled. You may continue your database operations (i.e. DDL, DML..etc) like any database from this point onward. Here are some helpful blogs for creating DDLs in a TimesTen database: How to Manage User Accounts and Privileges in TimesTen? How to Create Database Schema in the TimesTen Scaleout Database using TTISQL?     Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

  To use TimesTen Scaleout you must create a grid. A grid is a set of TimesTen instances; each instance is a running copy of the TimesTen software. There are several ways to create a grid. In this...

HowTo

How to Install TimesTen Scaleout

  To install TimesTen 18.1 on your host, create a directory to contain the software and uncompress the downloaded zip file into that directory. (You will need the Linux unzip utility in order to uncompress the downloaded file.) Once the downloaded software is uncompressed, then the installation is complete!  In this example we will install TImesTen to the /grid/sw directory. The original file that was downloaded from Oracle is in /tmp.  $ cd /grid/sw $ unzip /tmp/timesten181110.server.linux8664.zip ... $ You must also make sure that your Linux kernel is properly configured to run TimesTen. Since in this experiment we are installing everything on one host for experimentation, we can skip a few configuration parameters that would be required for a production environment.   % sudo vi /etc/sysctl.conf ... kernel.shmmax=51539607552 kernel.shmall=14680064 kernel.sem = 4000 400000 2000 2560 Once /etc/sysctl.conf is configured, run "sudo /sbin/sysctl -p" to reconfigure the kernel without needing to reboot. Once this is done, then you are ready to go to the next step in the quickstart blog.   Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

  To install TimesTen 18.1 on your host, create a directory to contain the software and uncompress the downloaded zip file into that directory. (You will need the Linux unzip utility in order to...

HowTo

How to Install ZooKeeper for TimesTen Scaleout

TimesTen Scaleout allows a single database to span multiple hosts. Each host will run at least one TimesTen instance. To keep track of the status of all the instances, TimesTen Scaleout uses Apache ZooKeeper. ZooKeeper serves as a membership service for TimesTen Scaleout. The Apache ZooKeeper distribution is packaged with the TimesTen software. For production use you should have at least 3 ZooKeeper servers, but for this demonstration only 1 server is configured. The Apache ZooKeeper membership service used by TimesTen Scaleout does require Java release 1.8 (JDK 8) or greater on the ZooKeeper server.   Follow these steps to install ZooKeeper and configure ZooKeeper members: Create a directory where you want to put the ZooKeeper installation and unpack ZooKeeper into that directory $ mkdir -p /grid/membership $ tar -zvxf /grid/sw/tt18.1.1.1.0/3rdparty/zookeeper-3.4.10.tar.gz -C /grid/membership Configure the ZooKeeper server. Create the following conf/zoo.cfg file in the location where you’ve untarred the ZooKeeper software, but replace ttgridvm with your local machine host name. % vi /grid/membership/zookeeper-3.4.10/conf/zoo.cfg tickTime=250 initLimit=40 syncLimit=12 dataDir=/grid/membership/data clientPort=2181 autopurge.snapRetainCount=3 autopurge.purgeInterval=1 server.1=ttgridvm:2888:3888 Start the ZooKeeper membership server. $ /grid/membership/zookeeper-3.4.10/bin/zkServer.sh start    ZooKeeper JMX enabled by default    Using config: /grid/zookeeper/zookeeper-3.4.10/bin/../conf/zoo.cfg    Starting zookeeper ... STARTED $ TimesTen Scaleout needs to know where the ZooKeeper server is. This is done through client membership configuration file.   This membership client configuration contains server.x and clientPort parameter values that was in the zoo.cfg file.  For this demo, the client membership configuration file is /grid/membership/membership.conf.   $ vi /grid/membership/membership.conf Servers ttgridvm.oracle.net!2181   If you need further information on ZooKeeper installation and membership configuration, please see TimesTen Documentation.You are now ready for the next step in the quickstart blog. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

TimesTen Scaleout allows a single database to span multiple hosts. Each host will run at least one TimesTen instance. To keep track of the status of all the instances, TimesTen Scaleout uses Apache...

HowTo

How to Create a Database Definition File

  A Database Definition file contains attributes defining the database. The database definition file must use .dbdef as the file name suffix. The name of the database definition derives from the name of the database definition file.  Here are the attributes used for this local machine demo database.  You should change the directory of the DataStore to fit the environment of your location machine where you would like the database files to be located.  Also, note the location of the .dbdef file is under directory /grid/config. $ cat /grid/config/sampledb.dbdef DataStore=/grid/db/!!INSTANCE_NAME!!/sampledb PermSize=128 TempSize=128 LogBufMB=64 LogFileSize=64 MemoryLock=4 DatabaseCharacterSet=AL32UTF8 ConnectionCharacterSet=AL32UTF8 Durability=0   Once this file is created, you are now ready to go to the next step in the quickstart blog.   Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

  A Database Definition file contains attributes defining the database. The database definition file must use .dbdef as the file name suffix. The name of the database definition derives from the...

HowTo

How to Start a TimesTen Scaleout Database using ttGridAdmin Command

  Before you can use a database, all of the instances in the grid must be started. When the grid is setup for the first time whether using the Setting up a Grid procedure outlined in the Oracle TimesTen In-Memory Database Scaleout User's Guide or using the Quickstart Steps for configuring a grid for experimentation blog, the instances are automatically started. If you manually shut down your grid using the procedure in the previous page then you can use this procedure to start it again and to load your database into RAM, reading it for use.   To start your grid and load your database, a number of steps are required: Start your management instance Start your data instances Load your database into RAM Open the database for use Like all management operations, these steps are all performed from your grid's management instance using the ttGridAdmin command. 1. Source the TimesTen environment on the management instance Before you can proceed to stop the TimesTen database on the management instance, you need to source the TimesTen environment.  For the example below, the management instance is installed to /grid/mgmtinstance1 directory. $ source /grid/inst/mgmtinstance1/bin/ttenv.sh Commands that you execute in this shell will now be executed against the management instance in your grid. Alternatively you can prefix any command you want to run with the ttenv script for the instance you wish to use, for example: $ /grid/inst/mgmtinstance1/bin/ttenv ttGridAdmin dbStatus 2. Start your management instance $ ttGridAdmin mgmtActiveStart 3. Start your data instances $ ttGridAdmin instanceExec -type data ttDaemonAdmin -start 4. Load Database into Memory $ ttGridAdmin dbload -wait sampledb 5. Open the TimesTen Database After the database is loaded into memory the instance administrator user can connect to it, but other users can not. When the database is ready for other users to connect, you should open the database: $ ttGridAdmin dbopen -wait sampledb For more information about stopping a database, see How to Stop a TimesTen Scaleout Database using ttGridAdmin blog or visit the TimesTen 18.1 documentation library. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

  Before you can use a database, all of the instances in the grid must be started. When the grid is setup for the first time whether using the Setting up a Grid procedure outlined in the Oracle TimesTen...

HowTo

How to Stop a TimesTen Scaleout Database using ttGridAdmin Command

When you are done using your grid, you should shut it down gracefully. Naturally TimesTen Scaleout can recover from a variety of failures, including abrupt, unplanned ones. But it is always preferable to shut the system down in a controlled manner whenever possible. There are several steps involved in gracefully shutting down a grid and the databases it provides: Prevent new connections to the database Unload the database from RAM Shut down your grid's data instances Shut down your grid's management instance  Like all management operations, these steps are all performed from your grid's management instance using the ttGridAdmin command. 1. Source the TimesTen Environment on the Management Instance Before you can proceed to stop the TimesTen database on the management instance, you need to source the TimesTen environment.  For the example below, the management instance is installed to /grid/inst/mgmtinstance1 directory. $ source /grid/inst/mgmtinstance1/bin/ttenv.sh Commands that you execute in this shell will now be executed against the management instance in your grid. Alternatively you can prefix any command you want to run with the ttenv script for the instance you wish to use, for example: $ /grid/inst/mgmtinstance1/bin/ttenv ttGridAdmin dbStatus 2. Closing the database When your database is "open" then applications can connect to it. Closing the database prevents new application connections (other than connections from the priveleged instance administrator user).  When ttGridRollout initially creates a database it opens the database.  To close it, run the ttGridAdmin dbClose command: $ ttGridAdmin dbclose -wait sampledb 3. Unloading the database Since TimesTen is an in-memory database, databases can only be used when they are loaded into RAM. TimesTen Scaleout provides the ttGridAdmin dbLoad and ttGridAdmin dbUnload commands to load and unload databases into memory. Before unloading a database from memory you should stop all applications that are connected to it.   $ ttGridAdmin dbunload -wait sampledb TimesTen databases are automatically persisted to disk storage using periodic checkpoints and transaction log files. Before a database is unloaded, a final checkpoint is automatically performed behind the scene. 4. Stopping your data instances When the database is closed and unloaded from memory, you can then shutdown your data instances.  $ ttGridAdmin instanceexec -type data ttDaemonAdmin -stop 5. Stopping your management instance Finally you can shut down your management instance $ ttGridAdmin mgmtActiveStop You have now stopped all of your instances in this grid.  If you plan to perform some sort of maintenance on the hardware you may proceed. See How to Restart TimesTen Scaleout Database blog  or TimesTen 18.1 documentation library for more information when you are ready to restart this grid. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

When you are done using your grid, you should shut it down gracefully. Naturally TimesTen Scaleout can recover from a variety of failures, including abrupt, unplanned ones. But it is always preferable...

HowTo

How to Manage User Accounts and Privileges in TimesTen?

For users to access your TimesTen database, you must create user accounts and grant appropriate database access privileges to those accounts. A user account is identified by a user name and a password. Only the instance administrator and users with the system privilege ADMIN can create user accounts. Creating a User Account To create a new database user, perform the following steps: On a data instance, source the TimesTen environment $ source /home/oracle/TimesTen/instances/instance1/bin/ttenv.sh Enter ttIsql <connectable_name> at the OS command prompt to launch ttIsql and connect to your database $ ttIsql sampledb Use the SQL statement CREATE USER to create a database user account. CREATE USER <username> IDENTIFIED BY <password>; You should create an database name "appuser" to work with the sampledb database. For example, CREATE USER appuser IDENTIFIED BY <password>;   Once a user account has been created, the appropriate user privileges should be granted to the account. They should be granted based on their needs. For example, an application developer needs privileges to insert into tables and to execute stored procedures. However, a developer would not typically be allowed to perform administrative tasks such as backing up the database and removing user accounts. There are two types of user privileges. 1. System privileges These privileges give a user the ability to perform system-level activities across multiple objects in the database, or to perform an action on any schema objects of a particular type. For example, the system privilege CREATE TABLE permits a user to create tables in the schema associated with that user. 2. Object privileges These privileges give a user the ability to perform a particular action on a specific schema object. The privilege to delete rows from the EMPLOYEES table is an example of object privileges. The owner (the creator) of the object always has full privileges to the object. For a listing of the available privileges, refer to the Oracle TimesTen In-Memory Database SQL Reference.   Granting and Revoking Privileges The SQL statements GRANT and REVOKE enable system and object privileges to be granted to and revoked from database users. For example: To grant the CREATE TABLE privilege to a user called HR, run the following command: GRANT CREATE TABLE to hr; To revoke the DELETE privilege on employees from hr, run the following command: REVOKE DELETE ON employees FROM hr; For more information on user accounts and privileges, refer to the Oracle TimesTen In-Memory Database Security Guide. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

For users to access your TimesTen database, you must create user accounts and grant appropriate database access privileges to those accounts. A user account is identified by a user name and...

HowTo

How to Create Database Schema in the TimesTen Scaleout Database using TTISQL

There are 3 database distribution types in TimesTen Scaleout.  A sample SQL script  (file name , cr_tables.sql) is included  utilizes all 3 distribution types: hash, duplicate and reference. Distributed by Hash is the default type if no distribution type is specified. The hash key can be the primary key (default) or user_defined columns. It uses the consistent hash algorithms. CUSTOMERS table is an example table of Distribution by Hash. Distributed by Duplicate tables is commonly used for reference tables because identical copies of the data are on all elements. Distributed by Duplicate tables are optimized for read and joins. The ACCOUNT_TYPE and ACCOUNT_STATUS are examples of such tables. Distributed by Reference tables allow related data to be co-locate together for join optimization. It is based on foreign key relationship. It supports multi-level hierachy. ACCOUNTS table is an example of a reference table. Entity Relation(ER) diagram of the sample script is followed: For more detail information on the distribution types, consult the TimesTen In-Memory Database Scaleout User's Guide. Create Schema using ttIsql Follow the steps below to create the sample schema using cr_tables.sql script file. 1: On a data instance, source the TimesTen environment. For this example, the data instance used here is /home/oracle/TimesTen/instances/instance1. $ source /home/oracle/TimesTen/instances/instance1/bin/ttenv.sh 2: Go to the directory where the sample scheme file is saved to. For this example, the sample schema file is saved to /home/timesten directory with the file name, cr_tables.sql. $ cd /home/timesten 3.: Run the SQL cr_tables.sql script against the database using the ttIsql -f option. TTISQL $ ttisql -f cr_tables.sql "dsn=sampledb;uid=appuser" At this point, the tables in the cr_tables.sql are created in your TimesTen Scaleout Database.  For more information about TimesTen Scaleout, please visit the TimesTen 18.1 documentation library. Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

There are 3 database distribution types in TimesTen Scaleout.  A sample SQL script  (file name , cr_tables.sql) is included  utilizes all 3 distribution types: hash, duplicate and reference. Distributed...

Do you want to win a Xbox One S?

Feeling lucky on July 18, 2018 to win a Xbox One S and other prizes?   GridGain Systems is sponsoring the raffle prizes as well as  food and drinks at the  London In-Memory Computing Meetup. Chris Jenkins from Oracle TimesTen In-Memory Database will present customer use cases for TimesTen.  Apache Ignite evangelist Akmal Chaudhri will present: "Machine and Deep Learning with in-memory computing." Registration is through the meetup website.   Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Feeling lucky on July 18, 2018 to win a Xbox One S and other prizes?   GridGain Systems is sponsoring the raffle prizes as well as  food and drinks at the  London In-Memory Computing Meetup. Chris...

Testing Distributed Joins in Oracle TimesTen Scaleout

This article was created by Jason Feldhaus, Consulting Member of Technical Staff at Oracle. Oracle TimesTen Scaleout is a high performance distributed shared-nothing in-memory RDBMS. TimesTen Scaleout provides comprehensive SQL support for both OLTP and analytic workloads. This article describes how table rows are distributed across nodes in a TimesTen Scaleout grid and how distributed SQL join queries are tested to ensure correct results in a wide range of operating conditions. The graphs in this article were generated using data from a running deployment of TimesTen Scaleout via the RStudio IDE and the ROracle and ggplot2 packages. Data Distribution TimesTen Scaleout supports multiple data spaces for high availability and fault tolerance. A data space consists of a set of nodes containing a complete copy of the entire database. A node in a given data space has a duplicate node in another data space. These duplicate nodes are called replica sets. A 3x2 grid is a TimesTen Scaleout grid configuration where there are three partitions of the database data set with two complete copies of the database for a total of 6 storage nodes. A key feature of TimesTen Scaleout is that applications do not need to be aware of the physical data distribution and structure of a grid. Applications can connect to any node and execute any type of standard SQL read or write operation without restriction. The storage node location of table rows in a TimesTen Scaleout grid is determined by selecting one of several different available methods. DISTRIBUTE BY HASH tables assign rows to particular nodes using a consistent hash key based on the values in one or more table columns. DISTRIBUTE BY REFERENCE tables co-locate child table rows with their foreign key linked parent rows. This optimizes join performance for related data. DUPLICATE tables store a complete copy of the table’s rows on each node, thereby avoiding the overhead of network operations. Defining the Schema For this particular join test, four tables (J1-J4) are defined with a common key on the ID column, but the row distribution method, which determines where each row is stored in the grid, is defined differently. CREATE TABLE J1 (ID INTEGER PRIMARY KEY, VALUE VARCHAR2 (64)) DISTRIBUTE BY HASH (ID); CREATE TABLE J2 (ID INTEGER PRIMARY KEY, VALUE VARCHAR2 (64),   FOREIGN KEY (ID) REFERENCES J1 (ID) ON DELETE CASCADE)   DISTRIBUTE BY REFERENCE; CREATE TABLE J3 (ID INTEGER PRIMARY KEY, VALUE VARCHAR2 (64))   DISTRIBUTE BY HASH (ID, VALUE); CREATE TABLE J4 (ID INTEGER PRIMARY KEY, VALUE VARCHAR2 (64))   DUPLICATE; Each table contains 16 rows and is populated with identical data. INSERT INTO J1 SELECT ROWNUM, CASE WHEN MOD (ROWNUM, 2) = 0 THEN ROWNUM END FROM ALL_OBJECTS WHERE ROWNUM <= 16; This graph shows where the individual rows for all four tables are stored in a 6x2 TimesTen Scaleout grid. The y axis represents the value of the ID column for a particular table row and the x axis indicates the grid node where the row is stored. The top and bottom rows of nodes are exact duplicates of each other belonging to separate data spaces. The co-located rows for tables J1 and J2 are always stored on the same node given a foreign key relationship. Table J1 is distributed using HASH (ID) and table J2 is distributed BY REFERENCE based on the location of the corresponding row in J1. Table J3 uses a composite distribution key: HASH (ID, VALUE). The rows in J3 are distributed independently of the rows in J1 and J2. Table J4 is distributed using the DUPLICATE method. A complete copy of this table is located on each node for the best possible performance. Note that the distribution of rows is not very uniform across nodes. This occurs because the test tables contain only 16 rows each. In practice, real tables containing thousands, millions or billions of rows are stored uniformly across grid nodes. Distributed Joins When joining table rows in a distributed database the best performance is realized when all of the joined rows are stored on the local executing node. These local joins are the ideal case, but in practice applications do require joins against rows stored on multiple nodes. TimesTen Scaleout executes distributed joins automatically via standard SQL without any special syntax, restrictions or operating modes. When a distributed join is executed the SQL engine creates an access plan to retrieve the required rows. This equi join (shown in two equivalent forms) requires access to at least one copy of every row from each of the four tables in order to return the correct result to the application. -- ANSI join syntax SELECT J1.*, J2.*, J3.*, J4.* FROM J1 JOIN J2 ON (J1.ID = J2.ID) JOIN J3 ON (J1.ID = J3.ID) JOIN J4 ON (J1.ID = J4.ID); -- equivalent traditional join syntax SELECT J1.*, J2.*, J3.*, J4.* FROM J1, J2, J3, J4 WHERE J1.ID = J2.ID AND J1.ID = J3.ID AND J1.ID = J4.ID; This graph shows which rows were accessed to satisfy the equi join query request. The blue frame indicates that the query was executed on Node 7. Rows accessed on Node 7 did not require network operations. Since Node 7 is located in Data Space 2, only nodes in Data Space 2 were accessed for remote rows. The duplicate nodes in Data Space 1 (not shown) did not participate in the query. The row access pattern reveals that the 4-way join operation was executed on each of the participating nodes and the results from each node were then collected and returned to the application on Node 7. Leveraging Randomness and Consistency Oracles Testing the reliability and correctness of distributed SQL joins is challenging. The number of scenarios and combinations is endless. One effective strategy involves automated generation of SQL queries. This type of SQL test case automation is inspired by Don Slutz and the RAGS system described in Massive Stochastic Testing of SQL. The SQL generation system used for TimesTen Scaleout relies on statement definitions expressed in a templating language. An abstract definition of a SQL join is first designed manually. Software then resolves the definition, using randomly selected elements, into a large number of concrete executable statements. Any one of these machine generated queries looks like nonsense. But when thousands of these statements are systematically generated, executed and evaluated for correctness, a significant area of the input space can be sampled in a short period of time. How are randomly generated queries evaluated for correct results? The answer involves consistency oracles. The SQL statements executed against TimesTen Scaleout can also be executed against other independent relational databases. The comparative results are stored and used to make automated judgements of what is correct in subsequent executions of the same statements. Thanks to the standardization of the SQL language, relational databases are full of consistency oracles that can be leveraged to find bugs. Executing and Evaluating The animation below uses data collected from an automated test procedure operating against a 6x2 TimesTen Scaleout grid. The row access patterns for twelve different machine generated join queries are displayed as the process executes each query on different nodes of the grid.   via i.imgur All twelve nodes in the 6x2 grid are laid out on the x axis of each plot. The nodes belonging to Data Space 1 are on the left of each plot area and the duplicate nodes of Data Space 2 are on the right. In normal operating conditions, only rows located in the data space where the query is executing will be accessed. But when certain nodes become unavailable (depicted in dark grey), the system will automatically compensate by accessing duplicate nodes in the other data space. Automated tests like this one are designed to make sure that the correct results for a wide range of potential queries are returned regardless of the grid topology, execution node, or system state - as long as at least one copy of the requested data is available. This article describes just one of many different testing techniques applied during the development of Oracle TimesTen Scaleout. Effective testing of distributed systems is no less challenging than designing and building them. Automation of test case generation, not just execution checking, in conjunction with test process data analysis, makes a real difference in the quality of the final product.   Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

This article was created by Jason Feldhaus, Consulting Member of Technical Staff at Oracle. Oracle TimesTen Scaleout is a high performance distributed shared-nothing in-memory RDBMS. TimesTen Scaleout...

Food, Drink, Raffle and In-Memory Database

Are you in London on Feb 7, 2018 and available for some food and drinks at 6:30pm?  Come and meet Chris Jenkins to learn more about Oracle's Next Generation Distributed Relational IMDB at the inaugural gathering of the London In-memory Computing Meetup!   Disclaimer: These are my thoughts, they don't represent Oracle's official point of view.  

Are you in London on Feb 7, 2018 and available for some food and drinks at 6:30pm?  Come and meet Chris Jenkins to learn more about Oracle's Next Generation Distributed Relational IMDB at...

HowTo

How do you develop apps for Oracle TimesTen Scaleout?

Summary Oracle TimesTen Scaleout is a scale out, shared nothing, OLTP, SQL RDBMS which is similar but different than the Oracle RDBMS. This article gives a brief overview of how to develop applications on TimesTen Scaleout and makes comparisons with the Oracle RDBMS.   Short Answer The way that you design, build, test and deploy applications for Velocity Scale is almost exactly the same to how that you would do it for an Oracle database. For most OLTP applications, the SQL APIs, table definitions and SQL statements can be exactly the same. In fact, often a program with the exact same source code can run on both TimesTen Scaleout and the Oracle database: An example JDBC program that runs the TPTBM OLTP workload on either Oracle or TimesTen Scaleout. An example OCI [Oracle Call Interface] program that runs the TPTBM OLTP workload on either Oracle or TimesTen Scaleout. The Oracle 8.1 OCI cdemo81.c sample program [with improved error handling] that works on either Oracle or TimesTen Scaleout. An Oracle 8.1 Pro*C sample program [sample.pc] with improved security and error handling that works on either Oracle or TimesTen Scaleout. An Oracle 8.1 Pro*C sample program for dynamic ANSI SQL that works on either Oracle or TimesTen Scaleout. When these OCI or Pro*C programs are compiled and linked, the same executable can run against either Oracle or TimesTen Scaleout. This JDBC program dynamically loads the JDBC driver, so it can also use the same Java Class to run against either Oracle or TimesTen Scaleout. The Open Source SQL APIs [Node.js, Python, Ruby, Go and Oracle R] that are OCI based and were created for the Oracle database also work for TimesTen Scaleout due TimesTen's support for OCI.   Long Answer This rest of this article looks at the edge cases where TimesTen Scaleout and Oracle can be different for applications. Database Column Types For OLTP applications, Oracle TimesTen Scaleout uses the common database columns types that the Oracle database uses. This above venn diagram, shows that the intersection covers the common OLTP database column types. The column types on the right are not supported in TimesTen.  TimesTen supports 4 MB varchar2 / CLOB columns and 16 MB varbinary / BLOB columns. This means that TimesTen Scaleout can use the simpler and faster varchar2/varbinary types rather than the Oracle LONG, LONG RAW and BFILE types. The column types on the left are not supported by the Oracle Database.  These types which start with 'TT_' tend to use less space and be faster than the equivalent Oracle column types.   Table Storage Clauses (transparent optimizations) The Oracle Create/Alter Table syntax is very feature rich.  Much of the create table syntax occurs 'outside' the table column definitions for what I am calling the storage clauses.  The various Oracle database storage clauses are focused on the data structures and disk storage configurations aimed to optimize performance mainly by minimizing disk IOs. These 'storage clauses' are transparent to an application [and developer] and when applied correctly they can significantly increase the performance and scalability of an Oracle Database. The Oracle TimesTen TimesTen Scaleout database also uses 'storage clauses' to increase performance and scalability. Oracle TimesTen TimesTen Scaleout is a scale out, shared nothing, In-Memory RDBMS. TimesTen Scaleout processes all of this SQL data in-memory so optimizing for disk IO [needed for persistence, rollback and recovery] is tends to be less of an issue than for an Oracle database. Rather than optimizing disk IOs, TimesTen Scaleout is focused on optimizing network messages. While network messages can be faster than disk IOs, the fewer the network messages the faster and more scalable the TimesTen Scaleout application will be. By default tables distribute their rows across TimesTen Scaleout machines by a consistent hash of the Primary Key.  If the primary and foreign key rows are on the same machine, any SQL joins between these rows will avoid network messages. Some tables that are commonly joined to and 'read mostly' can be duplicated across all machines. Joins with tables that are duplicated will always avoid network messages.   The distribution of tables in TimesTen Scaleout is transparent to an application and developer.  SQL applications will get the same results independent of the table distributions, but 'optimal' distributions will perform and scale much better. Developers can also use Materialized Views to enable optimal plans for multiple different columns access patterns.   SQL Analytic Functions Although TimesTen TimesTen Scaleout is focused OLTP workloads, it also supports the following subset of the Oracle analytic SQL functions: A benefit of TimesTen Scaleout's scale out architecture is that all SQL statements execute in parallel. This means that most SQL analytic functions will tend to execute faster as the number of TimesTen Scaleout nodes increase. This speedup will tend to be linear (more nodes means faster SQL). Due to Amdahl's Law, the time to execute a SQL statement will never be zero no matter how many machines are used. Every aspect of TimesTen Scaleout application development and performance tuning will be covered in more detail in future articles.   Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Summary Oracle TimesTen Scaleout is a scale out, shared nothing, OLTP, SQL RDBMS which is similar but different than the Oracle RDBMS. This article gives a brief overview of how to develop applications...

General

Beer, wine and the fastest In-Memory RDBMS

Do you want more performance and scalability from your SQL RDBMS? Do you need simple high availability than spans data centers? Do you like beer, wine and food? Do you want to learn more about state of the art In-Memory RDBMS? Are you free on Tuesday, Nov 14th from 6:30 to 8:30pm? Can you get to Mountain View? Come to the Bay Area In-Memory Computing Meetup to learn about Oracle Velocity Scale and Apache Ignite. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Do you want more performance and scalability from your SQL RDBMS? Do you need simple high availability than spans data centers? Do you like beer, wine and food? Do you want to learn more about state of...

TimesTen Talk

See Oracle TimesTen Velocity Scale at Big Mountain Dev & Data Conference @UtahGeekEvents #UtahGeekEvents

Learn about Oracle TimesTen Velocity Scale, see the demos in the cloud and on premise, ask the hard questions. Salt Lake City, Nov 10th and 11th, https://www.utahgeekevents.com/ Learn about Velocity Scale, how to develop applications for Velocity Scale and just how fast that it goes. It is not to late to register for this conference. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Learn about Oracle TimesTen Velocity Scale, see the demos in the cloud and on premise, ask the hard questions. Salt Lake City, Nov 10th and 11th, https://www.utahgeekevents.com/ Learn about Velocity...

Performance

OLTP write scaling is hard

Summary As a follow up article to The evolution of of DB architectures in the quest for scalability, this article talks about the different techniques that can be used for massive read and write scaling for OLTP databases.  The traditional methods for write scaling all have serious down sides.  Two alternative techniques are discussed.  Ultimately how you choose to scale your database is as much a business decision as a technical one.  Just make sure you know all of the pros and cons. OLTP systems need to be able to scale both reads and writes.  Generally scaling the reads is a lot easier than scaling the writes.   Scaling Reads for OLTP databases The following techniques are applicable for most database management systems: Make sure that the appropriate indexes are being used: Doing less work is always the fastest.  Doing a hash lookup for exact matches or reading a few B+-tree nodes for range queries will always tend to be faster than doing a sequential scan for potentially billions of rows for highly concurrent OLTP workloads. If your database management system is relational and uses a cost based optimizer, then make sure that your statistics are up to date. Use faster disks:   Most databases use a buffer pool to move data blocks to and from disk to enable the working set to reside in RAM. No matter how large the buffer pool is, you will still eventually need to do 'disk' reads or writes, so persistent devices with low latency and high bandwidth are desirable. SANs or arrays of 15K RPM SCSI disks used to be the solution, but now NVMe SSDs are the state of the art for low latency and high bandwidth storage devices. NVMe SSD are constantly improving, but the Intel Optane SSD DC P4800X Series is a good example of a fast SSD. You can even stripe the latest SSDs for even better throughput. Increase the buffer pool size: The bigger the buffer pool, the better the cache hit ratio.  A database read that finds the data blocks in the buffer pool will tend to be much faster than requiring one or more disk reads to get those data blocks from disk.   Make sure that you have enough CPU cores: If you do not have enough CPU cores your database management system will not be able to handle the concurrency required for read scaling. Modern CPUs from companies like Intel, IBM and Oracle have many CPU cores per socket. For example Intel (28 cores), IBM (24 cores) and Oracle (32 cores).  The number of cores per socket is only half of the CPU story.  The other half is how many CPU sockets do you have in your database server machine? Some examples of multi socket servers from companies like Oracle (8 sockets), Dell, (four sockets), IBM (four sockets) and HPE (32 sockets) Make sure that your DB server is balanced: The weakest link will be the scaling bottleneck.  Having lots of CPU cores but not enough RAM will limit read scaling. Having lots of RAM but not enough CPU cores will limit read scaling. Having lots of CPU but slow disks will limit read scaling. Having lot of CPU cores but a slow NIC will limit read scaling The above are all examples of scaling up. ie using a bigger / balanced box.  If the biggest, balanced box is the bottleneck to read scaling then you will need to scale out. Scale out by using read only replicas: Some database management systems support multiple read only replicas. For instance MySQL, MariaDB, PostgreSQL and Oracle TimesTen all support configurations where there is one read/write DB and many read only replicas Use read caches: Another approach is to use a set of low latency read only caches for your database management system. These caches need to be kept sufficiently fresh to be useful.  Read only caches are typically either refreshed based on time or changes. Some examples of low latency read only caches are Redis, memcached and Oracle Application Tier Database Cache. With Oracle Application Tier Database Cache, the DBA defines (on a table by table basis) the columns and rows of interest from an Oracle database and whether those rows should be a read only or a read/write cache. The DBA also defines how often that those rows should be refreshed into the TimesTen tables. Use Database Sharding: Another approach to enable database read scaling is database sharding. The shards are usually existing database management systems [eg MySQL or PostgreSQL] and 'glue code' is needed to route requests to the relevant shards. Combine the techniques: Theoretically there is nothing stopping you having a balanced machine [CPU/RAM/disks/NICs] that is sharded where each shard has read only replicas and there is a read cache in front of each shard. Each shard would also have the appropriate [local/global] indexes. While this approach is possible, you need to design and develop for this approach and every component needs to 'do the right thing' during failure scenarios.  As there are so many moving parts, monitoring and managing all of these components is non trivial. The following table shows the pros and cons of each read scaling technique:   Scaling Writes for OLTP databases There are less useful techniques for write scaling and they are harder to apply than those for read scaling: Adding indexes slows down writes and makes write scaling harder: OLTP systems usually have both reads and writes.  You want the indexes for the reads, but they hurt the writes. Use faster disks: Faster disks do help with write scaling.  NVMe SSDs are still the best technology for this. Usually you need fast sequential writes for write scaling. Usually you need fast random reads for read scaling.  Large buffer pools do not help write scaling: You want to persist the committed transaction as soon as possible. Having more CPU cores does help write scaling. Having a balanced DB server does help write scaling. Database writes tend to be much more expensive than database reads: Durably committed transactions usually require multiple disk writes. Database reads may not require any disk reads as the data may be in the buffer cache. Rolling back a database operation usually requires multiple disk reads and writes. It is very easy for a database to bottleneck waiting for disk IOs given highly concurrent database writes. NVMe disks really help here, but they do not have infinite bandwidth. Using Scale out read only replicas does not help write scaling: By definition, read only replicas cannot accept database writes. This means that writes can only occur on the active/master DB node. This means that the write scaling will be limited by the write bandwidth of a single database server. Read caches do not help write scaling. Database sharding does help write scaling: The database writes can be routed to the relevant shard. By having lots of database shards, the required write bandwidth can be spread over many database servers So in summary, to scale database writes, scale up as much as you can [afford] and then scale out. Developing applications for a sharded database [eg manually sharded MySQL or PostgreSQL] is difficult. You need to have custom code to route reads and writes to the correct shard. Usually doing complex queries that span database shards is either difficult or not allowed. Unless you have expert DBAs and developers who know all of the ins and outs of designing, developing, operating and maintaining a sharded database system, do not consider this approach.   Is there a simpler approach for designing, developing, operating and maintaining a database system which scales both reads and writes? I am aware of two approaches that make designing, developing, operating and maintaining a database system for massive read/write scaling simple: Using Oracle Exadata: Exadata uses a scale out, shared disk architecture based on Oracle Real Application Clusters and state of the art hardware. You choose the hardware size based on your needs [eg 1/8 rack up to multiple racks]. Exadata data uses NVMe storage, huge buffer pools, lots of CPU cores, Infiniband for networking and smart storage cells to minimize disk IO. Using a scale out RDBMS that automatically shards the database and is designed to make the scale out database transparent to the developers and applications:  Examples of this type of RDBMS are VoltDB, memSQL, NuoDB and Oracle TimesTen Scaleout. These four different database have many desirable properties: They are In-Memory RDBMS so reads and writes tend to be very fast They are RDBMS so you can use the full power of the SQL language You can read from any node to help read scaling You can write to any node to help write scaling Unlike sharded databases, you do not need expert developers or DBAs to design, develop, operate and maintain systems built for these RDBMS Based on some benchmarks, we believe that Oracle TimesTen Scaleout is the World's Fastest OLTP Database.   Summary There are many techniques to enable massive database read scaling. There are only a few techniques that enable massive database write scaling.  You need expert developers and DBAs to design, develop, operate and maintain manually sharded databases. Oracle Exadata, VoltDB, memSQL, NuoDB and Oracle TimesTen Scaleout enable massive read and write scaling without requiring expert developers or DBAs. If you need databases with massive read and write scaling, either hire expert developers & DBAs to design/develop/operate/maintain manually sharded databases and applications [forever], or choose from a set of RDBMS that make massive read/write scaling easy.   Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Summary As a follow up article to The evolution of of DB architectures in the quest for scalability, this article talks about the different techniques that can be used for massive read and write...

General

The evolution of DB architectures in the quest for scalability

Summary This article looks at how database architectures have evolved over time to address ever increasing scalability challenges.  Only the major architectural changes are covered and usually only a few examples for each phase are covered so your favorite technology is likely missing. A taxonomy of DB architectures is provided and the question of what the next evolution will be is raised. The emphasis is on the 'commercially successful' systems and systems used by the author.   Monolithic / Mainframe databases In the beginning, the application was the database. These business applications were often written in COBOL (1959), FORTRAN (1957) or assembly language. The application was monolithic in that it handled the user interface, application logic and basic CRUD (Create, Read, Update and Delete) operations. The CRUD operations used records and files. Some examples of this are COBOL with sequential, indexed sequential and direct access files from the 1960s. In time, this evolved to the separation of applications from the CRUD operations.  The 'database' handled CRUD operations, persistence, navigation and concurrency.  There was usually a one-to-one relationship between navigating the database structures and how those data structures were stored on disk. An example of this is the hierarchical DBMS, IBM Information Management System (IMS) from 1968. The one-to-one relationship between a file representation and its navigation usually meant that either the system could not be changed if the business needs changed, or that both the file representation and all of the associated application logic to traverse that file structure also needed to change.  Neither of these options were good.  Eventually this problem was solved with the introduction of the relational model and the first commercial relational databases (Oracle 1979, IBM SQL/DS 1981 and DB2/MVS 1983). Relational Database Management Systems (RDBMS) like DB2 enabled developers to declaratively define what data they wanted without needing to know how that data is stored or accessed. This declarative approach using SQL led to great gains in developer productivity and enabled RDBMS based systems to better suit business needs than the older file based systems. Interestingly, recent versions of IMS (2013) now also support SQL. Most of the current NoSQL databases could be considered to have evolved from the concepts and architecture of IBM IMS. However running the user interface, application logic and database all on the same/single mainframe was not always the most scalable or cost effective solution.   Client Server Databases Although Mini computers were cheaper than mainframe computers [and could also run RDBMS], it was the introduction of RISC based UNIX computers and Personal Computers than made client server databases practical. Client Server databases generally have a single large machine which runs only the RDBMS server. The client machines run the user application logic, present the user interface on some windowing system [eg MS Windows] and communicate with the RDBMS server over a LAN using a DB client library. This client server architecture was generally a lot cheaper and could also be faster than the monolothic mainframe model (eg COBOL with files, IMS or DB2) where everything (user interface, application logic and DB access) ran on a single mainframe computer.  The client server architecture also enabled the client machines to scale out horizontally. This meant that as long as the single RDBMS server was not the bottleneck, adding more [cheap] client machines enabled more throughput.  Common examples of client server RDBMS from the late 1980s and 1990s were Sybase, Oracle, SQL Server, Informix and DB2 running on platforms such as Sun Solaris, HP-UX, IBM AIX, OS/2 and Microsoft Windows. The problem with this client server model was that a single RDBMS server could easily become the bottleneck.   Scaling up with SMP or ccNUMA databases Back in the 1990s, multi core CPUs and hyper-threading did not exist.  Instead, large Unix servers either used a single RISC CPU with a higher clock speed, or used multiple CPU sockets (generally at lower clock speeds). It turned out that creating large SMP UNIX servers by adding more CPU sockets to a common system bus was complicated and they generally did not scale well. A more scalable approach was to associate CPU sockets with their own local memory.  This Non Uniform Memory Architecture (NUMA) also needs to deal with assumptions about loads and stores at the instruction level.  Most developers want the same cache behavior that they would see on a single core, single socket machine.  Cache coherent NUMA systems (ccNUMA) provide this desired behavior. Large SMP or ccNUMA machines tend to be very expensive and the individual CPUs may not run that fast.  These SMP and ccNUMA issues meant that RDBMS servers could not continue to scale up as you could only add some many CPUs to SMP or ccNUMA systems. RDBMS systems from Sybase, Oracle, Microsoft and IBM were available on SMP and ccNUMA systems from companies like Sun, HP, IBM and SGI. As RDBMS could not continue to scale up on large SMP or ccNUMA servers, the alternative was to scale out.   Scaling out Instead of scaling up with large SMP or ccNUMA servers, RDBMS vendors looked at using many small or medium sized servers to scale out. There are two main approaches to scaling out (shared disk or shared nothing): There are pros and cons of each approach and most vendors/users have strong feeling about why their approach is better.   Scaling out with shared disk databases Some examples of shared disk RDBMS are IBM Parallel Sysplex (1990), IBM IMS (1991), Oracle Real Application Clusters (2001), Sybase Adaptive Server Enterprise (2008) and IBM DB2 PureScale (2009). The Oracle Exadata Database Machine is an extension of Oracle Real Application Clusters which runs on specialized hardware and adds intelligence to the storage layer to enable more parallel processing and minimize the network traffic. Unlike most other shared disk databases, RAC and Extadata can use the full power of SQL and can usually run packaged applications (like SAP, PeopleSoft, JD Edwards, SalesForce and eBusiness Suite) unchanged.  RAC / Exadata do need to be tuned and run on good hardware to perform.   Scaling out with shared nothing Many data warehouse vendors have successfully used a shared nothing architecture for analytic workloads. For example Teradata (1983), IBM DB2 Parallel Edition (1994), Informix Extended Parallel Server (2002), Netezza (2003) and GreenPlum (2005). OLTP workloads can also sometimes be used with shared nothing RDBMS. For example Tandem NonStop SQL (1984) and MySQL NDB Cluster (2003). Key Value and NoSQL databases Another approach to database scaling using a shared nothing architecture is to eliminate the SQL RDBMS. Relational databases have tables and usually applications need to join tables to get the data that they need. Table joins which involve rows on different machines generally require network messaging to get the data in one place.  These network messages can really slow down a shared nothing database.  If the logical tables were de-normalized into one big wide table then no joins would be required. De-normalized tables tend to require much more space than normalized tables. So instead of using tables, these Key/Value Databases use keys which point to values. Another name for these Key/Value databases are NoSQL databases. Some examples of NoSQL databases are Cassandra (2008), Couchbase (2012), Oracle NoSQL (2012) and MongoDB (2013). A challenge for the NoSQL vendors was that most developers know and like SQL. So these NoSQL databases needed to add some SQL functionality to keep their customers happy and productive. Some examples of these [limited] SQL interfaces are CQL for Cassandra, Oracle Big Data SQL for NoSQL and N1QL for Couchbase.   Sharded RDBMS Another scale out approach is to 'shard' existing RDBMS. DB sharding usually requires the SQL applications to be written to deal with the fact that there are many independent databases rather than a single database. SQL operations that can be processed on a single shard will tend to be fast and scalable. SQL operations that need to span multiple shards are either dis-allowed or require custom code to cope with the multiple databases. For sharded databases to scale and be effective, the data needs to be evenly distributed across the different shards (databases). Often this data distribution is a manual operation. Customers with large development groups have created shared RDBMS from databases like Oracle, SQL Server, MySQL, PostgreSQL and TimesTen.   Shared Nothing In Memory SQL RDBMS The combination of Cassandra's ability to scale and the challenges of sharded RDBMS prompted SQL RDBMS vendors to revisit shared nothing architectures. VoltDB (2010), memSQL (2013), NuoDB (2013) and Oracle TimesTen Scaleout (2018) are examples of In-Memory, shared nothing SQL RDBMS. The idea of these shared nothing, In-Memory, SQL RDBMS is to benefit from the horizontal scaling of a shared nothing architecture while keeping the benefits of the SQL language.  As SQL is a declarative language, the data structures which enable the data access can continue to be optimized without needing to change the application code. Having the appropriate data distribution mechanisms for the access patterns is key to scalability for a shared nothing architectures. Elastic scalability with automatic distribution of data and allowing developers to write SQL code that appears to be for a single DB was the the next stage in the evolution from sharded RDBMS.  If these In-Memory SQL RDBMS can provide the same latency and scalability as the NoSQL and sharded RDBMS, does this mean that the NoSQL and sharded RDBMS will die off?   Database Architecture Taxonomy Based on the information covered in this article, the following chart attempts to provide a taxonomy of DB architectures as they have evolved over time.  It will be interesting to see which database architectures are most effective for database scaling over the next 10 years as this field is rapidly evolving. Modern multi core CPUs do not change the scale up vs scale out equation as scaling out will always tend to provide more scalability than scaling up.   Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Summary This article looks at how database architectures have evolved over time to address ever increasing scalability challenges.  Only the major architectural changes are covered and usually only a...

HowTo

How to create heat maps using R for Oracle TimesTen Velocity Scale

This article was created by Jason Feldhaus, Consulting Member of Technical Staff at Oracle. In data science, the open source R language is popular for statistical analysis and graphics. The combination of R and the RStudio IDE creates a powerful and productive environment for analysis, visualization and reporting. Oracle created the ROracle package which is an open source SQL interface enabling optimized access to data in Oracle Database. As the ROracle package uses OCI drivers it can also work unchanged with Oracle TimesTen Velocity Scale In-Memory Database. Velocity Scale is primarily an OLTP RDBMS, but it also has advanced analytic features and benefits from parallel processing due to its scale out, shared nothing architecture. This article shows how easy it is to create heat maps using ROracle with TimesTen Velocity Scale. Instead of using application data for the heat map, we will use run time performance metrics from the Oracle TimesTen Velocity Scale Database. The network diagram above was created by using ROracle to query the runtime topology of a TimesTen Velocity Scale database.  In this case, the database consists of eight machines (four nodes each with a copy for high availability, blue/yellow/red/green) which have some database connections (the pink circles). How that this chart was created using ROracle, will be covered in another article, but this eight node database is used to provide the runtime metrics for the following heat map: Heat maps display a color encoded summary of numeric data. This display also includes a dendogram. Dendograms are tree structures representing a hierarchy of clusters among row or column values based on a dissimilarity calculation. Each column in this heat map represents performance metric values from a particular element (node) in the Velocity Scale grid. And each row represents all of the values for a particular metric. The color of each cell in a given row is scaled so that the largest value in the row is the darkest and the smallest value is the lightest. When a metric’s value is identical across all elements in the grid the cells are not colored. If you compare the heat map with the grid network diagram above the similarities and differences between elements start to make sense. There is a general pattern where elements that are copies of each other are also similar. The element pairs 5-6, 3-4, 7-8 and 1-2 are copies (replica sets) and this sequence is close to the order of the columns in the heat map. Element 1 disrupts this pattern with distinctive differences compared to the others. This is likely due to three unique application connections (GridTxnMain, RTerm, GlobalDB_den00asr) that are not present on other elements. Element 5 also stands out. This element does not have any direct application connections but it seems to be working harder (with more lock contention). After some investigation the extra work was associated with a global sequence object used by the workload applications. The sequence object batch size was increased to resolve the lock contention. The following four chunks of R code were used to create the heat map:   Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.  

This article was created by Jason Feldhaus, Consulting Member of Technical Staff at Oracle. In data science, the open source R language is popular for statistical analysis and graphics. The combination...

General

Testing TimesTen, the world's fastest OLTP database

The content of this blog was provided by Olaf van der Geest, Senior Director, TimesTen QA/Release Management at Oracle. Our team develops, executes and evaluates tests for TimesTen, the world's fastest OLTP database. Testing a relational database is hard. The surface area to test is very large and requires a wide range of expertise and different programming skills. Over the years we have developed a very robust and extensive set of tests and completely streamlined our test environment. Our standard test run includes more than 100,000 individual tests and this number is growing daily. What defines a test completely depends on the test developer. Some tests are small and straightforward, like making sure 'select * from dual' returns exactly one row. Another test may stress our replication feature by executing one million DML operations and makes sure all transactions were replicated properly. We execute multiple of these test runs per night as we run them on different platforms, product versions and configurations. We use more than 1000 machines to execute these tests. The amount of test data to process every day is enormous, every single night we generate at least a quarter of a million test results. How do we handle this? One word: automation. The test harness automatically schedules test runs and distributes tests over the available hardware. Complete test run results are collected automatically. Tests are designed to be precise in their failure reporting, and tell us what failed and why it is considered a failure. The test framework recognizes known failures automatically. New failures stand out and are quickly recognized. Core files are analyzed automatically. Relevant details of failures are packaged up and kept for analysis. Details of the failure are automatically entered into the defects database, and assigned to the responsible component owner. It takes a considerable upfront investment to write tests suitable for automation. But the return on this investment is phenomenal, and a win-win both for the company and employees as it directly leads to greater product quality and much higher job satisfaction. Team members can now also focus all their time and energy on coding new tests instead of manually executing, triaging and recording test results. Automating this entire process and constantly looking for ways to improve it is a lot of fun and very rewarding. And remember, we are always looking for smart engineers with a passion for testing and automation to join our team! Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

The content of this blog was provided by Olaf van der Geest, Senior Director, TimesTen QA/Release Management at Oracle. Our team develops, executes and evaluates tests for TimesTen, the world's fastest...

Performance

How fast is Oracle TimesTen Velocity Scale?

Oracle TimesTen Velocity Scale is based on a foundation of the TimesTen In-Memory Database. TimesTen has very low latency for SQL operations which are measured in microseconds.   It is hard to make apples-to-apples comparisons between databases as there are so many variables. When DB vendors benchmark each others products, there is always the suspicion that the competitors DB is not tuned optimally. The following is an example of an apples-to-apples DB benchmark: In August 2016, Google published an OLTP DB benchmark for a high availability configuration between two Availability zones using the Sysbench DB read/write workload. Google optimized Google Cloud SQL Second Generation in Google Cloud and Google ran Amazon RDS MySQL and Aurora on AWS. A consulting firm (2ndWatch.com) soon re-ran the benchmarks showing that Amazon Aurora was not tuned optimally. 2ndWatch [with consulting from Amazon] was able to show that AWS Aurora was able to outperform Google Cloud SQL Second Generation. This meant that the DB vendors rather than Oracle verified that their databases were optimally tuned for this benchmark. As this Sysbench read/write DB is doing synchronous replication between two availability zones [data centers], the network round trip time was the dominant factor for TimesTen Velocity Scale.   The variables in the above throughput benchmark were the RDBMS and the public Clouds. The following is the latency [smaller is better] for the same Sysbench benchmark.     As you can see, TimesTen Velocity Scale has the lowest latency and highest throughput. Google Cloud SQL Second Generation, Amazon RDS MySQL and Amazon Aurora all use an active/standby replication configuration. Even though they can add more read-only replicas to help read scaling, their write throughput is limited by their single active database. Oracle TimesTen Velocity Scale uses an active/active replication architecture which enables write scaling by adding more machines to the Velocity Scale database. The following OLTP benchmark shows read/write scaling using the TPTBM benchmark. This workload is doing 80% reads and 20% writes.     This benchmark shows linear scaling for the TPTBM read/write workload using 32 Sun X5-2 Linux x8664 machines on the Oracle Bare Metal Cloud. As persisting the committed write transactions was the bottleneck, we used two instances of Velocity Scale per machine, so there were 64 Velocity Scale nodes running on 32 machines. Given the write IO bottleneck, we re-ran the test with a 100% read workload. The result was 1.2 Billion PK SQL Selects per second using 64 machines.     This 100% read only workload shows linear scalability to 64 Sun X5-2 Linux machines using the Oracle Bare Metal Cloud. We look forwards to the opportunity to repeat these tests with more and faster machines. For more details, come to Oracle Open World 2017. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.  

Oracle TimesTen Velocity Scale is based on a foundation of the TimesTen In-Memory Database. TimesTen has very low latency for SQL operations which are measured in microseconds.   It is hard to make...

Introduction

How do you create the world's fastest OLTP DB?

Trying to manually shard an RDBMS or scale out a NoSQL DB is so last year. There is a better way. At Oracle, we used the following recipe to create the world's fastest OLTP DB: Use the lowest latency SQL RDBMS as a building block (TimesTen In-Memory Database) Use many instances of TimesTen in a scale out, shared nothing architecture Enhance the TimesTen SQL so that the applications see a single database image Enable high availability via K-Safety Use ACID transactions so that your data is always consistent Enable centralized monitoring and management to make devops life simpler  Enable existing applications to work without re-writing (keep your schemas, PK, FK, SQL and SQL APIs) Give it a cool name (Velocity Scale) The result looks like this: Come to Oracle Open World 2017 to find out all of the details. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Trying to manually shard an RDBMS or scale out a NoSQL DB is so last year. There is a better way. At Oracle, we used the following recipe to create the world's fastest OLTP DB: Use the lowest latency...

General

Back to the future, Sql vs Non SQL

There is a lot of discussion about the pros and cons of NoSQL databases and where and when you should use them versus using an RDBMS. This discussion sounds really familiar.  Before relational databases existed, there were several choices: Sequential files Indexed sequential files (ISAM and VSAM) The Hierarchical Database Model The Network Database Model (CODASYL) These choices provided various ways to create, read, update and delete records of information which were stored in files for persistence. While these techniques each have their advantages and disadvantages, the thing that they have in common is that the programmer had to know their structure and navigate accordingly. This meant that there was a one-to-one relationship between how the data is stored and how it needed to be accessed. Many years ago, I had the displeasure of using these techniques using COBOL.  The arrival of relational databases and the SQL language meant that programmers did not need to know how that the data was stored when they had to access it.  The SQL query optimizer provided an abstraction such that the programmer would define what data that they wanted rather than how they should access that data. The challenge with early RDBMS was that they were not as fast as the non SQL alternatives and tended to need more expensive hardware to run on. This situation caused conflict as there were debates about : Whether it was better to minimize development costs by using SQL (at the cost of performance and/or more expensive hardware) or was it better to maximize performance by using the non SQL mechanisms (at the cost of longer developer / maintenance time) Eventually this debate was resolved as RDBMS and their SQL optimizers became more efficient and the hardware improved to the point that most system eventually ended up using an RDBMS with the SQL language. Back in the present, the discussion about SQL vs NoSQL databases is complex, but the following three things are critical: Performance Scalability How you model data It is true that there are some NoSQL databases which are very fast and very scalable.  It appears to me that the current NoSQL databases are like the old non SQL in that they force the programmer to know how that the data is physically stored in order to be able to access it. It is also true that some RDBMS do are not as fast as some of the NoSQL databases and do not scale as well as the best NoSQL databases.  If the data that you need to model is so simple that it can effectively be represented as name/value pairs, then using a relational data model is over kill.  However if the data that you need to model is complex and you want to minimize the size of the data stored, then using a normalized relational data model can be a good thing. What if there was an RDBMS which was as fast as or faster than the NoSQL databases?  What if there was an RDBMS which could scale as well or better than the NoSQL databases? If this were true and NoSQL databases no longer had performance or scalability advantages of RDBMS, then long term what would be the value proposition of NoSQL databases? It turns out that there is a new RDBMS which is fast as or faster than the NoSQL databases and that can scale as well or better than the NoSQL databases.  This new RDBMS is called Oracle TimesTen Velocity Scale In-Memory Database. Check out Oracle TimesTen Velocity Scale In-Memory Database at Oracle Open World 2017. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

There is a lot of discussion about the pros and cons of NoSQL databases and where and when you should use them versus using an RDBMS. This discussion sounds really familiar.  Before...

General

Do not manually shard your DB

While striving for maximum DB scalability, many people are tempted by the allures of DB sharding. While DB sharding may help scale out your DB system, there are some real dis-advantages of this approach: Instead of managing a single DB, you are now managing many independent databases Someone, usually the application developers, needs to design and develop [or re-write] their applications to use a sharded DB If your DB is relational, instead of being able to use SQL to do complex joins, you are usually limited to a sub-set of SQL and often can only do operations on the local shard or need to provide a extra parameter like a shard key to a SQL where clause If your DB is non relational [eg a key value store], often you need to 'de-normalize' your data model to work around the fact that you cannot do joins.  De-normalizing usually has the downside of requiring redundant data.  The bigger your database, the more bloat that this redundant data causes. This redundant data costs you in terms of disk, memory and CPU.  While these resources may be considered 'cheap', they are not free. Is there a better way? What if you could achieve the scaling benefits of DB sharding without the limitations? We designed Oracle TimesTen Velocity Scale In-Memory Database to provide the benefits of sharding without the disadvantages. Check out Oracle TimesTen Velocity Scale In-Memory Database at OOW 2017. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

While striving for maximum DB scalability, many people are tempted by the allures of DB sharding. While DB sharding may help scale out your DB system, there are some real dis-advantages of this...

Performance

How awesome is your DB?

Asking which is the coolest DB is bit like asking which is the coolest car. Debating whether a Porsche 911 is better than a Honda Civic is meaningless unless you define the comparison criteria (eg top speed & cornering vs price and MPG). I am defining some criteria for DB comparison. Given these criteria, how awesome is your DB? Does your DB have high availability? Can your DB scale both reads and writes? Does your DB use schemas with PK and FKs? Does your DB use popular APIs like JDBC, ODBC, ADO.NET, OCI and PLSQL? Does your DB provide ACID transactions? Does your DB automatically recover from failures? How many million PK SQL selects can your DB do per second? How many million SQL update transactions can your DB do per second using synchronous replication? Some of these critiera are easy, having all of these critera is hard. Doing > 1 million update transactions per second with synchronous replication is really hard. If your DB is not awesome enough using my criteria, then check out Oracle TimesTen Velocity Scale In-Memory Database at OOW 2017. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Asking which is the coolest DB is bit like asking which is the coolest car. Debating whether a Porsche 911 is better than a Honda Civic is meaningless unless you define the comparison criteria (eg top...

Performance

Is your DB too fast?

All DBAs, architects and developers. Ask yourself these questions: Is your DB too fast? Is the latency of your DB operations too low? Does your DB provide too much throughput? Is your DB more highly available than you need? Is your data more consistent than you need? Is your DB too easy to develop for? Is your DB too easy to install and manage? If True    Then do nothing. Put your feet up and chill. Even better, go to the beach :-) Else    Check out Oracle TimesTen Velocity Scale In-Memory Database at OOW 2017. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

All DBAs, architects and developers. Ask yourself these questions: Is your DB too fast? Is the latency of your DB operations too low? Does your DB provide too much throughput? Is your DB more highly...

Performance

Common Database Scaling Myths

For the last several years many people believed that you needed to abandon relational databases to produce highly scalable database systems.  The existence of various NoSQL, sharded, key-value or document databases that could scale seemed to be evidence of this theory. The good news is that Oracle TimesTen Velocity Scale In-Memory Database proves all of these myths to be false. Come to Oracle Open World 2017 to see how Velocity Scale busts these myths: Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

For the last several years many people believed that you needed to abandon relational databases to produce highly scalable database systems.  The existence of various NoSQL, sharded, key-value or docum...

General

Need a faster, more scalable SQL DB?

Need an OLTP SQL DB that scale writes as well as reads? Need HA with ACID transactions and massive scalability? Want standard Oracle SQL and PLSQL with common analytic functions? Need a database solution that works both on premise and in your favorite clouds? Come to Oracle Open World 2017 to learn about the Oracle Velocity Scale. Oracle Velocity Scale is a scale out, shared nothing RDBMS powered by TimesTen In-Memory Database. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Need an OLTP SQL DB that scale writes as well as reads? Need HA with ACID transactions and massive scalability? Want standard Oracle SQL and PLSQL with common analytic functions? Need a database solution...

General

What is Oracle TimesTen Velocity Scale ?

Come to Oracle Open World 2017 to find out about Oracle TimesTen Velocity Scale. Velocity Scale is a new SQL, shared nothing, scale out, In-Memory RDBMS based on TimesTen. At Oracle Open World 2017, you can find out the important things about Velocity Scale like: How to scale out SQL writes How to scale out SQL reads How does High Availability and recovery work Which SQL and PLSQL features are supported How is data distributed across machines Which APIs does Velocity Scale support How does Velocity Scale compare to competing products like Amazon Aurora Google Cloud SQL MySQL Cluster memSQL VoltDB Google Spanner Apache Cassandra How does Velocity Scale perform on benchmarks like YCSB, Sysbench and TPTBM On premise, which platforms does Velocity Scale run on Which public and private clouds does Velocity Scale run on How is Velocity Scale different from Oracle products such as RAC, NoSQL, Sharding and Coherence The schedule for Velocity Scale at OOW 2017 is here.

Come to Oracle Open World 2017 to find out about Oracle TimesTen Velocity Scale. Velocity Scale is a new SQL, shared nothing, scale out, In-Memory RDBMS based on TimesTen. At Oracle Open World 2017, you...

General

TimesTen and Velocity Scale at Oracle Open World 2017

The catalog for OOW 2017 is now available. This is a great opportunity for you to learn about the new Oracle TimesTen product Velocity Scale. Velocity Scale is a Scale Out, Shared Nothing, In-Memory RDBMS that will be announced at Oracle Open World. There are three TimesTen presentations at OOW that will include Velocity Scale: Oracle's New, Scale Out, OLTP-Optimized, In-Memory RDBMS Monday, Oct 02, 11:00 a.m. - 11:45 a.m. | Moscone West - Room 3014 Accelerate OLTP Performance with an Application-Tier In-Memory Database Tuesday, Oct 03, 4:45 p.m. - 5:30 p.m. | Moscone West - Room 3008 Oracle TimesTen in the Cloud Wednesday, Oct 04, 11:00 a.m. - 11:45 a.m. | Moscone West - Room 3004 There will be two Hands On Labs where you can test drive Velocity Scale: Hilton San Francisco Union Square (Ballroom Level) - Continental Ballroom 6 Monday, Oct 02, 12:45 p.m. - 1:45 p.m. Wednesday, Oct 04, 3:00 p.m. - 4:00 p.m. You can also meet the TimesTen experts at the Demo Grounds / Exchange: Monday 10:15 am to 6 pm Tuesday 11 am to 5:45 pm Wednesday 10:15 am to 4:30 pm You can also work with your Oracle Sales Rep to organize a CVC with the TimesTen team at OOW. See you at Oracle Open World 2017.

The catalog for OOW 2017 is now available. This is a great opportunity for you to learn about the new Oracle TimesTen product Velocity Scale. Velocity Scale is a Scale Out, Shared Nothing, In-Memory...

General

Why are my TimesTen queries getting slower?

Help! My once fast data lookups are now going slower. As databases grow, if you are not careful, data access can take longer. This is very undesirable and can be avoided by understanding the issues and tuning your database accordingly. There can be several reasons for data access getting slower over time: Incorrectly sized hash pages Statistics not being updated Shared memory getting paged in/out We will cover each of these issues: Incorrectly Sized Hash index Pages Remember that computer science class about hash collisions, load factors and chaining?  TimesTen hash indexes use a form of chaining when a hash collision occurs. This means that instead of getting constant/fast lookup times, the overloaded hash index will first do a hash lookup and then have to follow chains.  This chaining gives linear access time - the longer the chain the longer the access time. The way to avoid hash index chaining is to correctly size the your hash indexes: The hash buckets each have 256 entries and are called pages You want to size the pages based on the expected table rows and leave some room for overhead Divide the expected number of rows in your table by 256 Add some head room, say 10% for growth Use this number for pages.  For example Your table is expected to have 1 million rows 1,000,000 / 256 = 3906.25 3906.25 * 1.1 = 4296.875 round it up to 4300 pages Create hash index my_hash_index on my_table (my_columns) pages = 4300; ​​​Range indexes do not have buckets/pages that need to be sized as they are self managing. If your table uses a hash index for its primary key, you can re-size the number of pages: For hash indexes that are not for the primary key, you need to drop and re-create the hash index to change the number of pages. Statistics not being updated TimesTen uses a cost based optimizer for its query plans. All SQL cost based optimizers desire up to date statistics.  Stale statistics can result in bad query plans.  Bad query plans give slow performance.  The TimesTen In-Memory RDBMS is all about low latency, so help TimesTen give you the lowest possible latency by keeping your statistic up to date.  There are several ways to do this: The simplest and least efficient is using the ttIsql statsupdate command. This will update stats for all tables and check all rows The ttIsql statsupdate [schema.]tablename command updates statistics for just the specific table The ttIsql statsestimate [[schema.]tablename { n rows | percent } command allows you to estimate the statstic based on a percentage or number of rows.  For large tables estimates can be 'good enough'.  Estimating statistics has the advantage of completing faster than checking all rows in a table.  For instance estimating '3 percent' [of rows] will be a lot faster than checking 100% of the rows in a table with billions or rows. The builtin ttOptEstimateStats is like ttIsql statsestimate, but can be called at runtime from APIs like JDBC, OCI, PLSQL, ODBC etc. Shared Memory being paged in/out TimesTen stores its tables, indexes, sequences, Materialized Views, PLSQL etc in shared memory. Unless the shared memory segment is locked, it can be subject to paging [or worse swapping]. A well tuned TimesTen Database should wait for neither network nor disk IO. So for TimesTen to wait for the operating system to page back in some pages that are needed in your TimesTen shared memory segment is very annoying.  Paging could add milliseconds to your original microsecond queries.  Swapping could add seconds! You can lock the shared memory segment via two techniques: Using the MemoryLock attribute in your sys.odbc.ini config file Using Huge/Large Pages Whenever your TimesTen database is larger than 256 GB, you must use huge/large pages Using huge/large pages for DB < 256 GB can also give a performance improvement So plan ahead.  Correctly size your hash index pages, keep your stats up to date, lock the shared memory segments and use huge/large pages. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Help! My once fast data lookups are now going slower. As databases grow, if you are not careful, data access can take longer. This is very undesirable and can be avoided by understanding the issues and...

Performance

What is the best TimesTen index for my OLTP application

Recently I needed to do some TimesTen OLTP performance tuning for a customer and they wanted to know whether they should use a hash index or a B+tree index for optimal query performance.  I said that it depends on your workloads, but that sometimes you can have the best of both! Range and hash indexes have different pros and cons: So range and hash indexes are good at different things.  Unlike some RDBMS, TimesTen can support multiple different indexes for the same column.   For the above table test, there is a single unique range index for the primary key (column ID). You can also add other hash and ranges indexes to an existing table: You can see that: The primary key column (ID) has both range and hash indexes. Col4 has both range and hash indexes. Col3 + Col4 both have range and hash indexes on the same columns Range and hash indexes can span multiple columns and support many different data types Now that you have both range and hash indexes, how can they help your queries? As you can see from the explain plan, without any SQL hints, the TimesTen query optimizer knows to use the hash index for equality queries and the range index for in-equality queries. This not magic, you need to updated your statistics for the TimesTen query optimizer to generate good query plans. By having both range and hash indexes on the primary key, both equality and in-equality queries can be optimized. For the customer's workload (tables, queries and data), the equality queries could be executed in 1.3 microseconds and the in-equality queries in 2.5 microseconds. For any database, indexes can help speed up queries, but have some overhead and will tend to slow down writes.  Some complex inserts/updates/deletes that have where clauses can benefit from indexes. Adding more indexes is most appropriate for read intensive workloads   Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Recently I needed to do some TimesTen OLTP performance tuning for a customer and they wanted to know whether they should use a hash index or a B+tree index for optimal query performance.  I said...

HowTo

How to use a TimesTen Active Standby Pair on Oracle Bare Metal Cloud

My blog about How do I create a simple TimesTen HA config in the Oracle Bare Metal Cloud, resulted in a TimesTen Active Standby Pair being created on two HighIO instances in the cloud. This blog uses that TimesTen Active Standby Pair to do some simple SQL operations. My active standby pair used the following IP addresses: To use this TimesTen Active Standby Pair I needed to use a bash shell (from Linux, MacOS or Cygwin): ssh to the 'active database' Bare Metal Compute machine as user opc ssh opc@129.146.31.96 Become the oracle user and use a bash shell sudo -n -u oracle bash Run the TimesTen environment script /opt/TimesTen/ott-tt1122-env.sh . /opt/TimesTen/ott-tt1122-env.sh In a separate terminal window, I also logged on to the TimesTen standby database: ssh to the 'standby database' Bare Metal Compute machine as user opc. ssh opc@129.146.22.198 Become the oracle user and use a bash shell sudo -n -u oracle bash Run the TimesTen environment script /opt/TimesTen/ott-tt1122-env.sh . /opt/TimesTen/ott-tt1122-env.sh The ttStatus command shows that the ttimdb database is loaded into memory on machine doughigh2 and that there are various replication connections: ttStatus On the active database [doughigh1] I also run the ttStatus utility to verify that the database was loaded into memory and that replication was running: ttStatus On the active database [doughigh1], I connected to the ttimdb TimesTen database via ttIsql and verified the replication scheme via the repschemes command: ttisql ttimdb repschemes In the ttimdb database that I created, the schema owner was called appuser. To connect to the database as user appuser, I needed to do the following: quit the current ttisql session exit or quit Connect as user appuser ttIsql "dsn=ttimdb;uid=appuser;pwd=appuser" Use the tables command to show the tables owned by appuser tables Describe the customers and orders tables desc customers desc orders I could then start using SQL to select the current customers and add a new customer to the active DB on machine doughigh1: Select * from customers; Insert into customers values (123, 'Big', 'Bird', 'Seasame St'); I could also update and deletes rows in the active database: On the standby database [doughigh2], I checked the rows in the customers database before and after rows are changed on the active database. I could not write to the standby database as it is read only. To be able to create and alter tables on the active database, I needed to login as the TimesTen instance administrator as the DB user appuser did not have the admin privilege. The create table and alter table commands were replicated to the standby database: quit out of the ttIsql session that is connected as user appuser quit Connect to TimesTen as the database owner.  The current DB is called ttimdb ttIsql ttimdb The create and alter table statements are replicated to the Standby Database. On host doughigh2: tables desc t; Summary With an Active Standby Pair, if you have the correct privileges, you can do any DDL, DML or queries on the active database. On the standby database, only read only queries are supported Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

My blog about How do I create a simple TimesTen HA config in the Oracle Bare Metal Cloud, resulted in a TimesTen Active Standby Pair being created on two HighIO instances in the cloud. This blog uses...

How to create a simple TimesTen HA config on Oracle Bare Metal Cloud

My blog How do I install TimesTen in the Oracle Bare Metal Cloud, resulted in the TimesTen In-Memory Database software being installed on a set of machines in the Cloud. This blog uses those TimesTen installations and covers how to create the simplest TimesTen high availability configuration, a TimesTen Active Standby Pair via the mkasp script: The mkasp script will create a database, populate it with data, create the active standby pair and replicate some data. Using the Oracle Bare Metal Cloud Service console, I created some Bare Metal High IO instances [BM.HighIO1.36] in three availability domains: You need to click on the instance names (eg doughigh1 and doughigh2) to determine the public and private IP addresses for those machines. The resulting IP addresses were: From your on premises machine [ie a bash shell in Linux, MacOS or Cygwin] do the following: Change to the directory where the ott-bmc-byol scripts are Run the mkasp script with the private IP addresses for the machines that will host the active and standby databases -a means the Active host's IP address -s means the Standby host's IP address --admpwd is the password for the TimesTen Database user who will have the admin privilege --apppwd is the password for the TimesTen Database user who owns the schema objects For my on premise environment, the commands are: cd ott-bmc-byol/scripts/timesten ./mkasp -a 129.146.31.96 -s 129.146.22.198 --admpwd admin --apppwd appuser This script will run several other scripts which will create the active standby pair and replicate a row of data: The end of the script output should look like this: The script output showed 'Active DB OK' and that the row for order_number 68531180 was replicated to the standby database. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

My blog How do I install TimesTen in the Oracle Bare Metal Cloud, resulted in the TimesTen In-Memory Database software being installed on a set of machines in the Cloud. This blog uses those TimesTen...

HowTo

How do I install TimesTen in the Oracle Bare Metal Cloud

Many customers have asked me how do I run TimesTen in the Cloud. This series of blogs shows you how to quickly and cheaply run TimesTen in the Oracle Bare Metal Cloud. The formula is simple: A single script installs and configures TimesTen on any of the available Bare Metal compute shapes. A white paper explains all of the steps in great detail. TimesTen will run on metered compute instances which are charged by the hour. TimesTen will use a "bring your own license model".   Download TimesTen 11.2.2.8.20 for Linux x8664 from support.oracle.com using patch id 25078246  The four year old version of TimesTen 11.2.2.8.0 is available from OTN, but this version has neither the bugs fixes nor performance optimizations included in 11.2.2.8.20  The compute instances will use the Bare Metal Oracle Linux 7.3 image as the TimesTen Cloud install script uses systemd As the compute instances run in the customer's tenancy, customers can SSH to the compute instances and run their own applications.  This enables Direct Linked connections to the TimesTen RDBMS for latency critical applications. Optionally download JDK8 if you want to test your JDBC applications with TimesTen TimesTen can be installed and up and running on a set of machines (using the provided script) in under 5 minutes (if you have a broadband internet connection with fast upload speeds). TimesTen In-Memory Database can run really fast in your data center. TimesTen can also run really fast in the Oracle Bare Metal Cloud, but just how fast depends on the hardware that it runs on and how it is configured: Comments There are nine NVMe storage devices which can be striped for extreme IO performance. There are four NVMe storage devices which can be striped for extreme IO performance. These NMVe storage devices are local to the compute instance. The hardware is based on Sun X5-2. This is a dedicated server with no VMs. The hardware is based on Sun X5-2. This is a shared server with VMs. Only iSCSI (Block Volume Service) is available for storage.   The 32 GB local disk is not big enough for the TimesTen checkpoint files and transaction logs. The block volumes can be 256 GB or 2 TB in size. The block2 volumes are available with 1 GB increments (starting at 50 GB). While the Block Volume Service does not give as impressive IO performance as the NMVe SSDs, it is cheap and the block volumes exist independent of the compute shape.  The TimesTen DB cannot use all of the DRAM on the compute node as memory is needed for the OS, any non TimesTen applications and other data structures in the TimesTen (ie Log Buffer, Temp/Sort Area and PLSQL). At least two 256 GB block Volumes are needed for the TimesTen Checkpoint files A white paper which covers all of the details is available here.  Once you have provisioned one or more of the above Bare Metal compute shapes, run the following script from your on premise machine [bash shell in Linux, MacOS or Cygwin] to install TimesTen on your compute shapes. If you use Cygwin, you need to explicitly install the OpenSSH and Unzip packages $ ott-bmc-byol/scripts/ott_bmc_install <ipaddr> [<ipaddr> …] The script does the following on each of the IP address that were passed to the script: Installs TimesTen in /opt/TimesTen as user oracle Installs the TimesTen Quickstart to enable various demo databases and programs to be created Uses yum to install NTP, libaio, lvm2, yum-plugin-security, nc and dstat and updates any needed packages Creates the /u10 file system for the TimesTen checkpoint and transaction log files   The NVMe devices will be striped using LVM for the BM.DenseIO1.36 and BM.HighIO1.36 shapes The iSCSI Bare Metal Block Volumes will be striped using LVM Configures NTP to control clock skew Configures the Bare Metal network and adds firewall ports  Configures systemd to run the TimesTen main daemon as a service Starts the TimesTen main daemon, but no TimesTen databases are created An up-coming series of blogs will show you how to use TimesTen in the Oracle Bare Metal Cloud: How to create a simple TimesTen HA config on Oracle Bare Metal Cloud How to use a TimesTen Active Standby Pair on Oracle Bare Metal Cloud Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Many customers have asked me how do I run TimesTen in the Cloud. This series of blogs shows you how to quickly and cheaply run TimesTen in the Oracle Bare Metal Cloud. The formula is simple: A single scr...

General

How compatible is TimesTen In-Memory Database with the Oracle Database?

Customers often ask me how compatible is TimesTen In-Memory Database with the Oracle Database.  The answer, like most things is life is that it depends. If you are doing OLTP workloads using JDBC, OCI, ODP.NET, Pro*C or PLSQL with compatible data types then TimesTen is highly compatible.     plsqlJDBC.java Tptbm.java addemp.c plsqlOCI.c tptbmOCI.c DemoODP.cs addempPROC.pc cursorPROC.pc plsqlPROC.pc case_procedures.sql cursor_update_logic.sql inner_loop_block.sql select_exception.sql The TimesTen Quickstart Guide provides the instructions to build and run these samples programs. The Oracle 11.2.0.4 PLSQL language was ported to the TimesTen In-Memory Database. Since Oracle 11.2.0.4, the same Oracle Instant Client is used for both the Oracle Database and the TimesTen In-Memory Database.   The Oracle Instant Client routes connections to either the Oracle Database or TimesTen In-Memory Database depending on attributes in the tnsnames.ora file or via the easy connect string.  The Oracle 11.2.0.4 OCI Library was ported to the TimesTen In-Memory Database and is part of the Oracle Instant Client. Oracle SQL APIs such as Pro*C and ODP.NET that are dependent on OCI work with TimesTen as TimesTen supports OCI. Open Source SQL APIs based on OCI such as Node.js, Ruby-OCI8, Python cx_Oracle 5.3 and ROracle, while currently not supported by Oracle TimesTen, they do appear to work: This means that for latency critical OLTP applications, that familiar SQL APIs can be used with TimesTen In-Memory Database or TimesTen Application Tier Database Cache. TimesTen uses the following data types which are compatible with the Oracle Database BINARY_DOUBLE BINARY_FLOAT BLOB CHAR[ACTER][(n[BYTE|CHAR])] CHARACTER VARYING(n[BYTE|CHAR]) CHAR VARYING(n[BYTE|CHAR]) CLOB DATE DEC[IMAL][(p[,s])] DOUBLE [PRECISION] FLOAT[(b)] INT[EGER] NATIONAL CHARACTER(n) NATIONAL CHAR(n) NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n) NUMERIC[(p[,s])] NCHAR[(n)] NCLOB NUMBER[(p [,s])] NVARCHAR2(n) REAL ROWID SMALLINT TIME TIMESTAMP VARCHAR[2](n[BYTE|CHAR]) The following Oracle Data Types are NOT currently supported Bfile, Long, Long Raw, Raw, Urowid Any-types, Xmltypes, Spatial types, Media types TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE User defined types Although TimesTen is primarily an OLTP RDBMS, it can also do some analytic processing.  TimesTen supports the following analytic SQL: WITH CUBE ROLLUP GROUPING SETS DENSE_RANK FIRST_VALUE GROUP_ID GROUPING GROUPING_ID LAST_VALUE LEAST RANK OVER PARTITION BY ROWS BETWEEN ROW_NUMBER UNBOUNDED PRECEDING CURRENT ROW PRECEDING | FOLLOWING So depending on the SQL API, data types and SQL operations, TimesTen In-Memory Database can be very compatible with the Oracle Database. Download TimesTen In-Memory Database and give it a try today. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Customers often ask me how compatible is TimesTen In-Memory Database with the Oracle Database.  The answer, like most things is life is that it depends. If you are doing OLTP workloads using JDBC, OCI,...

General

What is the best High Availability option for TimesTen?

Almost all TimesTen In-Memory Database customers use some form of high availability configuration as they want high performance RDBMS solutions without a single point of failure. Not all customers have the same sort of applications or availability requirements, so they have to choose which TimesTen replication configuration best suits them.   The following picture shows a subset of the possible replication configurations: There are two main types of Replication, Active Standby Pair and Classic Replication. Active-Active, N-Way and Propagation are examples of TimesTen Classic Replication. The following picture summarizes the main differences between Active Standby Pair based and Classic Replication: Active Standby Pair replication schemes can also optionally be configured to include a set of read only subscriber databses: For 'classic replication' (ie non active standby pair configurations) there are several options. Replicating selected DB objects to multiple subscribers Split workload bidirectional replication Distributed Workloads Another thing to consider for replication configurations is whether the data is sent synchronously or asynchronously. Asynchronous Replication Return Two Safe (synchronous replication) Return Receipt (synchronous replication) Summary of synchronous and asynchronous replication options If you need to support read/write caching for Oracle 11g/12c data with TimesTen Application Tier Database Cache, then you have to use one of the Active Standby Pair configurations. Active Standby Pair replication configurations are the simplest to understand, configure and manage. The split workload and distributed workload configurations for Classic Replication give more flexibility, but careful consideration is needed to avoid and/or mitigate write/write conflicts. The active standby pair and classic replication configurations are building blocks that can be used for scale out architectures. For example, some TimesTen telecommunications customers with high throughput requirements, use application level partitioning to route transactions to a set of active standby pairs. Find out more about TimesTen In-Memory Database and TimesTen Application Tier Database Cache. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Almost all TimesTen In-Memory Database customers use some form of high availability configuration as they want high performance RDBMS solutions without a single point of failure. Not all customers have...

General

How does TimesTen give lower latency for OLTP when all of my data is in the Oracle SGA

The TimesTen In-Memory Database is optimized for low latency OLTP workloads.  A disk based database such as Oracle stores all of its data on disk and a subset of its data in the SGA. Oracle uses a buffer pool/cache to transfer data blocks to and from disk and the SGA as needed. Surely if all of the data is in a huge Oracle SGA so that no disk IOs are required, then Oracle would be as fast as TimesTen In-Memory Database? Even in this best case scenario when Oracle has cached all of the data blocks in the SGA, TimesTen can still be significantly faster.  How does it do it? TimesTen does not use a buffer cache, so it knows that all of the data is in memory all of the time.  This enables simpler and faster algorithms and data structures to be used. TimesTen can use Direct Linked connections.  When the application and TimesTen both run on the same machine, the application (Java, C, C++, .Net, PHP etc) can avoid network and IPC overheads and share the same address space as the database.   TimesTen can also use Client Server connections (over a TCP socket) when the application is on a different machine than the TimesTen Database In the following latency benchmark, an Oracle DB with and without the use of TimesTen Application Tier Cache are compared: For latency benchmarks, smaller is better. You can see that even when all of the Oracle data blocks were cached in the SGA that the TimesTen latency was significantly less.  This was the perfect apples to apples comparison: The same TATP HLR workload was used The same data was used The sample JDBC application was used. The only different was the connect string The same hardware was used (Intel Xeon E5-2680 @ 2.7 GHz) The same operating system was used (OEL 5) The combination of TimesTen Application Tier Database Cache for low latency and Oracle Exadata for scalability gives the best of breed solution for large scale OLTP applications. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

The TimesTen In-Memory Database is optimized for low latency OLTP workloads.  A disk based database such as Oracle stores all of its data on disk and a subset of its data in the SGA. Oracle uses a...

General

What is the difference between Oracle In-Memory and Oracle TimesTen In-Memory Database

Many customers ask me whether Oracle In-Memory and Oracle TimesTen-In Memory Database as the same thing; they are not. Oracle In-Memory is a feature of the Oracle Database and is great for analytics (read mostly workloads) Oracle TimesTen In-memory Database (is a separate product from the Oracle Database) and is great for latency critical OLTP applications Tirthankar Lahiri and I did a presentation at OOW 2016 that neatly summarized the differences in a picture: So Oracle In-Memory is a column store best suited for high speed analytic processing than runs in the Oracle DB and TimesTen In-Memory Database is a row store best suite for latency critical OLTP processing that runs in the application tier. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Many customers ask me whether Oracle In-Memory and Oracle TimesTen-In Memory Database as the same thing; they are not. Oracle In-Memory is a feature of the Oracle Database and is great for analytics...

General

Oracle TimesTen and Oracle In-Memory leaders in Forrester Wave In-Memory Databases Q1 2017

In the The Forrester WaveTM: In-Memory Databases, Q1 2017 report, Oracle TimesTen In-Memory Database and Oracle In-Memory were rated the top In-Memory Databases: The report states: "Oracle offers Oracle TimesTen In-Memory Database and Oracle Database In-Memory products. With the combination of these two options, customers can deploy any type of application -- transactional, operational, and analytical." "Oracle customers building new transactional or analytical applications should look at TimesTen, and existing Oracle databases would benefit by upgrading to the in-memory option in Oracle Database." Forrester evaluated thirteen In-Memory databases based on 24 criteria and considered the current offerings, strategy and market presence. The DBMS vendors in the Leaders category were: Oracle SAP Teradata IBM Microsoft The DBMS vendors in the Strong Performers category were: Red Hat Redis Couchbase DataStax AeroSpike VoltDB The DBMS vendors in the Contenders category were: memSQL StarCounter More information about TimesTen In-Memory Database and Oracle In-Memory. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

In the The Forrester WaveTM: In-Memory Databases, Q1 2017 report, Oracle TimesTen In-Memory Database and Oracle In-Memory were rated the top In-Memory Databases: The report states: "Oracle offers Oracle...

General

Is TimesTen an RDBMS or a Cache for Oracle 11g/12c?

A question that many customers have asked me is whether TimesTen is just a cache for their Oracle database or an (RDBMS) system of record. TimesTen can be configured to be either a read/write cache for Oracle databases, or act as a system of record. TimesTen as an RDBMS system of record TimesTen is a low latency In-Memory RDBMS which persists data to disk and supports ACID transactions. The checkpoint files and transaction logs persist the data to enable database recovery in the unlikely event of hardware or software failure. TimesTen uses the transaction logs files to support persistent ACID transactions with COMMIT and ROLLBACK operations. TimesTen also supports many replication configurations to enable high availability and online upgrades.  TimesTen uses SQL and PLSQL and supports many runtime environments: TimesTen In-Memory Database is a transactional, recoverable, RDBMS for SQL and PLSQL applications which acts as a system of record for most of our customers. TimesTen as a Cache for Oracle Database Tables in the TimesTen In-Memory RDBMS can be configured to act as read only or read/write caches for the corresponding tables in an Oracle database. When TimesTen is caching tables from Oracle, the data is still persistent and recoverable.  Both cached and non-cache [local] tables can exist in a TimesTen database at the same time.  Applications can do SQL joins between cache and non cache tables : Applications that connect to the TimesTen (cache) tables benefit from the lower latency that TimesTen provides.  Using TimesTen Application Tier Cache with for Oracle 12c Exadata gives you the best of both - low latency from TimesTen and massive scalability from Exadata.  This white paper gives more details on TimesTen Caching. This Telecommunications benchmark (TATP HLR) shows dramatic reductions in response times when the Oracle Database uses TimesTen Application Tier Database Cache. This benchmark is an apples to apple comparison: The exact same HLR workload is used in both cases The exact same JDBC code is used for the benchmark, only the connect strings are different The exact same data is used on both cases The exact same and hardware and OS is used for TimesTen and Oracle: Intel® Xeon CPU E5-2680 @2.7GHz 2 sockets 8 cores/socket 2 hyper-threads/core 32 vCPU Oracle Enterprise Linux 5 In summary, TimesTen can be configured as either a system of record or as a Read/Write Cache for Oracle 11g/12c.  Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form. 

A question that many customers have asked me is whether TimesTen is just a cache for their Oracle database or an (RDBMS) system of record. TimesTen can be configured to be either a read/write cache fo...

HowTo

Just how fast is TimesTen In-Memory Database?

The TimesTen In-Memory Database provides excellent performance on even commodity hardware. RDBMS performance has two important factors, latency and throughput. Latency is about how fast SQL Select, Insert, Update, Delete or Merge operations can be completed. TimesTen is known for enabling really low latency SQL transactions. We measure TimesTen latency in microseconds rather than milliseconds: This latency benchmark was run on commodity Linux / Intel hardware: 2 CPU sockets, 22 cores/socket, Intel Xeon E5-2699 v4 @ 2.20GHz The benchmark is TPTBM for Reads and Updates TimesTen 11.2.2.8.0 (100M rows of data, DB is 17 GB) Having really low latency also helps with throughput. RDBMS throughput is defined in terms of (ACID) transactions per second. This throughput benchmark was run on the same commodity hardware: 2 CPU sockets, 22 cores/socket, Intel Xeon E5-2699 v4 @ 2.20GHz The benchmark is TPTBM for mixed workload (80% reads, 10% updates, 5% inserts and 5% deletes) TimesTen 11.2.2.8.0 (100M rows of data, DB is 17 GB) The performance of TimesTen is dependent on the workload, hardware and tuned SQL statements.  These TPTBM benchmarks used primary key lookups and the SQL statements in each transaction only affected a few rows.  If your SQL workload has complex joins involving many tables or returns a huge number of rows then these operations will take longer.  The hardware that the workload runs on also affects the performance.  Faster CPUs in terms of GHz, size of the L3 cache and number of cores tend to give better TimesTen In-Memory Database performance. Download and try TimesTen In-Memory Database with your workload.  The TimesTen Quickstart Guide will lower the learning curve. Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

The TimesTen In-Memory Database provides excellent performance on even commodity hardware. RDBMS performance has two important factors, latency and throughput. Latency is about how fast SQL Select,...

HowTo

Back to Basics - Multiple Connections in ttIsql

A user recently asked if ttIsqlcan support multiple concurrent connections to a TimesTen database. We thoughtthe answer might be of interest to other users as well, and decided to includeit in this blog. ttIsql is an interactive SQL command line utility for workingwith TimesTen databases. In addition to supporting SQL command execution, ttIsql provides a rich set offunctionality to enable the users to connect to the databases, execute built-inprocedures and utilities, support flexible command editing, and the ability toinvoke host OS commands, all within the same ttIsql session. One of the scenarios where it’s convenient to have multiple connectionswithin the same ttIsql session iswhen you want to execute commands that require different user credentials.  For example, there are database operationsthat require the Admin privilege, and other operations that only require tableowner or general database access privileges.  If you plan to use multiple connections, it’s useful to name theconnections so that ttIsql can help you clearly identify which connection youare executing.  In the example below, we will create two connections to the sampledatabase, sampledb – first connectionas the application user (appuser) andthe second connection as the Admin user (adm). We will use the CONNECT commandand specify a connection name for each: $ ttIsq Command> CONNECT “DSN=sampledb;uid=appuser;pwd=appuser;ConnectionName=app_conn”; Command> SELECT count (*) from mytable; < 1000000 > 1 row found. Command> CONNECT "DSN=sampledb; uid=adm;pwd=adm; ConnectionName=adm_conn"; adm_conn: Command> In ttIsql, the lastconnection made is the current active connection, and the ttIsql prompt displays the connection name currently in use. Asshown above, adm_conn is theconnection currently in use. adm_conn: Command> CALL ttRamPolicySet (‘manual’); The ttRamPolicySet built-in procedurerequires the ADMIN privilege; instead of exiting the current ttIsql session or starting anothersession, we conveniently use the multiple connection functionality here. As shown above, we executed a command to setthe RAM policy to MANUAL using the Admin connection. To switch to a different connection, you simply execute the USE command: adm_conn: Command> USE app_conn; app_conn: Command> CALL ttVersion(); < 11, 2, 2, 8, 0 > 1 row found. Notice that after the USEcommand, the ttisql prompt changed tothe app_conn to indicate the currentconnection context. ttVersion is abuilt-in procedure that returns the current TimesTen version, and it can becalled by any user. To disconnect a connection, usethe DISCONNECT command: app_conn: Command> DISCONNECTadm_conn; Another useful feature in ttIsql is the ability to execute host OScommands. You can execute any OS commands that you are entitled to. As a simpleexample below, we executed the MORE command to view the content in myconfig.ini file: app_conn: Command> HOST more /home/appuser/myconfig.ini This is myconfig.ini file Line 1 Line 2 Line 3 app_conn: Command> exit $ There aremany other features in ttIsql you canuse to help automate your day-to-day operations. Refer to the TimesTenDatabase Reference product documentation for more details.

A user recently asked if ttIsql can support multiple concurrent connections to a TimesTen database. We thought the answer might be of interest to other users as well, and decided to includeit in this...

General

New TimesTen Plug-in Release 12.1.0.3.0 for Oracle Enterprise Manager

LastFriday, Oracle released a new version 12.1.0.3 of TimesTen Plug-in for OracleEnterprise Manager 12c Cloud Control. This is a landmark release with many newfeatures for database administrators in the enterprise. Besidesdatabase performance and availability monitoring; the new plug-in offersadministrators the ability to manage and administer their TimesTen instances anddatabases, such as starting and stopping TimesTen services, loading andunloading databases to and from memory, and scheduling backups and restoringdatabases. Additionally, users can monitor database and replication activities,memory and disk usages, workload performance statistics, and identify longestrunning and most executed SQL statements. Internationalusers will be pleased to know that the new TimesTen plug-in was globalized withits user interface translated to nine different languages, the same languagesas available in Oracle Enterprise Manager.   TimesTenplug-in 12.1.0.3.0 is downloadable through Enterprise Manager Self-Update. Formore information, visit the TimesTen product center on the Oracle TechnologyNetwork. Blog contributor: SimonLaw-oracle 

Last Friday, Oracle released a new version 12.1.0.3 of TimesTen Plug-in for Oracle Enterprise Manager 12c Cloud Control. This is a landmark release with many newfeatures for database administrators in...

General

Product News on SUSE 12 and HP-UX Itanium

Thursday – March 12, 2015 Recently, we added support for two new platforms with the latest release of Oracle TimesTen In-Memory Database: TimesTen 11.2.2.8.0 is certified for use with SUSE Linux Enterprise Server 12 (download the same binary for Linux X86-64 bit) TimesTen 11.2.2.8.1 is certified for the HP-UX Itanium platform (v11.31) The software is available for download from the Oracle Support site http://support.oracle.com

Thursday – March 12, 2015 Recently, we added support for two new platforms with the latest release of Oracle TimesTen In-Memory Database: TimesTen 11.2.2.8.0 is certified for use with SUSE...

General

Product News – TimesTen 11.2.2.8

In case you missed it, the latest TimesTen release, version 11.2.2.8.0,was released at the end of January for all supported platforms.  I want to call out a couple new featureswhich provide significant performance improvement. 1.  Dramatic Increase in Parallel Replication Throughput For users who have OLTP applicationworkloads running at very high transaction rates and are using TimesTenReplication for high availability, the new replication feature in this release willbe of interest to you. If your Replication configurationis currently using the single threaded Replication mode, you may want to considerupgrading your Replication scheme to take advantage of the Automatic ParallelReplication (APR) feature, which was made available in the 11.2.2.x releases.APR provides a significant boost for replication throughput – the rate oftransactions replicated from one TimesTen database to another TimesTen databaseon a remote host. The improvement varies depending on your workload; we haveseen a factor of 2 to 3 times increase in the rate of replicated transactions. AutomaticParallel Replication supports both Synchronous 2-Safe and Asynchronousreplication topologies using the Active-Standby Pair replication schemes. When automatic parallel replicationis enabled, transaction dependencies are tracked automatically and the commitordering is honored when applying the transactions on the remote standbydatabase. There are no application changes required to enable parallelreplication, and the setting of replication parallelism is a user-specifiedconfiguration. The attribute name is ReplicationParallelism,you set it to a value greater than one (1) to enable parallel replication. Many of the OLTP applications havetransactions that are independent of each other. For example, deducting the balanceof your account is independent of deducting the balance of my account; updatinglocation of your mobile device is independent of the location of my device; forsuch applications, it is typically not necessary to apply these transactions inthe same commit order when using replication for high availability. In the 11.2.2.8.0 release, users mayenable a new Parallel Replication feature to relax enforcing commit ordering onthe receiving hosts, when TimesTen determines the transactions have nodependencies on each other. This feature provides even more parallelism when applyingthe replicated transactions on the Standby and Subscriber databases. Dependingon your transaction workload characteristics, we have seen close to 80% improvementin replication throughput after relaxing the commit ordering on the standbydatabase. To relax the commit orderingon the receiving host, set the attribute ReplicationApplyOrderingto a value of 2 when configuring the Active-Standby Pair replication scheme. In a replicated environment, theability to replicate the rate of changes is important because if you have afailover, the rate of changes being replicated determines how close your Standbyis synchronized with the Active. Ideally, they should be the same, or as closeas possible if you choose to use asynchronous replication.  Refer to the TimesTenReplication documentation for more details. 2.  Reducedatabase restart time using parallel read operations from database checkpointfile In the 11.2.2.8.0 release, databaserestart time can be significantly faster by enabling parallel threads to readthe TimesTen database checkpoint files; this is especially useful when thecheckpoint files are relatively large and reside on Solid State or Flashstorage. The parallel checkpoint reads feature is enabled bysetting the new CkptReadThreadsattribute. CkptReadThreads is a First Connection attribute and should be setprior to loading the database. The attribute value specifies the number of threads that TimesTenuses to read checkpoint files when loading the database to memory. The default value of CkptReadThreads is set to 1 (generally intended for hard diskstorage). When using Solid State Drives or Flash storage, users can set the CkptReadThreads attribute to a valuefrom 2 to 8. The overall read rate from the SSD/Flash is best achieved bysetting the attribute to a value between 4 and 8; actual performance may varydepending on device models. Using the current generation of SSD and Flash storagewith 8 parallel checkpoint read threads, it’s possible to achieve 2 GB/sec readrate using a single SSD device or a PCIe Flash card, and to achieve 3.4 GB/sec sustainedread rate when using two SSD devices/Flash cards (via disk striping). Thismeans, a TimesTen database of one terabyte in size can be loaded to memory inabout 5 minutes. To improve database restart time, consider usingSSD/Flash devices for your TimesTen Checkpoint files, and enable the parallelcheckpoint reads feature. TimesTen software is available on the TimesTen ProductCenter on the Oracle Technology Network.

In case you missed it, the latest TimesTen release, version 11.2.2.8.0, was released at the end of January for all supported platforms.  I want to call out a couple new featureswhich provide...

General

TimesTen client support for OS X 10.9 (Mavericks)

A new TimesTen 11.2.2 client for OS X Mavericks is available for download from OTN.   This client driver allows applications running on OS X to access TimesTen databases that reside on other supported operating systems.  Developers using applications such as SQL Developer may find this very valuable!  Download the new TimesTen 11.2.2 client for OS X today at: http://www.oracle.com/technetwork/database/database-technologies/timesten/downloads Also ... thanks to everyone who came to see us at Oracle OpenWorld last week!

A new TimesTen 11.2.2 client for OS X Mavericks is available for download from OTN.   This client driver allows applications running on OS X to access TimesTen databases that reside on other supported...

General

TimesTen at Oracle OpenWorld 2014

Coming to Oracle OpenWorld 2014 in San Francisco?  It's almost here! It's also a great opportunity to learn more about the Oracle TimesTen In-Memory Database.   We have some exciting presentations, and will be available in the DemoGrounds to answer your questions!   Check out the schedule below.  We hope to see you there!  TimesTen In-Memory DatabaseOracle OpenWorld 2014 Sessions The Oracle OpenWorld session schedule and locations may change. Please refer to the Content Catalog for the latest information. Date Time ID Title Type Venue Tue9/30/2014 3:45pm CON7782 Speed Up Your Applications with an Application-Tier In-Memory Database Conference Session Moscone North 131 6:00pm MTE9326 Tips and Tricks for Using Oracle TimesTen In-Memory Database in the Application Tier Meet the Experts Session Moscone South 305 Wed10/01/2014 3:30pm CON7672 Application-Tier In-Memory Analytics: Best Practices and Use Cases Conference Session Moscone North 131 Visit us at the Oracle Database DEMOgrounds in Moscone South, LeftOracle TimesTen In-Memory Database booth SLD-135Oracle TimesTen Application-Tier Database Cache booth SLD-136

Coming to Oracle OpenWorld 2014 in San Francisco?  It's almost here! It's also a great opportunity to learn more about the Oracle TimesTen In-Memory Database.   We have some exciting presentations, and...

General

Oracle TimesTen In-Memory Database release 11.2.2.6 is available for download

This release provides new capabilities thatfurther improve performance and scalability for applications with extremelyfast response time and very high throughput requirements, specifically forOnline Transaction Processing (OLTP) workloads and Business Intelligence (BI)in-memory analytics workloads for the Oracle Exalytics In-Memory Machine. The release also contains bug fixes as detailed in therelease notes. Feature highlights: Performance for In-Memory Analytics Significant improvements to the SQL Optimizerand Execution engine for in-memory analytics. These improvements both reducequery prepare time while also enabling better plan generation resulting inimproved query response time for certain types of query. In particular, theperformance of queries using star-joins and left outer joins has been improved. Ability to interrupt data loading It is now possible to interrupt an inprogress data load that is using the ttLoadFromOracle builtin. The load can beinterrupted using the SQLCancel ODBC function, the Statement.cancel() JDBCmethod or, in ttIsql, by pressing Ctrl-C. Improved SQL support Support is added for constant expressions,dynamic parameters, and NULL values in the values lists used for IN, ANY, SOME, or ALL. Improved manageability of Clusterware managedActive-Standby pairs It is now possible to change the cacheadministrator password without dropping and re-deploying the active-standbypair. Integration with SQLDeveloper 4.0 This release supports, and is supported by,SQL Developer 4.0. The following new features are available as part of thisintegration: · DataLoading wizard for populatingTimesTen tables using existing data from the Oracle Database · IndexAdvisor wizard for queryperformance tuning · PerformanceMetrics wizard to simplifyperformance reporting TimesTen Quick Start Guide: Some additional 'best practices' sectionshave been added to the TimesTen Quick Start Guide while other material has beenupdated. Platform Availability: TimesTen 11.2.2.6.0 is available immediatelyon all supported platforms. Download Oracle TimesTen now! Oracle TimesTen 11.2.2.6 release supports thefollowing licensed products in the TimesTen product family: Oracle TimesTen In-Memory Database; Oracle TimesTen In-Memory Database for Exalytics; Oracle In-Memory Database Cache; and In-Memory Database Cache for Oracle Applications.

This release provides new capabilities that further improve performance and scalability for applications with extremely fast response time and very high throughput requirements, specifically forOnline...

General

Oracle TimesTen Software Update 11.2.2.5 Release Availability

Oracle TimesTen In-Memory Database release 11.2.2.5 is available for download. This release provides new capabilities that further improve performance and scalability for applications with extremely fast response time and very high throughput requirements, specifically for Online Transaction Processing (OLTP) workloads and Business Intelligence (BI) in-memory analytics workloads for Oracle Exalytics In-Memory Machine. Feature highlights for Business Intelligence In-Memory Analytics workloads: Performance and Scalability for In-Memory Analytics The SQL Optimizer for in-memory analytics added a number of optimizations targeted for common star-join query patterns. These enhancements resulted in the optimizer choosing better query plans and speeding up overall query response time: Enables up to 3-10x query response time improvements for BI workloads and selected Oracle BI Applications; Enhanced in-memory query processing for star schemas Full Hash Index support. Prior to this release, hash indexes were supported only for primary key columns. Hash indexes may now be created on any set of columns to help speed up complex queries with equi-joins; Improved Index Advisor recommendations provide optimal indexes based on a specific query set. Improvements to reduce data loading time Data loading has been enhanced with the ability to support parallel, concurrent writes into a large compressed table. This feature significantly reduces the overall data loading time from the Data Warehouse to the Oracle TimesTen database in the Oracle Exalytics machine. Enables up to 3x reduction in data loading time for compressed tables; Improved data loading from the Oracle Database to TimesTen using the (ttLoadFromOracle) built-in function, with added support for parallel inserts to compressed tables; New utility (ttImportFromOracle) to analyze the data from an Oracle Database; the utility generates DDLs for tables and indexes with optimal Oracle TimesTen native data types and recommendation for columnar compression. Feature highlights for Online Transaction Processing (OLTP) applications: Optimistic Concurrent B+ Tree index for write scalability A new B+Tree indexing capability featuring optimistic concurrency control provides increased scalability for write-intensive applications. This B+Tree index feature provides better throughput with higher numbers of concurrent writers, and is ideal for applications with very high volumes of insert and update transactions running in modern computing architectures with many processing cores. Greater concurrency support and higher transaction throughput for write intensive applications Enables up to 5x throughput improvement compared to existing B-tree indexes Full Hash Index support for read scalability The Hash index is a unique feature in Oracle TimesTen. A Hash index offers better performance for equality look up of Primary Key and is commonly used by OLTP application running on Oracle TimesTen databases. This release added the ability to create hash indexes on all columns in the table. Hash indexes can now be created for any single column and multiple-column indexes Enables up to 7x throughput improvement for read-intensive applications Improved Scalability for NUMA systems Modern computers are designed with high number of cores per server with large quantity of DRAM available. Such systems commonly exhibit Non Uniform Memory Access (NUMA) variations in memory latency, for example, when accessing memory outside of the local processor (socket). TimesTen as an in-memory database runs at memory (DRAM) speed and is more sensitive to the NUMA effect than a conventional disk-based relational database, where data access is optimized at the disk storage level. Improving TimesTen scalability on NUMA systems is an on-going effort in every release. A number of NUMA locality enhancements and code path optimizations were added in the 11.2.2.5 release, resulting in improved read and write scaling, especially on systems with very high number of processor cores such as the Oracle T5-8 servers, which have 128 cores and 1024 processing threads per server. 59.9 Million reads per second on Oracle T5-8 machine 1.03 Million update transactions per second on Oracle T5-8 machine New Manageability Capabilities New in this release is a utility for real-time monitoring and capturing snapshots of monitored metrics. This utility, ttStats, provides HTML reports of TimesTen database performance (similar to the Automatic Workload Repository (AWR) facility for the Oracle Database.) Also included in this new monitoring facility is a set of PL/SQL procedures for custom development. ttStats is a standalone TimesTen utility, invoked at the command line, and requires no additional software. ttStats can be used for real-time monitoring, snapshots captures, and HTML reports. In addition to database level performance, the ability to time individual SQL statements is available in this release allowing the administrator to quickly identify long running operations. Oracle TimesTen Quick Start Guide A user-friendly, self-guided HTML Guide is available to get started with Oracle TimesTen. The Quick Start Guide uses the “How to” approach to provide easy to follow instructions for all components of the product, from database configurations, setups, to programming APIs, to configuring replication and cache grid, the instructions are provided step by step with screen shots and explanations for new and existing TimesTen users. The 11.2.2.5 Quick Start Guide also added many new Best Practices for Oracle TimesTen database and OS settings, Replication, Caching from the Oracle Database, and Monitoring. Download Oracle TimesTen now! Oracle TimesTen 11.2.2.5 release supports the following licensed products in the TimesTen product family: Oracle TimesTen In-Memory Database; Oracle TimesTen In-Memory Database for Exalytics; Oracle In-Memory Database Cache; and In-Memory Database Cache for Oracle Applications.

Oracle TimesTen In-Memory Databaserelease 11.2.2.5 is available for download. This release provides new capabilities that further improve performance and scalability for applications with extremely...

General

ROracle support for TimesTen In-Memory Database

Today's guest post comes from Jason Feldhaus, a Consulting Member of Technical Staff in the TimesTen Database organization at Oracle.  He shares with us a sample session using ROracle with the TimesTen In-Memory database.  Beginning in version 1.1-4, ROracle includes support for the Oracle Times Ten In-Memory Database, version 11.2.2. TimesTen is a relational database providing very fast and high throughput through its memory-centric architecture.  TimesTen is designed for low latency, high-volume data, and event and transaction management. A TimesTen database resides entirely in memory, so no disk I/O is required for transactions and query operations. TimesTen is used in applications requiring very fast and predictable response time, such as real-time financial services trading applications and large web applications. TimesTen can be used as the database of record or as a relational cache database to Oracle Database. ROracle provides an interface between R and the database, providing the rich functionality of the R statistical programming environment using the SQL query language. ROracle uses the OCI libraries to handle database connections, providing much better performance than standard ODBC.The latest ROracle enhancements include: Support for Oracle TimesTen In-Memory Database Support for Date-Time using R's POSIXct/POSIXlt data types RAW, BLOB and BFILE data type support Option to specify number of rows per fetch operation Option to prefetch LOB data Break support using Ctrl-C Statement caching support Times Ten 11.2.2 contains enhanced support for analytics workloads and complex queries: Analytic functions: AVG, SUM, COUNT, MAX, MIN, DENSE_RANK, RANK, ROW_NUMBER, FIRST_VALUE and LAST_VALUE Analytic clauses: OVER PARTITION BY and OVER ORDER BY Multidimensional grouping operators: Grouping clauses: GROUP BY CUBE, GROUP BY ROLLUP, GROUP BY GROUPING SETS Grouping functions: GROUP, GROUPING_ID, GROUP_ID WITH clause, which allows repeated references to a named subquery block Aggregate expressions over DISTINCT expressions General expressions that return a character string in the source or a pattern within the LIKE predicate Ability to order nulls first or last in a sort result (NULLS FIRST or NULLS LAST in the ORDER BY clause) Note: Some functionality is only available with Oracle Exalytics, refer to the TimesTen product licensing document for details. Connecting to TimesTen is easy with ROracle. Simply install and load the ROracle package and load the driver. > install.packages("ROracle") > library(ROracle) Loading required package: DBI > drv <- dbDriver("Oracle")Once the ROracle package is installed, create a database connection object and connect to a TimesTen direct driver DSN as the OS user. > conn <- dbConnect(drv, username ="", password="", dbname = "localhost/SampleDb_1122:timesten_direct")You have the option to report the server type - Oracle or TimesTen? > print (paste ("Server type =", dbGetInfo (conn)$serverType)) [1] "Server type = TimesTen IMDB"To create tables in the database using R data frame objects, use the function dbWriteTable. In the following example we write the built-in iris data frame to TimesTen. The iris data set is a small example data set containing 150 rows and 5 columns. We include it here not to highlight performance, but so users can easily run this example in their R session. > dbWriteTable (conn, "IRIS", iris, overwrite=TRUE, ora.number=FALSE) [1] TRUEVerify that the newly created IRIS table is available in the database. To list the available tables and table columns in the database, use dbListTables and dbListFields, respectively. > dbListTables (conn) [1] "IRIS" > dbListFields (conn, "IRIS") [1] "SEPAL.LENGTH" "SEPAL.WIDTH" "PETAL.LENGTH" "PETAL.WIDTH" "SPECIES"To retrieve a summary of the data from the database we need to save the results to a local object. The following call saves the results of the query as a local R object, iris.summary. The ROracle function dbGetQuery is used to execute an arbitrary SQL statement against the database. When connected to TimesTen, the SQL statement is processed completely within main memory for the fastest response time. > iris.summary <- dbGetQuery(conn, 'SELECT SPECIES, AVG ("SEPAL.LENGTH") AS AVG_SLENGTH, AVG ("SEPAL.WIDTH") AS AVG_SWIDTH, AVG ("PETAL.LENGTH") AS AVG_PLENGTH, AVG ("PETAL.WIDTH") AS AVG_PWIDTH FROM IRIS GROUP BY ROLLUP (SPECIES)') > iris.summary SPECIES AVG_SLENGTH AVG_SWIDTH AVG_PLENGTH AVG_PWIDTH 1 setosa 5.006000 3.428000 1.462 0.246000 2 versicolor 5.936000 2.770000 4.260 1.326000 3 virginica 6.588000 2.974000 5.552 2.026000 4 <NA> 5.843333 3.057333 3.758 1.199333Finally, disconnect from the TimesTen Database. > dbCommit (conn) [1] TRUE > dbDisconnect (conn) [1] TRUE We encourage you download Oracle software for evaluation from the Oracle Technology Network. See these links for our software: Times Ten In-Memory Database,  ROracle.  As always, we welcome comments and questions on the TimesTen and  Oracle R technical forums.

Today's guest post comes from Jason Feldhaus, a Consulting Member of Technical Staff in the TimesTen Database organization at Oracle.  He shares with us a sample session using ROracle with the TimesTen...

General

Announcing Oracle TimesTen In-Memory Database 11g Release 2!

Big news!  The new major release of the Oracle TimesTen In-Memory Database is out!   TimesTen 11g Release 2 (aka TimesTen 11.2.2) is now available for download from oracle.com.   Since it first started shipping to customers 14 years ago, TimesTen has been used for performance-critical applications in real-time online transaction processing (OLTP) applications.  The newest release continues that tradition, providing significant scalability and throughput improvements that will enable even faster OLTP applications in the future.   These enhancements to TimesTen's already-blindingly-fast transaction processing capabilities are a perfect match for the Oracle Communications Billing and Revenue Management (BRM) product.  BRM provides advanced billing services for telecommunications service providers ... an area where TimesTen has been used since the beginning.  Oracle BRM now uses TimesTen 11.2.2 ... enabling BRM to provide significantly faster response time and higher throughput ... while simultaneously handling more subscribers than previous releases.   This release of TimesTen also includes significant new features that make it easy to use the performance of TimesTen in business intelligence and analytics applications as well.  Features such as analytic SQL functions and advanced columnar compression are key to TimesTen's role in the new Oracle Exalytics In-Memory Machine.  Exalytics, running Oracle Business Intelligence Foundation Suite and TimesTen, enables profound response time improvements of up to 20x ... enabling "speed of thought" visualization not possible before. Our team has worked hard on TimesTen 11.2.2.  We hope you like it!    Links: Press Release Download Documentation  Oracle Communications Billing and Revenue Management (BRM) Oracle Exalytics In-Memory Machine Oracle Business Intelligence Foundation Suite

Big news!  The new major release of the Oracle TimesTen In-Memory Database is out!   TimesTen 11g Release 2 (aka TimesTen 11.2.2) is now available for download from oracle.com.   Since it first started...

Introduction

Developing Applications using TimesTen

As we've discussed before, the TimesTen In-Memory Database is a fully featured relational database.  Because of its in-memory architecture it provides very fast response time, while providing standard APIs and interfaces as any relational database would. Due to these standard interfaces developing applications that run at in-memory speeds is easy with TimesTen, and leverages skills that developers already have.   Concepts Like any relational database, data in TimesTen is stored in a schema consisting of tables.  Tables contain multiple rows of information; each row is made up of named columns. The basic concepts used in TimesTen - schemas, users, tables, indexes, views, etc. - are compatible with Oracle Database.   TimesTen is a fully transactional database ... work can be committed or rolled back as you expect.  TimesTen is also multi-user, supporting thousands of simultaneous connections to a single TimesTen database.   Users are created and identified with passwords in ways that will be familiar to Oracle Database users, or users of other databases...and access to data is controlled by GRANT and REVOKE in standard ways. The net is that TimesTen is a fully featured relational database, providing the same basic facilities as any other.   SQL TimesTen's SQL dialect is also very compatible with Oracle Database.  Tables are created with CREATE TABLE; industry-standard types such as CHAR and NCHAR are supported, as are Oracle's VARCHAR2 and NUMBER.  Sequences and views are created in the same way they would be in Oracle Database.  SELECT, INSERT, UPDATE, and DELETE are there just the way you expect them.   PL/SQL Most users of Oracle Database are familiar with PL/SQL.  PL/SQL is a procedural language designed to be easy to use alongside SQL. TimesTen supports PL/SQL in many of the same ways that Oracle Database does.  TimesTen supports stored procedures, packages and functions written in PL/SQL, as well as anonymous blocks executed from application programs.   TimesTen's dialect of PL/SQL is quite compatible with Oracle Database - in fact, TimesTen includes the standard PL/SQL compiler and runtime environment from Oracle Database unchanged.   APIs Application Program Interfaces (APIs) are used by application programs to execute SQL and PL/SQL.  JDBC is the standard API used to talk to databases from Java; ODBC and OCI are similar APIs for "C" and C++ languages.  TimesTen supports all three APIs.  It also supports the Pro*C preprocessor for applications that would prefer to use embedded SQL instead of a call-level interface.   And in Windows environments TimesTen supports ADO.NET via the Oracle Data Provider for .NET (ODP.NET).   Tools and Environments Because TimesTen supports standard SQL and standard APIs, it follows that TimesTen also works with a variety of tools built on top of them.  In the Java world, many standard object-relational mapping facilities such as Hibernate and EclipseLink work with TimesTen, as do application server environments such as Oracle Weblogic Server, GlassFish, Websphere, JBoss, and many others.   SQL Developer is a fantastic tool that lets you explore and manage your tables, indexes and data in Oracle Databases and many others.  Out of the box SQL Developer fully supports TimesTen as well.   And to manage your TimesTen installations with Oracle Enterprise Manager (EM), TimesTen provides a plugin which lets EM manage TimesTen databases.   Summary TimesTen supports the most commonly used concepts and facilities from Oracle Database.  Given the amazing capabilities of Oracle Database, not every feature in Oracle Database is supported in TimesTen.  But by providing the most commonly used interfaces from Oracle in a compatible manner, TimesTen is easy to learn and to use. Allowing you to re-use existing concepts, schemas and code can greatly accelerate the development of fast in-memory solutions for your business.   In short, since the same tools, APIs and interfaces are used when developing for TimesTen as for Oracle Database, developing applications to run at in-memory speed with TimesTen is easy for database developers.  Give it a try!   Links Details of TimesTen's SQL are in the TimesTen SQL Reference. Manuals describing how to use TimesTen from C and Java, and with PL/SQL, are on the main TimesTen documentation page.  Information on integrating TimesTen with application servers, SQL Developer, Oracle Enteprise Manager, and other environments is located on the main TimesTen page at OTN.  

As we've discussed before, the TimesTen In-Memory Database is a fully featured relational database.  Because of its in-memory architecture it provides very fast response time, while providing standard...

Introduction

TimesTen and In-Memory Database Cache

Several recent posts I’ve written so far are intended to help explain the basics of what the Oracle TimesTen In-Memory Database is. If you’ve been reading along, you know that TimesTen is a very fast relational database that provides standard interfaces like SQL, PL/SQL, JDBC, ODBC and OCI to access data. TimesTen provides this very fast access to data in large part due to its “in-memory” architecture. TimesTen, unlike most databases, stores all data in RAM. (A copy is also maintained on disk.) Because TimesTen stores data in RAM, the size of a TimesTendatabase is limited by the amount of physical RAM on a machine. On modern 64-bit systems this isn’t much of alimitation! Today (late 2011) systemscan be purchased which have several Terabytes of RAM, and future hardware willnaturally support even more. But even though I really like TimesTen, I have to admit …some databases are bigger than that. J Traditional database systems, like OracleDatabase itself, store data primarily on disk. Oracle Database is obviously a fantastic product, and one of thebenefits it brings is the ability to support very large databases easily … oneslarger than could be cost-effectively handled in memory in many cases. So TimesTen is a fully featured database, and it can be usedon its own to provide data storage for many applications. But in many cases what we really want is thebest of both worlds … the fast memory-centric performance of TimesTen, combinedwith the large capacity and familiarity of the Oracle Database. If only you could have both! Database Caching Enter the “In-Memory Database Cache”. IMDB Cache is TimesTen … with a few extratricks. It lets one or more TimesTendatabases be used “in front” of a back-end Oracle Database to cache “hot”data. There are a number of different ways that IMDB Cache can beused. Just to get across the basicconcept, here’s one particular scenario. Suppose you run a “shopping” website, where customers log on, browseyour catalog of goods, and place orders. All that data is stored in an OracleDatabase. Your application runs in Javaapplication servers in the mid-tier. Alongside the application server of your choice, you run TimesTen on theapplication server. TimesTen isconfigured to cache tables from the Oracle Database. Let’s say user “Scott” logs on to your website. His first click on the site accesses his customerprofile from the local TimeTen database. But Scott hasn’t been to your site forsome time, so his data isn’t present in TimesTen. So under the covers, TimesTen automaticallyfetches Scott’s data from the Oracle Database, storing it in TimesTen forfuture reference. This initial loadoperation can load a number of items from a number of different tables. In this case, we’ll cache Scott’s preferences(such how he likes his web pages to be formatted) as well as his shopping cartcontents and recent order history. So once Scott has logged in to the site, everything aboutScott has been loaded into TimesTen. Hissubsequent clicks on the site are all handled by TimesTen. Queries are handled by TimesTen exclusively;when items are changed – for example, if Scott adds an item to his shoppingcart – those changes are made by the application in TimesTen, whichautomatically reflects them in Oracle Database as well. When Scott logs off from the site, the copy of his datastored in the local TimesTen database can be removed, freeing up RAM to be usedfor other active users. And if Scottsimply goes to lunch, when RAM is needed for other users then Scott’s data canbe automatically reclaimed. In this scenario, the bulk of database workload of your siteis handled in RAM, by the TimesTen database. The TimesTen database automatically interacts with Oracle Database tocache information for active users of the site. Users to your site see the fastest possible response time, and workloadon your Oracle Database is reduced. Since TimesTen’s APIs and interfaces are very compatiblewith Oracle Database, the best part is that your application can manage data ina single relational schema, with a single set of tools and APIs (SQL, PL/SQL,JDBC, etc.). You can re-use code andschema used to manipulate data in the Oracle Database to implement a highperformance in-memory cache … without needing to invent a new data model ordata access library. The cache is itselfa fully featured relational database … TimesTen. As I said, there are many different examples of how TimesTen(er, the IMDB Cache) can be used to work with Oracle Database to providescalable and high performance solutions. We’ll discuss this in more detail in futurearticles, I’m sure! Links Oracle In-Memory Database Cache User’s Guide … Cache Concepts White Paper: UsingIn-Memory Database Cache to Accelerate the Oracle Database:

Several recent posts I’ve written so far are intended to help explain the basics of what the Oracle TimesTen In-Memory Database is. If you’ve been reading along, you know that TimesTen is a very fast...

Introduction

Direct And To The Point

In my previous post I explained how the TimesTen In-MemoryDatabase is a very fast relational database. Like any relational database, TimesTen executes standard SQL viastandard APIs Because of TimesTen’s“In-Memory” architecture, TimesTen can often use fewer CPU cycles thancompeting technologies to achieve the same result … yielding very fast responsetimes. In addition to its use of RAM, TimesTen provides anotherfeature which helps it to provide very fast performance. Relational databases, including TimesTen, let applicationsrunning on many machines access the database. Applications make use of a client library or driver which provides thedatabase API. When the application callsthat API – to execute SQL or fetch a row, for example – the client libraryformats the request from the application into messages, which it then transmitsto the database server. Typically TCP/IPis used for communication. On the database server, a process reads those messages fromthe network and processes them…accessing or modifying data in thedatabase. Any results from the operationare then, in turn, packaged up into messages and sent back to the client fromthe database server. This “client/server” design makes a lot of sense, and nearlyall databases – including TimesTen – support it. However, TimesTen supports anotheralternative as well. Direct Mode TimesTen actually provides two different sets of libraries/driversthat applications can use. Both setsprovide the same APIs – JDBC, ODBC, OCI, Pro*C. One set, the “client/server drivers”, work as explained above. The others, called “direct mode” drivers,work in a more streamlined manner. The TimesTen database engine is included in its direct-modedrivers. When an application calls adirect mode driver to execute SQL or fetch rows the driver doesn’t package thatrequest into messages. Instead, thedriver in turn directly calls the TimesTen database engine, which executes therequest directly. This is done withoutthe overhead of packaging requests and responses into messages, and without theoverhead of transmitting those messages on a network. In fact, since the database engine runs inthe customer’s application process, there isn’t even any context switch or IPC(inter-process communication) overhead! By using direct mode drivers instead of client/serverdrivers, applications can achieve dramatic performance results. SQL can easily be executed in microseconds(millionths of a second) instead of the typical milliseconds or more. When combined with the fast performanceprovided by TimesTen’s in-memory architecture, direct mode applications canachieve levels of performance that are hard to beat. Choices Since both direct mode and client/server mode driversprovide the same APIs and capabilities, applications don’t need to be modifiedto use direct mode. Both modes can beused simultaneously, so some very response time critical applications might usedirect mode while other, less critical applications use client/server. When applications run on the same machine that contains theTimesTen database, they can use either TimesTen’s client/server or direct modedrivers. If the application runs on adifferent machine than TimesTen then the client/server drivers must beused. Even in client/server mode, TimesTen provides very fastperformance. But by eliminating theoverhead of network round-trips and context switches entirely, direct mode canprovide applications with the fastest possible access to data, while stillusing standard SQL, PL/SQL, and APIs. Links Using direct or client/server database connections fromJDBC: http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13068/writing_app.htm#BABJIJHI Using direct or client/server database connections from “C”applications: http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13066/writing_app.htm#CEGGJGDG

In my previous post I explained how the TimesTen In-Memory Database is a very fast relational database. Like any relational database, TimesTen executes standard SQL viastandard APIs Because of...

Introduction

What is TimesTen?

Since this blog is about the Oracle TimesTen In-MemoryDatabase, let’s start by explaining what it is. The product documentation contains an introduction, and the OracleTechnology Network has several overview presentations that get into thedetails. But here’s another quickversion. TimesTen is… A relational database Providing standard APIs and interfaces That runs very fast through its memory-centric architecture It can be used standalone Or in conjunction with an existing Oracle Database To provide extremely fast response time and high throughput Let’s talk about each of those points in more detail. A relational database Like Oracle Database or MySQL, TimesTen is a relationaldatabase. It is used to store andretrieve data from applications. Applications use Structured Query Language (SQL) to access data inTimesTen databases. Transactions allowapplications to safely implement complex operations in a multi-userenvironment, as in any other relational database. TimesTen databases are persistent … datastored in TimesTen is stored on disk and can be made highly available even inthe event of disk or system failure. Standard APIs and interfaces Also like any relational database, TimesTen providesstandard APIs like JDBC (for Java) and ODBC (for “C” / C++). Applications use these APIs to execute SQLstatements against TimesTen databases to store and retrieve data. The APIs and SQL that TimesTen provides are compatible withthe facilities provided by the Oracle Database. Our SQL dialect is compatible with theirs … with datatypes likeVARCHAR2, NUMBER and CLOB. TimesTensupports the PL/SQL language for writing stored procedures. And TimesTen also supports Oracle-specificAPIs and tools such as OCI and Pro*C . Memory Centric Architecture TimesTen was written from the ground up around the idea thatall the data in a TimesTen database resides in RAM – memory. It’s also stored on disk, for persistence …in case the power goes out, for example. But while most databases use RAM as a cache of recently-used data fromdisk, TimesTen stores all data in RAM all the time. It turns out that this simple change in architecture canyield some big benefits … letting us simplify algorithms used under the coversfor searching and storing data. Thatmeans that TimesTen can execute SQL with fewer instructions than manydatabases, resulting in faster response time. Standalone Database… Since TimesTen is a fully featured relational database, itcan be used by itself to store data for applications. Even though it’s called an “in memorydatabase”, data in TimesTen is also stored on disk. TimesTen also supports replication of databetween TimesTen databases, which can be used to provide fully fault tolerantsolutions for even the most demanding 24/7 applications. …or used in conjunction with Oracle Database TimesTen can also be used in conjunction with an OracleDatabase. For example, you might use a TimesTen database residing on yourapplication server to cache data from your Oracle Database. For example, as users log on to your websiteyou might cache their customer profile, shopping cart and order history in theTimesTen database. TimesTen canautomatically work with Oracle Database to move data back and forth as needed. This gives your users the response time benefits of anin-memory solution with the ability to store many terabytes of data of OracleDatabase. The best of both worlds! Extremely fast response time and high throughput By storing data in memory, and letting data be accessed atRAM speeds instead of network speeds, TimesTen can provide extremely fastresponse time… measured in microseconds (millionths of a second), rather thanseconds or milliseconds (thousandths of a second). Useful Links If you’re just learning about TimesTen, this too-shortintroduction probably has left you with more questions than you hadbefore. Check out the resources below for moredetails…and future posts here will dive deeper into some of the topics as well. Overview presentations, customer use cases, data sheets andwhite papers about TimesTen TimesTen documentation on OTN

Since this blog is about the Oracle TimesTen In-Memory Database, let’s start by explaining what it is. The product documentation contains an introduction, and the OracleTechnology Network has several...

Introduction

Welcome to this new blog!

Hi, my name is Sam Drake; I am an Architect here at Oracle. I have worked for the past fifteen years onthe Oracle TimesTen In-Memory Database. In this blog I’ll tell you a little bit about TimesTen … what it is, howit works, and how you can use it. I hope to post a variety of short articles here … some aimedat new users of TimesTen, others for long term users, and some for folks whoare just curious about what it is. Thisblog won’t replace TimesTen’s formal product documentation, or of the existingOracle Technology Network discussion forum about TimesTen. If you would like to keep up on what’s going on withTimesTen, check back here regularly! Byusing our RSS feed you can “subscribe” to it, so you’re automatically notifiedwhen new articles are posted. Sites likeGoogle Reader make this easy.  The best thing about the blog is that it's a two-way channel ... if you have comments or questions about any article, orsuggestions for some topics you’d like to learn more about, please let me know in the article comment section.  You might also considerchecking out the Discussion Forum about TimesTen on the Oracle TechnologyNetwork. These links and other useful links to TimesTen resources are always available in the Bookmarks section of this page. Just for the record, while I am writing here about Oracleproducts, I am not an official Oracle spokesperson … and I am not speaking forOracle. J More coming soon!  ...Sam  Useful Links For your reference, here are some quick links to usefulTimesTen sites. The main TimesTen product page on oracle.com TimesTen downloads on OTN TimesTen documentation on OTN TimesTen Discussion Forum on OTN

Hi, my name is Sam Drake; I am an Architect here at Oracle. I have worked for the past fifteen years onthe Oracle TimesTen In-Memory Database. In this blog I’ll tell you a little bit about TimesTen …...

Oracle

Integrated Cloud Applications & Platform Services