You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it.
What it does:
- Gather all statistics, of one or all sessions, and put them in an array.
- Sleep the desired amount of seconds
- Gather all statistics again anput them in another array
- Create one big array of the data
- Sort the data
- Display the data
First show some output.
First example I would like to see the change of statistics of session 460 (sid) over 1 minute (60 seconds):
(2021-01-18 23:03:26) SYS@CDB01_BLOEMKOOL > @stats 460 60 460 non-idle wait count User 555 564 9 460 non-idle wait time User 703 744 41 460 scheduler wait time User 592 634 42 460 session logical reads User 6422 6568 146 460 consistent gets Cache 6419 6565 146 460 consistent gets from cache Cache 4620 4766 146 460 consistent gets pin Cache 3817 3963 146 460 consistent gets pin (fastpath) Cache 3697 3843 146 460 no work - consistent read gets Debug 5464 5610 146 460 table scan blocks gotten SQL 4366 4512 146 460 buffer is pinned count Cache SQL 167753 192019 24266 460 table scan rows gotten SQL 560149 584684 24535 460 table scan disk non-IMC rows gotten SQL 560149 584684 24535 460 logical read bytes from cache Cache 37871616 39067648 1196032 (2021-01-18 23:04:40) SYS@CDB01_BLOEMKOOL >
A second example: Show the change of statistics of all sessions over a period of 1 second:
(2021-01-18 23:11:11) SYS@CDB01_BLOEMKOOL > @stats % 1 49 opened cursors cumulative User 1001 1002 1 403 recursive calls User 271229 271230 1 49 non-idle wait time User 118 119 1 396 background timeouts Debug 268428 268429 1 399 background timeouts Debug 788510 788511 1 403 background timeouts Debug 268385 268386 1 409 background timeouts Debug 268505 268506 1 410 background timeouts Debug 268290 268291 1 412 background timeouts Debug 268428 268429 1 419 background timeouts Debug 268428 268429 1 428 background timeouts Debug 268415 268416 1 429 background timeouts Debug 268412 268413 1 410 enqueue conversions Enqueue 273803 273804 1 49 calls to get snapshot scn: kcmgss RAC 520 521 1 ... ... ... 409 physical write total bytes Cache 9487723520 9487726592 3072 409 cell physical IO interconnect bytes SQL 9607496704 9607499776 3072 409 redo write finish time Debug 4294994669 4294998879 4210 409 redo write time (usec) Debug 4296653531 4296657744 4213 409 redo write total time Debug 4305361173 4305365398 4225 410 physical write total bytes Cache 13430349824 13430398976 49152 407 session pga memory User 4778872 4844408 65536 410 physical read total bytes Cache 25358295040 25358360576 65536 410 cell physical IO interconnect bytes SQL 38788644864 38788759552 114688 49 session uga memory User 2810200 2991720 181520 49 session uga memory max User 2875688 13222264 10346576 49 session pga memory User 5006336 50291712 45285376 49 session pga memory max User 7103488 60580864 53477376 (2021-01-18 23:18:43) SYS@CDB01_BLOEMKOOL >
Actually the usage is pretty straight forward:
The first argument is the session to want to see the statistics off. Or a % for all sessions. The second argument is the number of seconds of the interval.
You can find the script here.
Take some care with the usage of this script. In my RDBMS 19 there are 2036 named statistics. If you use % for the sid selection, and you have a lot of sessions, the script will get very busy.
(2021/01/18 23:55:11) SYS@CDB01_BLOEMKOOL > select count(*) from v$statname;
COUNT(*)
----------
2036
(2021/01/18 23:55:24) SYS@CDB01_BLOEMKOOL >