Online Stats in Action

In my last blog post I introduced you to Online Stats and Real Time Stats.
Today I will show you, in action, with examples, how the Online Stats work, to lay the foundation of your skills in this area!

Online Stats were introduced in 12c, for the CREATE TABLE AS SELECT statements and for direct-path inserts into empty tables or partitions, INSERT /*+APPEND */ AS SELECT.

The purpose of the Online Stats was to populate the statistics right away for the table, and avoid the optimizer being misled by stale stats or no stats at all.
Oracle is scanning the table anyways, why not take advantage of the process, and gather stats as well. The collected stats during this process are very accurate, and can be used right away.

Let’s see an example. We have the table HR.EMPLOYEES, and we are going to create a new table HR.EMPLOYEES_COPY. We will see that after we create the HR.EMP_COPY table, the table will have stats gathered as well, without us explicitly gathering stats.

SQL> create table hr.employees_copy 
     as
     select * from hr.employees;   -- CTAS method used.

Table created.

SQL> select num_rows from dba_tables 
     where table_name='EMPLOYEES_COPY' and owner='HR';

  NUM_ROWS
----------
       107

SQL> select table_name,num_rows from dba_tables 
     where table_name='EMPLOYEES_COPY' and owner='HR';

TABLE_NAME             NUM_ROWS
-------------------- ----------
EMPLOYEES_COPY              107        -- stats were gathered automatically

If I just create an empty table, and insert the data into it, the stats will not be gathered automatically.

SQL> create table hr.jobs_copy   --simplified create table statement
    (job_id VARCHAR2(10), 
     job_title VARCHAR2(35), 
     min_salary NUMBER(6), 
     max_salary NUMBER(6));

Table created.

SQL> insert into hr.jobs_copy select * from hr.jobs;

19 rows created.

SQL> select table_name, num_rows 
     from dba_tables where table_name='JOBS_COPY' and owner='HR';

TABLE_NAME             NUM_ROWS
-------------------- ----------
JOBS_COPY                          --> no stats gathered

Let’s look at the DIRECT PATH INSERT statement into an empty table.

SQL> truncate table hr.jobs_copy;
Table truncated.

SQL> select table_name, num_rows 
     from dba_tables where table_name='JOBS_COPY' and owner='HR';

TABLE_NAME             NUM_ROWS
-------------------- ----------
JOBS_COPY                         -->no stats on the table

SQL> insert /*+APPEND */  into hr.jobs_copy  select * from hr.jobs;

19 rows created.   --> watch out for the hint
                   --> to be exactly how I wrote it! 
                   --> No extra spaces!
SQL> commit;

Commit complete.

SQL> select table_name, num_rows 
     from dba_tables where table_name='JOBS_COPY' and owner='HR';

TABLE_NAME             NUM_ROWS
-------------------- ----------
JOBS_COPY                    19  --stats were gathered automatically.

We noticed in the previous two examples, that when we did a regular insert, no stats were gathered. When we did a direct path insert /*+APPEND */, stats were automatically gathered. With the hint, make sure the hint has no extra spaces, it will not work!

If your table is not empty, stats will not be gathered automatically on the direct path insert! Deleting the rows is not enough. You must truncate the table for it to work, or have a newly created table. Let’s see it how it works!

SQL> delete hr.jobs_copy;

19 rows deleted.

SQL> exec dbms_stats.delete_table_stats('HR','JOBS_COPY');

PL/SQL procedure successfully completed.

SQL> insert /*+APPEND */  into hr.jobs_copy  select * from hr.jobs;

19 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, num_rows 
     from dba_tables where table_name='JOBS_COPY' and owner='HR';

TABLE_NAME             NUM_ROWS
-------------------- ----------
JOBS_COPY

SQL> truncate table hr.jobs_copy;

Table truncated.

SQL> insert /*+APPEND */  into hr.jobs_copy  select * from hr.jobs;

19 rows created.

SQL> commit;

Commit complete.

SQL> select table_name, num_rows 
     from dba_tables where table_name='JOBS_COPY' and owner='HR';

TABLE_NAME             NUM_ROWS
-------------------- ----------
JOBS_COPY                    19

These stats that are gathered for the table are base statistics and column stats, and not histograms.

Remember the purpose of the online stats is to improve performance!

This feature (automatically gather stats for CTAS/IAS), is turned on by the _optimizer_gather_stats_on_load=TRUE parameter.
If for any reason you want to turn the feature off, you can set the parameter to FALSE. I personally do not see a reason why you would do it.

You might be wondering why I went through all these examples, and kept repeating some concepts. That my friend, is because “Repetition is the mother of skill” (not my quote), the more you see it, the more you repeat it, the more the foundation for this skill is set!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $13.99 CAD available until the end of the month!