Apr 28, 2014

TNS-12599: TNS:cryptographic checksum mismatch

After upgrading to 11.2.0.3.0, we had been receiving this error "TNS-12599: TNS:cryptographic checksum mismatch" quite frequently.

Found related information in Oracle support. The thing is - when Enterprise Manager logs in or out from a 11.1 (11gR1) or 11.2 (11gR2) target database or as the OMS database repository, it causes several TNS-12599 errors in the alert.log of the respective database.

This is for the bug 9953045.

Workaround:

1. On the target database side, set SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168)  in sqlnet.ora

2. Patches for bug 9953045 will fix the problem.



Apr 2, 2014

Replacing SQL literals with Global Temporary Table backfired at the beginning

I always emphasize on understanding data and business domain for query optimization. One must have technical skills in combination with the domain knowledge. Other than the blend, who could tune at this level!

We planned to tune some of the queries where we use literals and can't use bind variables as those are inside 'IN clause', and we don't exactly know how many values would be there (dynamically added by the application). We decided to replace the literals in 'IN clause' by Global Temporary Table (GTT). That change made every other queries perform better or at least same as before other than the SQL I am going to talk below. Changing literals in 'IN clause' caused to SQL to come up with very wrong execution plan and blundered our system's stability - the frequency of execution was very high for the SQL, we could not afford the bad plan.

After analyzing the plan, I found that the SQL with Global Temporary Table (GTT) doing unexpected Nested Loops at the very early state of execution order which was causing more data to process in other steps for the result. We know that, the join with GTT should be done at the later or last stage of execution order. And the best way (for the best selectivity) is to start execution with the underlying table mv_caseload_priv (through the index MV_PGM_CASE_I) we have in the client_access_role view. I drew the plan by hand and attached below for a clear understanding what was going on. The Nested Loop (NL, marked as red circle) was very unexpected, also the execution start point (marked as red cross) was not optimum.


The original SQL and the plan, only problem was not having bind variables, sometimes this 
ended up with terrible execution plan for some bind values in the SQL, was uncontrollable.

SQL > SELECT DISTINCT login_id
FROM client_access_role car
WHERE car.pgm_id = :pgm
AND car.role IN (21,22,112,......);


Elapsed: 00:00:00.11
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         | 11900 |   429K|   678   (1)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                             |                         | 11900 |   429K|   678   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                              |                         | 11900 |   429K|   676   (1)| 00:00:01 |       |       |
|   3 |    INLIST ITERATOR                       |                         |       |       |            |          |       |       |
|*  4 |     INDEX RANGE SCAN                     | AR_ROLE_ACL_I           | 16724 |   130K|    31   (0)| 00:00:01 |       |       |
|   5 |    NESTED LOOPS                          |                         |  9589 |   271K|   643   (1)| 00:00:01 |       |       |
|   6 |     MAT_VIEW ACCESS BY GLOBAL INDEX ROWID| MV_CASELOAD_PRIV        |   279 |  3348 |    83   (0)| 00:00:01 | ROWID | ROWID |
|*  7 |      INDEX RANGE SCAN                    | MV_CASE_PRIV_PGM_I      |   279 |       |     3   (0)| 00:00:01 |       |       |
|*  8 |     INDEX RANGE SCAN                     | CA_CASELOAD_LOGIN_ACL_I |    34 |   578 |     2   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CA"."ACL_ID"="R"."ACL_ID")
   4 - access("R"."ROLE"=21 OR "R"."ROLE"=22 OR "R"."ROLE"=112)
   7 - access("CP"."PGM_ID"=TO_NUMBER(:PGM))
   8 - access("CP"."CASELOAD_ID"="CA"."CASELOAD_ID")
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         97  consistent gets


Replaced 'IN clause' with a Global Temporary Table for generating fixed sort of plan, which 
backfired, caused severe performance problem for the unexpected terrible plan.

SQL> SELECT DISTINCT login_id
FROM client_access_role car
WHERE car.pgm_id = :pgm
AND car.role IN (SELECT value FROM gtt_in_list_2);


Elapsed: 00:00:18.98
Execution Plan
----------------------------------------------------------
Plan hash value: 1525097291
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |     1 |    50 |    34   (3)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                    |                 |     1 |    50 |    34   (3)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                  |                 |     1 |    50 |    33   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                 |                 |     1 |    38 |    32   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                |                 |     1 |    21 |     1   (0)| 00:00:01 |       |       |
|   5 |      INDEX FULL SCAN            | GTT2_VAL_I      |     1 |    13 |     0   (0)| 00:00:01 |       |       |
|   6 |      TABLE ACCESS BY INDEX ROWID| ACL_ROLE        |     1 |     8 |     1   (0)| 00:00:01 |       |       |
|*  7 |       INDEX RANGE SCAN          | AR_ROLE         |  3787 |       |     1   (0)| 00:00:01 |       |       |
|   8 |     TABLE ACCESS BY INDEX ROWID | CASELOAD_ACCESS |   137 |  2329 |    31   (0)| 00:00:01 |       |       |
|*  9 |      INDEX RANGE SCAN           | CASE_ACC_ACL    |   137 |       |     2   (0)| 00:00:01 |       |       |
|  10 |    PARTITION HASH ITERATOR      |                 |     1 |    12 |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 11 |     INDEX RANGE SCAN            | MV_PGM_CASE_I   |     1 |    12 |     1   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("R"."ROLE"="VALUE")
   9 - access("CA"."ACL_ID"="R"."ACL_ID")
  11 - access("CP"."PGM_ID"=TO_NUMBER(:PGM) AND "CP"."CASELOAD_ID"="CA"."CASELOAD_ID")
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
    2817221  consistent gets


The tuned SQL by 'ORDERED' hint to guide the optimizer doing correct join order as we know best.

SQL> SELECT /*+ ORDERED */ DISTINCT login_id
FROM client_access_role c
JOIN gtt_in_list_2 x ON (c.role = x.value)
WHERE c.pgm_id = :pgm;



Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 3611759149
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                         |     1 |    50 |   771   (7)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                              |                         |     1 |    50 |   771   (7)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                               |                         |     1 |    50 |   770   (7)| 00:00:01 |       |       |
|   3 |    INDEX FULL SCAN                        | GTT2_VAL_I              |     1 |    13 |     0   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                              |                         |   311K|    10M|   762   (6)| 00:00:01 |       |       |
|   5 |     NESTED LOOPS                          |                         |  9589 |   271K|   643   (1)| 00:00:01 |       |       |
|   6 |      MAT_VIEW ACCESS BY GLOBAL INDEX ROWID| MV_CASELOAD_PRIV        |   279 |  3348 |    83   (0)| 00:00:01 | ROWID | ROWID |
|*  7 |       INDEX RANGE SCAN                    | MV_CASE_PRIV_PGM_I      |   279 |       |     3   (0)| 00:00:01 |       |       |
|*  8 |      INDEX RANGE SCAN                     | CA_CASELOAD_LOGIN_ACL_I |    34 |   578 |     2   (0)| 00:00:01 |       |       |
|   9 |     TABLE ACCESS FULL                     | ACL_ROLE                |   571K|  4467K|   105  (23)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."ROLE"="X"."VALUE")
   4 - access("CA"."ACL_ID"="R"."ACL_ID")
   7 - access("CP"."PGM_ID"=TO_NUMBER(:PGM))
   8 - access("CP"."CASELOAD_ID"="CA"."CASELOAD_ID")
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        705  consistent gets






And we are back with the super performance!
***