AnySQL.net English

    In the last two years, I spent a lot of time to rebuild standby (for backup or data moving) for very busy databases, the archive log generated very fast, and the archive data volume have limited free space, which caused some trouble when rebuilding standby. When we put the database into begin backup mode, then archive generation speed was almost doubled, then we had to use RMAN's data file copy feature to perform data file backup without putting the whole tablespace or database into begin backup mode. But we had to copy the datafile to a local directory first with RMAN, we cannot directly copy the data to remote directory in another host. Why not NFS, in a complex environment (multiple data centers), the NFS configure was a relative complex job. In out company, DBA just do DBA's work.

    Rsync is a perfect file copy utility between different hosts under Linux or UNIX, it could compress the contents when copying files to fit for the lower bandwidth between different data centers. If it could be combined with RMAN's data copy feature, it will save us a lot of time on standby rebuilding. Some junior DBAs always copy the data file directly with OS utility without using RMAN's data file copy and without putting the tablespace or database into begin backup mode.

    Then I considered to build a program called osync, but it was too difficult for me and finally dumped this idea. If so we could directly backup data file to remote host by issuing "osync file_id host:path". And I posted this idea on Google oracle BBS, somebody said that it's a useless idea. Then I filed a tar (Cannot remind the tar number back), Oracle's developer said they were considering to implement this feature in RMAN 11g version, which will allow you copy data file to remote host in the RMAN's data file copy command.

    Oracle have announced the 11g beta test program for quite a few days, is there anyone get the beta version? I'd like to verify whethere this feature has been implemented in Oracle 11g. Thanks!

    Sometimes Oracle choose wrong index even with "INDEX" hint due to index maintenance job (rename, creating or dropping), you need to change the hint in the SQLs to make it work. And for developers, they may not know which index is better, but they should know which column is best for selectivity. So is there any way to tell Oracle optimizer to choose indexes on specified columns or leading columns. I was considering an INDEX_COL hint by specifying "index_col (table name, col1, ...)" to get this case work.

    I posted this idea to Google groups, and somebody reminded me that there is already an undocumented hint (found out by Jonathan Lewis) in Oracle 10g. It's really useful for both DBAs and developers, I like it very much.

    The following undocumented hint will let Oracle 10g choose an index leading with column "col1" and "col2", but it will not choose index start with column "col2", not sure whether it is changed in Oracle 11g version:

/*+ index(table_alias tablename(col1, col2) ) */

    Oracle have announced the 11g beta test program for quite a few days, is there anyone get the beta version? I'd like to know whether it become an documented hint now, we cannot use undocumented hint in our application now, but if it becomes an documented hint, we will absolutely use it.

    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.