DBMS_STATS Causes ORA-00600 [15851]

I noticed this in an Oracle 11.2.0.1.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index:

 

SQL> SELECT COUNT(*) FROM DBA_INDEXES

  2  WHERE OWNER = 'PDD'

  3  AND TABLE_NAME = 'DD_INSTRUCTION'

  4  AND INDEX_TYPE = 'FUNCTION-BASED NORMAL'

  5  /

 

  COUNT(*)

----------

         1

 

SQL>

 

… and that ESTIMATE_PERCENT was the same as AUTO_SAMPLE_SIZE:

 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS -

> ('PDD','DD_INSTRUCTION', -

>  'ESTIMATE_PERCENT',DBMS_STATS.AUTO_SAMPLE_SIZE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS -

> ('PDD','DD_INSTRUCTION');

BEGIN DBMS_STATS.GATHER_TABLE_STATS  ('PDD','DD_INSTRUCTION'); END;

 

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [15851],

[3], [2], [1], [1], [], [], [], [], [], [], []

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

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

ORA-06512: at line 1

 

SQL>

 

I did not want to remove the function based index so I changed the ESTIMATE_PERCENT:

 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS -

> ('PDD','DD_INSTRUCTION', -

>  'ESTIMATE_PERCENT',100);

 

PL/SQL procedure successfully completed.

 

SQL>

 

… and the problem went away:

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS -

> ('PDD','DD_INSTRUCTION');

 

PL/SQL procedure successfully completed.

 

SQL>

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.SET_PARAM

The example below worked as shown in Oracle 10. First create a test table with an index:

 

SQL> create table andrews_table (col1 number)

  2  /

 

Table created.

 

SQL> create index andrews_index

  2  on andrews_table(col1)

  3  /

 

Index created.

SQL>

Next run dbms_stats.set_param and set cascade to false:

 

SQL> exec dbms_stats.set_param('cascade','false');

 

PL/SQL procedure successfully completed.

SQL>

Then, if you analyze the table using gather_table_stats:

 

SQL> exec dbms_stats.gather_table_stats -

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

 

PL/SQL procedure successfully completed.

SQL>

The table is analyzed:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

SQL>

... but the index isn't:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

NULL

SQL>

However, if you run dbms_stats.set_param and set cascade to true:

SQL> exec dbms_stats.set_param('cascade','true');

 

PL/SQL procedure successfully completed.

SQL>

When you analyze the table, the index is analyzed too:

 

SQL> exec dbms_stats.gather_table_stats -

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

 

PL/SQL procedure successfully completed.

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL>

 

In Oracle 11, dbms_stats.set_param still runs but it has no effect. You can run

dbms_stats.set_param('cascade','false'); but the index might still be analyzed!

How to Gather Database Statistics

This post was tested on an Oracle 9 database. You can calculate optimizer statistics for an entire database using dbms_stats.gather_database_stats. The estimate_percent parameter allows you to base the statistics on a given percentage of the data, if you wish to do so. Note that statistics are not calculated for objects owned by SYS or DBSNMP. Conversely, you can delete database statistics with dbms_stats.delete_database_stats:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Sep 12 16:02:16 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

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

                 748

 

SQL> exec dbms_stats.gather_database_stats -

> (estimate_percent => 10);

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

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

12-SEP-11        424

                 324

 

SQL> select distinct owner

  2  from dba_tables

  3  where last_analyzed is null;

 

OWNER

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

DBSNMP

SYS

 

SQL> exec dbms_stats.delete_database_stats;

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

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

                 748

 

SQL>

The following statement was tested on Oracle 11.1. You can gather statistics just on tables with stale statistics as follows: 

SQL> exec dbms_stats.gather_database_stats -

> (options=>'gather stale',cascade=>true);

PL/SQL procedure successfully completed.

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>