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.