The Oracle Session Snapper v3 has some major improvements compared to old versions (v2 and v1). In addition to taking snapshots and reporting deltas of various V$ and X$ performance counters, it also samples session activity details from V$SESSION. This is pretty much like what Oracle's Active Session History does (ASH is essentially just a history of V$SESSION samples with few additional tricks). However, using ASH requires you to have additional Diagnostics Pack licenses, while querying V$SESSION (which is how Snapper works), doesn't. Starting from version 3.52, Snapper also supports the ASH-style sampling on Oracle 9.2 (in previous versions it required Oracle 10g+). For example, we can't be fully sure that this session really was 100% doing the operation mentioned above, perhaps it was only busy running that SQLID and waiting for that event only 99% of its response time and it did something else for 1% of time.
![]()
Project samples free download. Programming Without Coding Technology PWCT is not a Wizard for creating your application in 1 2 3 steps. PWCT is a general-purpose visual. The update to Snapper 3 is free for all registered Snapper 2 users. Simply download Snapper 3 and drag the app to your applications folder (make sure the old.
Snapper could just have missed this other 1% due sampling as this 'other' thing happened so fast between 2 samples, so it wasn't captured. However when something happens infrequently and very fast so that it isn't even recorded in one sample of many taken, then it can't be too significant!
If something takes 50% of the response time, then there will sure be some samples (roughly half of total taken) showing that 'something' happening. The footer of snapper report output shows when the snapshot period ended, over how many seconds the sampling was done and how many V$SESSION samples were done during that period.
Above we see that Snapper took 42 ASH samples during the 5 seconds (over 8 samples per second). Snapper is written the way that it samples very fast when the snapshot period is short (up to 10 seconds) but lowers the sampling frequency when the snapshot period is longer, to reduce measurement overhead. Over long periods the sampling frequency will be 1Hz, one sampling per second just like ASH. Continuing the above example, we have identified that the session 144 was waiting for an enqueue lock all of its time. We probably want to know now who's blocking us and what kind of resource we were trying to lock.
In case of enqueue lock waits, the wait event additional detail columns (Parameter2,3) will give us additional info about the object/resource we were trying to lock (look into V$LOCK type for their meanings). So we want to sample the P2,P3 columns from V$SESSION to get more detailed info about this wait. Also, in case of enqueue waits we can sample V$SESSION.
BLOCKINGSESSION column (available from 10g) to see which session was blocking us. There's one more interesting and important thing to see - the sum of Active% samples is 54% only! This means that this session was ative only (roughly) 54% during the sampling time!
Therefore the rest of time it was idle, waiting for next request to come in from the application, over the network. In cases where you see the database session being active only a small minority of time, then it doesn't make sense to tune anything in the database - you'll need to see why isn't the application sending new requests to the database fast enough. User think time.
User went to drink coffee or their PC is so slow that they can't do much with their application:-). Application think time. This often happens when application servers are overloaded or have some bad code in them. The application is so busy or stuck so that it isn't able to send the requests to database and process resulting data too fast. Lastly, network latency and throughput.
![]()
I deliberately left this as last, as it makes sense to see what the (higher levels) users and applications are actually doing, instead of immediately assuming that the problem must be in network. Sometimes just one TOP report as seen above may not be enough. You might want to look into the session / instance activity data from multiple different angles (group the ASH samples by different fields). That's why I have added ash1, ash2 and ash3 parameters to Snapper syntax, you can show up to 4 ASH TOP activity breakdowns in one Snapper run. For example, let say I want to break the session activity down by also PLSQL package's objectid and procedure ID in it, to see whether there's some PL/SQL package causing most of the work (these columns are available from 10.2.0.3 in V$SESSION).
All the stuff above is available from Snapper v3. However it's only a part of Snapper's full capabilities.
Sometimes knowing the TOP SQLID and wait event is not enough. Think about cases where a session is 100% on CPU and doesn't wait for anything and there's nothing obviously wrong with the SQL execution plan. This is when you want to know what exactly is the session doing. This is where the Oracle's dynamic performance counters come into play - I'm talking mainly about V$SESSTAT statistics: Snapper V$ Performance Counter / Statistics modeSnapper has had the Statistics mode since version 1. That's the reason why I initially wrote Snapper, to help me with performance troubleshooting, especially in cases where the conventional tools like SQL trace or instance-level performance tools like AWR/Statspack didn't show anything useful. Starting from v3, Snapper does not automatically show you the V$SESSTAT stats, as Snapper is meant to be used as an instance-wide troubleshooting tool now as well (and querying V$SESSTAT for all sessions may be expensive when you have thousands of sessions in your instance). Another thing to note is that even though the ASH section below says there was CPU activity worth of 105% of a single session's response time (which means that there must have been more than one session using that CPU), the time model stats above (stats with type=TIME) don't report significant DB CPU usage at all.
The problem here is measurement granularity. Time model stats are updated in the V$ views only in the end of the database call, but if the database call is a long-running one (like in my test case) then the stats in V$SESSTIMEMODEL are updated roughly every 5 seconds by default. Thanks to my short Snapper run time (5 seconds) the script apparently finished before the V$SESSTIMEMODEL in-memory array update took place by that session with the long running database call. If you run snapper with longer sampling time, like 60 second, you should see all the time model stats in place.
![]()
Snapper - An utility for making screen shots easier SnapperSnapper makes it really easy to make screenshots.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |