2023-04-11

23c free - new statistics

 Oracle is counting hundreds of events - both on system and session level - they are called statistics. They can provide significant information about the pasts activities - especially when deltas are calculated for a period of interest. 
In 23c free, 362 new statistics appeared (om comparison to 21.9):

STATISTIC# NAME CLASS CLASS_NAME
20 RM usage by this session 1 User
21 RM adjustments 1 User
74 spare statistic 1 128 Debug
75 spare statistic 2 128 Debug
205 control file read req (single-block) 4 Enqueue
206 control file read req (multi-block) 4 Enqueue
207 control file read req (single-block) RDMA optimized 4 Enqueue
208 control file read req (multi-block) RDMA optimized 4 Enqueue
914 index cmph, decomp full buffers 128 Debug
915 index cmph, decomp full buffers failures 128 Debug
916 index cmph sc, decomp buffers rows avail 128 Debug
917 index cmph sc, decomp buffers rows used 128 Debug
918 index cmph sc, decomp buffers released and found valid 128 Debug
1252 bytes sent via network packages 1 User
1253 bytes received via network packages 1 User
1254 bytes sent via UTL_HTTP 1 User
1255 bytes received via UTL_HTTP 1 User
1256 bytes sent via UTL_TCP 1 User
1257 bytes received via UTL_TCP 1 User
1258 bytes sent via UTL_SMTP 1 User
1259 bytes received via UTL_SMTP 1 User
1549 MLE JIT compilation success count 1 User
1550 MLE JIT compilation error count 1 User
1551 MLE JIT compilation duration max 1 User
1552 MLE JIT compilation duration cumulative 1 User
1553 MLE incremental GC count 1 User
1554 MLE incremental GC accumulated time 1 User
1555 MLE full GC count 1 User
1556 MLE full GC accumulated time 1 User
1598 cell xrmem log writes 258 Redo Instance level
1603 distributed operation count 384 Debug Instance level
1604 distributed operation active count 384 Debug Instance level
1605 distributed operation paused count 384 Debug Instance level
1606 distributed operation no-op count 384 Debug Instance level
1607 distributed operation waiting to archive count 384 Debug Instance level
1608 distributed operation dist_op count 384 Debug Instance level
1609 distributed operation count began in last second 384 Debug Instance level
1610 distributed operation count per second over last minute 384 Debug Instance level
1611 distributed operation count archives in last second 384 Debug Instance level
1612 distributed operation count archives per second over last minut 384 Debug Instance level
1613 distributed operation pool bucket grow error 384 Debug Instance level
1614 distributed operation pool buckets in use 384 Debug Instance level
1615 distributed operation pool bucket buffers in use 384 Debug Instance level
1616 distributed operation pool buckets available 384 Debug Instance level
1617 control file read block (multi-block) 256 Instance level
1618 control file read req (single-block) no enqueue 256 Instance level
1619 control file read req (multi-block) no enqueue 256 Instance level
1620 control file read block (multi-block) no enqueue 256 Instance level
1621 control file read req with MGA buffer 256 Instance level
1622 control file buffers scanned looking for a victim 256 Instance level
1623 control file warm buffers from cold region 256 Instance level
1624 control file exclusive latch success 256 Instance level
1625 control file exclusive latch failure 256 Instance level
1626 control file invalid cached buffers 256 Instance level
1627 control file blocks written 256 Instance level
1628 control file cache search for read hit 256 Instance level
1629 control file cache search for read miss 256 Instance level
1694 TrueCache: block requests to preferred primary 264 Cache Instance level
1695 TrueCache: block requests to primary 264 Cache Instance level
1696 rt prf local ORL write bytes 256 Instance level
1697 rt prf local elapsed time 256 Instance level
1725 rt wrn sequence skipped 256 Instance level
1726 pdc waits for new incarnation 258 Redo Instance level
1727 pdc slb flushes 258 Redo Instance level
1728 pdc slb flushes avoided 258 Redo Instance level
1729 pdc slb flushes partial 258 Redo Instance level
1730 pdc slb flushes (imu downgrade) 258 Redo Instance level
1731 pdc slb flushes (txn dependency) 258 Redo Instance level
1732 pdc slb flushes (get/steal) 258 Redo Instance level
1733 pdc slb flushes (no ri slots) 258 Redo Instance level
1734 pdc redo size 258 Redo Instance level
1735 redo entries (pdc) 258 Redo Instance level
1736 pdc commits (imu) 258 Redo Instance level
1737 pdc commits (non imu) 258 Redo Instance level
1738 pdc dependencies 258 Redo Instance level
1739 pdc dependencies (orl) 258 Redo Instance level
1740 pdc dependencies nlc 258 Redo Instance level
1741 pdc dependencies nlc (orl) 258 Redo Instance level
1742 pdc dependencies xfer (imu downgrade) 258 Redo Instance level
1743 pdc scn waits 258 Redo Instance level
1744 pdc scn waits (clearing orl session) 258 Redo Instance level
1745 pdc switches to orl (no free slb) 258 Redo Instance level
1746 pdc switches to orl (no ri slots) 258 Redo Instance level
1747 pdc switches to orl (background) 258 Redo Instance level
1748 pdc switches to orl (no ptn chunks) 258 Redo Instance level
1749 pdc switches to orl (rdma fail) 258 Redo Instance level
1750 pdc switches to orl (own thread dep) 258 Redo Instance level
1751 pdc switches to orl (slb full) 258 Redo Instance level
1752 pdc switches to orl (txn time limit) 258 Redo Instance level
1753 pdc switches to orl (pdc ratio exceeded) 258 Redo Instance level
1754 pdc switches to orl (open failure) 258 Redo Instance level
1755 pdc switches to orl (incarnation chg) 258 Redo Instance level
1756 pdc switches to orl (non-pdc session) 258 Redo Instance level
1757 redo size (parallel LGWR) 258 Redo Instance level
1758 redo size sample count (parallel LGWR) 258 Redo Instance level
1759 redo size (serial LGWR) 258 Redo Instance level
1760 redo size sample count (serial LGWR) 258 Redo Instance level
1761 redo size (test serial LGWR) 258 Redo Instance level
1762 redo size sample count (test serial LGWR) 258 Redo Instance level
1763 redo writes adaptive switch 258 Redo Instance level
1764 redo write test single time (usec) 258 Redo Instance level
1765 redo write test single count 258 Redo Instance level
1774 gc zcopy slow send 296 Cache Real Application Clusters Instance level
1801 pdc boc composite bcasts 258 Redo Instance level
1802 pdc boc composite bcast time (usecs) 258 Redo Instance level
1803 pdc boc composite bcasts avoided 258 Redo Instance level
1804 pdc boc instance successful bcasts 258 Redo Instance level
1805 pdc boc instance successful bcast time (usecs) 258 Redo Instance level
1806 pdc boc instance retried bcasts 258 Redo Instance level
1807 pdc boc instance failed connections 258 Redo Instance level
1808 pdc boc instance failed connection time (usecs) 258 Redo Instance level
1809 pdc boc instance failed bcasts 258 Redo Instance level
1810 pdc boc instance failed bcast time (usecs) 258 Redo Instance level
1811 pdc boc instance aborted bcasts 258 Redo Instance level
1812 pdc boc instance bcasts avoided 258 Redo Instance level
1813 pdc boc instance bcasts avoided thru lgwr 258 Redo Instance level
1814 pdc boc instance bcasts wasted 258 Redo Instance level
1815 pdc boc instance down 258 Redo Instance level
1816 pdc boc initializations 258 Redo Instance level
1817 pdc boc initialization time (usecs) 258 Redo Instance level
1818 pdc boc lgwr bcasts avoided 258 Redo Instance level
1819 pdc boc rcvd scn scans 258 Redo Instance level
1820 True Cache: message roundtrip time data send 256 Instance level
1821 True Cache: message roundtrip time request send 256 Instance level
1822 True Cache: message count data send 256 Instance level
1823 True Cache: message count request send 256 Instance level
1824 txns rollback txn_auto_rollback_high_priority_wait_target 384 Debug Instance level
1825 txns rollback txn_auto_rollback_medium_priority_wait_target 384 Debug Instance level
1826 txns track mode txn_auto_rollback_high_priority_wait_target 384 Debug Instance level
1827 txns track mode txn_auto_rollback_medium_priority_wait_target 384 Debug Instance level
1845 Heatmap SegLevel - Fast Full Index Scan 256 Instance level
1846 Heatmap SegLevel - Range Index Scan 256 Instance level
1921 IM private journal HT granule count 384 Debug Instance level
1922 IM private journal HT bucket count 384 Debug Instance level
1951 Native Transaction Guard inconclusive outcome count 256 Instance level
1952 Native Transaction Guard force_outcome count 256 Instance level
1953 Transaction Guard force_outcome count 256 Instance level
1954 Native Transaction Guard usage count 256 Instance level
1955 Native Transaction Guard Transaction Table rollback count 256 Instance level
1956 transaction recovery quarantines generated 256 Instance level
1957 transaction recovery quarantines escalated 256 Instance level
1958 quarantined transactions skipped 256 Instance level
1959 IM repopulate IMEI accumulated time (ms) 384 Debug Instance level
1960 IM repopulate reverse IMEI accumulated time (ms) 384 Debug Instance level
1961 IM graph change queue scheduling accumulated time (ms) 384 Debug Instance level
1962 IM graph change queue drain accumulated time (ms) 384 Debug Instance level
1963 IM graph change queue background drain accumulated time (ms) 384 Debug Instance level
1964 IM graph change queue fetch accumulated time (ms) 384 Debug Instance level
1965 IM graph change queue background fetch accumulated time (ms) 384 Debug Instance level
1966 IM graph transaction consistent CSR built accumulated time (ms) 384 Debug Instance level
1967 IM repopulate IMEIs 384 Debug Instance level
1968 IM repopulate reverse IMEIs 384 Debug Instance level
1969 IM graph change queue drain tasks scheduled 384 Debug Instance level
1970 IM graph change queue drained records 384 Debug Instance level
1971 IM graph change queue invalid records 384 Debug Instance level
1972 IM graph change queue background drained 384 Debug Instance level
1973 IM graph change queue drained downgraded records 384 Debug Instance level
1974 IM graph transaction consistent CSR built 384 Debug Instance level
1975 IM graph transaction consistent CSR drainless built 384 Debug Instance level
1976 IM graph change queue insert only object records 384 Debug Instance level
1977 IM graph change queue delete only object records 384 Debug Instance level
1978 IM graph change queue object records 384 Debug Instance level
1979 IMEI log records 384 Debug Instance level
1980 IMEI log vertex inserts 384 Debug Instance level
1981 IMEI log vertex deletes 384 Debug Instance level
1982 IMEI log edge inserts 384 Debug Instance level
1983 IMEI log edge deletes 384 Debug Instance level
1984 IMEI log record bytes allocated 384 Debug Instance level
1985 Temp Space Prewarm Request 384 Debug Instance level
1986 Temp Space Prewarm Extents 384 Debug Instance level
1987 Temp Space Rebalance Request 384 Debug Instance level
1988 Temp Space Shrink Extent Release Request 384 Debug Instance level
1989 Temp Space Rebalance Receive 384 Debug Instance level
1990 Temp Space Shrink Extent Release Receive 384 Debug Instance level
1991 Temp Space Dealloc Extents 384 Debug Instance level
1992 securefile alloc all sources 384 Debug Instance level
1993 securefile alloc curr descn 384 Debug Instance level
1994 Sub-segment created 384 Debug Instance level
1995 Sub-segment dropped 384 Debug Instance level
1996 Sub-segment state quiesce 384 Debug Instance level
1997 Sub-segment state interim 384 Debug Instance level
1998 Sub-segment state done 384 Debug Instance level
1999 kts spare statistic 1 384 Debug Instance level
2000 kts spare statistic 2 384 Debug Instance level
2001 kts spare statistic 3 384 Debug Instance level
2002 kts spare statistic 4 384 Debug Instance level
2023 Temp File Extend Request 384 Debug Instance level
2024 Temp File Reuse Any Instance 384 Debug Instance level
2025 Temp File Reuse Dead Instance 384 Debug Instance level
2026 Temp File Reuse Get Instance Lock 384 Debug Instance level
2027 Temp File Bitmap Reuse Execution 384 Debug Instance level
2028 Temp File Bitmap Reuse Extents 384 Debug Instance level
2029 Temp File Bitmap Pre-reuse Execution 384 Debug Instance level
2030 Temp File Bitmap Pre-reuse Extents 384 Debug Instance level
2032 IM area shrink 384 Debug Instance level
2033 IM area RO shrink granules 384 Debug Instance level
2034 IM area RW shrink granules 384 Debug Instance level
2035 IM area shrink RO granules swap 384 Debug Instance level
2036 IM area shrink RO granules failed 384 Debug Instance level
2037 IM area shrink RO granules replaced 384 Debug Instance level
2038 IM area shrink garbage collect waits 384 Debug Instance level
2039 IM area shrink garbage collect timeout 384 Debug Instance level
2040 IM area shrink fails 384 Debug Instance level
2041 IM area shrink fail insufficent granules 384 Debug Instance level
2042 IM area grow fails 384 Debug Instance level
2043 IM area concurrent resize fails 384 Debug Instance level
2044 IM area grow 384 Debug Instance level
2045 IM area grow granules 384 Debug Instance level
2046 IM area RO grow granules 384 Debug Instance level
2047 IM space metadata alloc fail 384 Debug Instance level
2048 IM raclocks alloc fail 384 Debug Instance level
2049 IM raclocks alloc bytes 384 Debug Instance level
2050 IM raclocks retry alloc shared pool 384 Debug Instance level
2051 Auto SF SHK total number of tasks 256 Instance level
2052 Auto SF SHK rejected due to activity 256 Instance level
2053 Auto SF SHK rejected due to past shrink 256 Instance level
2054 Auto SF SHK rejected fast SU unavailable 256 Instance level
2055 Auto SF SHK rejected due to disk SU check 256 Instance level
2056 Auto SF SHK rejected due to recycle bin check 256 Instance level
2057 Auto SF SHK rejected trickle (fast SU) 256 Instance level
2058 Auto SF SHK rejected trickle (disk SU) 256 Instance level
2059 Auto SF SHK total analyzed segments 256 Instance level
2060 Auto SF SHK total candidates 256 Instance level
2061 Auto SF SHK successfully shrunk 256 Instance level
2062 Auto SF SHK number shrinks failed 256 Instance level
2063 Auto SF SHK time (disk SU check) 256 Instance level
2064 Auto SF SHK time (shrink task) 256 Instance level
2065 Auto SF SHK time (releasing extents) 256 Instance level
2066 Auto SF SHK tasks failed: rowlock contention 256 Instance level
2067 Auto SF SHK tasks failed: concurrent DDL 256 Instance level
2068 cell IOT leaf blocks filtered 320 SQL Instance level
2069 cell IOT leaf blocks not filtered: nonkey where 320 SQL Instance level
2070 cell IOT rows returned 320 SQL Instance level
2071 cell IOT compressed leaf blocks filtered 320 SQL Instance level
2072 cell index leaf blocks filtered 320 SQL Instance level
2073 cell index compressed leaf blocks filtered 320 SQL Instance level
2074 cell index rows returned 320 SQL Instance level
2076 SV Ordered Sequence X gets 384 Debug Instance level
2077 SV Ordered Sequence S gets 384 Debug Instance level
2078 index coal, active block 384 Debug Instance level
2079 index coal, busy block 384 Debug Instance level
2080 index coal, examined blocks 384 Debug Instance level
2081 index coal, empty blocks removed 384 Debug Instance level
2082 index coal, redo limit hit 384 Debug Instance level
2083 index cmph, recompression failed 384 Debug Instance level
2084 index coal, skip blocks 384 Debug Instance level
2085 index coal, rows do not fit 384 Debug Instance level
2086 KDBL partition spilling starts 384 Debug Instance level
2087 cell IOT scans 320 SQL Instance level
2091 securefile multi-wgc use count 384 Debug Instance level
2093 cellmemory IM scan column CUs format dict 384 Debug Instance level
2094 cellmemory IM scan column CUs format rle dict 384 Debug Instance level
2095 cellmemory IM scan column CUs format non-dict 384 Debug Instance level
2096 cellmemory IM scan column CUs format null 384 Debug Instance level
2097 cellmemory IM scan column CUs format constant 384 Debug Instance level
2098 cellmemory IM scan column path value index 384 Debug Instance level
2099 HCC scan cell CUs requeue 384 Debug Instance level
2100 HCC scan cell CUs un-agg fallback 384 Debug Instance level
2101 cellmemory IM hpk bloom filter nrows 384 Debug Instance level
2102 cellmemory IM bloom filter returned nrows 384 Debug Instance level
2103 cellmemory IM bloom filter total calls 384 Debug Instance level
2104 cellmemory IM simd bloom filter calls 384 Debug Instance level
2105 cellmemory HCC simd compare calls 384 Debug Instance level
2106 cellmemory HCC simd decode calls 384 Debug Instance level
2107 cellmemory HCC simd rle burst calls 384 Debug Instance level
2108 cellmemory HCC simd set membership calls 384 Debug Instance level
2109 cellmemory HCC simd xlate filter calls 384 Debug Instance level
2110 cellmemory HCC simd decode unpack calls 384 Debug Instance level
2111 cellmemory HCC simd decode symbol calls 384 Debug Instance level
2112 HCC simd compare calls 384 Debug Instance level
2113 HCC simd decode calls 384 Debug Instance level
2114 HCC simd rle burst calls 384 Debug Instance level
2115 HCC simd set membership calls 384 Debug Instance level
2116 HCC simd bloom filter calls 384 Debug Instance level
2117 HCC hpk bloom filter nrows 384 Debug Instance level
2118 HCC bloom filter skipped calls 384 Debug Instance level
2119 HCC bloom filter skipped nrows 384 Debug Instance level
2120 HCC bloom filter returned nrows 384 Debug Instance level
2121 HCC bloom filter total calls 384 Debug Instance level
2122 HCC simd xlate filter calls 384 Debug Instance level
2123 HCC simd decode unpack calls 384 Debug Instance level
2124 HCC simd decode symbol calls 384 Debug Instance level
2125 HCC simd compare selective calls 384 Debug Instance level
2126 HCC simd decode selective calls 384 Debug Instance level
2127 HCC simd rle burst selective calls 384 Debug Instance level
2128 HCC simd set membership selective calls 384 Debug Instance level
2129 HCC simd bloom filter selective calls 384 Debug Instance level
2130 HCC simd xlate filter selective calls 384 Debug Instance level
2131 HCC simd decode unpack selective calls 384 Debug Instance level
2132 HCC simd decode symbol selective calls 384 Debug Instance level
2133 HCC simd KV add calls 384 Debug Instance level
2134 HCC simd KV add rows 384 Debug Instance level
2135 HCC simd KV probe calls 384 Debug Instance level
2136 HCC simd KV probe keys 384 Debug Instance level
2137 HCC simd KV probe rows 384 Debug Instance level
2138 HCC simd KV probe serial_buckets 384 Debug Instance level
2139 HCC simd KV probe chain_buckets 384 Debug Instance level
2140 HCC simd hash calls 384 Debug Instance level
2141 HCC simd hash rows 384 Debug Instance level
2142 HCC simd compare HW offload calls 384 Debug Instance level
2143 HCC simd decode HW offload calls 384 Debug Instance level
2144 HCC simd rle burst HW offload calls 384 Debug Instance level
2145 HCC simd set membership HW offload calls 384 Debug Instance level
2146 HCC simd decode unpack HW offload calls 384 Debug Instance level
2253 IM populate scan checkpoints 384 Debug Instance level
2254 IM populate scan 384 Debug Instance level
2381 IM hpk bloom filter nrows 384 Debug Instance level
2382 IM bloom filter skipped calls 384 Debug Instance level
2383 IM bloom filter skipped nrows 384 Debug Instance level
2384 IM bloom filter returned nrows 384 Debug Instance level
2385 IM bloom filter total calls 384 Debug Instance level
2486 IM populate max retry fail 384 Debug Instance level
2487 IM populate retries 384 Debug Instance level
2513 memopt w buffer miss nowait 384 Debug Instance level
2514 Count of population tasks submitted for IMEI 384 Debug Instance level
2515 Count of population tasks succeeded for IMEI 384 Debug Instance level
2516 Count of population tasks submitted for on demand IMPG 384 Debug Instance level
2517 Count of population tasks submitted for automatic IMPG 384 Debug Instance level
2518 Count of vertex tables populated 384 Debug Instance level
2519 Count of vertices populated 384 Debug Instance level
2520 Count of edge tables populated 384 Debug Instance level
2521 Count of edges populated 384 Debug Instance level
2522 Auto compression data movement success 384 Debug Instance level
2523 Auto compression data movement failure 384 Debug Instance level
2524 Auto compression data moved 384 Debug Instance level
2525 IM Graph Algo execs started 384 Debug Instance level
2526 Loaded side tables across all IM Graph Algo execs 384 Debug Instance level
2527 Materialized side tables across all IM Graph Algo execs 384 Debug Instance level
2528 Non-IM Graph Algo execs due to no available KSOLT threads 384 Debug Instance level
2529 IM Graph Algo startup time across all execs 384 Debug Instance level
2530 IM Graph Algo prop loading time across all execs (ms) 384 Debug Instance level
2531 IM Graph Algo core execution time across all execs (ms) 384 Debug Instance level
2532 IM Graph Algo prop materialization time across all execs (ms) 384 Debug Instance level
2533 IM Graph Algo cleanup time across all executions (ms) 384 Debug Instance level
2534 IM Graph Algo failures caused by a lack of available memory 384 Debug Instance level
2535 IM Graph Algo failures caused by missing reverse CSRs 384 Debug Instance level
2536 sql area evicted - failed 256 Instance level
2537 CCursor + sql area evicted - failed 256 Instance level
2568 new RECO protocol executions 384 Debug Instance level
2569 SPM_TEST_PLAN_VERIFICATIONS 320 SQL Instance level
2570 SPM_TEST_PLAN_IMPROVEMENTS 320 SQL Instance level
2571 SPM_TEST_PLAN_REGRESSIONS 320 SQL Instance level
2576 maximum user calls per request 288 Real Application Clusters Instance level
2577 maximum calls protected by Application Continuity per request 288 Real Application Clusters Instance level
2579 cell RDMA reads eligible 257 User Instance level
2580 cell RDMA read hash table probes 257 User Instance level
2581 cell RDMA reads issued 257 User Instance level
2582 cell RDMA probe failures - hash table buffer allocation failed 257 User Instance level
2583 cell RDMA probe failures - IPCDAT metadata allocation failed 257 User Instance level
2584 cell RDMA probe failures - IPCDAT errors 257 User Instance level
2585 cell RDMA read failures - lease expired 257 User Instance level
2586 cell RDMA reads - Exascale RPM incarnation matched 257 User Instance level
2587 cell RDMA read failures - invalid Exascale RPM ID or GUID 257 User Instance level
2588 cell RDMA reads rejected - ineligible 257 User Instance level
2589 cell RDMA read failures - client registration errors 257 User Instance level
2590 cell physical IO bytes saved during smart incremental backup 256 Instance level
2591 cell IO retries due to buffer allocation failures 320 SQL Instance level
2592 cell num bytes in filter passthru due to low mem 256 Instance level
2593 cell num bytes in filter passthru due to subheap size limit exc 256 Instance level
2594 cell smart IO foreground CPU time (msec) 256 Instance level
2595 cell smart IO background CPU time (msec) 256 Instance level
2596 cell num smart IO sessions in rdbms block IO due to online encr 256 Instance level


On the other side, 17 statistics were removed: 

STATISTIC# NAME CLASS CLASS_NAME
469 gc current blocks served with BPS 40 Cache Real Application Clusters
471 gc cr blocks served with BPS 40 Cache Real Application Clusters
951 index cmph sc, ffs decomp buffers 128 Debug
952 index cmph sc, ffs decomp buffers rows avail 128 Debug
953 index cmph sc, ffs decomp buffers rows used 128 Debug
954 index cmph sc, ffs decomp failures 128 Debug
955 index cmph sc, ffs decomp buffers released and found valid 128 Debug
1490 backup pieces compressed locally 1 User
1491 backup data compressed locally 1 User
1492 backup compressed data written locally 1 User
1493 backup piece local processing time 1 User
1494 backup pieces compressed remotely 1 User
1495 backup data compressed remotely 1 User
1496 backup compressed data written remotely 1 User
1497 backup piece remote processing time 1 User
1586 cell pmem log writes 258 Redo Instance level
1855 KTFT file extend req 384 Debug Instance level

Posted by at Keine Kommentare:
Labels: , ,

2023-04-09

23c free - new events

 Oracle 23c database has a lot of new features. Of course there are also a lot of new wait events. 

There are 212 new events (in comparison to 21.9):

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS DISPLAY_NAME
2 cached session cache     Idle cached session
74 OFS file IO completion opcode     Idle OFS file IO completion
80 idle class spare wait event 1 p1 p2 p3 Idle idle class spare wait event 1
81 idle class spare wait event 2 p1 p2 p3 Idle idle class spare wait event 2
82 idle class spare wait event 3 p1 p2 p3 Idle idle class spare wait event 3
83 idle class spare wait event 4 p1 p2 p3 Idle idle class spare wait event 4
84 idle class spare wait event 5 p1 p2 p3 Idle idle class spare wait event 5
85 idle class spare wait event 6 p1 p2 p3 Idle idle class spare wait event 6
86 idle class spare wait event 7 p1 p2 p3 Idle idle class spare wait event 7
87 idle class spare wait event 8 p1 p2 p3 Idle idle class spare wait event 8
88 idle class spare wait event 9 p1 p2 p3 Idle idle class spare wait event 9
89 idle class spare wait event 10 p1 p2 p3 Idle idle class spare wait event 10
96 spawn request deferred       Idle spawn request deferred
106 latch: MGA pid list latch address number why Concurrency latch: MGA list latch
107 latch: MGA pid object latch address number why Concurrency latch: MGA pid object latch
112 BRDG: bridge controller idle poller id     Idle BRDG: bridge controller idle
115 Distributed Trace: Archival Worker Idle       Idle Distributed Trace Archival Worker
157 kcb prewarm single block physical read       User I/O kcb prewarm single block physical read
201 log file sync: slb flush for orl dependency       Concurrency log file sync: slb flush for orl dependency
207 pmem direct commit file open time       System I/O pmem direct commit file open time
208 pmem direct commit file write time       System I/O pmem direct commit file write time
268 gc close file# block# class# Cluster gc close
269 gc downconvert file# block# class# Cluster gc downconvert
309 change tracking file read for backup block# blocks   User I/O CTF read during backup
328 DGPDB query SCN service idle timeout     Idle DGPDB query SCN service idle
329 True Cache: fetch request slot       Concurrency True Cache: fetch request slot
330 True Cache: background process idle       Idle True Cache: background process idle
331 True Cache: single block fetch file# block# blocks User I/O True Cache: single block fetch
332 True Cache: multiblock fetch file# block# blocks User I/O True Cache: multiblock fetch
333 True Cache: list of blocks fetch files blocks requests User I/O True Cache: list of blocks fetch
341 enq: TX - row lock (HIGH priority) name|mode usn<<16 | slot sequence Application enq: TX - row lock (HIGH priority)
342 enq: TX - row lock (MEDIUM priority) name|mode usn<<16 | slot sequence Application enq: TX - row lock (MEDIUM priority)
343 enq: TX - row lock (LOW priority) name|mode usn<<16 | slot sequence Application enq: TX - row lock (LOW priority)
346 enq: TX - switch undo tablespace name|mode usn<<16 | slot sequence Administrative enq: TX - switch undo tablespace
356 Flashback Data Archive retry of internal DML retry count VersionsQueryRetry|
Other		   Concurrency Flashback Data Archive retry of internal DML
357 Flashback Data Archive column map visibility obj# retry count   Concurrency Flashback Data Archive column map visibility
358 Flashback Data Archive history tables operation obj# create|drop|row retry count Concurrency Flashback Data Archive history tables operation
359 Flashback Data Archive ddl timer on global barrier SCN obj# pre|post retry count Concurrency Flashback Data Archive ddl timer on global barrier SCN
365 Escrow retry to drain journal retry count     Concurrency Esrow retry of DML/DDL
366 enq: JL - Escrow row lock name|mode object # 0 Concurrency enq: JL - Escrow row lock
370 enq: BE - Purge Space Pressure name|mode tablespace# block# Concurrency enq: BE - Purge Space Pressure
378 sequence instance lock       Concurrency sequence instance lock
385 cluster table update retry key dba backward dba forward dba Concurrency cluster table update retry
415 Free unpinned heap objects sleep heap ds min_size num freed Concurrency Free unpinned heap objects sleep
424 library cache: ADG handle purge handle address     Concurrency library cache: ADG handle purge
428 resmgr:cell cpu quantum pdb running pdb runnable cell usec Scheduler resmgr:cell cpu quantum
429 resmgr:cpu adjust pdb running pdb runnable reason Scheduler resmgr:cpu adjust
475 latch: Result Cache: Main Latch address number why Concurrency latch: Result Cache: Main Latch
491 REPL Capture: filter callback ruleset 2=>GoldenGate 
1=>XStream 
0=>Streams		     Application REPL Capture: filter callback ruleset
514 XA operation retry       Concurrency XA operation retry
540 Text sync complete       Concurrency Text sync complete
552 AUTO access ASM_CLIENT registration       Idle AUTO access ASM_CLIENT registration
598 cell scan blockio mode file number first dba block cnt User I/O cell scan blockio mode
603 LFQ: enqueue blocked due to flow control       Queueing LFQ: enqueue blocked due to flow control
604 Sharding: replication log replication unit ID block number size_bytes System I/O Sharding: replication log
605 Sharding: commit replication unit ID session# session serial# Commit Sharding: commit
606 Sharding replication replication unit ID sharding component   Idle Sharding replication
607 Sharding Replication: flow control replication unit ID upstream component downstream component Queueing Sharding Replication: flow control
649 Allocate PGA memory from OS Bytes where   Other Allocate PGA memory from OS
650 Allocate CGA memory from OS Bytes where   Other Allocate CGA memory from OS
651 Allocate UGA memory from OS Bytes where   Other Allocate UGA memory from OS
652 Allocate DGA memory from OS Bytes where   Other Allocate DGA memory from OS
653 Free private memory to OS Bytes where   Other Free private memory to OS
660 process multi spawn process hdl count   Other process multi spawn
690 worker quiesce completion slave class pdb id   Other worker quiesce completion
693 enq: SP - contention 1 name|mode id1 id2 Other enq: SP - contention 1
694 enq: SX - contention 2 name|mode id1 id2 Other enq: SX - contention 2
695 enq: UB - contention 3 name|mode id1 id2 Other enq: UB - contention 3
696 enq: UC - contention 4 name|mode id1 id2 Other enq: UC - contention 4
697 enq: UD - contention 5 name|mode id1 id2 Other enq: UD - contention 5
698 enq: WB - contention 6 name|mode id1 id2 Other enq: WB - contention 6
699 enq: ZM - contention 7 name|mode id1 id2 Other enq: ZM - contention 7
700 enq: WH - contention 8 name|mode id1 id2 Other enq: WH - contention 8
701 enq: WJ - contention 9 name|mode id1 id2 Other enq: WJ - contention 9
702 enq: XA - contention 10 name|mode id1 id2 Other enq: XA - contention 10
703 enq: XE - contention 11 name|mode id1 id2 Other enq: XE - contention 11
704 enq: XF - contention 12 name|mode id1 id2 Other enq: XF - contention 12
705 enq: XG - contention 13 name|mode id1 id2 Other enq: XG - contention 13
706 enq: UA - contention 14 name|mode id1 id2 Other enq: UA - contention 14
707 enq: ZI - contention 15 name|mode id1 id2 Other enq: ZI - contention 15
708 enq: ZJ - contention 16 name|mode id1 id2 Other enq: ZJ - contention 16
709 spare wait event 1 p1 p2 p3 Other spare wait event 1
710 spare wait event 2 p1 p2 p3 Other spare wait event 2
711 spare wait event 3 p1 p2 p3 Other spare wait event 3
712 spare wait event 4 p1 p2 p3 Other spare wait event 4
713 spare wait event 5 p1 p2 p3 Other spare wait event 5
714 spare wait event 6 p1 p2 p3 Other spare wait event 6
715 spare wait event 7 p1 p2 p3 Other spare wait event 7
716 spare wait event 8 p1 p2 p3 Other spare wait event 8
717 spare wait event 9 p1 p2 p3 Other spare wait event 9
718 spare wait event 10 p1 p2 p3 Other spare wait event 10
719 spare wait event 11 p1 p2 p3 Other spare wait event 11
720 spare wait event 12 p1 p2 p3 Other spare wait event 12
721 spare wait event 13 p1 p2 p3 Other spare wait event 13
722 spare wait event 14 p1 p2 p3 Other spare wait event 14
723 spare wait event 15 p1 p2 p3 Other spare wait event 15
724 spare wait event 16 p1 p2 p3 Other spare wait event 16
725 spare wait event 17 p1 p2 p3 Other spare wait event 17
726 spare wait event 18 p1 p2 p3 Other spare wait event 18
727 spare wait event 19 p1 p2 p3 Other spare wait event 19
728 spare wait event 20 p1 p2 p3 Other spare wait event 20
729 spare wait event 21 p1 p2 p3 Other spare wait event 21
730 spare wait event 22 p1 p2 p3 Other spare wait event 22
731 spare wait event 23 p1 p2 p3 Other spare wait event 23
732 spare wait event 24 p1 p2 p3 Other spare wait event 24
733 spare wait event 25 p1 p2 p3 Other spare wait event 25
734 spare wait event 26 p1 p2 p3 Other spare wait event 26
735 spare wait event 27 p1 p2 p3 Other spare wait event 27
736 spare wait event 28 p1 p2 p3 Other spare wait event 28
737 spare wait event 29 p1 p2 p3 Other spare wait event 29
738 spare wait event 30 p1 p2 p3 Other spare wait event 30
740 IPC busy for shutdown       Other IPC busy for shutdown
758 child service starting/stopping service hash id number of child services active loop count Other child service starting/stopping
771 Hang Analysis Timeout Test       Other Hang Analysis Timeout Test
773 leader diskmon startup       Other leader diskmon startup
774 leader diskmon read       Other leader diskmon read
777 RMF sync IO busy       Other RMF sync IO busy
799 scheduler group stop worker threads scheduler group context     Other scheduler group stop worker threads
800 action group quiesce action group context PDB id   Other action group quiesce
802 PMEM region recovery       Other PMEM region recovery
803 PMEM transaction or undo       Other PMEM transaction or undo
804 PMEM mutex 1       Other PMEM mutex 1
805 PMEM mutex 2       Other PMEM mutex 2
806 RAC Phased Rolling Patch callback bug number callback phase   Other RAC Phased Rolling Patch callback
807 NMON command execution       Other NMON command execution
808 Distributed Trace: Unit Test       Other Distributed Trace Unit Test
809 Distributed Trace: Remote Archival Status       Other Distributed Trace Remote Archival Status
933 CF cache circular buffer space       Other CF cache circular buffer full
958 remote log force       Other remote log force
970 enq: WL - destination management name|mode log # / thread id # sequence # Other enq: WL - destination management
994 enq: WL - Add foreign config name|mode log # / thread id # sequence # Other enq: WL - Add foreign config
995 enq: WL - Drop/rename foreign config name|mode log # / thread id # sequence # Other enq: WL - Drop/rename foreign config
996 enq: WL - Change foreign config name|mode log # / thread id # sequence # Other enq: WL - Change foreign config
1040 latch: pmem direct commit boc address number why Other latch: pmem direct commit boc
1041 latch: pmem direct commit slb address number why Other latch: pmem direct commit slb
1042 latch: pmem direct commit config address number why Other latch: pmem direct commit config
1044 pmem direct commit incarnation       Other pmem direct commit - new incarnation for offline mirrors
1045 pmem direct commit reconfig       Other pmem direct commit - mirror reconfig
1090 enq: MR - DGPDB shadow FL name|mode 0 or file # type Other enq: MR - DGPDB shadow FL
1106 enq: BR - RMAN Serialize CF Access name|mode operation file # Other enq: BR - RMAN Serialize CF Access
1218 enq: FD - PDB bitvec query name|mode Internal Internal Other enq: FD - PDB bitvec query
1221 flashback Misc loc#     Other flashback Misc
1261 DGPDB CF backup       Other DGPDB CF backup
1262 DGPDB query SCN service message operation pdbid   Other DGPDB query SCN service message
1282 parallel recovery sga cleanup       Other parallel recovery sga cleanup
1283 smon to restart parallel txn recovery       Other smon to restart parallel txn recovery
1285 wait for an undo record       Other wait for an undo record
1286 wait for a parallel recovery to abort       Other wait for a parallel recovery to abort
1315 breakpoint test mutex kgxaoltype kgxaolwhr kgxaolopr Other breakpoint test mutex
1327 Sort Segment Grow tsn     Other Sort Segment Grow
1333 enq: SK - multi-instance auto temp shrink name|mode tablespace # dba Other enq: SK - multi-instance auto temp shrink
1334 enq: SK - multi-inst temp ext rls name|mode tablespace # dba Other enq: SK - multi-inst temp ext rls
1344 IM Shrink garbage collect retry       Other IM Shrink garbage collect retry
1377 enq: ZE - IM segment load by other session name|mode tablespace # objd# Other enq: ZE - IM segment load by other session
1379 IMXT Populate       Other IMXT Populate
1386 latch: AIM scan ovhd HT parent latch address number why Other latch: AIM scan ovhd HT parent latch
1387 latch: AIM txn ovhd HT parent latch address number why Other latch: AIM txn ovhd HT parent latch
1388 latch: column ulevel hash table latch address number why Other latch: column ulevel hash table latch
1586 RAC: domain constraint DDL lock maximum attempts attempt count   Other RAC: domain constraint DDL lock
1632 enq: KF - Firewall Allowlist name|mode 0 0 Other enq: KF - Firewall Allowlist
1635 stop RECO txn recovery in PDB       Other stop RECO txn recovery in PDB
1706 online DDL delay table obj# phase   Other online DDL delay
1776 enq: JI - MV concurrent refresh name|mode object # purpose Other enq: JI - MV concurrent refresh
1777 enq: JI - MV setup name|mode object # purpose Other enq: JI - MV setup
1791 enq: UG - test only for sys.sync_global name|mode id 0 Other enq: UG - test only for sys.sync_global
1794 AQ: Dequeue flow control queue id id1 id2 Other AQ: Dequeue flow control
1843 enq: RQ - AQ Cache Shards name|mode Q_OBJ# shard:mode Other enq: RQ - AQ Cache Shards
1877 enq: AX - Saga id name|mode 0 0 Other enq: AX - Saga id
1892 enq: IQ - Text index maintenance name|mode event type objn Other enq: IQ - Text index maintenance
1893 Text batch free       Other Text batch free
1895 enq: XU - XDB Mem Init name|mode     Other enq: XU - XDB Mem Init
1913 enq: RF - Broker RSM Lock name|mode lock operation lock value Other enq: RF - Broker RSM Lock
1966 enq: AM - ASM Metadata checksum migration name|mode id1 id2 Other enq: AM - ASM Metadata checksum migration
1967 enq: AM - ASM XML file type update name|mode id1 id2 Other enq: AM - ASM XML file type update
2001 enq: AM - ASM Extent Map Share name|mode id1 id2 Other enq: AM - ASM Extent Map Share
2002 enq: AM - ASM sendMap file descriptor name|mode id1 id2 Other enq: AM - ASM sendMap file descriptor
2003 enq: AM - ASM BaD Block Media Recovery name|mode id1 id2 Other enq: AM - ASM BaD Block Media Recovery
2031 ASM Relocate offload context init/finalize where     Other ASM Relocate offload context init/finalize
2038 ASM Peel Complete group#     Other ASM Peel Complete
2039 Query ASM view       Other Query ASM view
2049 ASM client de-registration       Other ASM client de-registration
2050 ASMB starting/connecting locn asmbidx group Other ASMB starting/connecting
2051 ASM diskgroup renewing locn asmbidx group Other ASM diskgroup renewing
2052 ASM diskgroup block/unblock locn asmbidx group Other ASM diskgroup block/unblock
2053 ASM RBAL response locn     Other ASM RBAL response
2061 ASM file advisory lock gn fn   Other ASM file advisory lock
2116 SMON: disk group dismount group     Other SMON: disk group dismount
2133 ASM BMR Find group# file# msg type Other ASM BMR Find
2134 CSS Voting disk update       Other CSS Voting disk update
2143 enq: WO - AWR storage operation name|mode 0 0 Other enq: WO - AWR storage operation
2144 enq: WW - AWR sweep operation name|mode 0 0 Other enq: WW - AWR sweep operation
2145 enq: WC - AWR State Transition name|mode 0 0 Other enq: WC - AWR State Transition
2184 scheduler memory dump completion       Other scheduler memory dump completion
2189 cell worker online completion cellhash#     Other cell worker online completion
2190 cell worker retry cellhash#     Other cell worker retry
2206 Remote instance buffer cache warmup       Other Remote instance buffer cache warmup
2207 CDB quiesce: sessions draining       Other CDB quiesce: sessions draining
2208 Alter PDB Operation sub-op req-id   Other Alter PDB Operation
2211 Sharding Apply: commit replication unit ID transaction commit log index   Other Sharding Apply: commit
2212 Sharding: DDL syncpoint       Other Sharding: DDL syncpoint
2213 Sharding: ddl status update       Other Sharding: ddl status update
2214 enq: UR - GWR Change Role name|mode 0 0 Other enq: UR - GWR Change Role
2215 enq: UR - Sharding Read Replication Unit name|mode 0 0 Other enq: UR - Sharding Read Replication Unit
2216 enq: UR - Sharding Metadata Refresh name|mode 0 0 Other enq: UR - Sharding Metadata Refresh
2219 enq: RV - row version insert name|mode hash1 hash2 Other enq: RV - row version insert
2220 enq: MB - add blockchain table row name|mode txn id 0 Other enq: MB - add blockchain table row
2226 enq: FQ - FSD/RACD local resource name|mode ID1 ID2 Other enq: FQ - FSD/RACD local resource
2227 enq: FY - FSD/RACD global resource name|mode ID1 ID2 Other enq: FY - FSD/RACD global resource
2228 enq: FI - contention type|mode id1 id2 Other enq: FI - contention
2229 FSD internal test wait event level   Other FSD internal test wait
2230 enq: ME - FSD/RACD Metadata access name|mode 0 0 Other enq: ME - FSD/RACD Metadata access
2231 enq: MI - contention type|mode id1 id2 Other enq: MI - contention

And 26 wait events disappeared:

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
93 latch: MGA pid alloc latch address number why Concurrency
363 enq: IV - cross instance invalidation lock idx     Concurrency
402 dedicated server timer wait event     Network
592 PGA memory operation       Other
630 enq: SP - ASM Extent Map Share name|mode id1 id2 Other
631 enq: SP - AQ Cache Shards name|mode id1 id2 Other
633 enq: SP - contention 4 name|mode id1 id2 Other
634 enq: SX - contention 5 name|mode id1 id2 Other
635 enq: SX - contention 6 name|mode id1 id2 Other
637 spare 2 p1 p2 p3 Other
638 spare 3 p1 p2 p3 Other
639 spare 4 p1 p2 p3 Other
640 spare 5 p1 p2 p3 Other
641 spare 6 p1 p2 p3 Other
642 spare 7 p1 p2 p3 Other
643 spare 8 p1 p2 p3 Other
644 spare 9 p1 p2 p3 Other
645 spare 10 p1 p2 p3 Other
677 master diskmon startup       Other
678 master diskmon read       Other
703 PMEM resource       Other
863 FAL archive wait 1 sec for REOPEN minimum       Other
1159 wait for a undo record       Other
1160 wait for a paralle reco to abort       Other
1247 IMXT Populate - drop HT       Other
1892 ASM background starting       Other


Most of these events will rarely be seen or of any importance. Still some show at least interesting names which can lead to interesting technology.


Posted by at Keine Kommentare:
Labels: , ,

23c free - DBMS_USERDIAG

In Oracles 23c free Database, among other new packages which still need to be discovered, there is one which warms my heart. It's called DBMS_USERDIAG. 
Unfortunately it is not documented yet - this makes it worth to have a look at it. 

As the Package definition is human readable and proper comments are provided by developers, this can be taken as a first summary:

create or replace NONEDITIONABLE PACKAGE dbms_userdiag AUTHID CURRENT_USER AS

  PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

  -- last call did not return an error
  NOERROR                   CONSTANT INTEGER := 0;

  -- **********************************************************************
  -- enable_sql_trace_event
  --   Enable sql_trace event at a given level
  --   Optionally a sql_id can also be specified in which case tracing will be
  --   enabled for that sql_id alone. Default is to enable events without sql
  --   scope.
  --   Default is to set event in current session alone. Event can be disabled
  --   by setting disable to non-zero value (default zero).
  --
  -- **********************************************************************
  PROCEDURE enable_sql_trace_event
  (
    level     IN BINARY_INTEGER DEFAULT 1,
    sid       IN BINARY_INTEGER DEFAULT 0,
    ser       IN BINARY_INTEGER DEFAULT 0,
    binds     IN BINARY_INTEGER DEFAULT 0,
    waits     IN BINARY_INTEGER DEFAULT 0,
    plan_stat IN VARCHAR2       DEFAULT NULL,
    sql_id    IN VARCHAR2       DEFAULT NULL,
    disable   IN BINARY_INTEGER DEFAULT 0,
    sys       IN BINARY_INTEGER DEFAULT 0
  );

  -- **********************************************************************
  -- check_sql_trace_event
  --   Checks current sql_trace event and retrieves the level
  --   sql_id is currently ignored.
  --
  -- **********************************************************************
  PROCEDURE check_sql_trace_event
  (
    level     OUT BINARY_INTEGER,
    sql_id    IN VARCHAR2 DEFAULT NULL,
    sys       IN BINARY_INTEGER DEFAULT 0
  );

  -- **********************************************************************
  -- trace
  --   Write a message to "user trace file". (default)
  --   If alert is set to non-zero value, then message is written to alert log.
  --
  -- **********************************************************************
  PROCEDURE trace
  (
    message IN VARCHAR2,
    alert   IN BINARY_INTEGER DEFAULT 0
  );

  -- **********************************************************************
  -- set_tracefile_identifier
  --    This routine is used to set a custom trace file identifier
  --    for the active tracefile.
  -- **********************************************************************

  PROCEDURE set_tracefile_identifier
  (
    trc_identifier          IN VARCHAR2
  );

  -- **********************************************************************
  -- set_exception_mode
  --    This routine sets the exception mode for the package.
  --    If exception mode is set to true, then all the exceptions
  --    will be raised to client. If it is set to false, then all the
  --    exceptions will be suppressed, and the client will not see any
  --    exception, even if underlying APIs are raising exceptions.
  --
  --    Since this is a diagnosability API, clients might not expect these
  --    API calls to raise their own exceptions.
  --    By default, exception mode will be set to false.
  --    Clients can change it any time by calling this function.
  -- **********************************************************************

  PROCEDURE set_exception_mode
  (
    exc_mode                IN BOOLEAN DEFAULT FALSE       /* exception mode */
  );

  -- **********************************************************************
  -- get_call_status
  --    This routine is used to obtain the status of the last call to the
  --    DBMS_USERDIAG API. In case the previous call was successful, the value
  --    of this call will be NOERROR(0).
  -- **********************************************************************

  FUNCTION get_call_status RETURN NUMBER;

  -- **********************************************************************
  -- get_call_error_msg
  --    This routine is used to obtain the error message if the last call
  --    to DBMS_USERDIAG API returned an error. In case the previous call was
  --    successful, the value returned by this API will be NULL.
  -- **********************************************************************

  FUNCTION get_call_error_msg RETURN VARCHAR2;

END dbms_userdiag;
By default, this package is only granted to the DBA role. 
But when granted to an user, this user can use all procedures without additional grants (like ALTER SESSION or ALTER SYSTEM). 

I managed to set_tracefile_identifier, enable trace for a level and also for a specific SQL_ID - both for the current session and also system-wide (PDB) . It seems this package can be a replacement for some functionality of DBMS_SESSION.session_trace_enable and DBMS_SYSTEM.ksdwrt.  


For completion, the list of internal functions called by these procedures - as far as I could observe them: 

ENABLE_SQL_TRACE_EVENT  dbkpls_enable_sql_trace_event
CHECK_SQL_TRACE_EVENT dbkpls_check_sql_trace_event
TRACE dbkpls_trace
SET_TRACEFILE_IDENTIFIER  dbkpls_set_tracefile_id

Posted by at Keine Kommentare:
Labels: , ,

2023-04-08

23c free - SQL History

One new feature in 23c is the SQL History Monitoring and Reporting. 


If enabled (SQL_HISTORY_ENABLED => TRUE), it provides information about the last 50 SQL statements of a session. This only includes top-level SQLs, no recursive or internal SQLs are recorded. 

For these, tracing is a great feature to get them all!

According to v$system_parameter, SQL_HISTORY_ENABLED can be changed on session, system, pdb and instance level. 
For me it seems, SYS statements are not monitored. If this is a feature or a bug I couldn't find in the documentation. 

The history can be viewed in V$SQL_HISTORY. This view does not only contains the SQL_TEXT (first 100 characters) and SQL_ID,  but also a lot of additional information. Beside some expected basics like CON_ID, SID, SESSION_SERIAL#, SQL_ID and PLAN_HASH_VALUE, there are also a lot of execution statistics (ELAPSED_TIME, CPU_TIME, BUFFER_GETS or PHYSICAL_READ_REQUESTS). 

An important feature is the ability to log errors! Not only valid SQLs are recorded, but also failed ones - together with their ERROR_NUMBER. Some of my tests show: 


select key, sql_id, sql_text, plan_hash_value, sql_exec_id, error_number, error_facility, statement_type 
from v$sql_history
where (sql_text like '%1476!!%' or Sql_text like '%duhall%') and error_signalled='Y';


KEY SQL_ID SQL_TEXT PLAN_HASH_VALUE SQL_EXEC_ID ERROR_NUMBER ERROR_FACILITY STATEMENT_TYPE
1017907380484 0cuvy329px4j8 select 20;select /*1476!!*/ 1/0 0 0 923 ORA OTHER
1017907380740 462sqgw42fjby select /*1476!!*/ 1/0 1388734953 16777216 1476 ORA QUERY
1017907380996 462sqgw42fjby select /*1476!!*/ 1/0 0 0 1476 ORA QUERY
1017907381252 gwsvb6439kcxc select * from duhall 0 0 942 ORA QUERY

 If the error occurs during the SQLs execution, v$sql_history shows 2 lines - in my example for SQL_ID => 462sqgw42fjby. 

v$sql_history can be queried by all users. but depending on the users permissions, different details are shown: 
A DBA in the CDB can see all historic SQLs of all sessions. 
A DBA in a PDB can only see all historic SQLs in its PDB. 
A normal user in a PDB can only see all historic SQLs of all current sessions of this user (in the current PDB) - not only it's own session!

At disconnect, all historic SQLs related to this session are lost. (there is no view like DBA_HIST_SQL_HISTORY). 


All those which want to see more than 50 SQLs per session, can alter the undocumented parameter _sql_history_buffers. In my case I need to set it on each PDB, it seems not to be inherited from the CDBs setting. 

I did not find any parameter to change the 100 character limit for each SQL. 


At last, v$sql_history comes with a companion: V$SQL_HISTORY_STATS. This view contains some information about the sql history buffer for each PDB: 



Name                     Null? Type        
------------------------ ----- ----------- 
TOTAL_BUFFERS                  NUMBER      
ESTIMATED_BUFFERS              NUMBER      
TOTAL_SESSIONS                 NUMBER      
USED_SESSIONS                  NUMBER      
SQL_HISTORY_SGA_LIMIT_KB       NUMBER      
ENTRY_SIZE                     NUMBER      
ENTRY_LIMIT_PARAM              NUMBER      
LIST_BYTES                     NUMBER      
LIST_LENGTH                    NUMBER      
LIST_COUNT                     NUMBER      
SQLTEXT_LIMIT                  NUMBER      
ENABLED                        VARCHAR2(1) 
OVERFLOW                       VARCHAR2(1) 
CON_ID                         NUMBER


v$sql_history is based on x$kesmasmon (kesm might be an abbreviation for kernel event SQL manageability)- in my sandbox, each row (in fact memory structure) has a size of 368 bytes - but not all structures are aligned to the same number, so I expect there are several independent pools. 

V$SQL_HISTORY_STATS is based on x$kesmasmon_stat.


Posted by at 2 Kommentare:
Labels: , ,
Abonnieren Posts (Atom)