Bitmap Indexes and Foreign key

1.     Introduction

Several months ago I have published a sql script that checks the existence or the absence of an index covering the deadlock threat of unindexed foreign key constraint when DML are issued against the parent table. That script doesn’t bother about the index type. If an index of a bitmap type exists it will tell you that it is not necessary to create an extra index for your FK constraint. Let’s see that in action

2.     Bitmap Index and FK constraint

I will start by creating a classical parent/child table relation ship

houri> create table t_parent as select * from all_users;
Table created.
mhouri> alter table t_parent add constraint t_p_pk primary key (user_id);
Table altered.
mhouri> create table t_child (user_id references t_parent, data varchar2(10));
Table created.                 

Then I will create a bitmap index to cover my Foreign Key:

mhouri > create bitmap index t_bitmap_fk_i on t_child(user_id);
Index created.

Using my traditional script to check if I need to create an index to cover my FK or not, gives me the following situation

mhouri> start index_fk.sql
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
TABLE_NAME                INDEX_NAME                     COLUMN_NAM COLUMN_POS
------------------------- ------------------------------ ---------- ----------
T_CHILD                   T_BITMAP_FK_I                  USER_ID             1

Oh! Yes; it is telling me that I don’t need to create an extra index because there is already one index starting with the foreign key column. If I execute Tom Kyte script,  it is also not pointing out the need to index my user_id FK column

mhouri> start tkyte.sql
TABLE_NAME                     CONSTRAINT_NAME                COLUMNS
------------------------------ ------------------------------ -------------------
MY_XXX_TAB                   C_FK                           ID1,ID2

My T_CHILD table is not returned by Tom Kyte script as a table having an unindexed foreign key. So, according to both scripts it is safe to do DML operations on T_PARENT table. Let’s then submit our parent table to a classical OLTP operations

mhouri> select min(user_id), max(user_id) from t_parent;
MIN(USER_ID) MAX(USER_ID)
------------ ------------
0           73
mhouri> insert into t_child(user_id,data) values (0,'test');
1 row created.
mhouri> declare
2   pragma autonomous_transaction;
3   begin
4   delete from t_parent where user_id = 73;
5   commit;
6   end;
7  /

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

mhouri> rollback;
Rollback complete.

Ooops!!!  The existing bitmap index does not cover the deadlock threat induced by a DML operation on a parent table having a child table with an unindexed foreign key.  The bitmap index type has to be excluded from my script. The new script index_fk when executed against the t-child table gives this:

mhouri> start index_fk_2.sql
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
no rows selected

And now, it’s OK. The script is telling me that I need to index my user_id FK columns because the existing bitmap index will not cover the deadlock threat.

          2.1 Remark

It would have been very bizarre that a bitmap index covers the deadlock threat induced by an unindexed foreign key because the bitmap index itself is responsible of several deadlocks situations when it is used in an OLTP application and this is why Tom Kyte script and my initial script has not foresee to exclude this type of indexes from the verification script

3.     Function Base Index and FK constraint

In order for an index to successfully cover the deadlock threat induced by a DML on a parent/child relationship it needs:

1. to start with the FK column(or columns for a composite key)
2. to not be of a bitmap type

 Looking to the above two conditions, there is nothing that impeaches a function based index to cover the FK threat. Let’s work with few examples:

mhouri> create index t_fbi_fk_i on t_child(user_id desc);
Index created.
mhouri> insert into t_child(user_id,data) values (0,'test');
1 row created.
mhouri> declare
  2       pragma autonomous_transaction;
  3      begin
  4      delete from t_parent where user_id = 73;
  5      commit;
  6      end;
  7    /

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

My Function based index has only one column in it and it is a hidden column (SYS_NC00003$ for example). This is why it didn’t succeed to cover the above deadlock situation. Let’s check this with my new script:

mhouri> start index_fk_2
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
no rows selected

 My new updated script is working well in this case as far as it is telling me that I need to create an extra index.  Let’s now drop and change a little bit this function based index

mhouri> drop index t_fbi_fk_i;
Index dropped.
mhouri> create index t_fbi_fk_i on t_child(user_id, data desc);
Index created.
mhouri> insert into t_child(user_id,data) values (0,'testfbi');
1 row created.
mhouri> declare
  2       pragma autonomous_transaction;
  3      begin
  4      delete from t_parent where user_id = 73;
  5      commit;
  6      end;
  7    /

PL/SQL procedure successfully completed.

The newly created function based index is covering very well the FK on the child table. My new script is also working well in this case as it is telling me that I don’t need to create an extra index

mhouri> start index_fk_2
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
<pre>TABLE_NAME                INDEX_NAME                     COLUMN_NAM COLUMN_POS

------------------------- ------------------------------ ---------- ----------

T_CHILD                   T_FBI_FK_I                     USER_ID             1

4.     Conclusion

In order for an index to successfully cover the deadlock threat induced by a DML on a parent/child table relationship it needs:

1. to start with the FK column(or columns for a composite key in any order)
2. to not be of a bitmap type

Even a function based index when it starts with the FK columns will play the same role as a b-tree index will do when it will be asked to cover the deadlock threats induced by a DML operation on a parent/child relationship

Getting Explain Plan

1. Introduction

In order to tune a query you need first to get its real execution plan that describes the operations dictated by the Oracle optimizer and followed by the SQL engine to execute this query. It can’t be enough emphasized that getting the exact explain plan (and not the expected one) represents an important step when troubleshooting query performance problems. This document aims to describe in detail how to obtain the real explain plan followed by the SQL engine.

2. Obtaining Explain plans

First of all it’s worth mentioning to avoid using tools like TOAD to get the explain plan of your query because, more often than not, it does not provide all the information you need for a thorough analysis. This is why I am going to present only tools that have been developed by Oracle Corporation. Among those tools the first one is the classical explain plan for as shown in the below example:

     2.1 Explain plan for command and its limitation          

Unfortunately there exist situations where the ‘explain plan for’ command may not report the real explain plan followed by the Oracle Optimizer to execute the query. This situation occurs particularly when using bind variables.  You should know that the ‘explain plan for’ command treats all bind variables as of a VARCHAR2 data type; this is why an implicit data type conversion might happen during the ‘explain plan for’ command while there is no implicit data type conversion when the query is executed. And that’s where the limit of the ‘explain plan for’ command comes from.

mhouri.world> create table t (id varchar2(10), name varchar2(100));

Table created.

mhouri.world> insert into t select to_char(object_id), object_name from user_objects;

1004 rows created.

mhouri.world> create index i on t(id);

Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.
mhouri.world> var x number

mhouri.world> exec :x:=99999

PL/SQL procedure successfully completed.

mhouri.world> explain plan for select sum(length(name)) from t where id >:x;

Explained.

mhouri.world> select * from table(dbms_xplan.display) ;

-------------------------------------------------------------------------------------
Plan hash value: 1188118800
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    24 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    50 |  1200 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I    |     9 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">:X)

Remember that the id column has been declared as varchar2 and that we issued our ‘explain plan for’ command using a bind variable declared as number (var x number). As far as the ‘explain plan for’ command treats all bind variables as VARCHAR2 it has reported that the index has been used; which is, unfortunately, completely false. And that is the topic of the next paragraph.

            2.2     dbms_xplan.display_cursor             

In order to get the real explain plan we need to use the display_cursor function of the dbms_xplan package as shown below:

mhouri.world> select sum (length (name)) from t where id >:x;
SUM (LENGTH (NAME))
-----------------
8145

mhouri.world> select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  7zm570j6kj597, child number 0
-------------------------------------
select sum(length(name)) from t where id >  :x
Plan hash value: 1842905362
--------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    50 |  1200 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("ID")>:X)

Notice how the dbms_xplan.display_cursor() function has correctly reported a TABLE ACCESS FULL instead of the INDEX RANGE SCAN path (initially reported by the ‘explain plan for’ command) due to the TO_NUMBER (see the predicate information) conversion that occurred during the query execution.

            2.3     dbms_xplan.display_cursor (null, null, ‘ALLSTATS LAST’)            

Now that we are quite confident with the use of dbms_xplan.display_cursor function instead of the approximate ‘explain plan for’ function, It is worth mentioning that the function dbms_xplan.display_cursor when invoked via specific parameters, can give extra valuable information that might be of a great help for explaining query performance problems and very often to show stale statistics. Let’s look to that through the following example:

mhouri.world> select /*+ gather_plan_statistics */
2  ename, hiredate, sal, deptno
3  from emp
4  where deptno = 20;

ENAME      HIREDATE         SAL     DEPTNO
---------- --------- ---------- ----------
allen      30-MAR-10        815         20
jones      02-APR-81       2975         20
scott      09-DEC-82       3000         20
adams      12-JAN-83       1100         20
ford       03-DEC-81       3000         20

mhouri.world> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7xs5xf4bnkmgs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename, hiredate, sal, deptno from emp where
deptno = 20
Plan hash value: 2872589290
----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers| Reads  |
--------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      5 |      5 |00:00:00.01 |    16 |    14 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20)

The new extra information together with their explanations are shown here below:

1. Starts   : the number of time the operation has been started or executed
2. E-Rows : the number of Estimated Rows for the current Operation
3. A-Rows : the number of Actual Rows retrieved by the current Operation

It is well known that the Optimizer will do a good job only if E-Rows and A-Rows have close values. When, instead, these two cardinalities present two largely different values, then this is a clear indication of inefficient execution plan probably due to stale statistics on related tables and indexes.  However, be aware that, before jumping to conclusions, you need always to compare E-Rows with round (A-Rows/Starts).

 3 Grants and privileges

In order to be able to call the dbms_xplan.display_cursor() function you need to access to the following dynamic performance views

• v$session
• v$sql
• v$sql_plan
• v$sql_plan_statistics_all

And if you don’t supply the sql_id or the child_number to the dbms_xplan.display_cursor() function, it will reports the last executed SQL statement.

On tuning via explain plan

Explain plan taken from memory, i.e. explain plan generated by the dbms_xplan.display_cursor () function, contains important information such as where time is most spent by the related query operations and, hence, on which operations the tuning effort should be concentrated.   As far as it is well known that a picture is worth a thousand words, let me then present you with a real life explain plan I have used to diagnose the cause of a query performing badly in production and which I have used also to tune this query using the valuable information delivered by this explain plan (I have edited a little bit this explain plan for clarity):

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                          | Starts | E-Rows | A-Rows A-Time       |
---------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT UNIQUE                                  |                               |      1 |      2 |   3494 |00:00:11.41 |
|   2 |   UNION-ALL                                   |                               |      1 |        |   3497 |00:00:11.38 |
|*  3 |    FILTER                                     |                               |      1 |        |   3482 |00:00:11.29 |
|*  4 |     FILTER                                    |                               |      1 |        |   4309 |00:00:11.27 |
|*  5 |      HASH JOIN OUTER                          |                               |      1 |      1 |   4309 |00:00:11.27 |
|   6 |       NESTED LOOPS OUTER                      |                               |      1 |      1 |   4309 |00:00:11.15 |
|   7 |        NESTED LOOPS                           |                               |      1 |      1 |   4309 |00:00:11.03 |
|   8 |         NESTED LOOPS OUTER                    |                               |      1 |      1 |   4309 |00:00:10.90 |
|   9 |          NESTED LOOPS OUTER                   |                               |      1 |      1 |   4309 |00:00:10.76 |
|  10 |           NESTED LOOPS                        |                               |      1 |      1 |   3482 |00:00:10.57 |
|  11 |            NESTED LOOPS OUTER                 |                               |      1 |      1 |   3482 |00:00:10.00 |
|  12 |             TABLE ACCESS BY GLOBAL INDEX ROWID| SXPS_P_ACCNT_AMPM_J_ICIPD     |      1 |      1 |   1534 |00:00:03.27 |
|* 13 |              INDEX RANGE SCAN                 | SXPS_JTPP_PTC_DAT_ACC_HR_NI   |      1 |      1 |   1534 |00:00:00.20 |
|  14 |             PARTITION RANGE ITERATOR          |                               |   1534 |      1 |   3222 |00:00:06.73 |
|* 15 |              TABLE ACCESS BY LOCAL INDEX ROWID| SXPS_SIGNALIS                 |   1534 |      1 |   3222 |00:00:06.71 |
|* 16 |               INDEX RANGE SCAN                | SXPS_SIG_NUI_FK_I             |   1534 |      3 |  59193 |00:00:05.58 |
|  17 |            TABLE ACCESS BY GLOBAL INDEX ROWID | SXPS_ACCNT_AMPM_J             |   3482 |      1 |   3482 |00:00:00.56 |
|* 18 |             INDEX UNIQUE SCAN                 | SXPS_NUI_PK                   |   3482 |      1 |   3482 |00:00:00.40 |
|  19 |           PARTITION RANGE ITERATOR            |                               |   3482 |      1 |   1437 |00:00:00.19 |
|  20 |            TABLE ACCESS BY LOCAL INDEX ROWID  | SXPS_ACCNT_COMMENTS           |   3482 |      1 |   1437 |00:00:00.17 |
|* 21 |             INDEX RANGE SCAN                  | SXPS_TRC_NUI_FK_I             |   3482 |      2 |   1437 |00:00:00.14 |
|* 22 |          TABLE ACCESS BY INDEX ROWID          | SXPS_PTREF                    |   4309 |      1 |   4006 |00:00:00.13 |
|* 23 |           INDEX RANGE SCAN                    | SXPS_PTF_PK                   |   4309 |      3 |  17910 |00:00:00.04 |
|* 24 |         TABLE ACCESS BY INDEX ROWID           | SXPS_ICIPD                    |   4309 |      1 |   4309 |00:00:00.12 |
|* 25 |          INDEX RANGE SCAN                     | SXPS_PTC_PK                   |   4309 |      3 |  30163 |00:00:00.04 |
|* 26 |        TABLE ACCESS BY INDEX ROWID            | SXPS_PTREF                    |   4309 |      1 |   4018 |00:00:00.11 |
|* 27 |         INDEX RANGE SCAN                      | SXPS_PTF_PK                   |   4309 |      3 |  18416 |00:00:00.03 |
|  28 |       VIEW                                    |                               |      1 |     28 |   1955 |00:00:00.08 |
|  29 |        HASH GROUP BY                          |                               |      1 |     28 |   1955 |00:00:00.08 |
|  30 |         TABLE ACCESS BY GLOBAL INDEX ROWID    | SXPS_ACCNT_COMMENTS           |      1 |     28 |   1955 |00:00:00.08 |
|* 31 |          INDEX RANGE SCAN                     | SXPS_TRC_COM_FR_NI            |      1 |     29 |   1955 |00:00:00.04 |
|  32 |     SORT AGGREGATE                            |                               |    232 |      1 |    232 |00:00:00.01 |
|  33 |      PARTITION RANGE SINGLE                   |                               |    232 |      1 |    528 |00:00:00.01 |
|* 34 |       TABLE ACCESS BY LOCAL INDEX ROWID       | SXPS_ACCNT_COMMENTS           |    232 |      1 |    528 |00:00:00.01 |
|* 35 |        INDEX RANGE SCAN                       | SXPS_TRC_NUI_FK_I             |    232 |      1 |    528 |00:00:00.01 |
|* 36 |    FILTER                                     |                               |      1 |        |     15 |00:00:00.08 |
|* 37 |     FILTER                                    |                               |      1 |        |     15 |00:00:00.08 |
|* 38 |      HASH JOIN OUTER                          |                               |      1 |      1 |     15 |00:00:00.08 |
|  39 |       NESTED LOOPS OUTER                      |                               |      1 |      1 |     15 |00:00:00.07 |
|  40 |        NESTED LOOPS                           |                               |      1 |      1 |     15 |00:00:00.07 |
|  41 |         NESTED LOOPS                          |                               |      1 |      1 |     15 |00:00:00.07 |
|  42 |          NESTED LOOPS OUTER                   |                               |      1 |      1 |     15 |00:00:00.07 |
|  43 |           NESTED LOOPS OUTER                  |                               |      1 |      1 |     15 |00:00:00.05 |
|  44 |            NESTED LOOPS OUTER                 |                               |      1 |      1 |     15 |00:00:00.03 |
|  45 |             TABLE ACCESS BY GLOBAL INDEX ROWID| SXPS_P_ACCNT_AMPM_J_ICIPD_SUP |      1 |      1 |     14 |00:00:00.03 |
|* 46 |              INDEX RANGE SCAN                 | SXPS_PTPS_PTC_HR_NI           |      1 |      1 |     14 |00:00:00.03 |
|  47 |             PARTITION RANGE ITERATOR          |                               |     14 |     33 |      8 |00:00:00.01 |
|* 48 |              TABLE ACCESS BY LOCAL INDEX ROWID| SXPS_SIGNALIS                 |     14 |     33 |      8 |00:00:00.01 |
|* 49 |               INDEX RANGE SCAN                | SXPS_SIG_NUI_FK_I             |     14 |      3 |    390 |00:00:00.01 |
|* 50 |            TABLE ACCESS BY INDEX ROWID        | SXPS_PTREF                    |     15 |      1 |      8 |00:00:00.02 |
|* 51 |             INDEX RANGE SCAN                  | SXPS_PTF_PK                   |     15 |      3 |     65 |00:00:00.01 |
|* 52 |           TABLE ACCESS BY INDEX ROWID         | SXPS_PTREF                    |     15 |      1 |      8 |00:00:00.02 |
|* 53 |            INDEX RANGE SCAN                   | SXPS_PTF_PK                   |     15 |      3 |     63 |00:00:00.01 |
|  54 |          TABLE ACCESS BY GLOBAL INDEX ROWID   | SXPS_ACCNT_AMPM_J             |     15 |      1 |     15 |00:00:00.01 |
|* 55 |           INDEX UNIQUE SCAN                   | SXPS_NUI_PK                   |     15 |      1 |     15 |00:00:00.01 |
|* 56 |         TABLE ACCESS BY INDEX ROWID           | SXPS_ICIPD                    |     15 |      1 |     15 |00:00:00.01 |
|* 57 |          INDEX RANGE SCAN                     | SXPS_PTC_PK                   |     15 |      3 |    105 |00:00:00.01 |
|  58 |        PARTITION RANGE ITERATOR               |                               |     15 |      1 |      0 |00:00:00.01 |
|  59 |         TABLE ACCESS BY LOCAL INDEX ROWID     | SXPS_ACCNT_COMMENTS           |     15 |      1 |      0 |00:00:00.01 |
|* 60 |          INDEX RANGE SCAN                     | SXPS_TRC_NUI_FK_I             |     15 |      2 |      0 |00:00:00.01 |
|  61 |       VIEW                                    |                               |      1 |     28 |   1955 |00:00:00.01 |
|  62 |        HASH GROUP BY                          |                               |      1 |     28 |   1955 |00:00:00.01 |
|  63 |         TABLE ACCESS BY GLOBAL INDEX ROWID    | SXPS_ACCNT_COMMENTS           |      1 |     28 |   1955 |00:00:00.01 |
|* 64 |          INDEX RANGE SCAN                     | SXPS_TRC_COM_FR_NI            |      1 |     29 |   1955 |00:00:00.01 |
|  65 |     SORT AGGREGATE                            |                               |      0 |      1 |      0 |00:00:00.01 |
|  66 |      PARTITION RANGE SINGLE                   |                               |      0 |      1 |      0 |00:00:00.01 |
|* 67 |       TABLE ACCESS BY LOCAL INDEX ROWID       | SXPS_ACCNT_COMMENTS           |      0 |      1 |      0 |00:00:00.01 |
|* 68 |        INDEX RANGE SCAN                       | SXPS_TRC_NUI_FK_I             |      0 |      1 |      0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------------------------------

The corresponding SQL query has not been shown intentionally because what I intend to show here is how to enhance a query execution time using exclusively its corresponding explain plan. The predicate part is left outside the picture for the moment.   I will get back to it later in this article. In addition, looking at the Estimations (E-Rows) done by the CBO and the real returned rows (A-Rows), shows clearly that statistics are not up-to-date and should be collected again. However, I am not going to concentrate my effort here on the stale statistics but either on how to point out operations that need to be tuned. It could be that stale statistics are the main problem here, but we will suppose that they aren’t and we will focus our attention on reading explain plan operations.

Having said that, from where are we going to start investigating this complex explain plan?  Well, there are several points in this explain plan to be emphasized:

1. The total execution time of the query is 11,41 seconds and it returned 3494 rows

---------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                          | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------------------------------
|   1 |  SORT UNIQUE      |                               |      1 |      2 |   3494 |00:00:11.41 |

2. There are two sets of operations that are mainly contributing to this 11,41 seconds:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | E-Rows | A-Rows |   A-Time
-----------------------------------------------------------------------------------------------------------------
|  14 |     PARTITION RANGE ITERATOR           |                    |   1534 |   1      |   3222   |00:00:06.73 |
|* 15 |      TABLE ACCESS BY LOCAL INDEX ROWID | SXPS_SIGNALIS      |   1534 |   1      |   3222   |00:00:06.71 |
|* 16 |        INDEX RANGE SCAN                | SXPS_SIG_NUI_FK_I  |   1534 |   3      |  59193   |00:00:05.58 |

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Starts | E-Rows | A-Rows |   A-Time
-----------------------------------------------------------------------------------------------------------------
|  12 | TABLE ACCESS BY GLOBAL INDEX ROWID| SXPS_P_ACCNT_AMPM_J_ICIPD   |   1     |  1      |   1534 |00:00:03.27|
|* 13 |   INDEX RANGE SCAN                | SXPS_JTPP_PTC_DAT_ACC_HR_NI |   1     |  1      |   1534 |00:00:00.20|

These two sets of operations consume 90% of the total response time which is 10 seconds (6,73+3,27). Let’s now think about how to tune them. Looking at operation 16(INDEX RANGE SCAN) we can notice that it generates 59193 records and feeds back it parent operation 16 (TABLE ACCESS BY GLOBAL INDEX ROWID|) with the corresponding rowid of these 59193 records. The anomaly here is that the parent operation 16 visits the table SXPS_SIGNALIS and discards 55971 records letting only 3222 records to be sent back to its parent operation 14. That is an enormous waste of time and energy which is very often an indication of a non precise index. How would we remedy to this anomaly? And here where the Predicate part of the explain plan comes into play. Each time you see an asterisk in front of an operation Id (|* 15 |) this means that this operation has a predicate part which has to be investigated:

15 - filter("R"."NUM_OR"="P"."NUM_OR")
16 - access("R"."ACCNT_ID"="P"."ACCNT_ID" AND "R"."DAT_ACC"="P"."DAT_ACC")

There is an access predicate on operation 16 (INDEX RANGE SCAN   SXPS_SIG_NUI_FK_I) with ACCNT_ID and DAT_ACC followed by a filter predicate on NUM_OR column done by operation 15 (TABLE ACCESS BY LOCAL INDEX ROWID). The index SXPS_SIG_NUI_FK_I is defined on (ACCNT_ID, DAT_ACC); why not create a precise index with these three critical columns (ACCNT_ID, DAT_ACC, NUM_OR)?

create index MHO_SXPS_REP on SXPS_SIGNALIS(DAT_ACC, ACCNT_ID, NUM_OR);

Now that the new index is in place, queering again gives the new explain plan (in which I am showing only interested operations)

--------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Starts | E-Rows | A-Rows |   A-Time
--------------------------------------------------------------------------------------------------------
|  15 |    TABLE ACCESS BY GLOBAL INDEX ROWID | SXPS_SIGNALIS    |   1534 |     1  |   3222 |00:00:00.60
|* 16 |      INDEX RANGE SCAN                 | MHO_SXPS_REP     |   1534 |     1  |   3222 |00:00:00.17

With the corresponding predicate part reported here below:

16 - access("R"."DAT_ACC"="P"."DAT_ACC" AND "R"."ACCNT_ID"="P"."ACCNT_ID" AND "R"."NUM_OR"="P"."NUM_OR")

Observe the difference:  the infamous filter on SXPS_SIGNALIS table has gone! The index MHO_SXPS_REP is very precise because all the 3222 rowid passed to its parent operation have been kept and no time has been spent throwing out unnecessary records. The effect of this object change on the execution time is worth noticing: from 6,73 seconds to 0,6 seconds.

What, now, if the access to SXPS_SIGNALIS table is totally eliminated from the query operations?  To do so, we need to know the number of columns that are  selected from this table.  Observing the query reveals that, in addition to the three indexed columns, the query selects from SXPS_SIGNALIS table two supplementary columns, ACC_ID_ORG and ACC_ID_DEST. Let’s then re-create the above index with those two supplementary columns as shown below:

create index SXPS_SIG_NUM_OR_ptf_ni on SXPS_SIGNALIS(DAT_ACC,ACCNT_id, NUM_OR,ACC_ID_ORG, ACC_ID_DEST) compress 2;

I added a compress 2 clause to the creation of this index in order to make it small and to favor its placement on the data buffer cash generating less physical I/O. Compressing an index is a very interesting option that I will discuss about it in another occasion.  Re-executing the same query gives the following new situation:

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                          | Starts | E-Rows | A-Rows |   A-Time
------------------------------------------------------------------------------------------------------
|* 14 |  INDEX RANGE SCAN    | SXPS_JTPP_PTC_DAT_ACC_HR_NI   |    1   |    1   |   1534 |00:00:00.26 |
|* 15 |  INDEX RANGE SCAN    | MHO_SXPS_REP                  |   1534 |    1   |   3222 |00:00:00.10 |

15 - access("R"."DAT_ACC"="P"."DAT_ACC" AND "R"."ACCNT_ID"="P"."ACCNT_ID" AND "R"."NUM_OR"="P"."NUM_OR")

Access to the SXPS_SIGNALIS table has gone away. Remains only the access to the new MHO_SXPS_REP index which is completed in 0.1 seconds

At this tuning point, let’s summarise where we stand. We succeed via the creation of a precise index to replace two operations by one operation and to reduce the execution time from 6,73 to 0.1 seconds. No let’s now go further and try to find other oddities in the original explain plan. Look at the following operation and spot the problem:

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time
-----------------------------------------------------------------------------------------------
|* 24 | TABLE ACCESS BY INDEX ROWID  | SXPS_ICIPD     |   4309 |      1 |   4309 |00:00:00.12 |
|* 25 |  INDEX RANGE SCAN            | SXPS_PTC_PK    |   4309 |      3 |   30163|00:00:00.04 |

24 - filter("S"."VALID_TO_DATE">=SYSDATE@!)
25 - access("S"."ICIPD_NO"="P"."ICIPD_NO" AND "S"."VALID_FROM_DATE"<=SYSDATE@!)

In close comparison to what has been said above, there is here also a waste of time and energy consumed by the parent operation throwing out 25854(30163-4309) records. Generally speaking, each time you see such a kind of situation, you should immediately think about a non precise index. The index used here is, as its name clearly indicates it, the primary key index defined as (ICIPD_NO , VALID_FROM_DATE ) while the query involves (ICIPD_NO , VALID_FROM_DATE, VALID_TO_DATE ).  It seems that there is no such a kind of three columns index. An investigation shows however a presence of the following index SXPS_PTC_DAT_NI(VALID_FROM_DATE, VALID_TO_DATE , ICIPD_NO ). So, why this precise index has not been used?  There is a fairly simple answer to this question: the existing index has not been used because its leading column is used in a range based predicate (“S”.”VALID_TO_DATE”>=SYSDATE@!)and not in the equality ((“S”.”ICIPD_NO”=”P”.”ICIPD_NO”). It is worth advising not to put a column, on which you are going to apply range predicates (>, <), at the leading edge of the index.

To demonstrate this let’s create a new index (SXPS_PTC_PTCNO_VDAT_NI) in which we will change the order of its columns to be (ICIPD_NO , VALID_FROM_DATE , VALID_TO_DATE) and re-execute the query

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                     | Starts | E-Rows | A-Rows |   A-Time
--------------------------------------------------------------------------------------------------
|* 17 |  INDEX RANGE SCAN   | SXPS_PTC_PTCNO_VDAT_NI   | 4309   |    3   | 4309   |00:00:00.03   |

17 - access("S"."ICIPD_NO"="P"."ICIPD_NO" AND "S"."VALID_TO_DATE">=SYSDATE@! AND "S"."VALID_FROM_DATE"<=SYSDATE@!)
filter("S"."VALID_TO_DATE">=SYSDATE@!)

The access to the table has gone away and the new index became very precise because its leading column (ICIPD_NO) is the one used in the equality predicate.

The bottom line from this article is

1. Know how to locate in explain plans operations that are worth considering in the tuning process
2. Each time a big number of rows is discarded between an index operation and its parent table operation think about creating a precise index. The predicate part of the explain plan will help you finding the appropriate columns that are candidate to be in the precise index
3. Think carefully when designing a composite index to start this index by columns figuring in an equality predicate and not in a range based predicate.

DBMS_XPLAN : Starts in action

Very often, if not always, I am advocating the use of

select * from table(dbms_xplan.display_cursor(null,null, ‘ALLSTATS LAST’));

in order to  

1.  Get the real explain plan and not the approximate onet
2.  Judge about the Estimations the CBO is doing

And today I want to add

            3.  See if an operation has been started or not by the CBO

Here below a little demonstration of point 3.

mhouri > select empno, ename
  2  from emp
  3  where empno is null;
no rows selected

mhouri > select * from table(dbms_xplan.display_cursor);
-------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |    
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     1 (100)|         |       
|*  1 |  FILTER            |      |       |       |            |         |      
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   150 |     5   (0)| 00:00:01|
--------------------------------------------------------------------------
Predicate Information (identified by operation(id):
---------------------------------------------------                    
   1 - filter(NULL IS NOT NULL)                                                                                         
19 rows selected.

It’s a pity to apply a FILTER to operation 2 (TABLE ACCESS FULL) when you know that this filter will discard all the rows because NULL is never NOT NULL. So why the CBO execute a TABLE ACCESS FULL on EMP when it knows that the filter will discard all returned rows.

But wait a moment. Are you sure that the CBO did execute operation 2? Let’s then demonstrate that:

mhouri > select /*+ gather_plan_statistics */ empno, ename
  2  from emp
  3  where empno is null;
no rows selected

mhouri > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time  |
-------------------------------------------------------------------------
|*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01|
|   2 |   TABLE ACCESS FULL| EMP  |      0 |     15 |      0 |00:00:00.01|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
19 rows selected.

Ah ha ha!!! When we look at the column Starts for operation 2, you will notice that it equals 0 which means that this operation has never been started by the CBO. This is confirmed also by the information A-Rows of the FILTER operation which equals 0 meaning that the filter has been started only one time and applied on 0 rows

 Bottom line here, is that using the ALLSTATS LAST property of the DBMS_XPLAN package can reveals valuable information one can use to understand the work done by the CBO

Deadlock – Part 3: Non Indexed Foreign key in action

A recent question on otn about deadlock came up last week. The deadlock graph was as such that I was wondering if I have already encountered it before.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-001a0002-0002a0fe       196     197     X            166    1835           S
TM-0000c800-00000000       166    1835    SX            196     197    SX   SSX

Albeit it is an incomplete deadlock graph because it doesn’t present the type of sql session 197 and 1835 were executing before they deadlocked, we can point out that this deadlock involves two types of locks Transaction (TX) and DML (TM) locks. I have never seen yet both type of locks within the same deadlock graph.

Knowing that TM enqueue can almost always be due to a DML operation on a parent–child table where the foreign key is not indexed, I asked then the Original Poster (OP) to check using Tom Kyte script if his OLTP application contains non indexed foreign keys. But I was a little bit struggled when the OP said that the deadlock occurs on an insert statement. Thought that he was unable to feed us with the sql the other session was doing before the deadlock occurred. In a follow up to the same thread, Jonathan Lewis gave a possible scenario for this kind of deadlock.  I decided to implement this scenario and to analyze the generated trace file. I opened two sessions (session3 is just to select from v$lock) and followed the steps described below:

session1 > select distinct sid from v$mystat;
  SID                                                 
----------                                                 
  1074  

session1 > create table parent (x number primary key);
Table created.

session1 > create table child (y number references parent);
Table created.

session1 > insert into parent values (1);
1 row created.

session1 > insert into parent values (2);
1 row created.

session1 > insert into parent values (3);
1 row created.

session1 > insert into child values (1);
1 row created.

session1 > insert into child values (3);
1 row created.

session1 > commit;
Commit complete.

session1 > delete from child where y = 1;
1 row deleted.

session1 > delete from parent where x = 1;
1 row deleted.

 At this step there are still no locks acquired as it can be verified via a simple query against v$session and v$lock

  
session3 > start getlocks.sql
no rows selected

Now, open a new session (session2) and issue the following insert

session2 > select distinct sid from v$mystat;
SID                                                                     
-----                                                                     
333

session2 > insert into child values (1);

session2 (SID 333) starts hanging!!! From session3, we can see that a lock has been acquired

  
session3 > start getlocks.sql
SID       WSID LOCK_TYPE           MODE_HELD          MODE_REQUESTED                                                                                                
---------- ---------- ------------------- ------------- ---------------------                                                                    
1074      333 Transaction         Exclusive           Share        

Then, go back to session 1 (SID 1074) and issue the following delete

  
session1 > delete from parent where x = 2;

The last delete hangs at about 3 seconds before it successfully complete.  If you manage to check the new lock situation before the deadlock happens you will see the following picture:

  
session3 > start getlocks.sql
SID       WSID LOCK_TYPE           MODE_HELD              MODE_REQUESTED                                                                                               
---------- ---------- ------------------- ----------------------------------------
333       1074 DML                 Row-X (SX)             S/ROW-X (SSX)                                                                                                
1074       333 Transaction          Exclusive              Share                 

 And finally after about 3 seconds of wait, session2 with SID 333 has been deadlocked and its transaction stopped by Oracle

  
insert into child values (1)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

 The corresponding generated deadlock graph looks like:

  
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-00011fe5-00000000        28      333    SX             27     1074    SX   SSX  --> the session timed out
TX-000a0013-0000039d        27     1074     X             28      333           S  --> the persistent session
 session 333: DID 0001-001C-00000006      session 1074: DID 0001-001B-00000030
session 1074: DID 0001-001B-00000030     session 333: DID 0001-001C-00000006
 Rows waited on:
  Session 333: no row
  Session 1074: no row
 ----- Information for the OTHER waiting sessions -----
Session 1074:
  sid: 1074 ser: 93 audsid: 540090 user: 84/MOHAMED flags: 0x8000045
  pid: 27 O/S info: user: SYSTEM, term: FSC401216102802, ospid: 3708
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: FSC401216102802\Mohamed, term: FSC401216102802, ospid: 2816:748
    machine: WORKGROUP\FSC401216102802 program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  delete from parent where x = 2
 ----- End of information for the OTHER waiting sessions -----
 Information for THIS session:
 ----- Current SQL Statement for this session (sql_id=6cnrmyxw03k6f) -----
insert into child values (1)
===================================================

 Since then, I know that an unindexed foreign key can present a deadlock situation with a graph showing both TX and TM enqueue.

Special Merge

An interesting question came up on the otn thread ( http://forums.oracle.com/forums/thread.jspa?messageID=9772504&#9772504) a couple of days ago. The original poster was asking on how to avoid superfluous updates when using the MERGE command. He said that he wants the update to start only if one of the columns to be matched has been changed; otherwise he would like the update to be ignored by the MERGE command when a matching key has been found.

I included my answer within the thread. How, in your case, would you proceed to accomplish the original poster requirement?

On the use of dbms_metadata.get_ddl

A question recently came into an oracle French forum (http://www.developpez.net/forums/d1117051/bases-donnees/oracle/source-vue-format-text/)  asking about how to get a DDL of views containing a given text

I thought it is worth writing here the English translation of my French answer to that thread. I will start by creating a view in which I will use the term ‘Mohamed’

mhouri.world> create view view_test as
2  select *
3  from emp
4  where ename = 'Mohamed';
View created.

And now we would like to get views that contains the text ‘Mohamed’ in their DDL creation script.

mhouri.world> SELECT *
2    FROM
3    (SELECT
4           dbms_metadata.get_ddl('VIEW', view_name) txt
5     FROM  user_views
6    ) ww
7  WHERE
8     dbms_lob.instr (ww.txt, 'Mohamed') != 0
9  ;

CREATE OR REPLACE FORCE VIEW "MHOURI"."VIEW_TEST" ("EMPNO", "ENAME", "JOB", "MGR
", "HIREDATE", "SAL", "COMM", "DEPTNO") AS
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
from emp
where ename = 'Mohamed'

Hope this will help.

Extracting separate information from a character string

I have been recently asked to develop a pl/sql stored procedure in order to dequeue a payload message from an Oracle Advanced queue. It was a text message. Don’t ask me why they didn’t foresee an object type for that purpose, I did asked the same question and I have been told that this is the standard they have been successfully using since several years and they do not have the intention to change it.

The goal is to dequeue that text message and extract from it several fields that are separated by a given delimiter.  I decided to reproduce this development in a blog article so that I can found it easily and also redirect to it when similar request will be done in Oracle forums.

mhouri.world> create or replace package split_text
3  IS
4   TYPE t_msg_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
7    FUNCTION f_extract_message_info (piv_message IN VARCHAR2
8                                    ,piv_delim   IN VARCHAR2)
9       RETURN t_msg_array;
11  END split_text;
12  /

Package created.

 

mhouri.world> create or replace package body split_text
2  IS
3  FUNCTION f_extract_message_info (piv_message IN VARCHAR2
5                                   ,piv_delim   IN VARCHAR2)
6     RETURN t_msg_array
7  IS
8     l_t_msg_array   t_msg_array;
9     ln_pos          NUMBER          := 0;
10     li              INTEGER         := 0;
11     lv_str          VARCHAR2 (4000) := piv_message;
12
13  BEGIN
14     -- Get the position fo the first delimitor
15     ln_pos := instr(lv_str,piv_delim,1,1);
16
17     -- While there is still delimitors loop
18     WHILE ( ln_pos != 0)
19     LOOP
20
21       -- Remove the first delimitor from the message
22       lv_str := substr(lv_str,ln_pos + length(piv_delim),length(lv_str));
23
24       -- Determine now the new position of the first delimiter
25       ln_pos := instr(lv_str,piv_delim,1,1);
26
27       -- Fill each information of the message in an array
28       li := li + 1;
29       IF ln_pos != 0
30       THEN
31         l_t_msg_array(li) := substr(lv_str,1, ln_pos-1);
32       ELSE
33         l_t_msg_array(li) := lv_str;
34       END IF;
35
36     END LOOP;
37
38     -- return the array
39      RETURN l_t_msg_array;
41  END f_extract_message_info;
44  END split_text;
45  /

Package body created.

Few points should be taken into account

1. piv_delim represents the fields delimiter
2. the message text should starts by the delimiter

And here below is a simple test of  this function:

mhouri.world> set serveroutput on

mhouri.world> declare
2   piv_txt            varchar2(4000);
3   l_t_jus_msg_array  split_text.t_msg_array;
4
5  BEGIN
6
7   piv_txt := '#@test01#@test02#@test03';
8
9   l_t_jus_msg_array     := split_text.f_extract_message_info(piv_txt,'#@');
10
11   dbms_output.put_line(' item1 := '||l_t_jus_msg_array(1) );
12   dbms_output.put_line(' item2 := '||l_t_jus_msg_array(2));
13   dbms_output.put_line(' item3 := '||l_t_jus_msg_array(3));
14
15
16  END;
17  /

item1 := test01
item2 := test02
item3 := test03

PL/SQL procedure successfully completed.

Using Function Based index appropriately

Very often requests to enhance performance of queries resembling to the following one:

SELECT col1
,col2
,col3
FROM t
WHERE flag != 'N';

populate forums like OTN. Requesters have already created an index on the flag column  and are complaining about the query which is not performing well and which is not using the index they created to cover the  flag where clause column. The main problem here is the “!=” operator.  The Oracle Cost based Optimizer (CBO) is unable to use an index to handle this operator. Fortunately, there is a possibility to enhance this kind of queries by taking the advantage of using a function based index. And this is what I aim to explain here via this blog article:

mhouri.world > create table t as select * from all_objects;
Table created.

mhouri.world > select count(1) from t;
COUNT(1)
----------
39114

mhouri.world > SELECT /*+ gather_plan_statistics */
2         t.*
3    FROM t
4   WHERE status != 'VALID'
5  ;

30 rows selected.

mhouri.world > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=
'VALID'
Plan hash value: 2153619298
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'VALID')
Note
-----
- dynamic sampling used for this statement

22 rows selected.

Let’s now create an index on the status column, compute statistics and repeat the same select

mhouri.world > create index ind_status on t (status);

Index created.

mhouri.world >BEGIN
2     DBMS_STATS.gather_table_stats (ownname          => USER,
3                                    tabname          =>'t1',
4                                    estimate_percent => 100,
5                                    method_opt       => 'FOR ALL COLUMNS SIZE 1'
6                                   );
7  END;
8  /

PL/SQL procedure successfully completed.

mhouri.world > SELECT /*+ gather_plan_statistics */
2     t.*
3  FROM t
4  WHERE status != 'VALID';

30 rows selected.

mhouri.world > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=
'VALID'
Plan hash value: 2153619298
-----------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'VALID')

Note
-----
- dynamic sampling used for this statement
22 rows selected.

The newly created index has not been of any help. It hasn’t been used at all by the CBO. This is of course due to the “!=” operator. Let’s then, now, create a function based index and requery again.

mhouri.world > create index ind_fbi_status on t (case when status = 'VALID' then NULL else 'X' end );

Index created.

mhouri.world > SELECT /*+ gather_plan_statistics */
2     t.*
3  FROM t
4  WHERE status != 'VALID';

30 rows selected.

mhouri.world > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=
'VALID'
Plan hash value: 2153619298
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'VALID')
Note
-----
- dynamic sampling used for this statement

22 rows selected.

Nothing noticeable happened; we are still full scanning our T table. You know why? This is because to take advantage of using the newly created function based (FBI) index, we have to rewrite the query to make the query and the index consistent with each other.

mhouri.world > SELECT /*+ gather_plan_statistics */
2         t.*
3    FROM t
4    WHERE CASE
5            WHEN status = 'VALID'
6               THEN NULL
7            ELSE 'X'
8         END = 'X';

30 rows selected.

mhouri.world > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  32fcmm91ywcqf, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.*   FROM t  WHERE CASE           WHEN status =
'VALID'              THEN NULL           ELSE 'X'        END = 'X'
Plan hash value: 3817300654
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |      1 |      6 |     30 |00:00:00.01 |      14 |
|*  2 |   INDEX RANGE SCAN          | IND_FBI_STATUS |      1 |    130 |     30 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00014$"='X')

Note
-----
- dynamic sampling used for this statement

23 rows selected.

And here we are. This is a perfect example of how to cover queries that are not very well performing because of “!=” operator which impeaches a normal index on the corresponding column to be used.

Warning: the above FBI index will not work correctly if the status column is allowed to be null.

mhouri.world> update t
2  set status = null
3  where status != 'VALID'
4  and rownum <= 4;

4 rows updated.

mhouri.world> commit;

Commit complete.

mhouri.world> SELECT count(1)
2    FROM t
3   WHERE status != 'VALID';

COUNT(1)
----------
26

mhouri.world>  SELECT count(1)
2       FROM t
3       WHERE CASE
4               WHEN status = 'VALID'
5                  THEN NULL
6               ELSE 'X'
7            END = 'X';

COUNT(1)
----------
30

The query using the FBI index is not returning the correct number of rows because, now, the status column has null values in it.  Both the query and the FBI index should be re-factored in order to take into account the NULL values of the status column:

mhouri.world> create index ind_fbi_status on t(case when nvl(status,'VALID') = 'VALID' then null else 'X' end);

Index created.

mhouri.world> select count(1)
2  from t
3  where case when nvl(status,'VALID') = 'VALID'
4                 then null
5             else 'X'
6             end = 'X';

COUNT(1)
----------
26

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  ffj2y4c2q8vup, child number 0
-------------------------------------
select count(1) from t where case when nvl(status,'VALID') = 'VALID'
then null            else 'X'            end = 'X'
Plan hash value: 3388779105
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IND_FBI_STATUS |     6 |    30 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00014$"='X')

Note
-----
- dynamic sampling used for this statement

To be followed.

Bind variable, shared pool and cursor sharing parameter

I was asked to investigate a real life production problem concerning the shared pool (library cache) of the SGA which come to be full so that the only solution which remains available was to stop and restart the data base.  I did immediately start thinking if I have not already been confronted to a similar case until I do realize that I have read something close to this situation in Chapter 4 – Memory Structures – of Tom Kyte book Expert Oracle Data Base Architecture 9i and 10g Programming Techniques and Solution;

So my first step in the trouble shooting path of this memory problem was to issue the following select against the v$sql dynamic view:

SELECT sql_text
      ,executions
FROM v$sql
WHERE sql_text LIKE '%Package_Name.P_UPDATE%'
AND executions <= 2
;

Which returns the following data picture:

sql_text                                           executions
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
…/…
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1

Thousands of same PL/SQL BEGIN Package_Name.P_UPDATE_procedure(p_in_p1 ..) END; executed once for each call. It is clear that  this application is suffering from not using bind variables and is dramatically filling up the library cache of the shared pool. This needs absolutely to be fixed.

Then I asked myself how could they fill up the shared pool while all what they are doing is calling a stored PL/SQL procedure? We all know that when using PL/SQL (static SQL) we should not encounter issues related to bind variables. PL/SQL itself takes care of our code and uses bind variables behind the scene. The only situation where we have to look carefully to the use of bind variable within PL/SQL is when we use Dynamic sql into stored procedures or functions (this is in fact another reason to avoid using dynamic SQL).

So how could they arrive to such a shared pool filled by thousands of same non re-executed PL/SQL calls?

And here where the problem resides: it is true that when using static PL/SQL stored objects, you don’t have to care about using bind variables inside those stored programs but you have to care about the third party which will call your PL/SQL stored procedure; they should do that using input parameters as bind variables; otherwise you will have your shared pool (library cache) full of calls to your stored PL/SQL objects as it is the case here.

Knowing this, I started figuring out the solution. I have identified the application responsible of those thousands of PL/SQL BEGIN package. Procedure (p_in_p1 …) END.  It is a non Oracle application developed with a language named SNAP and which is calling our PL/SQL stored objects without using bind variables. I explained them that they need to use OCI interface to prepare their statement, and implement it correctly using bind variables via OCIStmtPrepare2 and OCIDefineByPos (http://www.amazon.fr/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/1590599179

Although they agreed doing that they have asked me to find a work around in between. The only dirty work around I was able to suggest them is to alter the session they are connecting to (and via which they are calling the stored PL/SQL procedure) so that the corresponding cursor_sharing value will be set to “FORCE”.

alter session set cursor_sharing=force;

Which they did immediately.

However, the same sql against the v$sql executed after this change was still showing the same picture of thousands of non re-executed calls

sql_text                                          executions
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
BEGIN Package_Name.P_UPDATE_procedure(p_in_p1          1
…/…

What went wrong?

I verified that they have really altered their session to set the cursor_sharing parameter to FORCE!!! So what’s going on here?

The problem in this case is that even if you set the cursor_sharing parameter to FORCE if you still keep the calls like:

BEGIN
Package_Name.P_UPDATE_procedure(p_in_p1,…);
END;

Oracle will not perform bind variables substitution (even when cursor sharing is set to force). In order for this substitution to occur we need to change the above code to be as follows (see http://apress.com/book/view/9781590596364)

   CALL Package_Name.P_UPDATE_procedure(p_in_p1,…);

So they did the change and so we obtain the new picture taken from v$sql:

SELECT
    substr(sql_text,1,55)
   ,executions
FROM v$sql
WHERE sql_text LIKE '%Package_Name.P_UPDATE%'
AND executions <= 2
;

which gives now the new following picture:

sql_text                                                 executions
------------------------------------------------------- ----------
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO        897
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO       8380
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO         24
CALL Package_Name.P_UPDATE_procedure (:"SYS_B_00",TO      18671

Woo. See how the number of executions has gone so high 18.671. The shared pool becomes very attractive now.

It is also known that setting cursor_sharing to force may have unexpected side effects; for example when there exist a function based index having literals in its definition (substr (col_nam, 4, 2) for example), in this case you will notice that your query which was using your function based index starts not using it since you’ve set your cursor sharing to force. This is simply because oracle will change your query predicate from substr (col_name, 4,2) to something like substr (col_name,:SYS_B_0,SYS_B_1) which will not match your query predicate anymore and hence will not use your index.

The bottom line is that setting the cursor_sharing to force has to be a temporary solution and that the definitive solution should be to use correctly bind variables when calling PL/SQL stored object.