Oracle Stale Optimizer Statistics Gather Guard with Kestra

id: oracle-stale-optimizer-stats-gather-guard
namespace: company.team

description: |
  Find Oracle tables whose optimizer statistics no longer describe the table, either
  because DBA_TAB_STATISTICS.STALE_STATS flipped to YES after enough DML or because the
  table was never analyzed at all, gather them per table with DBMS_STATS.GATHER_TABLE_STATS
  inside a per run cap, then read DBA_TAB_STATISTICS again and fail the run if a target is
  still stale.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: Oracle JDBC URL
    defaults: "jdbc:oracle:thin:@//localhost:1521/FREEPDB1"

  - id: username
    type: STRING
    displayName: Database user
    description: A user that can read dba_tab_statistics plus dba_tab_modifications
      (SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY). Gathering statistics on
      another schema's table needs ANALYZE ANY. Flushing the monitoring info
      needs ANALYZE ANY too. A DBA role covers all three.
    defaults: SYSTEM

  - id: password
    type: STRING
    displayName: Database password
    defaults: "{{ secret('ORACLE_PASSWORD') }}"

  - id: table_owners
    type: ARRAY
    itemType: STRING
    displayName: Schema owners to check
    description: Application schemas whose table statistics are checked. System
      schemas are left out, because Oracle's own automatic job owns those.
    defaults:
      - APP

  - id: include_never_analyzed
    type: BOOL
    displayName: Include tables with no statistics at all
    description: A table that was never analyzed has no row in the staleness report
      because STALE_STATS is null rather than YES. It is the worst case for the
      optimizer, so it is treated as a finding by default.
    defaults: true

  - id: max_tables_per_run
    type: INT
    displayName: Maximum tables gathered per run
    description: The per run cap. Findings past the cap are reported as DEFERRED so
      the next pass picks them up. This is how a schema with hundreds of stale
      tables is worked through without one run sampling all of them at once.
    defaults: 5

  - id: max_table_mb
    type: FLOAT
    displayName: Largest table to gather, in MB
    description: A table whose segments are bigger than this is reported only, never
      gathered. A gather on a very large table is real IO against the live
      instance, so the ceiling is explicit rather than implied.
    defaults: 2048.0

  - id: estimate_percent
    type: SELECT
    displayName: DBMS_STATS sample size
    description: AUTO passes DBMS_STATS.AUTO_SAMPLE_SIZE, which on 11g and later is
      a single pass hash based algorithm that reads every row yet costs about
      what a 10 percent sample used to. A fixed percentage reads less but gives
      the optimizer worse distinct value counts.
    values:
      - AUTO
      - "1"
      - "10"
      - "100"
    defaults: AUTO

  - id: degree
    type: SELECT
    displayName: Parallel degree for the gather
    description: 1 keeps the gather serial, which is the safe default on a busy
      instance. AUTO passes DBMS_STATS.AUTO_DEGREE and lets Oracle pick, which
      is faster on a large table but takes parallel servers away from user
      queries.
    values:
      - "1"
      - "2"
      - "4"
      - AUTO
    defaults: "1"

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    description: Allow DBMS_STATS.GATHER_TABLE_STATS to run. Off by default.
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for any gather to run.
    defaults: true

variables:
  history_sql: |
    SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION AS retention_days,
           NVL(TO_CHAR(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY, 'YYYY-MM-DD HH24:MI:SS'), 'none') AS history_since
      FROM dual

  totals_sql: |
    SELECT COUNT(*) AS table_count,
           NVL(SUM(CASE WHEN s.stale_stats = 'YES' THEN 1 ELSE 0 END), 0) AS stale_count,
           NVL(SUM(CASE WHEN s.stale_stats = 'NO' THEN 1 ELSE 0 END), 0) AS current_count,
           NVL(SUM(CASE WHEN s.last_analyzed IS NULL THEN 1 ELSE 0 END), 0) AS never_analyzed_count,
           NVL(SUM(CASE WHEN s.stattype_locked IS NOT NULL THEN 1 ELSE 0 END), 0) AS locked_count,
           NVL(SUM(s.num_rows), 0) AS stat_rows_total,
           NVL(TO_CHAR(MIN(s.last_analyzed), 'YYYY-MM-DD HH24:MI:SS'), 'never') AS oldest_analyzed
      FROM dba_tab_statistics s
      JOIN dba_tables t
        ON t.owner = s.owner AND t.table_name = s.table_name
     WHERE s.owner IN ('{{ inputs.table_owners | join("','") | upper }}')
       AND s.object_type = 'TABLE'
       AND t.temporary = 'N'
       AND t.nested = 'NO'

  audit_sql: |
    SELECT owner,
           table_name,
           stale_stats,
           stat_num_rows,
           sample_rows,
           last_analyzed,
           stats_age_days,
           segment_mb,
           dml_since_gather,
           locked,
           CASE
             WHEN locked <> 'NONE' THEN 'REPORT_ONLY_LOCKED'
             WHEN segment_mb > {{ inputs.max_table_mb }} THEN 'REPORT_ONLY_TOO_BIG'
             WHEN rn <= {{ inputs.max_tables_per_run }} THEN 'GATHER'
             ELSE 'DEFERRED'
           END AS action,
           fix_sql
      FROM (
        SELECT f.*,
               ROW_NUMBER() OVER (
                 PARTITION BY CASE WHEN f.locked = 'NONE' AND f.segment_mb <= {{ inputs.max_table_mb }} THEN 0 ELSE 1 END
                 ORDER BY CASE WHEN f.raw_analyzed IS NULL THEN 0 ELSE 1 END,
                          f.dml_since_gather DESC,
                          f.segment_mb,
                          f.table_name
               ) AS rn
          FROM (
            SELECT s.owner AS owner,
                   s.table_name AS table_name,
                   NVL(s.stale_stats, 'NO_STATS') AS stale_stats,
                   NVL(s.num_rows, -1) AS stat_num_rows,
                   NVL(s.sample_size, -1) AS sample_rows,
                   NVL(TO_CHAR(s.last_analyzed, 'YYYY-MM-DD HH24:MI:SS'), 'never') AS last_analyzed,
                   s.last_analyzed AS raw_analyzed,
                   NVL(ROUND(SYSDATE - s.last_analyzed, 2), -1) AS stats_age_days,
                   NVL(ROUND(seg.bytes / 1048576, 2), 0) AS segment_mb,
                   NVL(m.inserts, 0) + NVL(m.updates, 0) + NVL(m.deletes, 0) AS dml_since_gather,
                   NVL(s.stattype_locked, 'NONE') AS locked,
                   'BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => ''' || s.owner
                     || ''', tabname => ''' || s.table_name
                     || ''', estimate_percent => {{ inputs.estimate_percent == "AUTO" ? "DBMS_STATS.AUTO_SAMPLE_SIZE" : inputs.estimate_percent }}'
                     || ', degree => {{ inputs.degree == "AUTO" ? "DBMS_STATS.AUTO_DEGREE" : inputs.degree }}'
                     || ', method_opt => ''FOR ALL COLUMNS SIZE AUTO'', cascade => TRUE, no_invalidate => FALSE); END;' AS fix_sql
              FROM dba_tab_statistics s
              JOIN dba_tables t
                ON t.owner = s.owner AND t.table_name = s.table_name
              LEFT JOIN ( SELECT owner, segment_name, SUM(bytes) AS bytes
                            FROM dba_segments
                           WHERE segment_type LIKE 'TABLE%'
                           GROUP BY owner, segment_name ) seg
                ON seg.owner = s.owner AND seg.segment_name = s.table_name
              LEFT JOIN dba_tab_modifications m
                ON m.table_owner = s.owner AND m.table_name = s.table_name AND m.partition_name IS NULL
             WHERE s.owner IN ('{{ inputs.table_owners | join("','") | upper }}')
               AND s.object_type = 'TABLE'
               AND t.temporary = 'N'
               AND t.nested = 'NO'
               AND ( s.stale_stats = 'YES'
                     OR ( s.last_analyzed IS NULL AND {{ inputs.include_never_analyzed ? 1 : 0 }} = 1 ) )
          ) f
      )
     ORDER BY action, dml_since_gather DESC, table_name

  reverify_sql: |
    SELECT s.owner AS owner,
           s.table_name AS table_name,
           NVL(s.stale_stats, 'NO_STATS') AS stale_stats,
           NVL(s.num_rows, -1) AS stat_num_rows,
           NVL(TO_CHAR(s.last_analyzed, 'YYYY-MM-DD HH24:MI:SS'), 'never') AS last_analyzed,
           CASE
             WHEN s.last_analyzed IS NULL THEN 'still has no statistics at all'
             WHEN s.last_analyzed < TO_DATE('{{ outputs.mark_time.row.DB_CLOCK }}', 'YYYY-MM-DD HH24:MI:SS') THEN 'gather did not run, last_analyzed is older than the clock read before the pass'
             ELSE 'gathered but STALE_STATS still reads ' || NVL(s.stale_stats, 'NO_STATS')
           END AS reason
      FROM dba_tab_statistics s
     WHERE INSTR(',{{ outputs.targets.values.gather_names }},', ',' || s.owner || '.' || s.table_name || ',') > 0
       AND s.object_type = 'TABLE'
       AND ( s.last_analyzed IS NULL
             OR s.last_analyzed < TO_DATE('{{ outputs.mark_time.row.DB_CLOCK }}', 'YYYY-MM-DD HH24:MI:SS')
             OR NVL(s.stale_stats, 'NO_STATS') = 'YES' )
     ORDER BY s.owner, s.table_name

tasks:
  - id: flush_monitoring
    type: io.kestra.plugin.jdbc.oracle.Query
    description: >-
      DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO writes the in memory DML
      counters down into the dictionary, so a staleness flag derived from a
      stale counter cannot happen. Oracle also writes them down on its own
      schedule. On the Oracle Database Free 23ai test rig both
      DBA_TAB_STATISTICS.STALE_STATS and DBA_TAB_MODIFICATIONS already read the
      live counters, so this call changed nothing there. It stays because that
      write down interval is an implementation detail rather than a contract,
      the call is idempotent plus it writes no user data, touches no statistics
      and changes no plan. A detector that misses the load it was built for is
      the expensive failure here.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: NONE
    sql: "BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;"

  - id: stats_history
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. How far back DBMS_STATS.RESTORE_TABLE_STATS can roll a
      gather on this instance. Gathering does not destroy the old statistics, it
      supersedes them. The superseded copy stays inside this window.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH_ONE
    sql: "{{ render(vars.history_sql) }}"

  - id: totals_before
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. How many tables the checked schemas hold, split into
      current, stale, never analyzed plus statistics locked, with the oldest
      LAST_ANALYZED.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH_ONE
    sql: "{{ render(vars.totals_sql) }}"

  - id: audit
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. Lists every table whose STALE_STATS is YES or that has
      no statistics at all, with NUM_ROWS, LAST_ANALYZED, the sample size, the
      segment size in MB, the DML row count since the last gather plus the exact
      DBMS_STATS.GATHER_TABLE_STATS call for each. The row count and the size
      are the cost of the fix, which is why they are reported before anything is
      enabled.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    sql: "{{ render(vars.audit_sql) }}"

  - id: targets
    type: io.kestra.plugin.core.output.OutputValues
    description: >-
      Split the findings by what can actually be gathered this pass. Tables
      inside the per run cap are GATHER. Tables past the cap are DEFERRED for
      the next pass. Tables whose statistics are locked plus tables over the
      size ceiling are reported only.
    values:
      gather_sql: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "GATHER") |
        .FIX_SQL]') | first | toJson }}
      gather_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "GATHER")] |
        length') | first }}
      gather_names: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "GATHER") | .OWNER
        + "." + .TABLE_NAME] | (if length == 0 then ["__NO_TARGET__"] else .
        end) | join(",")') | first }}
      gather_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "GATHER") | .OWNER
        + "." + .TABLE_NAME + " [STALE_STATS " + .STALE_STATS + ", " + (if
        (.STAT_NUM_ROWS | tonumber) < 0 then "no statistics yet" else
        "statistics say " + (.STAT_NUM_ROWS | tostring) + " rows" end) + ", last
        analyzed " + .LAST_ANALYZED + ", " + (.SEGMENT_MB | tostring) + " MB, "
        + (.DML_SINCE_GATHER | tostring) + " DML rows since]"] | join("; ")') |
        first }}
      gather_rows: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "GATHER") |
        .STAT_NUM_ROWS | tonumber | select(. > 0)] | (add // 0)') | first }}
      gather_mb: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "GATHER") |
        .SEGMENT_MB | tonumber] | (add // 0)') | first }}
      deferred_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "DEFERRED")] |
        length') | first }}
      deferred_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "DEFERRED") |
        .OWNER + "." + .TABLE_NAME + " [" + (.SEGMENT_MB | tostring) + " MB, " +
        (.DML_SINCE_GATHER | tostring) + " DML rows since]"] | join("; ")') |
        first }}
      locked_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION ==
        "REPORT_ONLY_LOCKED")] | length') | first }}
      locked_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION ==
        "REPORT_ONLY_LOCKED") | .OWNER + "." + .TABLE_NAME + " [statistics
        locked: " + .LOCKED + "]"] | join("; ")') | first }}
      toobig_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION ==
        "REPORT_ONLY_TOO_BIG")] | length') | first }}
      toobig_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION ==
        "REPORT_ONLY_TOO_BIG") | .OWNER + "." + .TABLE_NAME + " [" +
        (.SEGMENT_MB | tostring) + " MB, over the ceiling]"] | join("; ")') |
        first }}
      never_analyzed_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.LAST_ANALYZED == "never") |
        .OWNER + "." + .TABLE_NAME] | join("; ")') | first }}

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      Oracle optimizer statistics on {{ inputs.jdbc_url }} for schemas {{
      inputs.table_owners | jq('map(ascii_upcase) | join(", ")') | first }}: {{
      outputs.totals_before.row.TABLE_COUNT }} table(s), {{
      outputs.totals_before.row.CURRENT_COUNT }} current, {{
      outputs.totals_before.row.STALE_COUNT }} with STALE_STATS YES, {{
      outputs.totals_before.row.NEVER_ANALYZED_COUNT }} never analyzed, {{
      outputs.totals_before.row.LOCKED_COUNT }} with locked statistics. Oldest
      LAST_ANALYZED {{ outputs.totals_before.row.OLDEST_ANALYZED }}. {{
      outputs.audit.size }} finding(s): {{ outputs.targets.values.gather_count
      }} gatherable now ({{ outputs.targets.values.gather_detail }}), {{
      outputs.targets.values.deferred_count }} deferred past the cap of {{
      inputs.max_tables_per_run }} ({{ outputs.targets.values.deferred_detail
      }}), {{ outputs.targets.values.locked_count }} with locked statistics ({{
      outputs.targets.values.locked_detail }}), {{
      outputs.targets.values.toobig_count }} over the {{ inputs.max_table_mb }}
      MB ceiling ({{ outputs.targets.values.toobig_detail }}). Tables with no
      statistics at all: {{ outputs.targets.values.never_analyzed_detail == '' ?
      'none' : outputs.targets.values.never_analyzed_detail }}. This pass would
      sample about {{ outputs.targets.values.gather_rows }} row(s) across {{
      outputs.targets.values.gather_mb }} MB with estimate_percent {{
      inputs.estimate_percent }} at degree {{ inputs.degree }}. A gather is
      reversible for {{ outputs.stats_history.row.RETENTION_DAYS }} day(s)
      through DBMS_STATS.RESTORE_TABLE_STATS, with history on this instance back
      to {{ outputs.stats_history.row.HISTORY_SINCE }}.

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.audit.size > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'Oracle ' ~ inputs.jdbc_url ~ ': ' ~ outputs.audit.size ~ ' table(s) have optimizer statistics that no longer describe the table, so the cost based optimizer is costing plans from the wrong row counts. Gatherable now: ' ~ (outputs.targets.values.gather_detail == '' ? 'none' : outputs.targets.values.gather_detail) ~ '. Deferred past the per run cap of ' ~ inputs.max_tables_per_run ~ ': ' ~ (outputs.targets.values.deferred_detail == '' ? 'none' : outputs.targets.values.deferred_detail) ~ '. Statistics locked: ' ~ (outputs.targets.values.locked_detail == '' ? 'none' : outputs.targets.values.locked_detail) ~ '. Over the ' ~ inputs.max_table_mb ~ ' MB ceiling: ' ~ (outputs.targets.values.toobig_detail == '' ? 'none' : outputs.targets.values.toobig_detail) ~ '. This pass would sample about ' ~ outputs.targets.values.gather_rows ~ ' row(s) across ' ~ outputs.targets.values.gather_mb ~ ' MB.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' DBMS_STATS.GATHER_TABLE_STATS is armed for this run.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: state_route
        type: io.kestra.plugin.core.flow.If
        description: Explain the findings this flow will not gather. Locked statistics
          plus an oversized segment are deliberate states, not bugs, so neither
          is handed to DBMS_STATS.
        condition: "{{ (outputs.targets.values.locked_count | number) +
          (outputs.targets.values.toobig_count | number) +
          (outputs.targets.values.deferred_count | number) > 0 }}"
        then:
          - id: state_explained
            type: io.kestra.plugin.core.log.Log
            level: WARN
            message: >-
              {{ outputs.targets.values.locked_count }} table(s) have locked
              statistics ({{ outputs.targets.values.locked_detail }}). A lock is
              usually somebody pinning a plan on purpose, often on a table whose
              real distribution misleads the optimizer, so DBMS_STATS raises
              ORA-20005 rather than overwriting it. Unlocking is a decision for
              whoever locked it, which is why this flow reports instead of
              forcing it. {{ outputs.targets.values.toobig_count }} table(s) are
              over the {{ inputs.max_table_mb }} MB ceiling ({{
              outputs.targets.values.toobig_detail }}). A gather on a segment
              that size is real IO against a live instance, so it belongs in a
              maintenance window with its own sample size, not in a scheduled
              sweep. {{ outputs.targets.values.deferred_count }} table(s) are
              past the per run cap of {{ inputs.max_tables_per_run }} ({{
              outputs.targets.values.deferred_detail }}). They stay stale until
              the next pass, by design. Raise the cap to work through a backlog
              faster, lower it to keep each run small.
        else:
          - id: state_clean
            type: io.kestra.plugin.core.log.Log
            message: "Every finding is inside the per run cap, under the size ceiling plus
              unlocked, so DBMS_STATS.GATHER_TABLE_STATS can address all of them
              in this pass."

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run and
          (outputs.targets.values.gather_count | number) > 0 }}"
        then:
          - id: mark_time
            type: io.kestra.plugin.jdbc.oracle.Query
            description: Read the database clock before the gather, so the re-read can
              require a LAST_ANALYZED at or after this moment. That proves the
              gather ran rather than trusting a flag that may have been NO
              already.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.username }}"
            password: "{{ inputs.password }}"
            fetchType: FETCH_ONE
            sql: "SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS db_clock FROM dual"

          - id: remediate
            type: io.kestra.plugin.core.flow.Loop
            description: One DBMS_STATS.GATHER_TABLE_STATS call per table. concurrencyLimit
              1 on purpose, because two gathers at once compete for the same
              buffer cache plus parallel servers, which is how a statistics
              refresh turns into the outage it was meant to prevent.
            concurrencyLimit: 1
            values: "{{ outputs.targets.values.gather_sql }}"
            tasks:
              - id: gather_one
                type: io.kestra.plugin.jdbc.oracle.Query
                description: An anonymous PL/SQL block calling DBMS_STATS.GATHER_TABLE_STATS for
                  one table. allowFailure, so a table that refuses the gather
                  does not stop the rest of the pass. The re-read is what
                  decides the run.
                allowFailure: true
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: NONE
                sql: "{{ item.value }}"

          - id: flush_after
            type: io.kestra.plugin.jdbc.oracle.Query
            description: Flush the monitoring info again before the re-read. A gather resets
              the DML counters for its own table, so this is here for the tables
              it did not reach.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.username }}"
            password: "{{ inputs.password }}"
            fetchType: NONE
            sql: "BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;"

          - id: reverify
            type: io.kestra.plugin.jdbc.oracle.Query
            description: Read dba_tab_statistics again for the exact tables that were
              gathered. A table only passes when STALE_STATS is not YES, it has
              statistics at all plus its LAST_ANALYZED is at or after the clock
              reading taken before the pass.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.username }}"
            password: "{{ inputs.password }}"
            fetchType: FETCH
            sql: "{{ render(vars.reverify_sql) }}"

          - id: totals_after
            type: io.kestra.plugin.jdbc.oracle.Query
            description: Re-read the per state counts so the run reports what the pass
              actually changed, including the total row count the dictionary now
              believes.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.username }}"
            password: "{{ inputs.password }}"
            fetchType: FETCH_ONE
            sql: "{{ render(vars.totals_sql) }}"

          - id: verify_result
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.reverify.size > 0 }}"
            then:
              - id: remediation_failed
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: >-
                  {{ outputs.reverify.size }} of {{
                  outputs.targets.values.gather_count }} gathered table(s) are
                  still not current: {{ outputs.reverify.rows | jq('map(.OWNER +
                  "." + .TABLE_NAME + " [STALE_STATS " + .STALE_STATS + ",
                  NUM_ROWS " + (.STAT_NUM_ROWS | tostring) + ", last analyzed "
                  + .LAST_ANALYZED + ", " + .REASON + "]") | join(", ")') |
                  first }}. The schemas now hold {{
                  outputs.totals_after.row.CURRENT_COUNT }} current, {{
                  outputs.totals_after.row.STALE_COUNT }} stale, {{
                  outputs.totals_after.row.NEVER_ANALYZED_COUNT }} never
                  analyzed, from {{ outputs.totals_before.row.CURRENT_COUNT }},
                  {{ outputs.totals_before.row.STALE_COUNT }}, {{
                  outputs.totals_before.row.NEVER_ANALYZED_COUNT }}. ORA-20005
                  means the statistics are locked, so DBMS_STATS refused.
                  ORA-01031 means the user has no ANALYZE ANY for another
                  schema's table. A table that keeps reading YES right after a
                  successful gather is being written faster than the gather
                  samples it, so that one needs a quieter window.
            else:
              - id: remediated
                type: io.kestra.plugin.core.log.Log
                message: >-
                  Gathered statistics on {{
                  fromJson(outputs.targets.values.gather_sql) | length }}
                  table(s) with estimate_percent {{ inputs.estimate_percent }}
                  at degree {{ inputs.degree }}: {{
                  outputs.targets.values.gather_detail }}. Every one now reads
                  STALE_STATS NO with a LAST_ANALYZED at or after {{
                  outputs.mark_time.row.DB_CLOCK }}, the database clock read
                  before the pass. The checked schemas went from {{
                  outputs.totals_before.row.CURRENT_COUNT }} current, {{
                  outputs.totals_before.row.STALE_COUNT }} stale, {{
                  outputs.totals_before.row.NEVER_ANALYZED_COUNT }} never
                  analyzed to {{ outputs.totals_after.row.CURRENT_COUNT }}
                  current, {{ outputs.totals_after.row.STALE_COUNT }} stale, {{
                  outputs.totals_after.row.NEVER_ANALYZED_COUNT }} never
                  analyzed. The dictionary row count for these schemas moved
                  from {{ outputs.totals_before.row.STAT_ROWS_TOTAL }} to {{
                  outputs.totals_after.row.STAT_ROWS_TOTAL }}, which is the
                  number the optimizer costs every new plan from. {{
                  outputs.targets.values.deferred_count }} table(s) are still
                  deferred for the next pass. Roll this back within {{
                  outputs.stats_history.row.RETENTION_DAYS }} day(s) with
                  DBMS_STATS.RESTORE_TABLE_STATS if a plan regresses the other
                  way.
        else:
          - id: no_change
            type: io.kestra.plugin.core.log.Log
            message: >-
              No gather ran. auto_remediate={{ inputs.auto_remediate }},
              dry_run={{ inputs.dry_run }}, {{
              outputs.targets.values.gather_count }} gatherable target(s). The
              findings are reported with their row counts plus segment sizes so
              the cost of the fix is known before it is enabled. Nothing in the
              dictionary changed.
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: >-
          Every table in the checked schemas has current optimizer statistics.
          {{ outputs.totals_before.row.TABLE_COUNT }} table(s) checked, {{
          outputs.totals_before.row.CURRENT_COUNT }} with STALE_STATS NO, oldest
          LAST_ANALYZED {{ outputs.totals_before.row.OLDEST_ANALYZED }}.

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'Oracle stale optimizer stats gather guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). Tables may still be feeding the optimizer row counts from before the last load. Check the logs.'} | toJson }}

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled and always in safe mode (report only).
    cron: "0 4 * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true

Oracle's cost based optimizer picks a plan from the statistics it has, not from the table that exists. After a bulk load, a migration or steady churn past roughly ten percent of the rows, DBA_TAB_STATISTICS.STALE_STATS flips to YES and the optimizer starts costing plans from numbers that describe a table from last month.

Then a plan regresses. A nested loop that was right for a thousand rows now drives against ten million. A query that took a second takes an hour. Nothing errors. Nothing is logged. It reaches you as "the database is slow today".

This blueprint reads the staleness flag, reports what a gather would cost before you enable it, gathers inside a cap, then proves the flag cleared.

This blueprint was created by zkasuran.

Findings

Measured on the test rig below, on a 2000 row table that then took 600 more rows.

	statistics say	the table holds	the optimizer's own estimate
before the gather	2000 rows	2600 rows	2000 rows
after the gather	2600 rows	2600 rows	2600 rows

That last column is EXPLAIN PLAN cardinality for SELECT * FROM app.orders, read out of PLAN_TABLE. The optimizer was planning for a table 23 percent smaller than the real one, which is the whole mechanism behind the bad join order.

Two things the catalog view does not say out loud:

A table that was never analyzed has STALE_STATS of NULL, not YES. A detector that filters on STALE_STATS = 'YES' misses the worst case in the schema, the table the optimizer knows nothing about at all. This flow treats a null LAST_ANALYZED as a finding in its own right. include_never_analyzed controls it.
STALE_STATS is derived from DML counters Oracle keeps in memory and writes down periodically, which is why DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO exists. On the rig the flag did not need it. 400 inserts into a 3000 row table read YES straight away. DBA_TAB_MODIFICATIONS already showed all 800 rows written since the last gather, identical before and after the flush. Oracle Database Free 23ai reads the live counters for both views. The flow still calls the flush first, because that write down interval is an implementation detail rather than a contract, the call is idempotent plus it writes no user data and no statistics.

Safety

This guard is deliberately not behind a Pause, which is a difference from the destructive guards in this family. Gathering statistics writes no user data, changes no schema plus drops nothing. It samples the table and updates the dictionary, so the old statistics are superseded rather than destroyed: Oracle keeps the previous version for DBMS_STATS.GET_STATS_HISTORY_RETENTION days (31 by default) and DBMS_STATS.RESTORE_TABLE_STATS rolls a table back to any point inside that window. The flow reads the retention plus the earliest available history on your instance and prints both, so the rollback window is a measured fact in the run log rather than an assumption. It does not write its own stats table with DBMS_STATS.EXPORT_TABLE_STATS, because the automatic history already covers the rollback and a second copy is one more thing to own. If your instance has had the retention set to 0, the history is off and an export is worth adding.

The real risk here is cost, not consent. A gather on a large table is genuine IO against a live instance. So this flow carries three cost controls instead of an approval prompt:

max_tables_per_run caps how many tables one pass touches. Findings past the cap are reported as DEFERRED plus picked up by the next run, so a schema with a hundred stale tables is worked through in slices rather than all at once.
max_table_mb is a hard ceiling. A table over it is reported, never gathered, because that one belongs in a maintenance window with its own sample size.
concurrencyLimit: 1 on the gather loop, so two gathers never contend for the same buffer cache plus parallel servers.

On top of those, every finding is reported with its row count, its segment size in MB plus the DML volume since the last gather. The summary adds up what the pass would sample before anything is enabled. You can see the bill before you pay it.

Two more things the flow will not do. A table whose statistics are locked is reported, never gathered: a lock is usually somebody pinning a plan on purpose, DBMS_STATS raises ORA-20005 rather than overwriting it, plus unlocking is a decision for whoever locked it. And the gather passes no_invalidate => FALSE, which invalidates the dependent cursors immediately so the next execution gets a plan from the new numbers. That is the point of the fix. It does mean a short hard parse wave on a busy instance.

How it works

flush_monitoring (io.kestra.plugin.jdbc.oracle.Query) calls DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO so the staleness flags reflect the DML of the last few minutes rather than the last flush cycle.
stats_history (read-only) reads GET_STATS_HISTORY_RETENTION plus GET_STATS_HISTORY_AVAILABILITY, the window a gather can be rolled back inside.
totals_before (read-only) counts the tables in the checked schemas as current, stale, never analyzed plus statistics locked, with the oldest LAST_ANALYZED and the total row count the dictionary believes.
audit (read-only) lists every table with STALE_STATS = 'YES' or no statistics at all, joined to DBA_SEGMENTS for size and DBA_TAB_MODIFICATIONS for the DML since the last gather, then builds the exact DBMS_STATS.GATHER_TABLE_STATS call for each. A ROW_NUMBER() window applies the per run cap, so the worst offenders go first: tables with no statistics, then the heaviest DML, then the smallest segment.
targets (io.kestra.plugin.core.output.OutputValues) splits the findings into gatherable, deferred, locked plus over the size ceiling, then sums the rows plus MB the pass would sample. summary logs all of it.
route (io.kestra.plugin.core.flow.If):
- clean: every table is current, so it logs the counts and stops.
- findings: alert posts the groups plus the sampling cost to Slack. state_route explains the locked, oversized plus deferred tables when there are any.
  - remediate_gate runs only when auto_remediate is true, dry_run is false plus there is at least one gatherable table.
  - mark_time reads the database clock before anything changes.
  - remediate (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs one DBMS_STATS.GATHER_TABLE_STATS per table with allowFailure, so one refusal does not stop the pass.
  - flush_after flushes the monitoring info again, reverify re-reads dba_tab_statistics for those exact tables, totals_after re-reads the counts.
  - verify_result fails the run through io.kestra.plugin.core.execution.Fail if any target still reads YES, still has no statistics or carries a LAST_ANALYZED older than the clock read before the pass.
errors posts to Slack if the flow fails for any reason.

What you get

A detector that catches the table with no statistics at all, not only the one flagged YES, because STALE_STATS is null for the first case.
A flush before the read, so the staleness flag can never be read off a counter that has not been written down yet.
The cost of the fix in the report: rows, MB plus DML volume per table, totalled for the pass, before anything is enabled.
A per run cap that turns a hundred stale tables into a queue instead of one huge pass, with the leftovers named in the log.
Locked statistics plus oversized tables reported rather than forced.
A re-read that fails the run when a gather did not land, keyed on the database clock so a table that was already current cannot be counted as a success.
The rollback window for the gather, read live off the instance.
Slack alerts on the findings and on any failure.

Who it's for

Teams whose ETL finishes with a bulk load plus no gather, where the next morning's reports run on yesterday's row counts.
DBAs on an instance where AUTOTASK was turned off or where the maintenance window is too short to reach every table, so the automatic job never gets to the big ones.
Anyone chasing a query that got slow with no code change plus no data problem, where the plan flipped under them.
Platform teams who want the statistics policy written down as a schedule plus a cap rather than as a DBMS_SCHEDULER job nobody reads.

Why orchestrate this with Kestra

Oracle's own auto optimizer stats collection task is the usual answer. It is a good one until the maintenance window is too short, the task is disabled or a load finishes at 09:00 and the window is at 22:00. When it does skip a table, it says so inside the database, in a view nobody opens. Kestra puts the check outside: it reads DBA_TAB_STATISTICS on a schedule, posts what is stale to Slack with the cost of fixing it, gathers a bounded slice when you arm it, then proves the flag cleared by reading the dictionary again plus failing the run if it did not. Every run keeps its logs, so you can show which table was stale, by how much, plus what the gather changed.

Tested end to end

On Kestra 2.0.5 OSS against Oracle Database Free 23ai (gvenzl/oracle-free, service FREEPDB1), with a local HTTP sink standing in for Slack. Schema APP held three tables: ORDERS with 2000 rows and statistics gathered, CUSTOMERS with 500 rows and statistics gathered, AUDIT_LOG with 300 rows and never analyzed at all. Then 600 rows went into ORDERS only, which is 30 percent of it, past the default STALE_PERCENT of 10.

sqlplus confirmed the state before every run. After the inserts, ORDERS read STALE_STATS = YES with NUM_ROWS still 2000 against a real 2600, CUSTOMERS read STALE_STATS = NO with NUM_ROWS 500 and LAST_ANALYZED untouched, AUDIT_LOG had a null STALE_STATS plus a null LAST_ANALYZED. The detector returned exactly ORDERS plus AUDIT_LOG, never CUSTOMERS, which is the precision check.

The payoff was measured, not assumed. EXPLAIN PLAN FOR SELECT * FROM app.orders gave a cardinality of 2000 rows at 44000 bytes while the table held 2600. After the flow gathered, the same statement gave 2600 rows at 57200 bytes. The number the optimizer plans from moved to the truth. sqlplus read NUM_ROWS 2600 with SAMPLE_SIZE 2600 and a LAST_ANALYZED matching the clock the flow took before the pass.

Run	Setup and inputs	Result
1	`ORDERS` stale, `AUDIT_LOG` unanalyzed, defaults	2 findings, posted to Slack, no change, SUCCESS
2	same, `auto_remediate: true`, `dry_run: true`	Same report, Slack said no change applied, `remediate_gate` never opened. The dry run gate held
3	same, `include_never_analyzed: false`	1 finding, `ORDERS` only, SUCCESS
4	same, `max_tables_per_run: 1`, armed	`AUDIT_LOG` gathered from no statistics to 300 rows, `ORDERS` reported DEFERRED and left at YES, SUCCESS
5	`ORDERS` stale, `max_table_mb: 0.01`, armed	Reported over the ceiling, 0 gather targets, nothing gathered, SUCCESS
6	statistics locked on `ORDERS`, armed	Reported with `stattype_locked` ALL, 0 gather targets. A direct gather raised ORA-20005, SUCCESS
7	`ORDERS` stale, armed, defaults	Gathered 2000 to 2600 rows, `reverify` found 0, SUCCESS
8	every table current, defaults	0 findings, clean branch, no alert, SUCCESS
9	a fourth table stale with its tablespace offline, armed	Gather failed with ORA-00376, `reverify` reported `gather did not run`, run FAILED, errors block alerted Slack
10	unknown service in `jdbc_url`, defaults	ORA-12514 on the first query, run FAILED, errors block alerted Slack

Runs 1 and 8 are the proof the check can fail: the same query returned exactly 2 rows against a drifted schema plus 0 rows once everything was current. Run 4 proves the cap leaves work for the next pass instead of dropping it, confirmed by sqlplus showing ORDERS still at YES with NUM_ROWS 2000 after that run. Run 9 is the re-verify gate doing its job: the statistics were still stale, so the run failed rather than reporting a fix that did not happen. Every STALE_STATS, LAST_ANALYZED plus NUM_ROWS value was read back with sqlplus, not only from the Kestra log.

One lever that did not work, measured rather than assumed. Setting STALE_PERCENT to 0 with DBMS_STATS.SET_TABLE_PREFS does not keep a table flagged stale after a gather, so it is not a way to force the re-verify failure. The gather resets the DML counters and the flag reads NO again.

Prerequisites

An Oracle Database reachable over the thin JDBC driver (jdbc:oracle:thin:@//host:1521/service).
A database user that can read dba_tab_statistics, dba_tables, dba_segments plus dba_tab_modifications (SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY).
ANALYZE ANY to gather statistics on another schema's table, plus to call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. A user working only on its own tables needs no extra privilege.
STATISTICS_LEVEL at TYPICAL or ALL, which is the default. Table monitoring is what populates STALE_STATS.

Secrets

ORACLE_PASSWORD: the database password. It is the default for the password input.
SLACK_WEBHOOK_URL: used by alert and the errors block.
In Kestra OSS, provide each one as a base64-encoded environment variable prefixed SECRET_.

Inputs

Input	Default	Purpose
`jdbc_url`	`jdbc:oracle:thin:@//localhost:1521/FREEPDB1`	The Oracle instance to check
`username`	`SYSTEM`	The database user
`password`	`{{ secret('ORACLE_PASSWORD') }}`	The database password
`table_owners`	`[APP]`	Schemas whose table statistics are checked
`include_never_analyzed`	`true`	Treat a table with no statistics as a finding
`max_tables_per_run`	`5`	Per run cap, the rest are reported as DEFERRED
`max_table_mb`	`2048.0`	Tables larger than this are reported, never gathered
`estimate_percent`	`AUTO`	Sample size, AUTO passes `DBMS_STATS.AUTO_SAMPLE_SIZE`
`degree`	`1`	Parallel degree, AUTO passes `DBMS_STATS.AUTO_DEGREE`
`auto_remediate`	`false`	Allow `DBMS_STATS.GATHER_TABLE_STATS`
`dry_run`	`true`	Must be false for any gather to run

Quick start

Add the ORACLE_PASSWORD and SLACK_WEBHOOK_URL secrets.
Import the flow, set jdbc_url plus username, then list the schemas in table_owners.
Run it with the defaults and read which tables are stale, how big they are plus how much DML they took since their last gather.
Set max_tables_per_run and max_table_mb to what your instance can absorb in one pass.
When you want it to fix things, run with auto_remediate: true plus dry_run: false.
Enable the daily trigger, which always runs in safe mode.

How to extend

Call DBMS_STATS.SET_TABLE_PREFS to lower STALE_PERCENT on a table whose plans are sensitive to small changes, so it is flagged before it drifts 10 percent.
Add a DBMS_STATS.EXPORT_TABLE_STATS call into your own stats table before the gather, for a copy that outlives the automatic retention window.
Gate the run on DBA_TAB_MODIFICATIONS volume rather than the staleness flag, for a policy expressed in rows written instead of a percentage.
Capture EXPLAIN PLAN cardinality for a few known queries before plus after the gather, so the run records the plan change it caused.
Point the audit at DBA_IND_STATISTICS as well, for indexes whose statistics are stale while the table's are current.
Do not add an age only trigger. Oracle's staleness flag already accounts for change, so gathering a table nothing has touched spends IO for an identical result.
Loop over several instances to check every database from one flow.

Oracle Stale Optimizer Statistics Gather Guard

Findings

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Oracle Stale Optimizer Statistics Gather Guard

Findings

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

Gate Apache Superset Dataset Sync and Cache Warming on dbt Builds and DuckDB Reconciliation

Dbt Test Failure AI Root Cause and Remediation Advisor

PostgreSQL Debezium CDC Backlog Sentinel

Gate MySQL Debezium CDC Loads on Schema Changes with Approval

Dgraph Alpha Pending Proposals and Raft Stall Sentinel

Delta Lake Uncompacted Small Files Compactor and Safe Vacuum Gate

More Infrastructure Blueprints

OWASP DefectDojo Vulnerability SLA Escalation Sentinel

Hold Production Deploys While a Freeze Window Is Active

Triage Dependabot pull requests and alert Slack only on risky updates

DeploymentWatch - Kubernetes stuck rollout watcher

PostgreSQL Debezium CDC Backlog Sentinel

Detect Dependency Relicensing with deps.dev and Slack Alerts

New to Kestra?