You should take care about statistics!

18. April 2020 Uncategorized 0

Ever since Oracle introduced the CBO (Cost Based Optimizer) there were some performance issues, mostly because of missing or staled statistics in the database.

I stopped to count the cases, when I just gathered fresh statistics for some table(s) and voilà – the query runs 100x faster!

I will mention just one of the latest cutomer’s issues: a relatively small schema (under 1 GB) gets exported every day from one database – without statistics! – and then imported into another database. As soon as the import job gets done, end users start to query the data. And they claim then:”Uh oh, why is the database so slow!?“

Well, you know …

After my recommendation to gather the fresh statistics for the whole schema after import, the issue has been gone.

But, let me ask you something – do you care manually & proactively about your statistics? Or do you simply let the automatic database job to gather all statistics as needed?

Thinking about 3rd party applications which I faced in the last 15 years, there was only 1 application with its own “statistics concept” to proactively take care about ALL statistics for the involved objects! The others have always blamed the database (or us, the DBAs).

Therefore – my opinion & recommendation is to create your own “statistics concept” to proactively take care about all objects (tables & indexes) used within your applications.

What does it mean? It means, you shouldn’t blindly rely on the automatic task ‘auto optimizer stats collection‘, at least for two reasons:

Instead of that, for every application you run, you should create some sort of “statistics concept”, to have complete control of the statistics.

Here are some tips:

  • If you do not review & make use of the results of the other two auto tasks (segment space advisor & sql tuning advisor), then you should disable them:
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);

  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
  • If you want stable & predictable execution plans, you should lock the statistics, and gather a fresh ones using the parameter FORCE, which allows you to override any lock on statistics.
DBMS_STATS.LOCK_TABLE_STATS
DBMS_STATS.LOCK_PARTITION_STATS
DBMS_STATS.GATHER_TABLE_STATS (
...
force => TRUE);
BEGIN
  dbms_stats.gather_table_stats(
    ownname => 'APP_SCHEMA',
    tabname => 'APP_TABLE',
    estimate_percent => 100,
    granularity => 'ALL',
    method_opt => 'for columns size 1 ORDER_DATE, CUSTOMER_ID, ORDER_ID',
    cascade => TRUE
  );
END;
/
  • If you are doing a bulk data load into DWH table with partitions, that will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table is often very time consuming.

    To avoid it, you can use INCREMENTAL statistics gathering.

    INCREMENTAL determines whether the global statistics for a partitioned table are maintained without performing a full table scan.

    When a table is partitioned, an application typically loads data into a new partition.
    As new partitions are added and data is loaded, global table statistics must be kept up to date.
    If the following conditions are met, then the database updates the global table statistics by scanning only the changed partitions instead of the entire table:
    • The INCREMENTAL value for the partitioned table is set to TRUE.
    • The PUBLISH value for the partitioned table is set to TRUE.
    • The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

      If the INCREMENTAL value for the partitioned table was set to FALSE (default value), then the database uses a full table scan to maintain the global statistics.
  dbms_stats.set_table_prefs('APP_SCHEMA','APP_PART_TB','INCREMENTAL','TRUE');
dbms_stats.set_table_prefs('APP_SCHEMA','APP_PART_TB','PUBLISH','TRUE');
DBMS_STATS.SET_TABLE_PREFS('APP_SCHEMA','APP_PART_T','APPROXIMATE_NDV_ALGORITHM','HYPERLOGLOG');
DBMS_STATS.SET_TABLE_PREFS('APP_SCHEMA','APP_PART_T','INCREMENTAL_STALENESS','NULL');
DBMS_STATS.SET_TABLE_PREFS('APP_SCHEMA','APP_PART_T','INCREMENTAL_STALENESS','USE_STALE_PERCENT, USE_LOCKED_STATS');
DBMS_STATS.SET_TABLE_PREFS('APP_SCHEMA','APP_PART_T','TABLE_CACHED_BLOCKS', 16);
DBMS_STATS.SET_TABLE_PREFS('APP_SCHEMA','APP_PART_T','STALE_PERCENT', 0.1);
DBMS_STATS.SET_TABLE_PREFS('APP_SCHEMA','APP_PART_T','DEGREE', DBMS_STATS.AUTO_DEGREE)
  • You can also sometimes just copy the statistics from some existing partition(s).
DBMS_STATS.COPY_TABLE_STATS
DBMS_STATS.GATHER_SYSTEM_STATISTICS
DBMS_STATS GATHER_FIXED_OBJECTS_STATS

I will stop here, because you should have now more than enough details to start making your own statistics concept.

Here are some useful articles about this topic (random ordered), which I also used as source for this blog post:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.