I am being tasked to explain why a feature of an in-house web app takes 10-30 seconds in our production environment (version 10.2.0.5). With the help of the user experiencing the slowness (slowness experienced on his side, in the web app), I manage to determine the SQL_ID of the feature in question: 2xpjrny0dwsyh.
I then query V$ACTIVE_SESSION_HISTORY to trace what the user session has been up to during the 20 second lapse of the issue (between 16H43:15 and 16H43:35). My only criteria are SID=9999 and sample_time BETWEEN 16H42 and 16H44. Here is the output ordered by SAMPLE_TIME:
TIME SQL_ID command EVENT WAIT_TIME
08 juin 2015 16:42:58 f20wj97jbn67h SELECT 2
08 juin 2015 16:43:11 9pjvj5vnh4tnq SELECT 820
08 juin 2015 16:43:32 2xpjrny0dwsyh SELECT 58
08 juin 2015 16:43:38 06f3pchfk9f6h SELECT 61
08 juin 2015 16:43:42 798mrd4hy8yu7 SELECT 9
08 juin 2015 16:43:46 28b1p89gmcnf5 SELECT 63
08 juin 2015 16:43:49 28b1p89gmcnf5 SELECT 10
I've got several questions about that output:
- V$ACTIVE_SESSION_HISTORY samples active sessions every second; so am I to understand that my 9999 session was active 3 seconds only (at 43:32, 43:38, 43:42)?
- The "EVENT" column has no data (came back with null): does that mean that my 9999 session experienced no waits whatsover in that period?
- my 2xpjrny0dwsyh SELECT appears once only: am I to understand it lasted <= 1 second?
- what really happened between 43:32 and 43:38 for example? Was my session inactive?