AnySQL.net English

    When creating or rebuilding indexes on hot tables, we will use parallel option to make the it quickly, but please remember to disable the parallel after creating or rebuilding, else it will make the relative SQL run in parallel. On busy OLTP system this will make the oracle instance crash (we hit this before). But you may hit resource busy error when you are trying to disable the parallel option of the index. How to make sure the modification succeed? Please use the following PL/SQL scripts:

alter index ...... rebuild ... parallel ... ONLINE;

declare
  resource_busy exception;
  pragma exception_init (resource_busy,-54);
begin
loop
   begin
     execute immediate 'alter index ...... noparallel';
     exit;
   exception
    when resource_busy then
     dbms_lock.sleep(1);
   end;
end loop;
end;
/

    These scripts can also be used in other cases, for example adding column on hot tables. Or you can change the error code to process another kind of error, it's all up to you!

    Few days ago, we got a task to create an new index on (A,B,C) for table T, but the table T already have a primary key constraint on (A,B) and a unique index on (A,B). This was to get a current SQL run more quickly with lower consistent gets by accessing index only. How will you do it?

    We can get the primary constraint using the new index need to be created without change the definition of the primary key, so we could drop the unique index created on (A,B) columns to save disk storage. As following:

SQL> CREATE TABLE T_OBJECTS AS
  2  SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 1000;

Table created.

SQL> CREATE UNIQUE INDEX T_OBJECTS_IDX
  2  ON T_OBJECTS (OBJECT_ID, OBJECT_NAME);

Index created.

SQL> ALTER TABLE T_OBJECTS ADD CONSTRAINT PK_T_OBJECTS
  2  PRIMARY KEY (OBJECT_ID) USING INDEX T_OBJECTS_IDX;

Table altered.

SQL> SELECT INDEX_NAME FROM USER_INDEXES
  2  WHERE TABLE_NAME='T_OBJECTS';

INDEX_NAME
------------------------------
T_OBJECTS_IDX

    However what should be considered in production? You must make decision whether the storage is really important to you. Because drop an index may change the running SQL's plan. And the new index will be bigger than the old one because we add one extra column to it, sometime the cost will be changed for using bigger index. And if the primary constraint is disabled or dropped, duplicated rows can be inputed by application.

    There is also another thing we need take it into consideration, let's create a trigger on this table :

    From 8i to 9i and then 10g, Oracle is make more and more parameters to be dynamic at system level to reduce the downtime of your database servers. Following are some new parameters compared to 9i, I omitted some parameters that I am not familiar with.

    Oracle memory:

sga_target
java_pool_size

    SQL Optimizer:

cpu_count
optimizer_index_caching
optimizer_index_cost_adj
optimizer_mode
optimizer_secure_view_merging
star_transformation_enabled

    Parallel Execution:

parallel_max_servers
parallel_min_servers
parallel_threads_per_cpu

    Others:

resumable_timeout
create_stored_outlines
skip_unusable_indexes
sql_trace

    I don't think making the parameter SQL_TRACE modifiable at system level is a good thing, I remember that one DBA have set it on at system level on production database by mistaken, others are good improvements.

    We found an relink error when installing Oracle 9.2.0.7 on Solaris platform, the second time we found relink error for libsrvm module. However it did not report any error when just "relink oracle" or "relink client", just appear when you type "relink all" command. Detailed error messages listed here:

ld: fatal: file ${ORACLE_HOME}/lib/libskgxn9.so: wrong ELF class: ELFCLASS64
ld: fatal: File processing errors. No output written to libsrvm.so
*** Error code 1
make: Fatal error: Command failed for target `libsrvm.so'

    I used "file" command to check the properties of file "${ORACLE_HOME}/lib/libskgxn9.so", found that it's an 64 bit library, while libsrvm.so is a 32 bit dynamic library, that's why the error is reported. So I make a small change to "env_rdbms.mk" under $ORACLE_HOME/srvm/lib directory as following:

# EXSYSLIBS = -lposix4 -lm -lskgxn9 #original line
EXSYSLIBS = -lposix4 -lm

    Now it works well when I issue "make -f ins_rdbms.mk isrvm" command to compile the single module, and also works well when typing "relink all".

    On Oracle 10g, I got the following wait of two sessions, session 32 blocked session 29. It's interesting and dangerous operation in OLTP system.

SQL> SELECT SID, EVENT FROM V$SESSION
  2  WHERE USERNAME='ANYSQL';

       SID EVENT
---------- -------------------------------------
        29 library cache lock
        32 SQL*Net message from client

    What I executed in session 32 is :

SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_IOT');

PL/SQL procedure successfully completed.

    And what it's executing in session 29 is a delete statement, which was blocked by library cache lock :

SQL> delete t_iot;

    Seems some of Oracle new features are dangerous, however if I submit a commit statement in session 32, then session 29 will continue to execute, the library cache lock will go away. Why not Oracle issue a default commit in this procedure?