Month: February 2015

Using Runtime Statistics

How would you optimize the statement that produced the following runtime statistics? You can do anything to make it faster without changeing the statement. In particular you can change or add an index.

 

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:15.42 |    3540 |      3262 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:15.42 |    3540 |      3262 |
|*  2 |   TABLE ACCESS BY INDEX ROWID|   T1 |      1 |  52695 |     10 |00:00:15.42 |    3540 |      3262 |
|*  3 |    INDEX RANGE SCAN          |   I1 |      1 |     17M|   4577 |00:00:00.16 |      11 |         9 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))
   3 - access("T1_ID"=:B2 AND "T2_ID"=:B4 AND "T3_ID"=:B1)

Solution: Mystery of the lost session

As speculated in Twitter the command was connect and the activity was logon. After some research the root cause could be found : The report server attempted logon using invalid credentials.

After we had corrected the credentials, the row locks disappeared.

The first good indication of the solution, I got the blog of Dominic Brooks: Failed Logins can cause row cache lock on dc_users. Martin Preiss, too, had found this website when searching for the solution. Stefan Koehler has documented a very similar case in great detail: Wait event “library cache lock” by (security) design and exploring system state dumps (oradebug). Franck Pachot mentioned on Twitter that the “connection management call elapsed time” event in AWR should have been a hint, had I only noticed… Of course, Martin Berger was as always on the right track.