Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Sokrates.

Asked: June 04, 2013 - 8:57 am UTC

Answered by: Tom Kyte - Last updated: November 18, 2019 - 12:14 pm UTC

Category: Database - Version: 11.2.0

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: DDL triggers - interesting results

You Asked

a. how does sampling work internally ?

meaning: how is it determined by Oracle which blocks are read by a sample-query and which not ?

create table t as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5;
create table i (i primary key, s) organization index as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5;
exec dbms_stats.gather_table_stats(null, 't'); dbms_stats.gather_table_stats(null, 'i');
sokrates@11.2 > set autotr traceonly explain
sokrates@11.2 > select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    95M|   771   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |   100K|    95M|   771   (2)| 00:00:01 |
--------------------------------------------------------------------------

sokrates@11.2 > select * from t sample(10);

Execution Plan
----------------------------------------------------------
Plan hash value: 2767392432

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 10000 |  9824K|   770   (1)| 00:00:01 |
|   1 |  TABLE ACCESS SAMPLE| T    | 10000 |  9824K|   770   (1)| 00:00:01 |
----------------------------------------------------------------------------



I have rather an idea of how a FULL TABLE SCAN works internally ( Oracle has to read every block of the segment ), but how does a "TABLE ACCESS SAMPLE" work internally ?
which blocks are read by Oracle, which blocks aren't read ?
( same for INDEX SAMPLE FAST FULL SCAN )




b. which views cannot be queried by a sample-query ?
create view v
as
select t.s t, i.s i
from t, i
where t.i = i.i;

select * from v sample(1);
1009 rows selected.
...

[
why are sample-queries not allowed for inline-views ?
select * from
(
select t.s t, i.s i
from t, i
where t.i = i.i
) sample(1);
ERROR at line 6:
ORA-00933: SQL command not properly ended
]


but

create view w
as
select t.s t, i.s i, count(*) c
from t, i
where t.i = i.i
group by t.s, i.s
/

select * from w sample(1);
select * from w sample(1)
*
ERROR at line 1:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.


does this mean that every view not containing DISTINCT and GROUP BY can be queried in a sampling manner ?



by the way:
on the "Please Enter Your Question" - form there is a drop-down-box named "Years Using Oracle", which goes from 0 to 20.
I just noticed, that I started using Oracle in May 1992, which is over 21 years now.
Does this mean:
- when you use Oracle longer than 20 years, you should not have anymore questions on it or
- when you use Oracle longer than 20 years, you are not allowed to enter a
question or
- when you use Oracle longer than 20 years, it's time to do something different
?

and we said...

Update: make sure to read the comments below as well as this - some more important details have been added

a) it reads them all, as it processes blocks/rows - it decides whether a block or row would be included in the sample based on a random number basically.

So conceptually, it would read all N blocks - and for each block generate a random number between 0 and 1 and use that as a percentage. if the random number was less than the sample percent - the block/row is processed, else it is not.

you can see that it reads everything in a straightforward manner:

ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set arraysize 1000
ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> select * from t;

76989 rows selected.


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
       1285  consistent gets
       1372  physical reads
          0  redo size
    3161061  bytes sent via SQL*Net to client
       1256  bytes received via SQL*Net from client
         78  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      76989  rows processed

ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> select * from t sample(1);

763 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        842  consistent gets
       1370  physical reads
          0  redo size
      42858  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        763  rows processed

ops$tkyte%ORA11GR2> set autotrace off



same number of physical IO's, just two orders of magnitude difference in the number of rows returned...

b) http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55298

we have to be able to determine that the view has a key preserved table in it, for example:

ops$tkyte%ORA11GR2> create table t1 as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> create table t2 as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace view v
  2  as
  3  select t1.object_name, t1.object_id, t1.object_type, t2.username, t2.user_id
  4    from t1, t2
  5   where t1.owner = t2.username;

View created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from v sample(0.01);
select * from v sample(0.01)
              *
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table


ops$tkyte%ORA11GR2> alter table t2 add constraint t2_pk primary key(username);

Table altered.

ops$tkyte%ORA11GR2> select * from v sample(0.01);

OBJECT_NAME                     OBJECT_ID OBJECT_TYPE         USERNAME                          USER_ID
------------------------------ ---------- ------------------- ------------------------------ ----------
/ec460654_ConstraintFieldToken      15488 JAVA CLASS          SYS                                     0
javax/sql/XADataSource              18345 JAVA CLASS          SYS                                     0
/2687a6dc_ClassReaderAnnotatio      20134 JAVA CLASS          SYS                                     0
/210646b9_CDRInputObject            24311 JAVA CLASS          SYS                                     0
sun/security/acl/AclImpl            32188 JAVA CLASS          SYS                                     0
CTX_ENTITY                          56095 PACKAGE             CTXSYS                                 43
photometricInterpretati232_T        57850 TYPE                ORDSYS                                 53
PK_MGMT_FAILOVER                    69324 INDEX               SYSMAN                                 71
BIN$3NNUaC1xBA3gQwEAAH/6kg==$0     162038 TABLE PARTITION     OPS$TKYTE                            1150

9 rows selected.



things with group bys and distincts at the top level are never "key preserved", we assume the aggregation will reduce the size of the result set (meaning the key of one of the tables is not a key in the the resulting set/table).

anything that removes ROWID from the


c) I laughed at that :) No, I have now 26 years using Oracle and I still ask questions!!!

maybe your last point is the correct one, I can feel like that somedays...

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

cannot reproduce

June 04, 2013 - 4:40 pm UTC

Reviewer: Sokrates

c. maybe your last point is the correct one
yes, but I'm still fascinated by the software and things to discover ...

b. thanks for the link to the docs !

a. it reads them all, as it processes blocks/rows - it decides whether a block or row would be included in the sample based on a random number basically.

So conceptually, it would read all N blocks - and for each block generate a random number between 0 and 1 and use that as a percentage. if the random number was less than the sample percent - the block/row is processed, else it is not.

you can see that it reads everything in a straightforward manner:


cannot reproduce that
sokrates@11.2 > create table t as select rpad('a', 4000, 'a') a from dual connect by level<=1e6;

Table created.
sokrates@11.2 > alter system flush buffer_cache;
/
System altered.

sokrates@11.2 >

System altered.

sokrates@11.2 > set autotr traceonly statistics
sokrates@11.2 > select * from t;

1000000 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
    1000016  consistent gets
    1000007  physical reads
          0  redo size
 4037400441  bytes sent via SQL*Net to client
     733850  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

sokrates@11.2 > alter system flush buffer_cache;
/
System altered.

sokrates@11.2 >

System altered.

sokrates@11.2 > select * from t sample(0.01);

93 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        436  consistent gets
        452  physical reads
          0  redo size
     376006  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         93  rows processed



Tom Kyte

Followup  

June 04, 2013 - 7:53 pm UTC

doh, and I know that - dynamic sampling shows this, the sample scan just hits a bit - it has to do with the size of the table, number of blocks and the percentage. My example wasn't large enough to see that.



in such a simple case - we'll roll the dice for each read to see if we should include those blocks - or not. Yours is an extreme case - one row per block. Once we started doing the sample scan - you would find we dropped down to db file sequential reads (turn on trace) after a few multiblock reads and discovering "hey, there is a row per block here".

If you put a few more rows/block and have a slightly larger sample size, you'll see different behavior:

ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> create table t as select rpad('a', 100, 'a') a from dual connect by level<=1e6;
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotr traceonly statistics
ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> select * from t;

1000000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      80396  consistent gets
      14707  physical reads
          0  redo size
   13467221  bytes sent via SQL*Net to client
     733745  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> select * from t sample(1);

10014 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       8675  consistent gets
      14707  physical reads
          0  redo size
     135418  bytes sent via SQL*Net to client
       7756  bytes received via SQL*Net from client
        669  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10014  rows processed

ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> select * from t sample(0.01);

102 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        247  consistent gets
        406  physical reads
          0  redo size
       1911  bytes sent via SQL*Net to client
        485  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        102  rows processed

ops$tkyte%ORA11GR2> set autotrace off




so, it looks like my example was too small to demonstrate everything it might be doing under the covers - and yours was too large on the other end.

In short, it is more complex than I thought it was - I hadn't tried it out on a table with a row per block, or such a small sample.

In many cases, it works as described, but as the sample gets smaller - or the table has really large rows giving us only one, two or very few rows per block - it will roll the dice on rowid ranges or block reads - to decide whether to do an IO or not.

I *believe* this only works for simple single table scans...

thanks for following up with this example - it fills in the picture much more.

But after 20+ years

June 04, 2013 - 6:12 pm UTC

Reviewer: Martijn Hoekstra from 42 io's away from a complete resultset

c. maybe your last point is the correct one
yes, but I'm still fascinated by the software and things to discover ...


@Sokrates
Me too, there are lots of things to (un)learn, understand and (re)discover. And I still fit in the 'age-poplist' ;)

@Tom
However, I'd really wonder what you would do for a living if Oracle (or whatever database) didn't exist. Albeit after more than 20 years, what would be/would have been your (imaginary) "Plan B" in that case? ;)

Tom Kyte

Followup  

June 04, 2013 - 8:02 pm UTC

hah, I have no idea, no idea at all.

If you told me 25 years ago that I would be making a living mostly through writing and talking on stage in front of people - I would have laughed at you...

I came out of college with a degree in Math, I accidentally got a programming job and that accidentally introduced me to Oracle and then I accidentally got a job here 20 years ago...

I didn't have a plan "A" to begin with :)

Why isn't sampling faster? less physical reads?

November 11, 2019 - 7:10 pm UTC

Reviewer: Matt from California

We have a department at my company that spends 2-3 months out of the year producing an annual report using data from our very large data warehouse. I've just started telling them to speed up their processing by sampling data instead of selecting all the data - tens of billions of rows - and getting nearly as good results. As statisticians they should understand the margin of error will be small if you sample 10% or 1% of the table.

However, when I test this theory there is really no increase in speed and sampling appears to still visit the all or nearly all the blocks of data compared to not sampling.

Raw results below but here is a synopsis of the results (1 month of data is all in one partition):

qry type         time   cons gets    phys reads
---------------- ----- ------------ -------------
full scan        15:08  2,073,000    2,067,000
10% sample        6:03  2,072,000    2,067,000
10% sample block 12:23    207,000    2,070,000
1% sample         6:14  1,959,000    2,067,000
1% sample block  12:27     21,000    1,383,000


Why doesn't sampling reduce the time of a query significantly and why does it appear to not reduce the number of physical reads significantly?

Raw output (table names changed to protect the innocent):

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>
SQL> select /* full scan */ avg(cost) from user1.resource_awards where trade_dt_s between '01-Jan-2019' and '31-Jan-2019';

Elapsed: 00:15:08.60

Statistics
----------------------------------------------------------
        136  recursive calls
          0  db block gets
    2073385  consistent gets
    2067427  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /* 10% sample */ avg(cost) from user1.resource_awards sample (10) where trade_dt_s between '01-Jan-2019' and '31-Jan-2019';

Elapsed: 00:06:03.02

Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
    2072421  consistent gets
    2067423  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /* 10% sample block */ avg(cost) from user1.resource_awards sample block (10) where trade_dt_s between '01-Jan-2019' and '31-Jan-2019';

Elapsed: 00:12:23.89

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     207080  consistent gets
    2070318  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /* 1% sample */ avg(cost) from user1.resource_awards sample (1) where trade_dt_s between '01-Jan-2019' and '31-Jan-2019';

Elapsed: 00:06:14.96

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1958710  consistent gets
    2067422  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> select /* 1% sample block */ avg(cost) from user1.resource_awards sample block (1) where trade_dt_s between '01-Jan-2019' and '31-Jan-2019';

Elapsed: 00:12:27.36

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      21397  consistent gets
    1383406  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Connor McDonald

Followup  

November 12, 2019 - 11:15 pm UTC

The key difference here is row sampling versus block sampling, and sampling is meant to get a "random" sample.

So think about a typical Oracle block is 8k, and lets say for the sake of example, the average size of a row in your table is 80 bytes. This means that on average there are 100 rows per 8k block.

So when you say "I want 1% of the rows", then to get a 1% *random* sample, you want to get 1 row out of each 100 rows. And since there are 100 rows per block, this means you will probably visit *every* block to get a 1% random sample.

Hence physical reads can easily be the same as the table.

Conversely, if you go with *block* sampling, then we will sample 1% of the *blocks*. You will see a decrease in physical reads *but* you are forsaking the true randomness of the *rows*

That's what I thought, but..

November 13, 2019 - 1:16 am UTC

Reviewer: Matt again from California

> Conversely, if you go with *block* sampling, then we will
> sample 1% of the *blocks*. You will see a decrease in
> physical reads *but* you are forsaking the true randomness
> of the *rows*

That's exactly what I thought but when I block sampled at 10% it still read the exact same number of blocks (phys reads). When I block sampled at 1% ot still had 50% of the phys reads or the full scan.

qry type         time   cons gets    phys reads
---------------- ----- ------------ -------------
full scan        15:08  2,073,000    2,067,000
10% sample        6:03  2,072,000    2,067,000
10% sample block 12:23    207,000    2,070,000
1% sample         6:14  1,959,000    2,067,000
1% sample block  12:27     21,000    1,383,000

Connor McDonald

Followup  

November 14, 2019 - 11:15 pm UTC

This will be a factor of the "efficiency" of your storage (or how Oracle interacts with it).

SAMPLE only makes sense with full scans, or more accurately, a multiblock read.

Whenever you do a a read request to the OS, you will request a chunk of blocks, which might be as high as 1MB. (Various factors influence the size).

But assuming a typical block size of 8k, this might be 128 blocks.

Once that read is done, we'll use a "randomizer" to decide on a random sample of those blocks, and then extract the rows from them. We'll continue to do that until the number of blocks extracted comes to 10% (hence the 207,000 consistent gets).

So its quote possible that you'll end up getting a larger chunk of the table because of the multiblock reads you are doing.

Excelently put. Need to set db_file_multiblock_read_count if using sample for performance

November 15, 2019 - 12:54 am UTC

Reviewer: Matt from California

After setting db_file_multiblock_read_count to 1 at session level it does read exactly 1% of the blocks in the table!

SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> select /* 1% sample block no multi */ avg(cost) from user1.resource_awards sample block (1) where trade_dt_s between '01-Jan-2019' and '31-Jan-2019';

AVG(COST)
---------------
     35.1466136

Elapsed: 00:04:13.48

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      22124  consistent gets
      <b>20836  physical reads</b>
       5816  redo size
        372  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Since reading 1 block at a time rather than 32 (this DBs multiblock parameter setting) is less efficient it only sped it up 3x.... but I'll take it. I now have to account for differences I am seeing in the averages with this not-exactly-random distribution. THANKS CONNOR!
Connor McDonald

Followup  

November 18, 2019 - 12:14 pm UTC

Glad we could help