DBA Tools

Save 15 mins downtime by tuning the sequence.

    I were doing some Oracle tuning jobs in last two weeks, and got a SQL like following : INSERT /*+ APPEND */ INTO B   SELECT /*+ PARALLEL(A,4) FULL(A) */         SEQ_B.NEXTVAL, A.* FROM A;...

Posted on July 16, 2008 9:39 AM

Tuning Oracle SQL performance by rewriting it.

    I got a task to run the following two SQLs in 20 site databases, the table is very big, about 100GB size in every database. So it not a easy task. SELECT /*+ FULL(T) PARALLEL(T,4) */  COUNT(*) FROM BIGTABLE...

Posted on October 11, 2007 10:16 AM

Performance issue by OR operation because filter at table level.

    I create a composite index (DEPTNO and ENAME columns) on table SCOTT.EMP. Then I run the following SQL. SQL> SELECT /*+ first_rows no_expand */ * FROM EMP    2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME >...

Posted on October 11, 2007 10:00 AM

Analyze or DBMS_STATS, choose one but don't mix them

    I am not so familiar with the DBMS_STATS procedure, so I always use the "ANALYZE" command. One day I got the following problem. Let's start a demo, first step is creating demo partitioned table. SQL> CREATE TABLE T_PARTDEMO(...

Posted on September 29, 2007 12:37 PM

MyLOG, an offline Oracle log miner to extract the redo SQLs

    I spent some time in researching the Oracle log file format. Previous research was done about two years ago for Oracle 9i. Fortunately I did get something for these days research. And wrote a tool called "MyLOG" (8i/9i,10g/11g)...

Posted on September 21, 2007 2:11 PM

Using UltraEdit macro to format the HTML when bloging.

    When posting scripts or query result to blog (Many DBAs have personal blogs), I always use an UltraEdit macro to format it, it will give us good text align. The macro defination is very simple, first step is...

Posted on September 8, 2007 2:05 PM