AnySQL.net English

    Usually I get it from the first row of V$MYSTAT, this is a good way.

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;

       SID
----------
     19949

    In Oracle 10g or above, we can use a more simple way with the USERENV function

SQL> SELECT USERENV('SID') FROM DUAL;

USERENV('SID')
--------------
         19949

    Sometime I also use the following method, but in Oracle 10g, it does work but return 2 rows for you, so actually not work for you in Oracle 10g or above. We should not use it any more.

SQL> SELECT SID FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');

       SID
----------
     19949
     19829

    Are there any other ways?.

    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.

    I have introduced a fast way to access X$KSLEI kernel table. But after lots of test on different versions of Oracle database, I found that sometime this hint does not work. I hit this problem on 10g version. For example:

SQL> SELECT /*+ USE_CONCAT */ * FROM X$KSLEI WHERE INDX IN (1,2);

----------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT |         |    20 |  1680 |
|*  1 |  FIXED TABLE FULL| X$KSLEI |    20 |  1680 |
----------------------------------------------------

    Finally I have to get it done by complex way, rewrite the SQL with UNION ALL, then Oracle have no way except the right way.

SQL> SELECT * FROM X$KSLEI WHERE INDX = 1 UNION ALL
  2  SELECT * FROM X$KSLEI WHERE INDX = 2
  3  /

----------------------------------------------------
| Id  | Operation                | Name            |
----------------------------------------------------
|   0 | SELECT STATEMENT         |                 |
|   1 |  UNION-ALL               |                 |
|*  2 |   FIXED TABLE FIXED INDEX| X$KSLEI (ind:2) |
|*  3 |   FIXED TABLE FIXED INDEX| X$KSLEI (ind:2) |
----------------------------------------------------

    I do think of direct SGA access, but with well tuned SQLs, we still can get the job done well.