Real-time Statistics ORA-00600s / Integer Overflow

Unfortunately this is just an anecdotal long after-the-fact note on issues observed on upgrade from 11.2.0.4 to 19.6 with new feature real-time statistics. I thought I had joted this down previously, but doesn’t appear to be another article published or in draft. Issue was not reproducible outside of the application unfortunately and was also intermittent.

Feature was causing several issues in one particular area of the application.

First off was ORA-00600s in the processing with immediate disconnect on the processing session (apart from trace file generation)

Second was sometimes leaving evidence of integer overflow issues in column statistics.

Third, “corrupt” statistics then leading to subsequent bad execution plans for complicated queries evidenced by MERGE JOIN / MERGE JOIN CARTESIAN and row estimates of max 18E.

Examples of the latter complex queries with cardinality issues leading to bad plans and severe performance problems, when distilled to simplest form, could be seen as follows:

explain plan for
SELECT *
FROM   XXXXX
WHERE  business_date     = :busines_date
AND    id                = :id
AND    processed_flag    = 'Y';

select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    18E|    15E|   647K  (1)| 00:00:26 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| XXXXXXXXXXXX |    18E|    15E|   647K  (1)| 00:00:26 |
|*  2 |   INDEX RANGE SCAN                  | XXXXXXXXXXXX |   733K|       |  1538   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: statistics for conventional DML
explain plan for
SELECT /*+ opt_param('optimizer_features_enable','18.1.0') */ *
FROM   XXXXX
WHERE  business_date     = :busines_date
AND    id                = :id
AND    processed_flag    = 'Y';

select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     5 |  1405 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| XXXXXXXXXXXX |     5 |  1405 |    13   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | XXXXXXXXXXXX |    10 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

The root cause corruption evidenced as follows:

select num_nulls, notes from dba_tab_col_statistics where....

NUM_NULLS                SAMPLE_SIZE NOTES
----------------------   ----------- ------------------
-917567866115141580119           267 STATS_ON_CONVENTIONAL_DML
                   500           267 STATS_ON_CONVENTIONAL_DML
                   500           267 STATS_ON_CONVENTIONAL_DML
    -10558214104677900           267 STATS_ON_CONVENTIONAL_DML
-917567866114408000000           267 STATS_ON_CONVENTIONAL_DML
-917567866114408000000           267 STATS_ON_CONVENTIONAL_DML
         -115153305100           267 STATS_ON_CONVENTIONAL_DML

It should be obvious that NUM_NULLs cannot be < 1 and also some strange, unsuitable values in some parts on LOW_VALUE which I’ve omitted from output above.

Traction with Oracle Support was disappointing and, unfortunately, once they knew we didn’t have a problem if we just turned the feature off, that was good enough for them and to be honest we just needed to move on in our testing – this was back in August 2020.

So, feature real-time statistics is turned off across the board via the parameters below which being underscore parameters we had Oracle Support’s blessing to do.

_optimizer_use_stats_on_conventionaml_dml

_optimizer_gather_stats_on conventional_dml

2 Responses to Real-time Statistics ORA-00600s / Integer Overflow

  1. hourim says:

    Hi Dom

    Thanks a lot for sharing this information. I was wondering why Oracle has decided to disable Real time Statistics as from 19c RU10:

    https://blogs.oracle.com/optimizer/real-time-statistics-parameter

    This might be one of the reasons that pushed them to disable it.

    I will check that “Notes” on dba_tab_col_statistics to see whether we have negative num_nulls

    Best Regards
    Mohamed

    • Dom Brooks says:

      Thanks Mohamed.

      Also some interesting food for thought on doc Id 2747507.1
      – not tracking DELETE operations
      – only expanding the high/low value boundaries and number of rows
      – Not touching histograms
      – Issues with NUM_NULL for massive deletes/updates although I didn’t manage to think that one through properly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: