Skip to Main Content
  • Questions
  • Online Statistics Gathering for direct path load

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajneesh.

Asked: May 06, 2020 - 5:08 am UTC

Last updated: May 12, 2020 - 8:18 am UTC

Version: Oracle 19c

Viewed 1000+ times

You Asked

Hello TOM,

What is Online Statistics Gathering for direct path load? How it differs from traditional Auto and Manual gather stats? How it works? what are possible use cases and some examples of scenarios when it is recommended to opt?

Thanks,
Rajneesh

and Connor said...

It saves you time....

Lets say I have a brand new (empty) table, and I want to load 1billion rows into it.

You used to do this:

a) load the data ... let say that takes 2hrs
b) now you have to gather stats on it... that possibly going to take ANOTHER 2 hours

But if we loading into an *empty* table then the data we are *reading* from (either another table or a flat file) is the same as the data that will end up in the table. So as we *read* that data we can keep track of the statistics on that data as we load it. Once the load is complete, we can then set the statistics immediately without have to do step (b) above.

Simple example

SQL> create table t as select * from dba_objects;

Table created.

SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'T';

    BLOCKS   NUM_ROWS
---------- ----------
      1615      81982



The stats are already there without needing to run dbms_stats

Rating

  (2 ratings)

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

Comments

from Optimizer lady's blog.

Rajeshwaran, Jeyabal, May 11, 2020 - 5:22 pm UTC

Few more detail from Maria's blog on this

https://sqlmaria.com/2017/01/03/online-statistics-gathering/
Connor McDonald
May 12, 2020 - 8:18 am UTC

good input

Thank you

A reader, May 12, 2020 - 2:36 am UTC

Thanks, it helps !!!
Connor McDonald
May 12, 2020 - 8:18 am UTC

glad we could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.