These days a client I support had severe performance issues in a lower environment that, after some investigation, we determined were caused by some indexes with wrong statistics. Consequently, bad plans were being generated and caused the performance degradation.
There is a process we run to mask sensitive data in lower environments. This is a long, complex process that involves a lot of phases, and in order to speed up some phases of this process, indexes are put in UNUSABLE state during the data changes and movements, and rebuilt afterwards.
During the execution of this masking process weeks ago, a problem occurred with the script and some of the local partitioned indexes ended up not being rebuilt, so they were still as UNUSABLE.
Somebody from the team detected the problem later and rebuilt all the UNUSABLE partitioned indexes (it must be done by partition, it’s not possible to rebuild partitioned indexes as a whole).
But, even after the rebuild, people testing the application continued complaining about poor performance and we found that it was being caused by that indexes that had UNUSED partitions. Even after rebuilding the partitions, the statistics at the index level were still wrong, causing the performance problems.
I know, long explanation, hard to figure out. So I’ve created a test case to reproduce the issue at will and to better understand it.
First, I will create a partitioned table with some data, a local partitioned index, and gather stats:
create table t1
partition by range (category)
(
partition p1 values less than (3),
partition p2 values less than (6),
partition p3 values less than (MAXVALUE)
) as
select rownum as ID, case when rownum <= 6 then rownum else 99 end as CATEGORY, lpad('X',1000,'X') as NAME
from dual connect by level <= 50000;
Table created.
create index i1 on t1 (category) local;
Index created.
exec dbms_stats.gather_table_stats(user,'T1',cascade=>TRUE)
PL/SQL procedure successfully completed.
Here is the data generated and the stats for the index and its partitions:
select category, count(1) from t1 group by category order by 1;
CATEGORY COUNT(1)
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
99 49994
7 rows selected.
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED
----------------------------------- -------------------- -------- ---------- -------------------
I1 N/A 50000 2023-04-19 17:23:18
I1 P1 USABLE 2 2023-04-19 17:23:18
I1 P2 USABLE 3 2023-04-19 17:23:18
I1 P3 USABLE 49995 2023-04-19 17:23:18
4 rows selected.
Please note that N/A in the STATUS simply means that the index is partitioned, so the status that counts is at the partition level.
Since the data I’ve create is skewed, different predicates for CATEGORY would generate different execution plans:
set feedback only
select * from t1 where category = 1;
1 row selected.
set feedback off
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category = 1
Plan hash value: 3860318639
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE SINGLE | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 |
| 3 | INDEX RANGE SCAN | I1 |
-----------------------------------------------------------
set feedback only
select * from t1 where category = 99;
49994 rows selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category = 99
Plan hash value: 1737577267
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE SINGLE| |
| 2 | TABLE ACCESS FULL | T1 |
---------------------------------------
set feedback only
select * from t1 where category <= 100;
50000 rows selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category <= 100
Plan hash value: 589593414
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL| |
| 2 | TABLE ACCESS FULL | T1 |
------------------------------------
Now the fun begins :-). I will simulate what that masking process does in order to reproduce the issue.
First, I will make the index UNUSABLE. This is a very common step to speed up processes that make bulk data loads or changes. It’s much faster just do all the data changes and rebuild the index at the end than letting Oracle maintain the index for every single change we are doing.
alter index i1 unusable;
Index altered.
select index_name, partition_name, status, num_rows, last_analyzed from user_ind_partitions where index_name='I1'
UNION ALL
select index_name, NULL partition_name, status, num_rows, last_analyzed from user_indexes where index_name='I1'
order by 2 nulls first;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED
----------------------------------- -------------------- -------- ---------- -------------------
I1 N/A 50000 2023-04-19 17:23:18
I1 P1 UNUSABLE 2 2023-04-19 17:23:18
I1 P2 UNUSABLE 3 2023-04-19 17:23:18
I1 P3 UNUSABLE 49995 2023-04-19 17:23:18
4 rows selected.
Note that the status of the index partitions changed to UNUSED, but the index’s remains as N/A.
Now I will simulate data changes (the “masking” process):
alter table t1 enable row movement;
Table altered.
update t1 set id = 1000000-id;
50000 rows updated.
commit;
Commit complete.
I had to enable row movement for the table because the update I executed move rows among partitions.
Now, instead of rebuilding the UNUSABLE partitions before, I will “forget” this step and just gather new statistics for the index:
exec dbms_stats.gather_index_stats(user,'I1')
PL/SQL procedure successfully completed.
select index_name, partition_name, status, num_rows, last_analyzed from user_ind_partitions where index_name='I1'
UNION ALL
select index_name, NULL partition_name, status, num_rows, last_analyzed from user_indexes where index_name='I1'
order by 2 nulls first;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED
----------------------------------- -------------------- -------- ---------- -------------------
I1 N/A 0 2023-04-19 17:28:53
I1 P1 UNUSABLE 0 2023-04-19 17:28:53
I1 P2 UNUSABLE 0 2023-04-19 17:28:53
I1 P3 UNUSABLE 0 2023-04-19 17:28:53
4 rows selected.
Note that the NUM_ROWS both at the partitions and the index level are all 0.
I am not showing here, but all other statistics-related columns are with 0 as well.
I would guess Oracle should let these columns as NULL, since having 0 there can be kind of misleading. Let me know if you agree or disagree.
Let’s see how it impacts the execution plans:
set feedback only
select * from t1 where category = 1;
1 row selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category = 1
Plan hash value: 1737577267
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE SINGLE| |
| 2 | TABLE ACCESS FULL | T1 |
---------------------------------------
14 rows selected.
set feedback only
select * from t1 where category = 99;
49994 rows selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category = 99
Plan hash value: 1737577267
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE SINGLE| |
| 2 | TABLE ACCESS FULL | T1 |
---------------------------------------
14 rows selected.
set feedback only
select * from t1 where category <= 100;
50000 rows selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category <= 100
Plan hash value: 589593414
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL| |
| 2 | TABLE ACCESS FULL | T1 |
------------------------------------
14 rows selected.
Wait, WHAT??? All the queries doing FULL SCAN now? Why?
Oh ok, of course, since the index is UNUSABLE, Oracle is skipping it. If I change the DB parameter SKIP_UNUSED_INDEXES, I will receive an error:
alter session set skip_unusable_indexes = FALSE;
Session altered.
select * from t1 where category = 1;
select * from t1 where category = 1
*
ERROR at line 1:
ORA-01502: index 'CLAROE.I1' or partition of such index is in unusable state
alter session set skip_unusable_indexes = TRUE;
Session altered.
Ok, now I will solve the problem by rebuild the index partitions:
alter index i1 rebuild partition p1;
Index altered.
alter index i1 rebuild partition p2;
Index altered.
alter index i1 rebuild partition p3;
Index altered.
select index_name, partition_name, status, num_rows, last_analyzed from user_ind_partitions where index_name='I1'
UNION ALL
select index_name, NULL partition_name, status, num_rows, last_analyzed from user_indexes where index_name='I1'
order by 2 nulls first;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED
----------------------------------- -------------------- -------- ---------- -------------------
I1 N/A 0 2023-04-19 17:28:53
I1 P1 USABLE 2 2023-04-19 17:34:09
I1 P2 USABLE 3 2023-04-19 17:34:11
I1 P3 USABLE 49995 2023-04-19 17:34:14
4 rows selected.
Great, now the index partitions are USABLE again. But notice that the NUM_ROWS at the index level is still 0! The partitions rebuilt had their stats updated automatically, but the index-level stats did not change.
So, this is exactly the situation we were facing in our environment, and causing weird performance problems.
Here is an example:
set feedback only
select * from t1 where category <= 100;
50000 rows selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category <= 100
Plan hash value: 1976518054
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 |
| 3 | INDEX RANGE SCAN | I1 |
-----------------------------------------------------------
15 rows selected.
Did you notice that??? My predicate is CATEGORY <= 100, which would bring ALL the 50000 rows. So clearly a FULL SCAN would be preferable. But the Optimizer chose to go via the index. Probably because the index stats are messed.
Let me finally fix this and see what happens:
exec dbms_stats.gather_index_stats(user,'I1')
PL/SQL procedure successfully completed.
select index_name, partition_name, status, num_rows, last_analyzed from user_ind_partitions where index_name='I1'
UNION ALL
select index_name, NULL partition_name, status, num_rows, last_analyzed from user_indexes where index_name='I1'
order by 2 nulls first;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS LAST_ANALYZED
----------------------------------- -------------------- -------- ---------- -------------------
I1 N/A 50000 2023-04-19 17:41:27
I1 P1 USABLE 2 2023-04-19 17:41:27
I1 P2 USABLE 3 2023-04-19 17:41:27
I1 P3 USABLE 49995 2023-04-19 17:41:27
4 rows selected.
Finally, only after rebuild the index partitions AND re-gatherings statistics on the index, now everything seems normal again.
Let me see if my query now picks the right plan, first removing the plan from memory to make sure the Optimizer will recalculate it:
begin
for i in (
select address || ',' || hash_value as add_hash
from V$SQLAREA where sql_text like 'select * from t1 where category%'
) loop
sys.DBMS_SHARED_POOL.PURGE (i.add_hash, 'C');
end loop;
end;
/
PL/SQL procedure successfully completed.
select address || ',' || hash_value as add_hash
from V$SQLAREA where sql_text like 'select * from t1 where category%';
no rows selected
set feedback only
select * from t1 where category = 1;
1 row selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category = 1
Plan hash value: 3860318639
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE SINGLE | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 |
| 3 | INDEX RANGE SCAN | I1 |
-----------------------------------------------------------
15 rows selected.
set feedback only
select * from t1 where category <= 100;
50000 rows selected.
set feedback on
select * from table(dbms_xplan.display_cursor(null,null,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t1 where category <= 100
Plan hash value: 589593414
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL| |
| 2 | TABLE ACCESS FULL | T1 |
------------------------------------
14 rows selected.
Wonderful, now the queries are again picking the right plan, everything is OK!
NOTE: this was tested in Oracle versions 19.18 and 23.2, with the same results.
And that’s it. I hope you understood how dangerous this situation is, performance-wise. If you find something similar in the future, you already know: re-gather stats at the index level AFTER rebuilding the UNUSABLE partitions, and everything is going to be OK.
I hope you enjoy.
See you on the next one!
EDUARDO CLARO 