The upgrade of a database from Oracle 11.2 to 19.24 was completed, and the applications were restarted. Minutes later, the first incident is reported: a batch process aborts after a few moments.
java.sql.SQLException: ORA-01502: index 'SCOTT.EMP_IX' or partition of such index is in unusable state ORA-06512: at "SCOTT.EMP_LOAD", line 1714 ORA-06512: at line 1 As this routine is executed daily, this failure is automatically attributed to some problem with the new version of Oracle, so let’s get to work and see what it is.
Join me on this interesting adventure!
The problem
INSERT INTO SCOTT.EMP SELECT . . .
EXECUTE IMMEDIATE 'ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS';I try to run it directly and succeed in reproducing the error:
SQL> ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS;
ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS
*
ERROR at line 1:
ORA-01502: index 'SCOTT.EMP_IX' or partition of such
index is in unusable state
The question is: why is the EMP_IX index in UNUSABLE state?
It turns out that this routine is invoked by another one (EMP_BATCH), which contains the following code:
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP REUSE STORAGE';
EXECUTE IMMEDIATE 'ALTER TABLE SCOTT.EMP DEALLOCATE UNUSED';
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX UNUSABLE';
INSERT /*+APPEND*/ INTO SCOTT.EMP SELECT . . .
EMP_LOAD;
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX REBUILD';
EXECUTE IMMEDIATE 'ANALYZE TABLE SCOTT.EMP ESTIMATE STATISTICS';
It is observed that in line 7 the routine that executes the ANALYZE that aborts is invoked, and in line 10 ANALYZE is invoked again.
It turns out that since they want to do a massive data load, the first thing they do is truncate the table and, to reduce the work, they put the indexes in UNUSABLE state.
After that they fill the table from both EMP_BATCH and EMP_LOAD, to finally rebuild the indexes and update the statistics.
Everything would be fine if it were not for the fact that in EMP_LOAD the statistics are also updated and—unlike in previous versions—it is evident that in Oracle 19c it is not allowing to calculate them if there is any index in UNUSABLE state.
The solution
To begin with, the use of ANALYZE should be avoided. Since Oracle 8i the DBMS_STATS package appeared as a replacement and the documentation has been warning us about it.
The use of ANALYZE for the collection of optimizer statistics is obsolete.
If you want to collect optimizer statistics, use the DBMS_STATS package
After verifying that the execution of DBMS_STATS has no problems if there is any index in UNUSABLE state, the immediate measure is to replace all occurrences of ANALYZE by DBMS_STATS.
The problem now is that there are 67 packages containing 1,215 calls to ANALYZE, so a manual change will be a herculean task. It is not a matter of simply opening SQL Developer, loading the package code and running a Search & Replace.
Regular expressions to the rescue
A visual inspection of the code allows us to identify that the calls to ANALYZE have the following pattern:
EXECUTE IMMEDIATE 'ANALYZE TABLE OWNER.TABLE_NAME ESTIMATE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE OWNER.TABLE_NAME ESTIMATE STATISTICS SAMPLE 40 PERCENT';
EXECUTE IMMEDIATE 'ANALYZE TABLE OWNER.TABLE_NAME COMPUTE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE INDEX OWNER.INDEX_NAME ESTIMATE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE INDEX OWNER.INDEX_NAME COMPUTE STATISTICS';That is: it can be invoked for a table or for an index, and in some cases the SAMPLE keyword is added.
Everything points to a scenario where we can make use of regular expressions to implement the massive change from ANALYZE to DBMS_STATS.
Although I had never used regular expressions before, the rush to solve the incident and avoid others that may arise later, makes it inevitable to investigate on the subject, and fortunately it turns out that it is not so complicated.
We start by defining that the pattern to identify and transform is the following:
EXECUTE IMMEDIATE 'ANALYZE TABLE|INDEX [[:alnum:]_\$#]+\.[[:alnum:]_\$#]+ ESTIMATE|COMPUTE .+';Yes, it is a hieroglyphic, but if you pay attention for a few moments I will try to help you decipher it:
- The text to be searched starts with EXECUTE IMMEDIATE ‘ANALYZE, that is invariable.
- After that you may want to process a table or an index, that “o” translates to ‘|’, hence ‘TABLE|INDEX’ is used.
- Next comes the schema name and the table or index name, separated by a period. As these names are limited to the use of letters, numbers, ‘_’ and ‘#’, we use ‘[[:alnum:]_\$#]+’ as the pattern for each name.
- As the characters ‘$’ and ‘.’ have a special meaning in regular expressions, we must precede them with ‘\’ so that they are interpreted literally.
- Since we do not know how many characters the schema and table or index names have, we use ‘+’, which means “one or more occurrences”.
- Now comes ESTIMATE or COMPUTE, which we translate by ‘ESTIMATE|COMPUTE’.
- What comes next does not interest us, because with what we have identified we are already sure that it is a statistical calculation with ANALYZE and we have all the data to rewrite it. We express this with ‘.+’ which means ‘one or more characters’.
- Finally we have ‘;’, to close the text we are interested in processing.
With this we are now ready to transform the code using a PL/SQL program:
- We will start by creating a table in which we will obtain a backup of the code to be processed.
CREATE TABLE system.ddl_analyze_objects (
object_id NUMBER,
data_object_id NUMBER,
owner VARCHAR2(128),
object_name VARCHAR2(128),
object_type VARCHAR2(23),
ddl_object CLOB,
CONSTRAINT ddl_analyze_objects_pk PRIMARY KEY ( object_id ) );- Now we will identify the stored source code (packages, procedures and functions) that contains lines that follow the pattern we have established, and using DBMS_METADATA we will dump the code into the previously created table. Note: we must ignore the objects that are managed by Oracle.
DECLARE
ddl_tmp CLOB;
h NUMBER;
th NUMBER;
BEGIN
FOR i IN ( SELECT DISTINCT obj.object_id, obj.data_object_id,
obj.owner, obj.object_name,
DECODE( obj.object_type, 'PACKAGE BODY', 'PACKAGE_BODY', object_type) AS object_type
FROM dba_objects obj,
dba_source src
WHERE obj.owner = src.owner
AND obj.object_name = src.name
AND obj.object_type = src.type
AND obj.oracle_maintained = 'N'
AND REGEXP_LIKE( src.text, 'EXECUTE IMMEDIATE ''ANALYZE TABLE|INDEX [[:alnum:]_\$#]+\.[[:alnum:]_\$#]+ ESTIMATE|COMPUTE .+'';', 'i' )
ORDER BY obj.object_id, obj.data_object_id, obj.owner, obj.object_name, object_type
)
LOOP
h := DBMS_METADATA.open( object_type => i.object_type );
DBMS_METADATA.set_filter( handle => h, name => 'SCHEMA', value => i.owner) ;
DBMS_METADATA.set_filter( handle => h, name => 'NAME', value => i.object_name );
th := DBMS_METADATA.add_transform ( handle => h, name => 'DDL' );
ddl_tmp := DBMS_METADATA.fetch_clob( handle => h );
INSERT INTO system.ddl_analyze_objects (
object_id, data_object_id, owner, object_name, object_type, ddl_object )
VALUES (
i.object_id, i.data_object_id, i.owner, i.object_name, i.object_type, ddl_tmp
);
DBMS_METADATA.close( handle => h );
END LOOP;
COMMIT;
END;
/
- Explaining all the code is beyond the scope of this post, so we will focus on the use of REGEXP_LIKE in the predicate of the main query. The first parameter required is the column to evaluate, in this case it is DBA_SOURCE.TEXT. The second parameter is the pattern to search for, which we explained in detail above. The final parameter is ‘i’, which means “ignore case sensitivity”.
- We will now process the rows of our DDL_ANALYZE_OBJECTS table, using REGEXP_REPLACE to replace the original code using ANALYZE by its equivalent with DBMS_STATS.
BEGIN
FOR i IN ( SELECT owner, object_name, object_type,
REGEXP_REPLACE(
ddl_object,
'EXECUTE IMMEDIATE ''ANALYZE (TABLE|INDEX) ([[:alnum:]_\$#]+)\.([[:alnum:]_\$#]+) (ESTIMATE|COMPUTE) .+'';',
UPPER('DBMS_STATS.GATHER_\1_STATS(''\2'',''\3'');'),
1, 0, 'i'
) as ddl_object
FROM system.ddl_analyze_objects
)
LOOP
EXECUTE IMMEDIATE i.ddl_object;
END LOOP;
END;
/
- The first parameter required by REGEXP_REPLACE is the column to transform, in this case it is DDL_OBJECT.
- The second parameter is the pattern to search for, but you will notice that there is a small difference: the data we are interested in are now surrounded by ‘(‘ and ‘)’, this transforms them into variables. In other words ‘(TABLE|INDEX)’ is transformed into variable ‘1’, the first ‘([[:alnum:]_\$#]+)’ is transformed into variable ‘2’ (i.e. the name of the schema), and the second ‘([[:alnum:]_\$#]+)’ is transformed into variable ‘3’ (i.e. the name of the table or index).
- The third parameter is the replace string. In order to be able to reference the variables mentioned in the previous step, they must be prefixed with ‘\’.
- The fourth parameter indicates that the evaluation must be done from the first character of the column we are processing.
- The fifth parameter indicates that we want to process all occurrences within the column we are processing.
- The final parameter is ‘i’, which means “case-insensitive matching”.
End result
Reviewing again the code of the stored procedure EMP_LOAD, line 1714 that previously executed the ANALYZE command now calls DBMS_STATS:
INSERT INTO SCOTT.EMP SELECT . . .
DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP REUSE STORAGE';
EXECUTE IMMEDIATE 'ALTER TABLE SCOTT.EMP DEALLOCATE UNUSED';
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX UNUSABLE';
INSERT /*+APPEND*/ INTO SCOTT.EMP SELECT . . .
EMP_LOAD;
EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.EMP_IX REBUILD';
DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');With the change already implemented, we tried to execute the routine that gave life to this incident and now it finishes without errors.
With this we can now consider the work completed and the incident resolved. What would have been a time-consuming and error-prone manual task of several days now takes only a few seconds!
To complement the above, especially on the use of DBMS_METADATA, I recommend reading the notes:
| 237293.1 | How to Move from ANALYZE to DBMS_STATS - Introduction |
| 263140.1 | How to Use Regular Expressions from Oracle 10G Onwards |
| 556823.1 | How To Use The Package DBMS_METADATA With SCHEMA_EXPORT To Extract DDLs Owned By Another User |