I’ve written in the past about oddities with descending indexes ( here, here, and here, for example) but I’ve just come across a case where I may have to introduce a descending index that really shouldn’t need to exist. As so often happens it’s at the boundary where two Oracle features collide. I have a table that handles data for a large number of customers, who record a reasonable number of transactions per year, and I have a query that displays the most recent transactions for a customer. Conveniently the table is partitioned by hash on the customer ID, and I have an index that starts with the customer_id and transaction_date columns. So here’s my query or, to be a little more accurate, the client’s query – simplified and camouflaged:

select  /*+ gather_plan_statistics */
        *
from    (
             select
                    v1.*,
                    rownum rn
             from   (
                             select   /*
                                         no_eliminate_oby
                                         index_rs_desc(t1 (customer_id, transaction_date))
                                      */
                                      t1.*
                             from     t1
                             where    customer_id = 50
                             and      transaction_date >= to_date('1900-01-01','yyyy-mm-dd')
                             order by transaction_date DESC
                ) v1
                where  rownum <= 10 -- > comment to avoid WordPress format issue
         )
where    rn >= 1
;

You’ll notice some hinting – the /*+ gather_plan_statistics */ will allow me to report the rowsource execution stats when I pull the plan from memory, and the hints in the inline view (which I’ve commented out in the above) will force a particular execution plan – walking through the index on (company_id, transaction_date) in descending order.

If I create t1 as a simple (non-partitioned) heap table I get the following plan unhinted (I’ve had to edit a “less than or equal to” symbol to avoid a WordPress format issue):

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |      1 |        |    14 (100)|     10 |00:00:00.01 |      14 |
|*  1 |  VIEW                           |       |      1 |     10 |    14   (0)|     10 |00:00:00.01 |      14 |
|*  2 |   COUNT STOPKEY                 |       |      1 |        |            |     10 |00:00:00.01 |      14 |
|   3 |    VIEW                         |       |      1 |     10 |    14   (0)|     10 |00:00:00.01 |      14 |
|   4 |     TABLE ACCESS BY INDEX ROWID | T1    |      1 |    340 |    14   (0)|     10 |00:00:00.01 |      14 |
|*  5 |      INDEX RANGE SCAN DESCENDING| T1_I1 |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM .LE. 10)
   5 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE" IS NOT NULL AND "TRANSACTION_DATE">=TO_DATE('
              1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Notice the descending range scan of the index – just as I wanted it – the minimal number of buffer visits, and only 10 rows (and rowids) examined from the table. But what happens if I recreate t1 as a hash-partitioned table with local index – here’s the new plan, again without hinting the SQL:

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |       |      1 |        |   207 (100)|     10 |00:00:00.01 |     138 |       |       |          |
|*  1 |  VIEW                                          |       |      1 |     10 |   207   (1)|     10 |00:00:00.01 |     138 |       |       |          |
|*  2 |   COUNT STOPKEY                                |       |      1 |        |            |     10 |00:00:00.01 |     138 |       |       |          |
|   3 |    VIEW                                        |       |      1 |    340 |   207   (1)|     10 |00:00:00.01 |     138 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY                      |       |      1 |    340 |   207   (1)|     10 |00:00:00.01 |     138 |  2048 |  2048 | 2048  (0)|
|   5 |      PARTITION HASH SINGLE                     |       |      1 |    340 |   206   (0)|    340 |00:00:00.01 |     138 |       |       |          |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |      1 |    340 |   206   (0)|    340 |00:00:00.01 |     138 |       |       |          |
|*  7 |        INDEX RANGE SCAN                        | T1_I1 |      1 |    340 |     4   (0)|    340 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM. LE. 10)
   4 - filter(ROWNUM .LE. 10)
   7 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE">=TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRANSACTION_DATE" IS NOT NULL)

Even though the optimizer has recognised that is will be visiting a single partition through a local index it has not chosen a descending index range scan, though it has used the appropriate index; so it’s fetched all the relevant rows from the table in the wrong order then sorted them discarding all but the top 10. We’ve done 138 buffer visits (which would turn into disk I/Os, and far more of them, in the production system).

Does this mean that the optimizer can’t use the descending index when the table is partitioned – or that somehow the costing has gone wrong. Here’s plan with the hints in place to see what happens when we demand a descending range scan:

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |   207 (100)|     10 |00:00:00.01 |       8 |
|*  1 |  VIEW                                 |       |      1 |     10 |   207   (1)|     10 |00:00:00.01 |       8 |
|*  2 |   COUNT STOPKEY                       |       |      1 |        |            |     10 |00:00:00.01 |       8 |
|   3 |    VIEW                               |       |      1 |    340 |   207   (1)|     10 |00:00:00.01 |       8 |
|   4 |     PARTITION HASH SINGLE             |       |      1 |    340 |   206   (0)|     10 |00:00:00.01 |       8 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| T1    |      1 |    340 |   206   (0)|     10 |00:00:00.01 |       8 |
|*  6 |       INDEX RANGE SCAN DESCENDING     | T1_I1 |      1 |    340 |     4   (0)|     16 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=1)
   2 - filter(ROWNUM .LE. 10)
   6 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE" IS NOT NULL AND "TRANSACTION_DATE">=TO_DATE('
              1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

The optimizer is happy to oblige with the descending range scan – we can see that we’ve visited only 8 buffers, and fetched only 10 rows from the table. The cost, however, hasn’t made any allowance for the limited range scan. Check back to the plan for the simple (non-partitioned) table and you’ll see that the optimizer did allow for the reduced range scan. So the problem here is a costing one – we have to hint the index range scan if we want Oracle limit the work it does.

You might notice, by the way that the number of rowids returned in the index range scan descending operation is 16 rather than 10 – a little variation that didn’t show up when the table wasn’t partitioned. I don’t know why this happened, but when I changed the requirement to 20 rows the range scan returned 31 rowids, when I changed it to 34 rows the range scan returned 46 rows, and a request for 47 rows returned 61 index rowids – you can see the pattern, the number of rowids returned by the index range scan seems to be 1 + 15*N.

Footnote:

If you want to avoid hinting the code (or adding an SQL patch) you need only re-create the index with the transaction_date column declared as descending (“desc”), at which point the optimizer automatically chooses the correct strategy and the run-time engine returns exactly 10 rowids and doesn’t need to do any sorting. But who wants to create a descending index when they don’t really need it !

If you want to reproduce the experiments, here’s the script to create my test data.

rem
rem     Script:         pt_ind_desc_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2018
rem     Purpose:        
rem
rem     Last tested 
rem             18.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table t1 (
        customer_id,
        transaction_date,
        small_vc,
        padding 
)
partition by hash(customer_id) partitions 4
nologging
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        mod(rownum,128)                         customer_id,
        (trunc(sysdate) - 1e6) + rownum         transaction_date,
        lpad(rownum,10,'0')                     v1,
        lpad('x',100,'x')                       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

create index t1_i1 on t1(customer_id, transaction_date) 
local 
nologging
;

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1'
        );
end;
/

I’ve run this test on 12.1.0.2, 12.2.0.1, and 18.3.0.0 – the behaviour is the same in all three versions.

Update (1st Feb 2019)

As the client reminded me after reading the post, it’s worth pointing out that for more complex SQL you still have to worry about the errors in the cardinality and cost calculations that could easily push the optimizer into the wrong join order and/or join method – whether you choose to hint the ascending index or create a descending index.  Getting the plan you want for this type of “pagination” query can be a messy process.

I’m very excited to announce I’ll be running my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar in conjunction with the German Oracle User Group (DOAG) in Berlin, Germany on 8-9 May 2019. The venue will be the DOAG Offices in Berlin (DOAG Office, 6th Floor, Tempelhofer Weg 64, 12347 Berlin). Because of venue […]

Oracle is very well instrumented, for decades, from a time where measuring the memory in bytes was ok. But today, we spend a lot of time converting bytes in KB, GB, TB to read it easily. I would love to see a Human-Readable format for TO_CHAR, but there’s not. Here is a workaround without having to create a new function.

DBMS_XPLAN does that when displaying execution plans and we can access the functions it uses internally. The metrics can be numbers, and then the Kilo, Mega, Giga applies to powers of 1000. Or they can be a size in bytes, and we prefer the powers of 1024. Or they can be a time in seconds, and then we use a base 60. And then we have 3 sets of functions:

• FORMAT_SIZE for base 2 numbers where we use powers of 1024 

I have been talking about Vagrant for a long time and use it extensively on my Ubuntu-powered laptop. I am using Oracle Linux 7.6 for most of my lab builds, and I like to have specific tools such as collectl, perf, and many others available when the VM boots. I als like to stay in control of things, especially when it comes to downloading otherwise unknown things from the Internet I decided to learn how to create a Vagrant box myself.

Using Vagrant with my custom images, all I need to do is run a single command and it will spin up a clean VM using the VirtualBox provider with the exact software configuration I want. I can also supply so-called provisioners to further configure my environment. I found this particularly useful when writing and testing Ansible scripts. Sometimes I just wanted to go back to my starting point but that can be tricky at times: imagine you just partitioned your block devices for use with the database and discovered you wanted to change the flow. Getting back to unpartitioned, unformatted block devices is possible, but I don’t think it’s terribly elegant. Plus I have to manually do it, and I prefer the Ansible approach.

Building a base box

The Vagrant documentation is pretty good, so this is mostly pulling together information from 2 sources: The starting point I used was Creating a Base Box with specifics for the VirtualBox driver I’m using. I don’t claim I’m an expert in this field.

Running Vagrant VMs can be inherently insecure as you will see in a bit. It’s fine for me because I’m creating/trashing short-lived VMs on a regular basis and all I do is play around with them whilst they remain perfectly isolated from the rest of the world. If you are ok with this limitation feel free to read on, otherwise please refrain from following the steps in this blog post.

The overall process isn’t too hard to follow:

• Create your gold image
  • Install the Operating System in VirtualBox
  • Install/upgrade any software you want to have available
  • Configure the system for Vagrant specifics
• Create a base box off your gold image
• Add the box to your environment
• Start the VM and enjoy

Creating the VM and installing the Operating System

The first step obviously is to create the VM and install the operating system. For quite some time now I’m creating a VM with sufficient RAM and a couple of block devices: the first one is used as the root volume group, the second block device will be used for Oracle. Plenty of articles have been written about installing Oracle Linux on VirtualBox, I won’t write the 42nd variation here ;)

There are only a few things to pay attention to. These can all be found in the documentation I referenced earlier. First of all, please ensure that your network adaptor uses NAT. You can use port forwarding to access a NAT device in VirtualBox (configured later). The documentation furthermore recommends removing any necessary components such as USB and audio from the VM. I have used a strong password for “root” as I have no intention at all of sharing my VM. Apply security hardening at this stage.

A common error is not to enable the network device to start up automatically when the system boots. Vagrant uses port-forwarding to the NAT device and SSH keys to authenticate, there doesn’t appear to be a mechanism circumventing the network stack. With the network interface down it’s quite hard to connect via SSH.

Install/upgrade software

Once the operating system is installed and the VM rebooted, it’s time to configure it for your needs. I usually end up completing the pre-requisites for an Oracle database installation. This, too, has been covered so many times that I don’t feel like adding value by telling you how to complete the steps.

Configure the system for Vagrant

At this stage your VM should be properly configured for whichever purpose you have in mind. All that remains now is the addition of the specific configuration for Vagrant. There are a few steps to this, all to be completed on the guest.

Install VirtualBox Guest Additions

Vagrant offers the option of mounting a file system from your host on the guest VM. I very much like this feature, which is enabled by default. Please refer to the Vagrant documentation for security implications of sharing file systems between guest and host.

As with every VirtualBox VM, shared folders won’t work without installing the guest additions though so that’s what I do next. This is pretty straight forward and for Oracle Linux 7 generally speaking requires tar, bzip2, gcc and kernel-uek-devel matching your current kernel-uek. If you just completed a “yum upgrade” and your kernel was upgraded you need to reboot first. After VBoxLinuxAdditions.run has completed successfully (I am using VirtualBox 5.2.x) it’s time to move on to the next step.

Add a Vagrant user

Vagrant expects a user named vagrant to be present on the VM. It uses SSH-keys when connecting to the VM. The documentation mentions a so-called insecure key-pair I decided not to use. Instead, I created my own key pair for use with the machine and added it to ~/.ssh/authorized_keys in the vagrant user’s home directory. It is a new keypair I created on the host specifically for use with Vagrant. If you are on MacOS or Linux it’s convenient to add it to the SSH agent (ssh-add …). There are similar tools for Windows users.

Creating the user is easy and should be completed now unless you already created the user during the initial installation:

# useradd -c 'vagrant user' -m -s $(which bash) vagrant 

The user should have passwordless sudo enabled as well as per the documentation. It is also recommended by the Vagrant documentation to assign a weak password to the vagrant account, which I didn’t. I never ran the passwd command to set a password for the vagrant user and so far seem to be doing ok.

Create a base box

This concludes the preparations on the VM side. Next up you need to create the base box, which you can then refer to in your own Vagrantfile. The command to do so is just one line. Be careful though: it will create a compressed file named package.box in your current working directory. This file can be rather large, so make sure you have enough space to store it.

$ vagrant package --base 

Depending on how powerful your laptop is this can take a little while.

Add the box to your environment

The previous command will complete eventually. This is the moment where you add the box to Vagrant’s local inventory as shown here:

$ vagrant box add --name blogpost /home/martin/package.box 

This command shouldn’t take too long to complete. If you see a line “box: successfully added box ‘blogpost’ (v0) for ‘virtualbox’ you are good. You can assign any name to the box you add, it will alter on show up under that designation when you run “vagrant box list”

Start the VM and enjoy

The remaining tasks are identical to using Vagrant boxes off their repository. Start off by vagrant init and make all the changes you normally do to the Vagrantfile. As I’m using my own SSH key I have to make sure that I’m telling Vagrant where to find it using a configuration option:

config.ssh.private_key_path = "/path/to/ssh/keyfile" 

Once you start the VM using “vagrant up” you are good to go!