System Statistics on Exadata and its impact on Optimizer cost

In this post I will discuss about the Exadata mode system statistics and how this impacts the cost of a full table scan.
Let's get started with the test case. I'm running this test case on an Exadata machine with database version 18.1

SQL> create table T (ID number) segment creation immediate ;

I don't want to insert a lot of rows, so I just fudge the statistics.

SQL> exec dbms_stats.set_table_stats(user,'T',numrows=>50000,numblks=>50000);

Optimizer now believes that this table has 50000 rows and 50000 blocks.

I currently  have NOWORKLOAD statistics 

FLAGS	0
CPUSPEEDNW	3326
IOSEEKTIM	10
IOTFRSPEED	4096
SREADTIM
MREADTIM
CPUSPEED
MBRC

FLAGS set to 0 indicate No Workload statistics. Note that SREADTIM and MREADTIM is not set. They are derived from IO Seek time and IO transfer speed. MBRC is also not set, it is derived from the parameter db_file_multiblock_read_count. Note that this parameter has been explicitly set to 64 on my database ( Why did I use the word "explicitly" - will explain that in some other post).

Now let's see the plan

SQL> explain plan for 

                                select * from T;

Plan hash value: 1601196873

----------------------------------------------------------------------------------

| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      | 50000 |   634K| 14857   (1)| 00:00:02 |

|   1 |  TABLE ACCESS STORAGE FULL| T    | 50000 |   634K| 14857   (1)| 00:00:02 |

----------------------------------------------------------------------------------

Cost of this full table scan is 14857. 

We will see how Oracle computes the cost

Cost = IO Cost + CPU Cost

IO Cost of FTS = (Blocks/MBRC) * (MREADTIME/SREADTIM)

Blocks = 50000

MBRC = 64

SREADTIM = IOSEEKTIME + DB_BLOCK_SIZE/IOTFRSPEED

                     = 10 + 16384/4096

                     = 14 

MREADTIM =IOSEEKTIME + (MBRC*DB_BLOCK_SIZE) / IOTFRSPEED

                      = 10 + (64*16384)/4096

                      = 266

Cost of FTS = (50000/64) * ( 266/14)

                    = 14843 

14843 is the IO cost. Very close to 14857 which we see in the execution plan. The difference is the CPU cost which is very minimal here.

Now let's gather the statistics in Exadata Mode and compute the numbers again

SQL> exec dbms_stats.gather_system_stats('EXADATA')

These are my new values now

FLAGS	1
CPUSPEEDNW	3326
IOSEEKTIM	13
IOTFRSPEED	204800
SREADTIM
MREADTIM
CPUSPEED
MBRC	64

FLAGS set to 1 indicate workload statistics. Now let's compute the cost of FTS

Blocks = 50000

MBRC = 64

SREADTIM = IOSEEKTIME + DB_BLOCK_SIZE/IOTFRSPEED

                     = 13 + 16384/204800

                     = 13.08

MREADTIM =IOSEEKTIME + (MBRC*DB_BLOCK_SIZE) / IOTFRSPEED

                      = 13 + (64*16384)/204800

                      = 18.12

Cost of FTS = (50000/64) * ( 18.12/13.08)

                    = 1082

Let's look at the plan to see if our calculations are correct

SQL> explain plan for 

                                select * from T;

Plan hash value: 1601196873

----------------------------------------------------------------------------------

| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      | 50000 |   634K|  1095   (2)| 00:00:01 |

|   1 |  TABLE ACCESS STORAGE FULL| T    | 50000 |   634K|  1095   (2)| 00:00:01 |

----------------------------------------------------------------------------------

Yes. Our calculations look correct. Plan shows 1095 and we calculated the value to be 1082. Difference is the CPU cost.

FTS cost dropped from 14843 to just 1082 .Fact that Exadata is a powerful hardware and that has to be accounted in the cost. Full table scans are much faster on Exadata and therefore the cost of FTS should be much lesser. This is why you must gather system statistics in Exadata mode.