Inserting 1 Billion Rows in an Oracle Autonomous Database

 Oracle’s Autonomous Database recently scored highest in all four Use Cases in the 2020 Gartner “Critical Capabilities for Cloud Database Management Systems for Operational Use Cases” report. Oracle’s fully cloud native Autonomous Database comes in three flavours — Autonomous Transaction Processing (ATP), Autonomous Datawarehouse (ADW) & Autonomous JSON Database (AJD). With ATP being the database of choice for transactional OLTP like systems.


https://medium.com/oracledevs/inserting-1-billion-rows-in-an-oracle-autonomous-database-f9519902c9db

In this blog post we will stress test the Transaction Processing Autonomous instance using a PL/SQL loop of inserting 1 billion rows and selecting records from all_objects system table. Our test will be inserting about half a terabyte of transactional data to the lowest possible shape for Autonomous database. Even with such low specs the instance was hardly under any stress and blazed through the workload. So lets get started…

Setup

  1. First create an Autonomous Transaction Processing Database from the OCI console.

With the Always Free tier you are eligible to create one free Transaction processing and one free Data warehouse instance with 20G of storage.

2. Download the ATP Instance’s Wallet from “DB Connection” Tab

3. Download and Install SQLCL on your client machine or cloud instance from where you will run this test

4. Copy the Wallet file and connect to your Autonomous Database

5. Create the Test Table in the ATP instance.

I’m using the default ADMIN schema for my test but it is advisable to create a new schema and run it from there.

6. Run the PL/SQL block to Insert 1 Billion rows

Make sure to enter 1000000000 when prompted twice

Wait for the execution of the job to finish. It can take sometime depending on your instance size. In my case it took about 40mins to complete. So if your session might close, it will be better to save the PL/SQL block as a .sql file; hardcode l_rows and rownum and then run it in nohup mode.

Results

With just 1 ocpu and 1TB of storage it blazed through the workload. Overall it took 36mins to finish the select and insert for 1 billion rows.

This was just a quick and simple test to check the capabilities of the smallest possible ATP shape. Below is the SQL Monitoring report from the Autonomous DB Performance Hub.

SQL Monitoring Report

Just look at these numbers :

Rows Processed : 1,000M (1B)

Time to Complete : 36mins

IO Requests : 444K

IO Bytes : 458GB

Conclusion

The Oracle Autonomous Database has been named a Leader in 2020 for Gartner Magic Quadrant for Cloud Database Management Systems for a reason. Even with a quick test we can see the amount of performance which can be drawn from it. It is a visionary piece of technology which will change the landscape of Databases in the coming years for both Enterprises and SMEs.

Comments

Post a Comment

Popular posts from this blog

Easy Text-to-Speech with Python

Image
Text-to-speech (TTS) technology reads aloud digital text. It can take words on computers, smartphones, tablets and convert them into audio. Also, all kinds of text files can be read aloud, including Word, pages document, online web pages can be read aloud. TTS can help kids who struggle with reading. Many tools and apps are available to convert text into speech. Python comes with a lot of handy and easily accessible libraries and we’re going to look at how we can deliver text-to-speech with Python in this article. Source:  https://www.youtube.com/watch?v=eiP-12qHM-c Different API’s are available in Python in order to convert text to speech. One of Such API’s is the Google Text to Speech commonly known as the gTTS API. It is very easy to use the library which converts the text entered, into an audio file which can be saved as a mp3 file. It supports several languages and the speech can be delivered in any one of the two available audio speeds, fast or slow. More detai
Read more

Flutter for Single-Page Scrollable Websites with Navigator 2.0

Image
  Single page scrollable websites are everywhere. In this website design pattern, all the content is presented in one long-scrolling layout that contains multiple sections. Visitors can scroll or jump to a section by clicking buttons of a sticky menu. This pattern is a good fit for small content such as brochure websites, software library documentation, portfolios, and landing pages that are used to convert users [ 1 ]. Designers also love this pattern because it is simple, clean, and enables cool scroll animations. Jetbrains Mono single-page scrollable website Hive docs multi-page scrollable website In this series of articles, we will explore how to build a  single-page scrollable website  using Flutter. We will benefit from the Navigator 2.0 API to provide a good navigation experience to the users. You can find the source code of this series’ sample apps on my  Github page . The implementation details of the sample apps will be explained in the following articles: Part 2: Scroll to P
Read more

Better File Storage in Oracle Cloud

Image
Use Oracle Application Express with Oracle Cloud Infrastructure REST APIs to upload, download, and manage big files in Oracle Cloud. By Adrian Png November 27, 2019 Oracle Cloud Infrastructure  provides a comprehensive suite of REST APIs for accessing and managing cloud resources. For example, administrators can use REST APIs to create and manage autonomous databases and initiate backups. Oracle Application Express (Oracle APEX)  makes working with REST easy. The  APEX_WEB_SERVICE  package, available since Oracle APEX 4.0, provides developers with a function for consuming REST APIs. Oracle APEX 5.0 introduced the  APEX_JSON  package, allowing easy parsing of JSON responses. Oracle APEX 18.1 introduced the Web Source Module (WSM), which enables developers to access REST services and use the data in APEX components such as reports, interactive reports, and interactive grids. These features, when used together with Oracle Cloud Infrastructure REST APIs, enable APEX developers
Read more