One Row to Rule Them All in Exadata on the cloud or not.

Way back in 2018 (Seems like a life time ago, doesn’t it?) I did a post about retrieving one row from a table you can see it HERE.   The other day I was looking at my blog and saw this post and wondered, what would this test be like on Exadata?  And thanks to Oracle having the super cool always free option in the cloud I can easily test it.  

 

One thing I found out quickly is that in 21c the statistic numbers have changed. (This may have change in 18 I’m not sure.)  The statistic number for “consistent gets” used to be 139 when I did this in my 12 and earlier databases.  It’s now 209.  There are a bunch of new statistics in the consistent gets group.  I believe that the change was made  to keep them all together they were all moved to the numbers of 209 to 222, they used to be 139 to 146.  Several of the new ones are PMEM for persistent memory.  I’ve not dived into that just yet to see all details about these, but just the name gives a decent clue about what those are about.

 

Back to my test.  In the original post I was showing that to get a single row from a table, it’s best to use an index.  A unique index is even better and the best was a unique covering index. 

 

But is that still true in Exadata?  

 

Yes, it is.  

 

Here is the run output.  As I did in the original post, I ran this several times and the results are consistent.   The test code, which is at the bottom of this post, creates a small table and then selects the 10 rows one at a time 100,000 times.  I time it  in both elapsed time and CPU time.  Also, I capture how many consistent gets (LIOs) were done.

 

 

......................................................

selecting all 10 rows without an index 100000 times

......................................................

In hundredths of a second

**** TIME - 4419

**** CPU  - 4214

**** LIO  - 7001404

 

......................................................

selecting all 10 rows with an index 100000 times

......................................................

In hundredths of a second

**** TIME - 968

**** CPU  - 932

**** LIO  - 2000003

......................................................

selecting all 10 rows with a unique index 100000 times

......................................................

In hundredths of a second

**** TIME - 855

**** CPU  - 831

**** LIO  - 2000004

 

......................................................

selecting all 10 rows with a unique covering index 100000 times

......................................................

In hundredths of a second

**** TIME - 824

**** CPU  - 816

**** LIO  - 1000002

 

 

The output didn’t surprise me, I figured it would be the same on Exadata was it was on a non-Exadata configuration, and it sure is good to have it prove out in a test.   

 

The key take away is that if all you want to get from a table is one row, the fastest way is to use and unique index.  If available, a covering index (one that has all the columns you want) is even better, in an Exadata configuration or non-Exadata.

 

What this doesn’t mean is that if you are doing a join on a primary key and joining a significant number of rows from this table to another, that the index is a good thing in Exadata.  A Smart Scan during a full table scan is very likely to be better.  I talk about this in a post HERE.

 

Here is the code I ran to do the above test.

 

rem testing to get one row out of a table.

rem SEP2018  RVD

rem JUL2021  RVD updated for 21c 

rem

set echo on feedback on serveroutput on

drop table iamsmall purge;

create table iamsmall (id_col number, data_col varchar(20));

begin

 for x in 1..10 loop

   insert into iamsmall values (x, (dbms_random.string('A',20)));

 end loop;

end;

/

 

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    --the consistent gets statistic is #139 in 12c and #209 in 21c 

    --select value into l_start_cr from v$mystat where STATISTIC# = 139;

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows without an index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    --select value into l_end_cr from v$mystat where STATISTIC# = 139;

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

rem create a nonunique index on id_col

create index ias_id on iamsmall(id_col);

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows with an index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

rem create a unique index on id_col

drop index ias_id;

create unique index ias_id on iamsmall(id_col);

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows with a unique index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

 

rem create a unique covering index on id_col,data_col

 

drop index ias_id;

create unique index ias_id on iamsmall(id_col, data_col);

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows with a unique covering index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

Exadata SQL Optimization in the cloud or not.

“We just moved our Oracle system to Exadata, I don’t need to optimize my SQL any more, right?”

 

Chances are that a lot of your SQL will run amazingly faster in Exadata then it did on a non-Exadata machine.  But to think that optimization is no longer needed is a bit naïve at best.  It is still very possible to have SQL that runs much slower than it should on an Exadata machine. 

 

Here are a two big things that I’ve seen that can cause a query to run in a less than optimal way on an Exadata machine.  

 

1.  Not getting a Smart Scan (offloading).   

 

The number one way that Exadata speeds up a query is the ability to do this thing called Smart Scan.  This is where some (maybe all) of the predicates are “offloaded” to the storage cells to scan the blocks and only return the blocks that satisfy these predicates to the Buffer Cache in the SGA.  

 

Many predicates can be offloaded, but not all.  Generally, the simpler the predicate the more likely it will be offloaded.   You can get a list of what can be offloaded with this query: 

 

SELECT DISTINCT name, datatype, offloadable

FROM v$sqlfn_metadata

WHERE offloadable = 'YES'

ORDER BY name;

 

In a 21c database there are 394 operations that can be offloaded.  That puts the odds in your favor but there could be things you do that aren’t in this list and that will slow things down, a lot.  

 

An example of something that isn’t offloadable is the function calls used to support case insensitive searching when you set the NLS parameters as follows:

 

NLS_SORT = BINARY_CI   (accent-insensitive and case-insensitive sort)

NLS_COMP = LINGUISTIC (sorting and comparison are based on the linguistic rule specified by 

NLS_SORT)

 

Here is a simple query to show what happens (see the code at the end of this if you’d like to run it yourself).  The table is the set of owners form the DBA_OBJECTS view.  The query is run once with the default setting for the two parameters and then run with them set for case insensitive searching as noted above.  Of interest is the predicate information.  I’m using DBMS_XPLAN.DISPLAY_CURSOR to show the plan. 

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric';

 

With the default settings this returns zero (0) rows since the names are stored in upper case.  The predicate information in the plan looks like this:

 

storage("OWNER"='ric')

filter("OWNER"='ric')

 

Notice the first one is called STORAGE.  This is the one that can be pushed out to the storage cells for the Smart Scan (offloaded).   Depending on how you get the plan this may just say “Access” and not Storage.  If you see the same predicate with Access and Filter on a same step that is showing one that can be offloaded, and the other is used when it isn’t offloaded.  (More about this is a moment.)  

 

With the NLS parameters set for case insensitive searching this is the predicate information:  

 

filter(NLSSORT("OWNER",'nls_sort=''BINARY_CI''')=HEXTORAW('72696300'))

 

This returned 44 for the count, which is correct in my simple test.  The problem is that this predicate cannot be offloaded. The access step in the plan is the same “TABLE ACCESS STORAGE FULL” but it is being applied after the blocks are returned to the buffer cache in the SGA.  As such, this isn’t taking advantage of the Exadata machine. For this test query it hardly mattered as the table only has about 55,000 rows.  But for tables of significate size, not doing offloading will be a major problem. 

 

How to solve this simple example?  Change the predicate to something like this:

 

select /*+ gather_plan_statistics */ count(*) from case_test where lower(owner) = 'ric';

 

The predicates used are below, and the first one can be offloaded (it likely wasn’t in this case since all the blocks of the table were in the buffer cache).  This returned a count of 44:

 

storage(LOWER("OWNER")='ric')

filter(LOWER("OWNER")='ric')

 

When else might something not get offloaded?  A Smart Scan is a run time decision.  You may have done everything correctly to get a Smart Scan, but it doesn’t happen sometimes.  This is because if the storage cells get over loaded, they will stop doing Smart Scans.   From a code point of view there isn’t really anything you can do about this.  

 

At the DBA level they could spread out the data, mostly by getting more storage cells and redistribute the data.  This can avoid contention at the storage cell level.  Also, it can be a timing issue, don’t try to run everything at the same time.  Even just a few minutes (or even seconds) between jobs might make all the difference.  

 

2.  Using Indexes.

 

This one is a bit tricky.  Indexes are still useful in an Exadata environment, and necessary for things like primary keys.  To compound be this problem, you may be running a mixed work load of transactional based queries (that indexes still will be helpful) and data warehouse queries (that indexes don’t work well for).  Knowing which indexes to keep and which to drop is not exactly clear cut. 

 

You will likely need fewer indexes as compared to non-Exadata.   A really big problem with indexes is that they tend to drive NESTED LOOPS joins (potentially large “stacks” of them) and those can be rather inefficient in Exadata with huge tables.   HASH and SORT MERGE joins tend to be better in Exadata.  Both of these tend to favor full table scans over index scans.  A full table scan can benefit from the Smart scan.  Not so much with index scans.   

 

Other then the INDEX FAST FULL scan (which is works much like a full table scan), offloading doesn’t happen with index scans.  Which as just explained, is the real advantage of using Exadata.  

 

Indexes can still be useful when you are getting a small amount of data from a table. But even here it can be tricky.  Testing is the key.  Here is where you may need to do some SQL tricks to get the optimizer to not use an index, two of my favorites are:

 

COALESCE (NVL doesn’t always work)

In Line Views (ILV), need the NO_MERGE hint

 

Using COALESCE the code would look something like this:

 

COALESCE(my_tab.id,-99999) = other_tab.id

 

The reason that NVL doesn’t always work is that the optimizer knows about things like NOT NULL constraints.  For example, if the column you’re trying not to use an index on is a primary key, the optimizer knows it can’t be null so it will remove the NVL function.  But it wouldn’t remove a COALESCE because it could have multiple options. 

 

To use an ILV it would look something like this:

 

JOIN (select /*+ no_merge */ id, name, status from my_tab) my_tab ON my_tab.id = other_tab.id

 

The NO_MERGE is needed because without it, the optimizer will merge it back into the query and it will be just a normal join, and use the index you’re trying to not use.  With an ILV there are no indexes on any column so it has to do a full scan, which is exactly the point of doing this.  It’s really a good idea to only include the columns you use in the query in the ILV.  You want to keep the ILV small, both in width (columns) and depth (rows) if possible.

 

Of course, other approaches would be to make indexes invisible or use hints to force full table scans.  And there are plenty of other “tricks” to use to make an index not useable for query, like add zero to a number or concatenate a NULL to a string.  Whatever technique you do use, I highly recommend that you put in a comment to explain why you put in something odd in your code.  Use the comment to help yourself know why you did something.  It might be months or years later when you look at this code again, so help yourself and add a comment to anything that is non-standard. 

 

Below is the code I used for the example on the case insensitive searching with the NLS parameters.  If you run it on your system, you’ll like want to change the owner.  I suspect you don’t have a user of RIC that owns any objects in your database. 

 

create table case_test 

as (select owner from dba_objects);

set LINESIZE 200

set PAGESIZE 2000

show parameter NLS_SORT

show parameter NLS_COMP

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

 

alter session set NLS_SORT = BINARY_CI;

alter session set NLS_COMP = LINGUISTIC;

show parameter NLS_SORT

show parameter NLS_COMP

 

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

 

alter session reset NLS_SORT;

alter session set NLS_COMP = BINARY;

show parameter NLS_SORT

show parameter NLS_COMP

 

 

select /*+ gather_plan_statistics */ count(*) from case_test where lower(owner) = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/