It just occurred to me that I haven’t blogged about SQLDeveloper yet, something I really need to change ;) This post covers SQLDeveloper 19.4, and although I don’t know exactly when the feature you are reading about was added I doubt it was in the latest release.
A little bit of background first
Sometimes the wait interface isn’t enough, so you need to dig a little deeper into the performance issue. In my opinion there are two layers inside Oracle when it comes to performance troubleshooting:
- The Wait Interface provides valuable insights about session activity, especially when off-CPU and waiting
- Session counters provide insights into ongoing operations at a lower level, and finer granularity
Tanel Poder’s post I linked to (yes, it’s from 2010, time flies!) provides an excellent introduction to the topic. His (session) snapper.sql script is one of my favourite and most heavily-used tools, especially when investigating an ongoing issue. Have a look at the Snapper landing page to get some more details and ideas about its many capabilities, it can do a LOT.
Adrian Billington’s mystats.sql tracks sessions counters as well, but it takes a different approach. The mystats.sql script works particularly well when you can sandwich a piece of (PL/)SQL between calls to mystats start and mystasts stop. I have been using it extensively for research and even managed to sneak a little improvement in ;)
Here is an example of mystat use. It captures session counters before and after the execution of a SQL statement and prints the delta. Have a look at the GitHub repository for more details about this very useful utility as I’m not going into the details…
SQL> @mystats start s=s
PL/SQL procedure successfully completed.
SELECT /*+ noparallel gather_plan_statistics */
2 oi.order_id,
SUM(oi.unit_price * oi.quantity)
4 FROM
5 soe.orders o,
6 soe.order_items oi
7 WHERE
8 o.order_id = oi.order_id
9 AND o.delivery_type = 'Express'
10 GROUP BY
11 oi.order_id
12 HAVING
SUM(oi.unit_price * oi.quantity) > 45000
14 ORDER BY
15 2 DESC;
ORDER_ID SUM(OI.UNIT_PRICE*OI.QUANTITY)
---------- ------------------------------
1449606 49324
577982 48606
468019 47970
1977685 46786
1891120 45347
2310221 45209
6 rows selected.
SQL> @mystats stop t=1
==========================================================================================
MyStats report : 26-MAY-2020 18:44:51
==========================================================================================
------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------
Type Statistic Name Value
------ ---------------------------------------------------------------- ----------------
TIMER snapshot interval (seconds) 14.76
TIMER CPU time used (seconds) 2.38
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------
Type Statistic Name Value
------ ---------------------------------------------------------------- ----------------
STAT CPU used by this session 239
STAT CPU used when call started 238
STAT CR blocks created 13
STAT DB time 692
STAT Effective IO time 4,415,766
STAT Number of read IOs issued 1,336
STAT Requests to/from client 20
STAT SQL*Net roundtrips to/from client 20
STAT application wait time 9
STAT buffer is not pinned count 7
STAT bytes received via SQL*Net from client 20,586
STAT bytes sent via SQL*Net to client 10,594
STAT calls to get snapshot scn: kcmgss 791
STAT calls to kcmgcs 47
STAT cell physical IO interconnect bytes 1,370,603,520
STAT consistent changes 944
STAT consistent gets 278,437
STAT consistent gets direct 167,309
STAT consistent gets examination 251
STAT consistent gets examination (fastpath) 251
STAT consistent gets from cache 111,128
STAT consistent gets pin 110,877
STAT consistent gets pin (fastpath) 110,876
STAT data blocks consistent reads - undo records applied 247
STAT db block changes 838
STAT db block gets 2,415
STAT db block gets from cache 2,415
STAT db block gets from cache (fastpath) 1,970
STAT dirty buffers inspected 33
STAT enqueue conversions 4
STAT enqueue releases 8
STAT enqueue requests 8
STAT enqueue waits 1
STAT execute count 17
STAT execute count 17
STAT file io wait time 10,452,219
STAT free buffer inspected 105
STAT free buffer requested 69
STAT heap block compress 2
STAT hot buffers moved to head of LRU 193
STAT index fetch by key 2
STAT lob writes 391
STAT lob writes unaligned 391
STAT logical read bytes from cache 930,144,256
STAT no work - consistent read gets 277,738
STAT non-idle wait count 1,153
STAT non-idle wait time 452
STAT opened cursors cumulative 21
STAT parse count (hard) 1
STAT parse count (total) 17
STAT physical read IO requests 1,337
STAT physical read bytes 1,370,603,520
STAT physical read total IO requests 1,337
STAT physical read total bytes 1,370,603,520
STAT physical read total multi block requests 1,336
STAT physical reads 167,310
STAT physical reads cache 1
STAT physical reads direct 167,309
STAT process last non-idle time 15
STAT recursive calls 2,022
STAT recursive cpu usage 3
STAT rollbacks only - consistent read gets 26
STAT rows fetched via callback 1
STAT session cursor cache count 7
STAT session cursor cache hits 10
STAT session logical reads 280,852
STAT session pga memory 2,276,704
STAT session pga memory max 65,518,944
STAT session uga memory 261,952
STAT session uga memory max 63,036,496
STAT sorts (memory) 1
STAT sorts (rows) 6
STAT table fetch by rowid 1
STAT table scan blocks gotten 277,764
STAT table scan disk non-IMC rows gotten 31,341,195
STAT table scan rows gotten 31,341,195
STAT table scans (direct read) 1
STAT table scans (long tables) 2
STAT temp space allocated (bytes) 1,048,576
STAT user I/O wait time 442
STAT user calls 28
STAT workarea executions - optimal 6
STAT workarea memory allocated 12
------------------------------------------------------------------------------------------
3. Options Used
------------------------------------------------------------------------------------------
- Statistics types : s=s
- Reporting filter : t=1
------------------------------------------------------------------------------------------
4. About
------------------------------------------------------------------------------------------
- MyStats v3.0 by Adrian Billington (http://www.oracle-developer.net)
- Original version based on the SNAP_MY_STATS utility by Jonathan Lewis
==========================================================================================
End of report
==========================================================================================
PL/SQL procedure successfully completed.
I have modified mystats slightly so as to sort output by statistic name, rather than the change in value. I find it easier to read that way, but your mileage may vary.
Each time I look at performance instrumentation in the Oracle database I am pleasantly surprised about the level of detail available. I haven’t blogged about it lately, but going back a bit to 12.2 shows the progress made. And Oracle didn’t stop there, either. Using mystats’s output I can derive all sorts of interesting facts, none of which are of importance for this post as I’m purely interesting in reporting the change in session stats ;) When researching how Oracle works, this is quite a wealth of information, and you can find further examples on this blog.
SQLDeveloper can do this, too
If you are using SQLDeveloper and don’t want/need to use the command line, you can get something quite similar as well. Just enter your query into the text window, then hit F6 for “Autotrace”. In the lower part of your Autotrace results, you can see the change in session counters. If not, you may have to swipe the statistics pane into view using the mouse. Figure 1 shows an example:
Summary
Session counters are super useful for understanding what Oracle does at a more granular level than the wait interface. For the most part, using the wait interface is enough, yet there is the occasional case where more details are needed. I often consider changes in session statistics when researching a particular problem, and thankfully I don’t have to come up with ways to do so as the community has already lent a helping hand!
Martins Blog 