Search

Top 60 Oracle Blogs

Recent comments

June 2009

Oracle 10g Statistics History Retention in PeopleSoft

I have been working on a system where many Application Engine programs are running throughout the day, and are frequently collecting Optimizer statistics with the %UpdateStats macro on many working storage tables. Concurrent calls to dbms_stats are typical.

There are two new behaviours in Oracle 10g RDBMS that can in extreme cases, in combination with a system that calls dbms_stats very frequently, create a significant performance overhead.

From Oracle 10g, histograms may, by default, be collected automatically. That means that rows are concurrently deleted from and inserted into histgrm$ and hist_head$, leading to contention and consistent read.

  • Also from Oracle 10g, every time you collect statistics on a table the old statistics are retained in the SYS.WRI$_OPTSTAT%HISTORY tables. If histograms have previously been collected, these are also copied. DBMS_STATS has the additional overhead of writing this history. I found in excess of 10,000 versions of previous statistics for some tables, because the batch processes have updated statistics on working storage tables that many times.
  • dbms_stats also appears to be responsible for purging history older than the retention limit. The default retention period is 31 days. I have seen concurrent calls to dbms_stats blocked on row level locks on the statistics history tables. For me, this occurred 31 days after the system went live on a significantly increased volume.
  • #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
    SELECT dbms_stats.get_stats_history_retention FROM dual;
    GET_STATS_HISTORY_RETENTION 
    --------------------------- 
                             31

Statistics history was designed to work in conjunction with schema wide statistics jobs that only refreshed stale statistics. There is an option on gather_schema_stats to collect only statistics on tables where the current statistics are stale. However, there is no such option on gather_table_stats. If you have decided to call this procedure for a particular table, then it is assumed you know you need to refresh the statistics. However, by calling dbms_stats from a batch program you can end up calling it much more frequently than is really necessary.

Recommendations

  • Disable statistics history by using dbms_stats.alter_stats_history_retention to set the retention period to zero. Unfortunately this can only be set at database level. The statistics history is there in case you want to revert to a previous version of the statistics should a new set of statistics produce a problem, but it is only used rarely, and I think this is a necessary sacrifice.
  • #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
    EXECUTE dbms_stats.alter_stats_history_retention(retention=>0);
  • Use Oracle Optimizer Dynamic Sampling. However, I suggest increasing the level from the default of 2 to 4 to increase the situations in which it is used.
  • Introduce the new version of the PL/SQL wrapper package for dbms_stats so that you can specify the records for which statistics will be explicitly collected, and whether histograms are to be collect. Thus you can reduce the number of calls to dbms_stats.

If you have allowed the statistics history to grow before you disable history retention, then you might like to read John Hallas' posting on Purging statistics from the SYSAUX tablespace.

Oracle 10g Statistics History Retention in PeopleSoft

I have been working on a system where many Application Engine programs are running throughout the day, and are frequently collecting Optimizer statistics with the %UpdateStats macro on many working storage tables. Concurrent calls to dbms_stats are typical.

There are two new behaviours in Oracle 10g RDBMS that can in extreme cases, in combination with a system that calls dbms_stats very frequently, create a significant performance overhead.

From Oracle 10g, histograms may, by default, be collected automatically. That means that rows are concurrently deleted from and inserted into histgrm$ and hist_head$, leading to contention and consistent read.

  • Also from Oracle 10g, every time you collect statistics on a table the old statistics are retained in the SYS.WRI$_OPTSTAT%HISTORY tables. If histograms have previously been collected, these are also copied. DBMS_STATS has the additional overhead of writing this history. I found in excess of 10,000 versions of previous statistics for some tables, because the batch processes have updated statistics on working storage tables that many times.
  • dbms_stats also appears to be responsible for purging history older than the retention limit. The default retention period is 31 days. I have seen concurrent calls to dbms_stats blocked on row level locks on the statistics history tables. For me, this occurred 31 days after the system went live on a significantly increased volume.
  • #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
    SELECT dbms_stats.get_stats_history_retention FROM dual;
    GET_STATS_HISTORY_RETENTION 
    --------------------------- 
                             31

Statistics history was designed to work in conjunction with schema wide statistics jobs that only refreshed stale statistics. There is an option on gather_schema_stats to collect only statistics on tables where the current statistics are stale. However, there is no such option on gather_table_stats. If you have decided to call this procedure for a particular table, then it is assumed you know you need to refresh the statistics. However, by calling dbms_stats from a batch program you can end up calling it much more frequently than is really necessary.

Recommendations

  • Disable statistics history by using dbms_stats.alter_stats_history_retention to set the retention period to zero. Unfortunately this can only be set at database level. The statistics history is there in case you want to revert to a previous version of the statistics should a new set of statistics produce a problem, but it is only used rarely, and I think this is a necessary sacrifice.
  • #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
    EXECUTE dbms_stats.alter_stats_history_retention(retention=>0);
  • Use Oracle Optimizer Dynamic Sampling. However, I suggest increasing the level from the default of 2 to 4 to increase the situations in which it is used.
  • Introduce the new version of the PL/SQL wrapper package for dbms_stats so that you can specify the records for which statistics will be explicitly collected, and whether histograms are to be collect. Thus you can reduce the number of calls to dbms_stats.

If you have allowed the statistics history to grow before you disable history retention, then you might like to read John Hallas' posting on Purging statistics from the SYSAUX tablespace.

Dynamic sampling and set current_schema anomaly

Sometimes when I'm asked to check a particular SQL in a production like environment at first I only get rather limited access, e.g. a read-only user that has the required privileges to perform the SQL statement, but doesn't own the objects.

If I'm now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command to switch any unqualified object references to the schema set via CURRENT_SCHEMA rather than editing the SQL and add all the object references.

This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn't do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.

The following simple test case demonstrates the issue in 10.2.0.4 Win32:

SQL>
SQL> drop user dynsamp_test cascade;
drop user dynsamp_test cascade
*
ERROR at line 1:
ORA-01918: user 'DYNSAMP_TEST' does not exist

SQL>
SQL> create user dynsamp_test identified by dynsamp_test;

User created.

SQL>
SQL> grant create session, alter session to dynsamp_test;

Grant succeeded.

SQL>
SQL> drop table dynsamp_test_table purge;
drop table dynsamp_test_table purge
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> create table dynsamp_test_table
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;

Table created.

SQL>
SQL> grant select on dynsamp_test_table to dynsamp_test;

Grant succeeded.

SQL>
SQL> -- this is the plan we get as schema owner
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> -- It uses obviously dynamic sampling as instructed
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')

Note
-----
- dynamic sampling used for this statement

17 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> -- We get the same from different schema with qualified reference (or valid synonym)
SQL> connect dynsamp_test/dynsamp_test@orcl
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 cbo_test.dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> -- Still uses dynamic sampling
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')

Note
-----
- dynamic sampling used for this statement

17 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> -- Now switch the current_schema
SQL> alter session set current_schema = cbo_test;

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> -- No more dynamic sampling!
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 10 | 1280 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')

13 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> -- Check the 10053 trace file
SQL> -- It will show that the dynamic samping query fails
SQL> -- and therefore no dynamic sampling will be performed
SQL> -- presumably because the current_schema is not obeyed
SQL> -- by the recursively executed query
SQL> alter session set tracefile_identifier = 'dynamic_sampling_current_schema_10053';

Session altered.

SQL>
SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> alter session set events '10053 trace name context off';

Session altered.

SQL> -- Check the 10046 trace file
SQL> -- which confirms a ORA-00942 while parsing the dynamic sampling query
SQL> alter session set tracefile_identifier = 'dynamic_sampling_current_schema_10046';

Session altered.

SQL>
SQL> alter session set sql_trace = true;

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> alter session set sql_trace = false;

Session altered.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> spool off

So as you can see the simply query uses dynamic sampling as instructed (and in 10.2 would use dynamic sampling by default anyway since the table has no statistics), but when using the CURRENT_SCHEMA trick and then an unqualified reference, the dynamic sampling is not performed and the estimate is based on hardcoded defaults.

Checking the 10053 optimizer trace file confirms that the dynamic sampling is attempted, but the recursive query fails, and the 10046 SQL trace shows that an "ORA-00942 table or view does not exist" error is encountered while parsing the recursive dynamic sampling query.

So be careful when using this (rather unusual I have to admit) setup, the execution plans might be different if you're using a non-default CURRENT_SCHEMA and dynamic sampling is involved.

The issue seems to be fixed in 11.1.0.7 (dynamic sampling is performed as expected when using non-default CURRENT_SCHEMA), however I couldn't identify a corresponding bug on Metalink. If anyone comes across a bug number or document ID this would be helpful.

(Secret) Preview of Oracle 12g CBO leaked from Oracle labs…

After doing my seminar in Spain last month, one of the attendees sent me a secret note about what the CBO would look like in Oracle 12g. Apparently it’s re-engineered from ground and completely different from anything we’ve seen before. It goes beyond being automatic, intelligent and auto-tuning, it’s actually edible too!
The New CBO Thanks to John Ospino for sending me this insider-info ;)

(Secret) Preview of Oracle 12g CBO leaked from Oracle labs…

After doing my seminar in Spain last month, one of the attendees sent me a secret note about what the CBO would look like in Oracle 12g. Apparently it’s re-engineered from ground and completely different from anything we’ve seen before. It goes beyond being automatic, intelligent and auto-tuning, it’s actually edible too!
The New CBO Thanks to John Ospino for sending me this insider-info ;)

Oracle memory troubleshooting, Part 3: Automatic top subheap dumping with heapdump

If you haven’t read them – here are the previous articles in Oracle memory troubleshooting series: Part 1, Part 2

In Oracle, the HEAPDUMP dump event in Oracle allows you to dump various heap contents to tracefile. With adding the “level” parameter to this dump event, you can specify which heaps to dump.

Julian Dyke has documented most of the levels here.

There are two little known, but useful level bits for heapdumps – bit 0x10000000 and 0x20000000. These allow Oracle to dump top-5 biggest subheaps in a heap recursively.

Oracle memory troubleshooting, Part 3: Automatic top subheap dumping with heapdump

If you haven’t read them – here are the previous articles in Oracle memory troubleshooting series: Part 1, Part 2

In Oracle, the HEAPDUMP dump event in Oracle allows you to dump various heap contents to tracefile. With adding the “level” parameter to this dump event, you can specify which heaps to dump.

Julian Dyke has documented most of the levels here.

There are two little known, but useful level bits for heapdumps – bit 0x10000000 and 0x20000000. These allow Oracle to dump top-5 biggest subheaps in a heap recursively.

An Essay on Science

Richard Feynman defined science as "the belief in the ignorance of experts." Science begins by questioning established ideas. ...Even those ideas promoted by so-called experts.

The value of science that's obvious to everybody is the chance you might discover some valuable truth that nobody else has discovered before. That's the glamorous idea that might motivate you to begin the hard work that science sometimes requires. Science is also valuable to you when you learn that an established idea, no matter how much you may not like it, really is true after all. That second value of science is not as glamorous, but it's just as important. My little prayer with respect to that possibility is, "If an idea I believe is wrong, please let me find out before anybody else does."

Everyone can do science. Not just "scientists"; all of us. But you need to do science "right," or it's not science. Do it right, and you accumulate a little bit of truth. Do it wrong, and and you've wasted your time, or worse, you've doomed yourself to waste more of your time in the future, too.

The difference between "right" and "wrong" in science is not some snooty, bureaucratic concept. You don't need a license or a blessing to do science right. You just need to ensure that the cause-effect relationships you choose to believe are actually correct. One of the rules for doing science right is that you measure instead of just asserting your opinion.

Different people have different thresholds of skepticism. Some people believe new ideas, whether they're true or false, with very little persuasion. The people who are persuaded easily to believe false things cannot contribute much useful new knowledge to their communities (irrespective of how much they might publish).

Locked table statistics and subsequent create index

Just a minor thing to consider: By default in 10g and later index statistics are generated along with an index creation (option COMPUTE STATISTICS in previous releases enabled by default), so a newly created index usually has computed statistics.

10g also introduced the option to lock table statistics.

Now if you lock statistics in 10g in later using DBMS_STATS.LOCK_TABLE_STATS or LOCK_SCHEMA_STATS and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command. Unfortunately there is no corresponding "FORCE" option in CREATE INDEX available to overwrite that behaviour that I'm aware of so it looks like you're only left with two choices:

1. Use a separate DBMS_STATS.GATHER_INDEX_STATS call with the FORCE=>true option to override the lock on the statistics

2. Temporarily unlock the table statistics before creating the index

The first option can be costly if the index is large, the second option requires additional steps to be taken, and it obviously needs to be ensured that the table statistics are not modified while they are unlocked (e.g. by the default statistics job in 10g and later).

A small testcase run on 10.2.0.4 Win32 follows to demonstrate the issue. I got the same result on 11.1.0.7 Win32.

SQL>
SQL> drop table lock_test purge;

Table dropped.

SQL>
SQL> create table lock_test
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;

Table created.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> create index lock_test_idx on lock_test (object_name) compute statistics;
create index lock_test_idx on lock_test (object_name) compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL>
SQL> create index lock_test_idx on lock_test (object_name);

Index created.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------

SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX')
BEGIN dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1

SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX', force=>true)

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> drop index lock_test_idx;

Index dropped.

SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> create index lock_test_idx on lock_test (object_name);

Index created.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> delete from lock_test where rownum <= 500;

500 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter index lock_test_idx rebuild compute statistics;
alter index lock_test_idx rebuild compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL>
SQL> alter index lock_test_idx rebuild;

Index altered.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> alter index lock_test_idx rebuild;

Index altered.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
500 21.06.09

SQL>
SQL> analyze index lock_test_idx compute statistics;
analyze index lock_test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL>
SQL> spool off

The same applies to index rebuilds obviously.

Consequently the hopefully no longer used ANALYZE INDEX ESTIMATE/COMPUTE STATISTICS command can also not be used on locked tables and its indexes.

Avoiding pls-00436 with forall

Workarounds to the FORALL PLS-00436 implementation restriction. July 2005 (updated June 2009)