AnySQL.net English

    This is the secondary test of single table hash cluster, I want to verify the efficiency of the way I used to define the SIZE and HASHKEYS option. And I also want to verify the efficiency of hash function provided by Oracle for whether it can provide evenly distributed key. I choose 10M rows from a big table, the unloaded text file size is about 1.2G. In this case, I will set the HASHKEYS * SIZE to about 2GB. The SQL used to create the cluster is :

CREATE CLUSTER C_USER_HOST_ID_LOOKUP (USER_ID NUMBER(38,0))
    SIZE 8192 SINGLE TABLE HASHKEYS 262144;

    Then I use sqlldr to load these 10M rows, because you cannot use direct load mode for cluster table, so it goes a little slowly, it take me about 2 hours to finish the data loading. Then run the following PL/SQL code to verify the performance of the HASH access method.

DECLARE
   TEMP VARCHAR2(64);
BEGIN
   FOR REC IN (SELECT USER_ID FROM CR_FLOUTEST) LOOP
      SELECT /* FLOU_TEST */ userid INTO TEMP FROM CR_FLOUTEST
             WHERE USER_ID = REC.USER_ID;
   END LOOP;
END;
/

    After the PL/SQL finished, find out the hash value of the select statement, and query some data from V$SQL performance view. It's seems the SIZE and HASHKEYS is setting correctly, and also Oracle provide a very effective built-in hash function for us.

SQL> SELECT HASH_VALUE, BUFFER_GETS, EXECUTIONS
  2  FROM V$SQL WHERE HASH_VALUE=2716314996;

HASH_VALUE BUFFER_GETS EXECUTIONS
---------- ----------- ----------
2716314996     9998912    9998777

    Due to the low speed of the data load, I cannot make the decision to convert a table with 200M rows to single hash cluster table. Hope Oracle can add hash based index in later version.

    We have a very old database which upgraded from Oracle 7, when I was creating an index on this database, the database run out of memory. Before I started to create the index, I did check the free memory of the host, it's 8GB free memory there.

    I run the following statement to tuning the index creation.

alter session set sort_area_size=100000000;
alter session set sort_area_retained_size=100000000;
alter session set db_file_multiblock_read_count=128;

    We found that each parallel process is allocated about 5G memory as PGA, this is abnormal. Did anyone else hit this problem before? We filed a tar, but Oracle did not give us a solution, just told us that do not specify 128 as the multiple block read count.

    After we change the value to 64, it works fine.

    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.