Analyze versus dbms_stats

Many years ago, if you wanted to collect optimizer statistics for a table, you had to use the analyze command. Then Oracle brought out dbms_stats and said you should use that instead as it produced better statistics. I decided to test this out on an Oracle 10.2.0.3.0 database. I did this by analyzing a table then running a query on it. Then I ran dbms_stats on the table, ran the query again and compared the results. First I set sql_trace to true to give me a trace file to run through tkprof later:

SQL> alter session set sql_trace = true

  2  /

Session altered.

SQL>

Then I created a table, added lots of data to it and counted the rows:

SQL> create table andrews_table

  2  storage (maxextents unlimited)

  3  as select * from dba_tables

  4  /

Table created.

SQL> begin

  2  for a in 1..9 loop

  3  insert into andrews_table

  4  select * from andrews_table;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

   1074688

SQL>

Then I counted the number of rows with SYSTEM as the owner. The percentage was quite small so using an index to access them should be better than doing a full table scan:

SQL> select owner, count(*)

  2  from andrews_table

  3  where owner = 'SYSTEM'

  4  group by owner

  5  /

OWNER                            COUNT(*)

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

SYSTEM                              75264

SQL>

Next I created an index, analyzed the table and checked that both the table and the index had been analyzed:

SQL> create index andrews_index

  2  on andrews_table(owner)

  3  /

Index created.

SQL> analyze table andrews_table

  2  compute statistics

  3  /

Table analyzed.

SQL> select last_analyzed from user_tables

  2  where table_name = 'ANDREWS_TABLE'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select last_analyzed from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL>

Then I queried the rows with SYSTEM as the owner. Sometimes when I run tests like this, I give Oracle a perfectly good index to use but it does a full table scan instead and I never know why so I added a hint to make sure that Oracle used the index:

SQL> select /*+ index(andrews_table andrews_index) */

  2  count(*) after_analyze from andrews_table

  3  where owner = 'SYSTEM'

  4  and pct_used = 40

  5  /

AFTER_ANALYZE

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

        50176

SQL> alter session set sql_trace = false

  2  /

Session altered.

SQL>

The next step was to repeat the test using dbms_stats instead:

SQL> alter session set sql_trace = true

  2  /

Session altered.

SQL> create table andrews_table

  2  storage (maxextents unlimited)

  3  as select * from dba_tables

  4  /

Table created.

SQL> begin

  2  for a in 1..9 loop

  3  insert into andrews_table

  4  select * from andrews_table;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

   1074688

SQL> select owner, count(*)

  2  from andrews_table

  3  where owner = 'SYSTEM'

  4  group by owner

  5  /

OWNER                            COUNT(*)

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

SYSTEM                              75264

SQL> create index andrews_index

  2  on andrews_table(owner)

  3  /

Index created.

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', -

>  tabname=>'andrews_table', -

>  cascade=>true);

PL/SQL procedure successfully completed.

SQL> select last_analyzed from user_tables

  2  where table_name = 'ANDREWS_TABLE'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select last_analyzed from user_indexes

  2  where index_name = 'ANDREWS_INDEX'

  3  /

LAST_ANAL

---------

09-MAY-12

SQL> select /*+ index(andrews_table andrews_index) */

  2  count(*) after_dbms_stats from andrews_table

  3  where owner = 'SYSTEM'

  4  and pct_used = 40

  5  /

AFTER_DBMS_STATS

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

           50176

SQL> alter session set sql_trace = false

  2  /

Session altered.

SQL>

Finally I ran the trace file through tkprof and compared the results. CPU time, elapsed time, disk reads and buffer gets were all quite a bit less when dbms_stats was used to collect statistics:

********************************************************************************

select /*+ index(andrews_table andrews_index) */

count(*) after_analyze from andrews_table

where owner = 'SYSTEM'

and pct_used = 40

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.01       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      2.54      31.74      12696      13209          0           1

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

total        4      2.55      31.75      12696      13209          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 32  (ORACLE)

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=13209 pr=12696 pw=0 time=31745246 us)

  50176   TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=13209 pr=12696 pw=0 time=43170772 us)

  75264    INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=191 pw=0 time=633026 us)(object id 120507)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

  50176    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

               'ANDREWS_TABLE' (TABLE)

  75264     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'

                (INDEX)

********************************************************************************

select /*+ index(andrews_table andrews_index) */

count(*) after_dbms_stats from andrews_table

where owner = 'SYSTEM'

and pct_used = 40

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      2.12      24.39      11777      12711          0           1

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

total        4      2.12      24.39      11777      12711          0           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 32  (ORACLE)

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=12711 pr=11777 pw=0 time=24395897 us)

  50176   TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=12711 pr=11777 pw=0 time=26819905 us)

  75264    INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=134 pw=0 time=378268 us)(object id 120510)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   SORT (AGGREGATE)

  50176    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

               'ANDREWS_TABLE' (TABLE)

  75264     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'

                (INDEX)

********************************************************************************

DBMS_STATS Does Not Count Chained Rows

I tested this example on Oracle 11.2. First you need a table with some chained rows. An easy way to do this is to create a table with pctfree = 0 then add lots of data to each row:

SQL> create table chaining_test

  2  pctfree 0

  3  as select object_id

  4  from dba_objects

  5  where rownum < 5000

  6  /

Table created.

SQL> alter table chaining_test add

  2  (owner varchar2(30),

  3   object_name varchar2(128))

  4  /

Table altered.

SQL> update chaining_test x set owner =

  2  (select owner from dba_objects

  3   where object_id = x.object_id)

  4  /

4999 rows updated.

SQL> update chaining_test x set object_name =

  2  (select object_name from dba_objects

  3   where object_id = x.object_id)

  4  /

4999 rows updated.

SQL>

You can still use analyze to count the chained rows:

SQL> analyze table chaining_test compute statistics

  2  /

Table analyzed.

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

      4906

SQL>

If you then run dbms_stats.gather_table_stats against the table, the chain_cnt is not updated although, in this case, it makes no difference as the number of chained rows has not changed:

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'chaining_test');

PL/SQL procedure successfully completed.

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

      4906

SQL>

However, if you delete the statistics:

SQL> analyze table chaining_test delete statistics

  2  /

Table analyzed.

SQL>

... then run dbms_stats.gather_table_stats against it again:

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'chaining_test');

PL/SQL procedure successfully completed.

SQL>

The chain_cnt is left as zero:

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

         0

SQL>

Locking and Unlocking Table Statistics

DBMS_STATS.LOCK_TABLE_STATS allows you to lock a table’s statistics. This stops the table being analyzed, even with an old ANALYZE command. DBMS_STATS.UNLOCK_TABLE_STATS has the reverse effect and allows you to analyze the table again:

 

SQL> exec dbms_stats.lock_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

BEGIN dbms_stats.gather_table_stats  (ownname => 'ANDREW', tabname => 'LOCK_TEST'); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype

= ALL)

ORA-06512: at "SYS.DBMS_STATS", line 13182

ORA-06512: at "SYS.DBMS_STATS", line 13202

ORA-06512: at line 1

 

SQL> analyze table andrew.lock_test

  2  compute statistics

  3  /

analyze table andrew.lock_test

*

ERROR at line 1:

ORA-38029: object statistics are locked

 

SQL> exec dbms_stats.unlock_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL>

ANALYZE (Part 1)

(Tested on an Oracle 10 database.)

I was going through some old notes recently and was reminded of the ANALYZE command. This allows you to calculate statistics on a table or index. The optimizer can then use these to improve the efficiency of subsequent DML on that object. Oracle have said for a long time now that you should DBMS_STATS in preference to ANALYZE. However, I thought it might still be useful to look at ANALYZE in case you find it in an old SQL script yourself.

In the example below, a table is created and its rows counted. At this point, NUM_ROWS (the number of rows in the table when it was last analyzed) is blank. SAMPLE_SIZE (the number of rows used for the last ANALYZE) is also blank. The table is then analyzed with the COMPUTE STATISTICS option. This analyzes the entire table and populates these two columns in the process.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 100 ROWS option. You might expect SAMPLE_SIZE to be 100 afterwards but it is not. I will be returning to this in a future post.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 20 PERCENT option. You might expect SAMPLE_SIZE to be 306 afterwards but it is not. I will be returning to this too in a future post.

Finally, the table is analyzed with the DELETE STATISTICS option. This removes the statistics so the NUM_ROWS and SAMPLE_SIZE columns become null again:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1531

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL> analyze table andrew compute statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 100 rows
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 20 percent
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew delete statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL>

You Cannot Analyze a Table Through a Synonym

This was tested on an Oracle 11 database. First I created a table and analyzed it: 

SQL> create table andrew1 (one_col number)

  2  /

Table created.

SQL> analyze table andrew1 compute statistics

  2  /

Table analyzed.

SQL>

Then I created a synonym for the table:

SQL> create synonym andrew2 for andrew1

  2  /

Synonym created.

SQL>

I found that I was able to describe the table via the synonym:

SQL> desc andrew2

 Name                    Null?    Type

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

 ONE_COL                          NUMBER

SQL>

... but I could not analyze it:

SQL> analyze table andrew2 compute statistics

  2  /

analyze table andrew2 compute statistics

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>