Oracle Performance – Full Table Scan

In previous posts, we have discussed different Oracle wait events and the performance improvements that will be seen with the Kaminario K2 system. In this post, I would like to discuss how such an improvement affects the execution of a query and ultimately the end user queries.

Let’s look at a simple select count from a large table. Running the query using “autotrace on” identified a full table scan performed with parallel steps:

Plan hash value: 1844227803

| Id  | Operation

|   0 | SELECT STATEMENT

|   1 |  SORT AGGREGATE

|   2 |   PX COORDINATOR

|   3 |    PX SEND QC (RANDOM)

|   4 |     SORT AGGREGATE

|   5 |      PX BLOCK ITERATOR

|   6 |       TABLE ACCESS FULL

Statistics

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

         96  recursive calls

          0  db block gets

    2330213  consistent gets

    2295202  physical reads

          0  redo size

        529  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed          0  sorts (disk)

          1 rows processed

Looking at the statistics we can see that there was a total of 2.2M (2295202) blocks read from the storage and the query completed in 5 seconds. This is an 8KB block size database so this translates to around 3-4 GB/s throughput from the storage ( 2295202 * 8 / 1024/1024/5 ). Indeed, when we look at the Kaminario K2 GUI during the full scan execution we can see more than 3 GB/s of throughput with sub 1ms latencies

 

This video demonstrates an even higher full table scan performance when you push higher on the parallelism and it also demonstrates full scan as part of an ETL process.

New Call-to-action