Partition corruption: ORA-8103 Object no longer exists

Recently we observed intermittent issues on on of the table that is hash partitioned with 64 partitions on item column.

The same table has  composite local index on 4 columns out of that one is part of partition.  This table is part of daily batch and getting truncated during start of batch and consists of output data of batch.

We identified and able to replicate the below query is failing with given exception.

 select distinct optionset from udt_skups;

These are the four columns those are part of local index.
OPTIONSET,STARTDATE ,ITEM, LOC

below queries are going fine
select  distinct OPTIONSET,STARTDATE ,ITEM, LOC from udt_skups;

select *  from udt_skups where rownum<10


select distinct userid from udt_skups;

select  optionset from udt_skups;

below queries are failing;

 select distinct optionset from udt_skups;

 select distinct OPTIONSET,STARTDATE ,ITEM from udt_skups;
--or rest other combinations on local index.

latter to identify corrupted partitions , executed below query on each partition.

This query went fine on partition 5
SELECT  count(OPTIONSET) FROM udt_skups PARTITION (P5) where rownum<5
but
SELECT  count(OPTIONSET) FROM udt_skups PARTITION (P5)
failed with same error.

So this is pretty clear that some of the partitions are corrupted in table and if those partitions hold data retrieved by query, it will fail.

To automate partition corruption, following code is written.

CREATE OR REPLACE PROCEDURE proc_check_partition_health (
   tabname   IN VARCHAR,
   colname   IN VARCHAR)
IS
   i_count   NUMBER;
BEGIN
   FOR i IN (SELECT partition_name
               FROM user_tab_partitions
              WHERE table_name = tabname)
   LOOP
      BEGIN
         EXECUTE IMMEDIATE
               'SELECT  count('
            || colname
            || ') FROM '
            || tabname
            || ' PARTITION ('
            || i.partition_name
            || ')'
            INTO i_count;

         DBMS_OUTPUT.put_line (
            'The count in partition' || i.partition_name || ' is ' || i_count);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
               'The  partition ' || i.partition_name || ' is corrupted');
      END;
   END LOOP;
END;
/
show errors

The proc takes input as partitioned table name and the column on which table is partitioned or any column that is part of local index.

automatic DOP: skipped because of IO calibrate statistics are missing

Because I/O calibration is not run to gather the required statistics. I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
-------------
NOT AVAILABLE

then used DBMS_RESOURCE_MANAGER.CALIBRATE_IO Procedure.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/

max_iops = 5944
latency = 9
max_mbps = 75

PL/SQL procedure successfully completed.

if using DBMS_RESOURCE_MANAGER.CALIBRATE_IO and error ORA-56708: Could not find any datafiles with asynchronous i/o capability
Need to enable asynch I/O, set two values in the init.ora file.

disk_asynch_io = true
filesystemio_options = asynch

After used CALIBRATE_IO, then

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
-------------
READY

select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name='Data File' or filetype_name='Temp File')

Gather_system_stats

The dbms_stats.gather_system_stats procedure is especially useful for multi-mode Oracle shops that run OLTP during the day and DSS at night. This is the typical mode we have and it becomes imperative to have these stats good.

We can gather these stats in three different ways:

1. During heavy workload.

execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

2. During no workload.

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD')

This actually gathers the IOTFRSPEED and IOSEEKTIM values in addition to CPUSPEEDNW rather than using the default values of 4096 and 10.

3. Setting your own stats.

This is important when testing which stats will be good and then setting them manually.

begin
    dbms_stats.delete_system_stats;
    dbms_stats.set_system_stats('MBRC',        64);
    dbms_stats.set_system_stats('MREADTIM',    10);
    dbms_stats.set_system_stats('SREADTIM',     5);
    dbms_stats.set_system_stats('CPUSPEED',  2000);
    dbms_stats.set_system_stats('MAXTHR',  262144);
    dbms_stats.set_system_stats('SLAVETHR', 65536);
    dbms_stats.set_system_stats('SLAVETHR', 47000);
    dbms_stats.set_system_stats('SLAVETHR', 16384);
end;
/

The output from dbms_stats.gather_system_stats is stored in the aux_stats$ table and you can query it as follows:

select pname, pval1 from sys.aux_stats$;

Here are the data items collected by dbms_stats.gather_system_stats:

No Workload (NW) stats:

• CPUSPEEDNW - CPU speed
• IOSEEKTIM - The I/O seek time in milliseconds
• IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related stats:

• SREADTIM  - Single block read time in milliseconds
• MREADTIM - Multiblock read time in ms
• CPUSPEED - CPU speed
• MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
• MAXTHR - Maximum I/O throughput (for OPQ only)
• SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)  

Sql plan management practice

--To check if optimizer is using baseline or not
select * from v$parameter where name='optimizer_use_sql_plan_baselines'

--Load baseline from cursor cache
--specific plan hash value also givenas it was best plan identified
--Fixed baseline so that no new plan baselines will be captured
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1b26c3w3un3a4',
    plan_hash_value => '2168508692',
    fixed => 'YES');
END;
/

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '8tx67bgzqwnvc',
    plan_hash_value => '3455307351',
    fixed => 'YES');
END;
/

--to check query etc.
SELECT plan_hash_value FROM V$SQL WHERE SQL_ID='8tx67bgzqwnvc';

--to check existing plan baselines and handle
SELECT * FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%INSERT INTO sim_res%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


--Check plan
set serveroutput on size unlimited
set line 1000
set pagesize 1000
select * from table(dbms_xplan.display_CURSOR('8tx67bgzqwnvc', 0, 'ADVANCED'));

--change parameter autodrop
SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_830a045284cdac6c',
    plan_name       => 'SQL_PLAN_862h4aa2cvb3c89f28544',
    attribute_name  => 'fixed',
    attribute_value => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_8da397267248b10a',
    plan_name       => 'SQL_PLAN_8v8wr4tt4jc8a1931a7ad',
    attribute_name  => 'autopurge',
    attribute_value => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/


--To drop baselines
SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SQL_PLAN_8v8wr4tt4jc8a1931a7ad');
   
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

Sql plan management

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

More theory can be found here:
https://oracle-base.com/articles/11g/sql-plan-management-11gr1

Steps to configure sql plan management:

Check if Automatic Plan Capture is enabled or not. IT IS NOT NECESSARY ALSWAY TO HAVE TO ENABLED AND CAREFULL TESTING NEEDS TO BE DONE TO ENABLE IT IN PROD.

SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

If not already enabled, enable it.

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by defaul.

show parameter optimizer_use_sql_plan_baselines;

Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

If it is not available, grant it.
grant ADMINISTER SQL MANAGEMENT OBJECT to SCOTT;



Manual Plan Loading:
LOAD_PLANS_FROM_CURSOR_CACHE:
The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache.
There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME, MODULE and ACTION. Manually loaded plan are by default accpeted
The following example identifies the SQL statement using the SQL_ID.
SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'gat6z1bc6nc2d');
   
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Note that once this plan loaded it will automatically create baseline if not already present or add it to existing baseline. Manual loading automatically enables and accepts plan.
Now even if this plan is sub-optimal query will continue to use this plan even if better plan exists. To make use of better plan we either need to evolve current plan or accept other plans if automatic plan capture is enabled.

Assume that above baseline uses FTS and latter index added on the column and index scan is optimal. So to make query use better plan we have to evolve this plan.

We can check the baseline created in DBA_SQL_PLAN_BASELINES view.

SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

To evolve sql baseline to accept new plan generated automatically;

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')
FROM   dual;

LOAD_PLANS_FROM_SQLSET:
alternatively you can load plan from sql tunning set as well.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    sqlset_name => 'my_sqlset');
END;
/

ALTERING PLAN BASELINES:

The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:

enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
description : Used to amend the SQL plan description, up to a maximum of 30 character.
The following shows a specific plan being marked as fixed.

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_7b76323ad90440b9',
    plan_name       => 'SYS_SQL_PLAN_d90440b9ed3324c0',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SQL MANAGEMENT BASE:

We can check parameters those can be configured by dbms_spm package for baseline by using below query;
SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

how to set these parameters:

BEGIN
  DBMS_SPM.configure('space_budget_percent', 11);
  DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/

TRANSFERRING SQL PLAN BASELINES:

The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    tablespace_name => 'USERS');
END;
/
The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.

SET SERVEROUTPUT ON
DECLARE
  l_plans_packed  PLS_INTEGER;
BEGIN
  l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST');

  DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

Then export this table and import it to destination schema. Post importing we can unpack baselines by using UNPACK_STGTAB_BASELINE function

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    creator         => 'TEST');

  DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

DROPPING PLANS AND BASELINES:

The DROP_SQL_PLAN_BASELINE function can drop a specific plan from a baseline, or all plans if the plan name is not specified.

CONN sys/password@db11g AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SYS_SQL_7b76323ad90440b9');
   
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

Oracle materialized views

Materialized views are oracle objects those contains results of the query. They are local copies of the objects located remotely or used to create summary table based on the aggregation of a tables's data.

grant from system:
grant create materialized view, query rewrite to SCOTT;

Create matview:
create materialized view meta_table_info_mview
build immediate refresh complete enable query rewrite as select * from meta_table_info;
--Enable query rewrite needs EE and it will fail if database is XE.
create materialized view md_table_info_mview
build immediate refresh complete as select * from meta_table_info;

Create matview logs:
create materialized view log on meta_table_info;

DML on mat view: DML operations are not permitted on mat views.'
delete from meta_table_info_mview where table_name='SKUIPOPARAM'

ORA-01732: data manipulation operation not legal on this view

Refresh matview:
Execute dbms_mview.refresh('MD_TABLE_INFO_MVIEW');


--Query to check all mviews
select * from user_mviews;

--query to check master table and mview log
select MASTER, LOG_TABLE from USER_MVIEW_LOGS

--Find related mview log tables by using following queries
select TABLE_NAME from USER_TABLES where table_name like '%MD_TABLE_INFO%';
 or
select  master, log, temp_log from sys.mlog$ where mowner = 'WWFMGR' and master = 'MD_TABLE_INFO';

--Update a record in master table and check if mview log table updated or not

update MD_TABLE_INFO set table_name='XXX' WHERE TABLE_NAME='SS';

select * from MLOG$_MD_TABLE_INFO;

HYBRID histograms

Oracle has introduced HYBRID histograms with Oracle 12C.  When number of buckets are more than 254, some almost popular values may get lost , resulting in sub-optimal index usage.

A single bucket can now store popularity as well , effectively increasing number of buckets without actually increasing it.

With oracle 12C, oracle merged Frequency and Height balanced histograms into hybrid histograms(Though original one also co-exists for their original purpose).

If we don’t specify AUTO_SAMPLE_SIZE(but use user defined sampling) as estimate, Hybrid histograms will not be created.If users specify their own percentage, then the database creates frequency or height-balanced histograms.

So when in doubt that issue is due to Hybrid histograms, we can try this approach. 

we can also set hybrid histgrams of by changing preferences.

TOP FREQUENCY is also a concern since it ignores the least occurred values and may generate wrong plan. It’s good to set this one also OFF.

BEGIN

  dbms_stats.set_global_prefs(pname => 'ENABLE_HYBRID_HISTOGRAMS', pvalue => 0);

  dbms_stats.set_global_prefs(pname => 'ENABLE_TOP_FREQ_HISTOGRAMS', pvalue => 0);

END;