AnySQL.net English

    ociuldr is more and more widely used by DBAs. Someone sent me a email few days ago said that he had successfully implemented this tool in one of his project. Even it's just a very small utility, it take me some time to add lot's of option to make it more useful. Today I will introduce the LOG option to you.

log = log file name, prefix with + to append mode

    For example :

ociuldr user=ansyql/anysql@s8i query="select * from tab" log=tab.log
ociuldr user=ansyql/anysql@s8i query="select * from tab" log=+tab.log

    If the LOG option is provided, ociuldr will not print any message to the screen (standard out put device), they will be printed to the log file specified by the LOG option. If the LOG option value is prefixed with a plus char, ociuldr will append the message to the log file, else create a new one. This option is very useful when you schedule ociuldr in a crontab job, keep all the message output for trouble shooting.

    Welcome to new version. You can download source code to compile your binary, I only provide the binarys of Windows(x86), RedHat Linux (x86) and Solairs Sparc 64.

    About half years ago, someone told me to add this. I was afraid of change any code at that time, so just left him along. Today I made some change to the source code, the return or exit code is implemented as following :

0 = Successful
1 = Cannot login to database
2 = Cannot create cursor handle
3 = Cannot prepare SQL statement
4 = Cannot execute SQL query
5 = Cannot get the metadata of the result set
6 = Cannot create output file
7 = Oracle error found when fetching rows, such as ORA-01555 etc.

    Let's begin verification. When we cannot connect to database :

bash-2.03$ ./ociuldr.bin user=anysql/anysq1@test  query="select * from tab"
Cannot connect as anysql/anysq1@s8i.
Connection failed.  Exiting...
bash-2.03$ echo $?
1

    When wrong SQL is provided :

bash-2.03$ ./ociuldr.bin user=anysql/anysql@test  query="select * from tab1"
ORA-00942: table or view does not exist
bash-2.03$ echo $?
3

    When no permission of creating text file.

bash-2.03$ ./ociuldr.bin user=anysql/anysql@test  query="select * from tab" file=/test.txt
   15500 bytes allocated for column TNAME (1)
    4000 bytes allocated for column TABTYPE (2)
   20500 bytes allocated for column CLUSTERID (3)

ERROR -- Cannot write to file : /test.txt
bash-2.03$ echo $?
6

    Hope he can read this article, sorry for the later.

    Half month ago, someone (MSN: omerozasik@hotmail.com) from a Turkey company (elektromed) sent me an email about data recovery, they got there system tablespace data file deleted of a 70GB size database. After reached a simple agreement on MSN, I created an account on my site to ask official AUL license. The engineer who performed recovery with AUL is his friend (MSN: fuatture@hotmail.com) .

    The following is the account log of AUL license requests :

DAY=20070504 IP=85.98.13.7 CODE=YBQG-JQIV-NXAQ-YB5T-4C4W
DAY=20070504 IP=85.98.13.7 CODE=B4LJ-OL06-5AQ5-0NX6-2BXE
DAY=20070504 IP=85.98.13.7 CODE=360Y-WE5X-JV2H-H5WY-R0RV
DAY=20070506 IP=195.174.160.2 CODE=A9JA-UYT1-2QHY-T7XR-NCHO
DAY=20070507 IP=213.139.224.14 CODE=UBWU-2M6Q-4TWF-MJNA-1XY8

    However, I cannot get in touch with them anymore, no response from email, no response from offline MSN message, and they never get online any more. I am quite sure that they have successfully recover their data. But I was ignored after providing AUL licenses.

    I think people are basically good and trustful, but it seems not true always. So I create a rule for the data recovery service, "License after payment without exception"!

    I have a table named "T_OBJECTS" and one index on it named "T_OBJECTS_IX1". They are not analyzed now:

TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- ---------- ---------------
T_OBJECTS
T_OBJECTS_IX1

    Now I receive a task to create a second index on this table, I add the compute statistics option :

SQL> CREATE INDEX T_OBJECTS_IX2 ON T_OBJECTS
  2  ( OBJECT_NAME ) compute statistics;

Index created.

    Let's query the table's statistics data again :

TABLE_NAME       NUM_ROWS LAST_ANA
-------------- ---------- --------
T_OBJECTS             555 00:30:54
T_OBJECTS_IX1
T_OBJECTS_IX2         555 00:30:54

    Because of the table is analyzed, but another index is not analyzed, so the optimizer choose the wrong execution plan, and get the server in performance trouble. I later test it on 10g version, the table will not be analyzed. So when you create index with compute statistics option in 9i, you should be really carefully, because table will be analyzed too.

    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.