Introduction

This blog describes a fun competiton for Oracle Database performance tuning focused on minimizing the SQL response time (ie latency).

• Use your Oracle expertise to tune the SQL queries
• You can choose from three different environments on Oracle Cloud
• As long as you follow the rules, you have a lot of flexibility to tune the SQL, Oracle Database and Orace Linux kernel
• Given a fixed set of tables, data and queries learn
  • Your current level of performance tuning expertise
  • The top performers in the different categories
  • The techniques use by the top performers

The idea is for you to tune a known system and submit your results.  The results and techniques used by the fastest solutions will be published.  This enables everybody to learn from the most effective techniques.  There are multiple different ways to be a winner in this competition.

The SQL statements to tune include

• I created 42 queries derived from the above SQL clauses which use the Oracle Order Entry demo schema and data

Order Entry Data Model

• The Oracle Order Entry demo schema is used for this SQL tuning exercise
• The OE schema is familiar to many Oracle database users as it has been used as a demo schema for over 20 years
• The OE schema is simple and enables many types of table joins

Oracle Order Entry Row Counts

• There are a minimal number rows in each of the tables which makes the OE schema cost effective easy to use on low end hardware
• There is about 2 MB of data in these order entry tables

Oracle Cloud Environments for this SQL tuning challenge

• You can choose from three environments to compete in this SQL tuning challenge
  • You can use a free VM in the Oracle LiveLabs Sandbox
  • You can create a VM for the Oracle LiveLab in your OCI tenancy
  • Or, you can enter the Open Class, where you can choose to use any Oracle Database Service, or install/configure an Oracle Database on your favorite OCI resources

LiveLabs vs Open Class

• The LiveLabs provide everything you need with step by step instructions
  • The LiveLabs are two known environments where everybody can compete with the same hardware/software
• The Open Class enables Oracle experts to achieve the maximum possible performance for their desired configuration 
  • It is your responsibility to provision, configure and tune your Oracle Database in the Open Class

What are the Prizes

• There are six different challenges, each with their own prizes
  • Two for the LiveLab Sandbox
  • Two for the LiveLab run in your tenancy
  • Two for the Open Class run in your tenancy
• The prizes for each challenge are bragging rights and public recognition of your Oracle Performance tuning expertise
  • You can choose to provide your name, photo and/or social media details in the submission
  • I will only publish the details that your are comfortable to share
• Everybody can benefit from this SQL tuning competition
  • You can try different techniques to see which are the most effective
  • You can learn from the techniques used by the people with the top results
  • You can try those top techniques yourself
  • The best results may come from a combination of Oracle features, database tuning, SQL tuning and Linux kernel tuning

LiveLab Sandbox Tuning Challenges and Prizes

• You must use
  • The free LiveLab Sandbox Virtual Machine in Oracle Cloud using the included official Oracle 19c Docker image
  • The LiveLab Sandbox VM is a VM.Standard.E4.Flex with 4 OCPU, 64 GB RAM and 53 GB block storage using Oracle Linux 8.6
  • This is a known / free configuration that all participants can use
• The prizes
  • I will publish the names and results of the top 3 people for the minimum total response time for all of the queries 
  • I will publish the names and results for the top 3 people who got the fastest speed up of any individual query 

LiveLab Tuning Challenges and Prizes

• You must use
  • A LiveLab virtual machine in your Oracle Cloud tenancy using the included official Oracle 19c Docker image
  • The LiveLab VM is a VM.Standard.E4.Flex with 4 OCPU, 64 GB RAM and 100 GB block storage using Oracle Linux 8.6
  • This is a known / cheap configuration that all participants can use
• The prizes
  • I will publish the names and results of the top 3 people for the minimum total response time for all of the queries 
  • I will publish the names and results for the top 3 people who got the fastest speed up of any individual query 

Open Class Tuning Challenges and Prizes

• You can use any supported Oracle Cloud compute shape with any supported Oracle Database or Database Service
  • This is an open class where you can use your favourite hardware/software configuration
  • You must use Oracle Linux 7.9 or 8.x with x86-64 CPUs
• The prizes
  • I will publish the names and results of the top 3 people for the minimum total response time for all of the queries 
  • I will publish the names and results for the top 3 people who got the fastest speed up of an individual query 

The Rules

• Constraints
  • You cannot change the logical structure [columns, types and constraints] of the tables in the Oracle Order Entry demo schema
  • You cannot change the data or the number of rows in the Oracle Order Entry demo schema tables
  • You cannot change the SQL queries
• Variables
  • You can use SQL hints 
  • You can use indexes
  • You can use any supported Oracle Database features
  • You can change the Oracle initialization parameter file
  • You can change the Oracle Linux kernel parameters
• Required
  • You must use the provided queryTimer OCI C program to accurately measure the execute and fetch time of each query
  • The results must be repeatable
    • I should be able to re-create your configuration on Oracle Cloud and be able to get similar performance results
    • The latency of a SQL query will have variances between each run. These latencies will tend to have a normal-ish distribution 
    • The queryTimer program allows to you define the number of times that the query is run and will give you the average latency in microseconds
    • If you cannot repeat your results, then I probably cannot either
    • Results that I can reproduce are valid [within 10% of your latency for any query]
    • Results that I cannot reproduce will not be considered
  • You must document all of the tuning changes that you made [OS, database and SQL]
  • You must email the results and tuning changes to douglas.hood@oracle.com for judging [subject: SQL tuning Challenge #1]
    • I want to get three winners for each of the six challenges
    • I want everybody to learn something about Oracle Database performance tuning for this excercise
  • You must state your configuation, eg
    • Oracle LiveLab SandBox, Oracle LiveLab, or Open Class
    • For the Open Class, define the Oracle Cloud configuration, eg
      • The Database Service and its configuration
      • or the custom compute shape with the Oracle Database that you installed
        • You need to define
          • The compute shape
          • The memory
          • The storage configuration and capacity
          • The Oracle Database version
          • The Oracle Database features used
          • The Oracle pfile
          • Any SQL hints used
          • Any SQL tuning at the table level
  • For the LiveLabs and LiveLabs Sandbox configurations, you must publish three sets of results for each of the queries
    • The default / untuned results for your Oracle 19c Database
    • Your tuned results for the Oracle 19c Database
    • The results for the TimesTen 22c Cache
  • For an Open Class configuration, you must publish two sets of results for each of the queries
    • The default / untuned results for your Oracle Database
    • Your tuned results for the Oracle Database
  • To submit an Open Class configuration, you first must submit your results for the LiveLabs configuration run in your tenancy
  • You can submit up to three sets of results
    • You can submit one set of results for the LiveLabs Sandbox
    • You can submit one set of results for the LiveLabs run in your tenancy
    • You can submit one set of results for the Open Class
  • You must submit your results by Feb 15th, 2023
• Definitions 
  • Total response time for all of the queries 
    • The sum of the latencies from queryTimer for query_1.sql … query_42.sql using -count=1000
      • eg if all of your baseline queries took a total of 415,435 microseconds [ie 0.415 seconds]
      • You want the sum of your tuned SQL queries to be much less than 415,435 microseconds
  • Speed-up of a query
    • The ratio of your baseline latency divided by the latency of your tuned latency for a single SQL query
    • eg if query_17.sql took 40,958 microseconds for your baseline and 24,880 microseconds after tuning then
      • 40958 / 24880 = 1.65
      • You want to maximize the speed-up [ratio] for your tuned queries

The SQL Queries to Tune

The 42 SQL queries are defined in the LiveLab VM in the tthost1 container in the /tt/livelab/extras directory.

The following are four examples of those queries.

query_7.sql

SELECT
    cust_last_name,
    order_mode,
    sum(order_total)
FROM
  (SELECT
      c.cust_last_name,
      order_mode,
      order_total
    FROM
      customers c left outer join orders o on
      c.customer_id = o.customer_id
    WHERE
      order_mode = 'direct'
      and order_total > 40000
  )
GROUP BY
   cust_last_name,
   order_mode
HAVING
   sum(order_total) is not null
ORDER BY 1, 2;

query_19.sql

SELECT
    o.order_id,
    oi.line_item_id,
    oi.product_id,
    count(oi.unit_price)
FROM
    customers c,
    orders o,
    order_items oi,
    product_information pi
WHERE
    c.customer_id = o.customer_id
    and o.order_id = oi.order_id
    and oi.product_id = pi.product_id
GROUP BY
  cube(o.order_id, oi.line_item_id, oi.product_id)
ORDER BY 1, 2;

query_31.sql

Select
  product_id,
  product_name,
  sum(weight_class) OVER (PARTITION BY product_id ORDER BY list_price) as t1,
  sum(list_price) OVER (PARTITION BY supplier_id ORDER BY catalog_url) as t2,
  sum(min_price) OVER (PARTITION BY product_id ORDER BY list_price) as t3
FROM
  product_information
ORDER BY
  1, 2;

query_42.sql

SELECT
    oi.product_id,
    pi.product_name,
    pi.supplier_id
  FROM
    order_items oi,
    product_information pi
WHERE
    oi.order_id in (
      select
         o.order_id
      from
        orders o
      where
        o.customer_id in (
          select
            customer_id
          from
            customers c
          where
            c.credit_limit in (500, 600, 700, 3500)
            or city in ('Paris', 'London', 'Bangalore', 'Roma', 'Firenze', 'Des Moines')
        )
        and order_total > 5000
    )
    and pi.product_id = oi.product_id
    and pi.product_id in (
      select
        product_id
      from
        inventories
      where
        quantity_on_hand > 200
        or product_id in (3004, 3072, 3086, 3091, 3139, 3246, 3247, 3255, 3301, 3334, 3391, 3502, 3511)
        or WAREHOUSE_ID between 2 and 4
    )
ORDER BY 1, 2;

Using the Oracle TimesTen Cache LiveLab

The TimesTen Cache LiveLab is a VM which runs on Oracle Cloud.  The VM uses Docker containers to run the Oracle Database 19c and TimesTen 22c Cache.

• You can use either the NoVNC Linux desktop, or ssh to the Sandbox VM
• I find it easier to cut-n-paste commands to an ssh window than using the clipboard feature of the NoVNC environment 
• You can use sftp to copy files to/from the VM
• You can ssh into the tthost1 container from the VM
• You can use sftp to copy files to/from the tthost1 container from the VM
• You can use yum to install packages in your LiveLab VM and containers

The Oracle TimesTen Cache LiveLab to use for this tuning challenge is here.

• This link is used for both the LiveLab Sandbox and for the LiveLab VM running in your tenancy
• If you are using the LiveLab SandBox, then you MUST complete Labs 1 to 9 to correctly setup the environment and data for this tuning challenge
• If you are using the LiveLab VM, then you MUST complete Labs 1 to 12 to correctly setup the environment and data for this tuning challenge
  • If you really mess up, you can use the /home/oracle/lab/labReset.sh script to start again
• By default, each free LiveLab Sandbox is available for 2.5 hours.  You should be able to complete this tuning excercise in that time. You can extend your Sandbox reservation if needed
• The LiveLab VM run in your tenancy is available for as long as you want to keep it

Once you have completed the above labs, you also need to do the following to enable the performance tuning challenge:

• From within a ssh session in tthost1, grant the privileges for creating views and materialized views to the OE user in both the Oracle 19c database and TimesTen 22c Cache
• Create the view & materialized view as user OE in both the Oracle Database 19c and TimesTen 22c Cache
• Create the indexes for TimesTen 22c Cache
• Updated the optimizer statistics for both the Oracle 19c database and TimesTen 22c Cache

Oracle grants

TimesTen Grants

Create Oracle views and update optimizer statistics

Create TimesTen views, indexes and update optimizer statistics

Using the queryTimer executable

To get reproducible results you need to run the SQL queries many times and get the average response time.  An executable utility called queryTimer is provided in the LiveLab Sandbox and LiveLab VM to make this simple and fair:

• This performance tuning challenge must use the queryTimer executable for all SQL queries
• queryTimer is written in C using the Oracle Call Interface for optimal performance
• The same queryTimer executable works with both Oracle 19c and TimesTen 22c Cache
  • You just need to specify a different tnsnames.ora service name
  • Use orclpdb1 for the Oracle 19c PDB service name and sampledb for the TimesTen 22c Cache service name
• The source code for queryTimer executable is in the LiveLab VM

  • /home/oracle/lab/src/queryTimer.c

To run query_1.sql and get the average response time for 1000 iterations ( execute + fetch all rows) against Oracle 19c in the LiveLab:

…

To run query_42.sql and get the average response time for 1000 iterations ( execute + fetch all rows) against Oracle 19c in the LiveLab:

To run query_1.sql and get the average response time for 1000 iterations ( execute + fetch all rows) against Oracle TimesTen 22c Cache in the LiveLab:

…

To run query_42.sql and get the average response time for 1000 iterations ( execute + fetch all rows) against Oracle TimesTen 22c Cache in the LiveLab:

My Baseline LiveLab Results

• All of the results are in microseconds
• Oracle Database 19c is pretty fast without any tuning. All 42 queries run in under a second!
  • All of the 42 queries run in 0.450 seconds in the free LiveLab Sandbox VM (VM.Standard.E4.Flex with 4 OCPU and 64 GB RAM)
  • All of the 42 queries run in 0.406 seconds in my tenancy with a LiveLabs VM (VM.Standard.E4.Flex with 4 OCPU and 64 GB RAM)
• The Oracle 19c Enterprise Edition feature, Application Tier Database Cache [TimesTen 22c Cache] can help speed-up your queries 
  • Using the same VM with the same tables, data and SQL, TimesTen 22c Cache was about 10x faster than Oracle Database 19c
• Your challenge is to tune the SQL, database and/or Linux kernel to improve upon the un-tuned Oracle 19c query results
  • Your un-tuned Oracle 19c results will be similar but different to my baseline results for the LiveLab Sandbox and LiveLab
  • You want to minimize the latency of your tuned queries for the Oracle 19c database
  • You are also trying to maximize the speed-up of un-tuned vs tuned queries for the Oracle 19c database

How to submit your results

• Create a spreadsheet to record your results
• For the LiveLabs Sandbox and LiveLabs, you need four columns in the spreadsheet
  • Query
  • Un-tuned Oracle 19c
  • Tuned Oracle 19c
  • TimesTen 22c Cache
• For the Open Class, you need three columns in the spreadsheet
  • Query
  • Un-tuned Oracle Database
  • Tuned Oracle Database
• All performance tuning changes to the Oracle Database, tables, queries and Linux kernel need to be recorded 
  • Include these changes in the email
  • These changes need to be clear enough so that any Oracle DBA could reproduce the changes in their Oracle database environment
• Email your results and tuning changes to douglas.hood@oracle.com

Summary

• This SQL tuning challenge is just for fun
• You should increase your Oracle Database performance tuning knowledge by doing these challenges
• The details of the top three results for each of the six challenges will be published
• We can all learn from the techniques used by the top results
• You can find out how you rate against the top three results in each of the challenges

Learn more about TimesTen XE:

• Oracle TimesTen XE Home Page
• Oracle TimesTen XE Download
• Oracle TimesTen XE Docker Container
• Oracle TimesTen Classic Home Page
• Oracle TimesTen Scaleout Home Page
• Oracle TimesTen VM with Hands On Labs
• Oracle TimesTen Documentation

More TimesTen XE Blogs

• An introduction to TimesTen XE
• How fast is TimesTen XE
• How to create a database on TimesTen XE
• TimesTen XE SQL
• TimesTen XE SQL Profiles
• Using TimesTen XE on WSL
• Using client/server without config files on TimesTen XE
• Using client/server with config files on TimesTen XE

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.