One of our customers was suffering some memory issues that lead us to check the behaviour of the database. We found tha mainly activity in the database was performed by a single query:
SQL_ID 3x5fn7ptysps8, child number 0 ------------------------------------- SELECT * FROM (SELECT * FROM CDS C WHERE C.CALLE IS NULL OR C.CALLE = '' ORDER BY ID_CDS DESC) WHERE ROWNUM <=100 Plan hash value: 1018703595 --------------------------------------------------------------------------------------------------- | Id | Operatistartupon | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 584K(100) | | | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | VIEW | | 2 | 1292 | 584K (1) | 01:56:49 | | | | |* 3 | SORT ORDER BY STOPKEY| | 2 | 334 | 584K (1) | 01:56:49 | 2048 | 2048 | 13/0/0| |* 4 | TABLE ACCESS FULL | CDS | 2 | 334 | 584K (1) | 01:56:49 | | | | ---------------------------------------------------------------------------------------------------
It’s indeed a simple query that is performed using a Full Table Scan operation (Operation Id 4, the TABLE ACCESS FULL in the execution plan). It’s not possible to remove thie FTS even with the existence of a simple index on CALLE column. The reason is we are looking for a value that will never be indexed by Oracle, NULL. This is a normal and expected behavior in Oracle, NULL values are ignored and never added to an index (they are really being treated as what they are, nothing).
This leads to a situation where even having created an index on this column, condition “CALLE IS NULL” will always be solved with a FTS because we cannot identify NULL values using the index, making necessary to check every single row in the table to find if the condition is met.
In our case, the application was repeating this query very frequently just to maintain current some important real time data for the users. A session was almost constantly performing User I/O operations over and over. When reviewing the incidence, executions could take more than 150 seconds to complete, due to a table size of 16GiBs. The OS is a 32 bit Windows version with 4GiBs RAM where we initially trying to solve a issue related to the 2GiB process memory restriction (3GiB if the OS is configured for that)
The applied solution for this query was the creation of a index that includes NULL values. In 11g, the version of this database, we can do this creating an composite index where one value is the column with NULLs and the other is just a constant and not another table column. We initially test the solution in our session creating a virtual index to check if we get the expected execution plan:
create index idx_nc_virt on cds(calle,0) nosegment; alter session set "_use_nosegment_indexes" = true;
Once verified, we apply the solution globally creating the real index (OK, and dropping the virtual one).
drop index idx_nc_virt; create index idx_nc on cds(calle,0);
The result is the following plan, much more efficient:
Plan hash value: 3682771407 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 1292 | 4 (25)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 2 | 1292 | 4 (25)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 2 | 334 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| CDS | 2 | 334 | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_NC | 2 | | 2 (0) | 00:00:01 | ------------------------------------------------------------------------------
Now the query takes about 0,25″ to complete.
This is an interesting alternative to using functional indexes to solve this same situation (maybe using NVL function in the index). The advantage is we didn’t need to modify the application to adapt the SQL code to this new index. Using a functional index (and a Standard Edition 🙂 ), we would need to modify the WHERE filter for it to be similar to the function used in the index so the optimizer will evaluate its usage. We are generating a composed index where one column is the NULLable column in our table, and the other column is just a constant value. This will make all NULL entries in the column to appear in the index and will transform the initial FTS into a more efficient INDEX RANGE SCAN.
Credits:
Fotografía: Alicia Constela, Vera Pereiro e Alberto Pereiro. Clube de Esgrima 100Tolos.
Revisiones: Alicia Constela.
