There are special indexes on Oracle kernel tables (Usually the INDX column), if your query can use it, it can run more quickly, else it can be very slow. The simplest way is to equal access. Let's choose the base table of V$SYSTEM_EVENT : X$KSLEI for example.
SQL> SELECT INDX, KSLESWTS,KSLESTIM FROM X$KSLEI WHERE INDX=1;
INDX KSLESWTS KSLESTIM
---------- ---------- ----------
1 0 0
Elapsed: 00:00:00.01
Replace it with IN? It becomes slowly because index is not used.
SQL> SELECT INDX, KSLESWTS,KSLESTIM FROM X$KSLEI WHERE INDX IN (100,200);
INDX KSLESWTS KSLESTIM
---------- ---------- ----------
100 0 0
200 6 57927
Elapsed: 00:00:02.11
I cannot believe it! Let's add a hint into the SQL.
SQL> SELECT /*+ USE_CONCAT */ INDX, KSLESWTS,KSLESTIM
2 FROM X$KSLEI WHERE INDX IN (100,200);
INDX KSLESWTS KSLESTIM
---------- ---------- ----------
200 6 57927
100 0 0
Elapsed: 00:00:00.03
In one of my program I use -1 to represent a non-existent wait event, the result is very bad performance!
SQL> SELECT /*+ USE_CONCAT */ INDX, KSLESWTS,KSLESTIM
2 FROM X$KSLEI WHERE INDX IN (100,200,-1);
INDX KSLESWTS KSLESTIM
---------- ---------- ----------
200 6 57927
100 0 0
Elapsed: 00:00:02.12
One of my friend suggest to replace -1 with a big positive number.
SQL> SELECT /*+ USE_CONCAT */ INDX, KSLESWTS,KSLESTIM
2 FROM X$KSLEI WHERE INDX IN (100,200,100000);
INDX KSLESWTS KSLESTIM
---------- ---------- ----------
200 6 57927
100 0 0
Elapsed: 00:00:00.02
The kerenl table indexes are not the same as normal indexes, it's just a algorith based on hash method.