Sunday, November 30, 2014

How many different type of wait event parameters in Oracle 12c (12.1.0.2)

SQL> select parameter1,count(*) from v$event_name group by parameter1 order by 2 desc;

PARAMETER1                                                         COUNT(*)
---------------------------------------------------------------- ----------
name|mode                                                               463
                                                                        456
type|mode                                                               200
address                                                                  50
file#                                                                    40
sleeptime/senderid                                                       40
cellhash#                                                                22
location                                                                 19
count                                                                    18
                                                                         17
driver id                                                                16
group                                                                    16
group#                                                                   14
Session ID                                                               13
2=>GoldenGate 1=>XStream 0=>Streams                                      12
session_id                                                                8
where                                                                     8
idn                                                                       7
sleep time                                                                7
function_id                                                               7
waittime                                                                  6
blkno                                                                     5
p1                                                                        5
filectx                                                                   5
handle address                                                            4
le                                                                        4
log#                                                                      4
file number                                                               4
block#                                                                    4
wait count                                                                4
retry count                                                               4
type                                                                      3
op                                                                        3
nalive                                                                    3
duration                                                                  3
object                                                                    3
files                                                                     3
cluinc                                                                    3
channel context                                                           3
msg ptr                                                                   3
clientid                                                                  3
requests                                                                  3
segment#                                                                  3
circuit#                                                                  2
msgop                                                                     2
buffer#                                                                   2
tsn                                                                       2
maximum attempts                                                          2
lmd/lms id                                                                2
object_id                                                                 2
Caller ID                                                                 2
dba                                                                       2
seghdr                                                                    2
waited                                                                    2
iowp id                                                                   2
pname                                                                     2
group_name1                                                               2
fileno                                                                    2
cache id                                                                  2
pid                                                                       2
wait for scn's hi 4 bytes                                                 2
channel handle                                                            2
Owner ID                                                                  2
copy latch #                                                              2
type|mode|where                                                           1
pool                                                                      1
undo seg#|slot#                                                           1
lock                                                                      1
hashid                                                                    1
action                                                                    1
File number                                                               1
Type                                                                      1
who am I                                                                  1
alive slaves                                                              1
send count                                                                1
index                                                                     1
event                                                                     1
table                                                                     1
undo segment#                                                             1
rootdba                                                                   1
end-point#                                                                1
kxfxsp debug wait: stalling for slave 0                                   1
component                                                                 1
ktelc_wait1s                                                              1
Slave ID                                                                  1
sleeptime                                                                 1
outstanding #aio                                                          1
wrap                                                                      1
blocks                                                                    1
operation count                                                           1
force SCN                                                                 1
succ                                                                      1
dismount force                                                            1
msg                                                                       1
by thread#                                                                1
dump                                                                      1
kxfxse debug wait: stalling for slave 0                                   1
delegation count                                                          1
timeout                                                                   1
FileOperation                                                             1
tape operation                                                            1
filename_hash                                                             1
object address                                                            1
netp id                                                                   1
dbwr#                                                                     1
xidusn                                                                    1
stage                                                                     1
function                                                                  1
process#                                                                  1
slave id                                                                  1
group_id                                                                  1
connection broker                                                         1
session#                                                                  1
defer segment index                                                       1
indicator                                                                 1
latch addr                                                                1
lwn_id                                                                    1
Instruction id                                                            1
branch#                                                                   1
session_num                                                               1
nservers                                                                  1
slowcellhash#                                                             1
kdcph_mai                                                                 1
from_process                                                              1
rolling mig                                                               1
domain id                                                                 1
caller instance number                                                    1
id                                                                        1
ts#                                                                       1
thread#                                                                   1
process number                                                            1
scans                                                                     1
tries                                                                     1
Broker Component                                                          1
test #                                                                    1
max operations                                                            1
component id                                                              1
retries                                                                   1
1=>MASTER 2=>SLAVE                                                        1
process_pid                                                               1
PURPOSE                                                                   1
pending_nd                                                                1
SCN                                                                       1
layer                                                                     1
clsrrestype                                                               1
serial                                                                    1
event #                                                                   1
limit                                                                     1
kdcphc_ack                                                                1
heap ds                                                                   1
wait event                                                                1
queue id                                                                  1
gopp id                                                                   1
error                                                                     1

154 rows selected.

SQL> select parameter2,count(*) from v$event_name group by parameter2 order by 2 desc;

PARAMETER2                                                         COUNT(*)
---------------------------------------------------------------- ----------
                                                                        602
id1                                                                     258
0                                                                        81
number                                                                   50
block#                                                                   44
passes                                                                   41
operation                                                                38
                                                                         24
QT_OBJ#                                                                  21
object #                                                                 20
Q_OBJ#                                                                   20
obj#                                                                     19
disk group                                                               15
intr                                                                     14
#bytes                                                                   14
disk group #                                                             12
log # / thread id #                                                      12
lock operation                                                           11
service ID                                                               11
tablespace #                                                             10
serial                                                                    8
value                                                                     8
diskhash#                                                                 7
0 or file #                                                               7
record type                                                               7
Internal                                                                  7
blocks                                                                    6
logid                                                                     6
file#                                                                     6
type                                                                      6
sleeptime                                                                 5
mode                                                                      5
kdlw lobid first half                                                     5
container group                                                           5
p2                                                                        5
resource id                                                               5
2                                                                         4
group ID / file ID                                                        4
where                                                                     4
wait flags                                                                4
opcode                                                                    4
first dba                                                                 4
persistent DG number                                                      4
usn<<16 | slot                                                            4
sqlid                                                                     3
TYPE                                                                      3
group and disk number                                                     3
count                                                                     3
failed                                                                    3
dbid                                                                      3
blkno                                                                     3
process#                                                                  3
reg id                                                                    3
waited                                                                    3
1                                                                         3
channel handle                                                            2
wait for scn's lo 4 bytes                                                 2
lms id                                                                    2
redo thread                                                               2
attempt count                                                             2
poll                                                                      2
lock address                                                              2
break?                                                                    2
0-MMON, 1-MMON Slave                                                      2
table space #                                                             2
message                                                                   2
groupid                                                                   2
Log #                                                                     2
disk group #:file #                                                       2
tablespace ID                                                             2
group_name2                                                               2
node#/parallelizer#                                                       2
checkpoint ID                                                             2
lock_mode                                                                 2
master object #                                                           2
#blks                                                                     2
Thread                                                                    2
bytes                                                                     2
workspace #                                                               2
interrupt                                                                 2
wait_count                                                                2
client id/group num                                                       2
sync scn                                                                  2
class id                                                                  2
size                                                                      1
fsb                                                                       1
num procs                                                                 1
low file obj add                                                          1
current aio limit                                                         1
property name                                                             1
op|restype                                                                1
pending_insts                                                             1
class*10+mode                                                             1
session ID                                                                1
undo segment #                                                            1
serial number                                                             1
view object #                                                             1
Session-id                                                                1
lock#                                                                     1
wait time                                                                 1
loop                                                                      1
slave_id                                                                  1
wait                                                                      1
file group id                                                             1
cluster incarnation number                                                1
scnwrp                                                                    1
amrv$ key                                                                 1
xidslt                                                                    1
requests                                                                  1
KZAM Fga Partition                                                        1
transaction entry #                                                       1
Wait Argument 1                                                           1
disk group number                                                         1
buffer length                                                             1
sleep time                                                                1
File Type or File number                                                  1
plan #                                                                    1
param                                                                     1
tx flags                                                                  1
retry_count                                                               1
l1bmb                                                                     1
hash value                                                                1
status                                                                    1
margin                                                                    1
consumer group id                                                         1
connection                                                                1
record length                                                             1
id                                                                        1
is_process                                                                1
nodeid                                                                    1
LTHREAD TYPE                                                              1
location                                                                  1
thread number                                                             1
base                                                                      1
scn                                                                       1
level                                                                     1
Schedule Id                                                               1
Flags                                                                     1
operation flags                                                           1
EnqMode                                                                   1
chunkNo                                                                   1
filename                                                                  1
dest|rcvr                                                                 1
pending_nd                                                                1
file #                                                                    1
KZAM Aud Partition                                                        1
element                                                                   1
Index Id                                                                  1
locn                                                                      1
gtrid hash value                                                          1
min_size                                                                  1
allocation mode                                                           1
process_sno                                                               1
rcvinc                                                                    1
chain#                                                                    1
wrap#                                                                     1
Slave process id                                                          1
KSBXIC Action                                                             1
error                                                                     1
instance                                                                  1
Op1                                                                       1
node#+sess#,cursor#+kv#                                                   1
file                                                                      1
map id                                                                    1
dginc                                                                     1
client id                                                                 1
fileno                                                                    1
channel handle count                                                      1
current size                                                              1
iocode                                                                    1
parno                                                                     1
waits                                                                     1
request                                                                   1
our thread#                                                               1
thread id #                                                               1
phase                                                                     1
pool #                                                                    1
Instance ID                                                               1
tsn#                                                                      1
segid                                                                     1
table obj#                                                                1
handle                                                                    1
edition obj#                                                              1
dg                                                                        1
pin address                                                               1
obj                                                                       1
block number                                                              1
startscn                                                                  1
client opcode                                                             1
inst id                                                                   1
tablespace # & objd                                                       1
Spillover audit file                                                      1
kjha_action                                                               1
lms#                                                                      1
group                                                                     1
task id                                                                   1

196 rows selected.


SQL> select parameter3,count(*) from v$event_name group by parameter3 order by 2 desc;

PARAMETER3                                                         COUNT(*)
---------------------------------------------------------------- ----------
                                                                        743
id2                                                                     258
0                                                                       150
tries                                                                    48
type                                                                     27
                                                                         25
timeout                                                                  25
block#                                                                   20
file #                                                                   20
shard:mode                                                               20
class#                                                                   17
sequence #                                                               13
queue type                                                               11
lock value                                                               11
operation parm                                                           10
blocks                                                                    9
dba                                                                       9
where                                                                     7
record id                                                                 7
thread                                                                    7
Internal                                                                  7
bktId<<16|instid                                                          6
p3                                                                        6
filetype                                                                  5
0/1                                                                       5
kdlw lobid sec half                                                       5
container id                                                              5
bytes                                                                     5
unused                                                                    4
block cnt                                                                 4
file ID                                                                   4
qref                                                                      4
workspace #                                                               4
non-DG number enqs                                                        4
sequence                                                                  4
disk #                                                                    4
id#                                                                       3
requests                                                                  3
wait                                                                      3
execid                                                                    3
2                                                                         3
AU number                                                                 3
notused                                                                   3
operation                                                                 3
instance                                                                  3
loop                                                                      3
size                                                                      2
block number                                                              2
slaveid                                                                   2
intr                                                                      2
100*mode+namespace                                                        2
location                                                                  2
zero                                                                      2
virtual extent number                                                     2
block                                                                     2
rdba                                                                      2
Bytes                                                                     2
file                                                                      2
event                                                                     2
bloom#                                                                    2
File number                                                               2
Sequence                                                                  2
process#                                                                  2
object #                                                                  1
set-id#                                                                   1
fail                                                                      1
ackscn                                                                    1
request identifier                                                        1
task id                                                                   1
ulevel                                                                    1
nbusy                                                                     1
Partition Id                                                              1
growth                                                                    1
target size                                                               1
100*mask+namespace                                                        1
op                                                                        1
wait time                                                                 1
allocated                                                                 1
1 or block                                                                1
count                                                                     1
objd#                                                                     1
group_name3                                                               1
1                                                                         1
not used                                                                  1
read/write                                                                1
version id                                                                1
mtype                                                                     1
log number                                                                1
childdba                                                                  1
insert/update                                                             1
Wait Argument 2                                                           1
extent                                                                    1
nothing                                                                   1
table/partition                                                           1
clatch                                                                    1
instanceid                                                                1
flag                                                                      1
only dml                                                                  1
relative file #                                                           1
hash value                                                                1
enqueue                                                                   1
serial #                                                                  1
slave ID                                                                  1
sequence # / apply #                                                      1
blockNo                                                                   1
instance|serial                                                           1
dtp                                                                       1
number of blocks                                                          1
mem_id                                                                    1
kv #                                                                      1
file number                                                               1
vol                                                                       1
wait time(millisec)                                                       1
pos                                                                       1
P3                                                                        1
id1|id2                                                                   1
startdba                                                                  1
Op2                                                                       1
Serial#                                                                   1
none                                                                      1
entry                                                                     1
bqual hash value                                                          1
diskno                                                                    1
high file obj add                                                         1
broadcast message                                                         1
scnbas                                                                    1
xidsqn                                                                    1
undo segment # / other                                                    1
waited                                                                    1
request                                                                   1
new aio limit                                                             1
key hash                                                                  1
buf_ptr                                                                   1
hash(logname)                                                             1
handle                                                                    1
generation                                                                1

136 rows selected.