Everyone knows "Joel on Software", doesn't it?

Well, this is a post is out of the things directly related to Oracle technologies, but a thing about of software development itself. Joel Spolsky, if it says shortly, is author of Joel on Software, if it says a bit wider then Joel has written more then 1000 articles and a number of books about software development, management, business, and the Internet. I recommend to read his top articles like "Things You Should Never Do" or "Painless Functional Specifications" and many more articles, answering the daily questions we ask while developing or managing the development.

DWH Core Perfomance Fundamentals

I've added blog Structured Data: Oracle Database Performance And Scalability Blog in my blog list. There are a lot of cool stuff, dont be lazy to check it out. Here is a series of articles regarding DWH core perfomance fundamentals:

The Core Performance Fundamentals Of Oracle Data Warehousing – Introduction

ANSI join syntax vs. non-ANSI join syntax

Recently I've participated in discussion about the SQL ANSI join syntax vs. non-ANSI. In my opinion, non-ANSI join syntax is much more simpler to read and 99% of the statements could be effectivlly written using Oracle syntax. The only case when i use ANSI syntax is FULL OUTER JOIN, because it's simple to use, to understand and to write. One of the additional arguments of using ANSI syntax was the following statement:

 

SQL> with A as
  2   (select 1 COL1, 'A' COL2
  3      from DUAL
  4    union all
  5    select 2, 'B'
  6      from DUAL
  7    union all
  8    select 3, 'C' from DUAL),
  9  B as
 10   (select 1 COL1, 'D' COL2
 11      from DUAL
 12    union all
 13    select 2, 'E' from DUAL)
 14  select A.COL2, B.COL2 from A left outer join
 15                             B on (A.COL1 = B.COL1 and B.COL2 in ('D', 'E'))
 16  /
 
COL2 COL2
---- ----
A   D
B   E
C   
 
SQL>

as you can see LEFT OUTER JOIN is beign using with IN clause which is not allowed in non-ANSI syntax:

 

SQL> with
  2  a as (select 1 col1, 'A' col2 from dual
  3         union all
  4         select 2, 'B' from dual
  5         union all
  6         select 3, 'C' from dual),
  7    b as (select 1 col1, 'D' col2 from dual
  8       union all
  9       select 2, 'E'  from dual)
 10  select a.col2, b.col2
 11  from a, b
 12  where a.col1 = b.col1 (+)
 13        and b.col2 in ('D','E')
 14  /
 
COL2 COL2
---- ----
A   D
B   E
 
SQL> 
SQL> with
  2  a as (select 1 col1, 'A' col2 from dual
  3         union all
  4         select 2, 'B' from dual
  5         union all
  6         select 3, 'C' from dual),
  7    b as (select 1 col1, 'D' col2 from dual
  8       union all
  9       select 2, 'E'  from dual)
 10  select a.col2, b.col2
 11  from a, b
 12  where a.col1 = b.col1 (+)
 13        and b.col2(+) in ('D','E')
 14  /
 
with
a as (select 1 col1, 'A' col2 from dual
       union all
       select 2, 'B' from dual
       union all
       select 3, 'C' from dual),
  b as (select 1 col1, 'D' col2 from dual
     union all
     select 2, 'E'  from dual)
select a.col2, b.col2
from a, b
where a.col1 = b.col1 (+)
      and b.col2(+) in ('D','E')
 
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
 
SQL>  

Yes, it's not allowed, but could we re-write the query with non-ANSI syntax without much overhead? Yes, we are:

 

SQL> with
  2  a as (select 1 col1, 'A' col2 from dual
  3         union all
  4         select 2, 'B' from dual
  5         union all
  6         select 3, 'C' from dual),
  7    b as (select 1 col1, 'D' col2 from dual
  8       union all
  9       select 2, 'E'  from dual)
 10  select a.col2, b.col2
 11  from a, b
 12  where a.col1 = b.col1 (+)
 13        and (b.col2 in ('D','E') or b.col2 is null)
 14  /
 
COL2 COL2
---- ----
A   D
B   E
C   
 
SQL> 

let's check the execution plans for ANSI syntax and non-ANSI:

 

SQL> select * from v$version
  2  /
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

ANSI:

 


---------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |
|   1 |  NESTED LOOPS OUTER|      |      1 |      6 |      3 |00:00:00.01 |
|   2 |   VIEW             |      |      1 |      3 |      3 |00:00:00.01 |
|   3 |    UNION-ALL       |      |      1 |        |      3 |00:00:00.01 |
|   4 |     FAST DUAL      |      |      1 |      1 |      1 |00:00:00.01 |
|   5 |     FAST DUAL      |      |      1 |      1 |      1 |00:00:00.01 |
|   6 |     FAST DUAL      |      |      1 |      1 |      1 |00:00:00.01 |
|   7 |   VIEW             |      |      3 |      2 |      2 |00:00:00.01 |
|*  8 |    VIEW            |      |      3 |      2 |      2 |00:00:00.01 |
|   9 |     UNION-ALL      |      |      3 |        |      6 |00:00:00.01 |
|  10 |      FAST DUAL     |      |      3 |      1 |      3 |00:00:00.01 |
|  11 |      FAST DUAL     |      |      3 |      1 |      3 |00:00:00.01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter("A"."COL1"="B"."COL1")

non-ANSI syntax:

 

----------------------------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      3 |00:00:00.01 |       |       |          |
|*  1 |  FILTER          |      |      1 |        |      3 |00:00:00.01 |       |       |          |
|*  2 |   HASH JOIN OUTER|      |      1 |      1 |      3 |00:00:00.01 |  1348K|  1348K|  907K (0)|
|   3 |    VIEW          |      |      1 |      3 |      3 |00:00:00.01 |       |       |          |
|   4 |     UNION-ALL    |      |      1 |        |      3 |00:00:00.01 |       |       |          |
|   5 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|   6 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|   7 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|   8 |    VIEW          |      |      1 |      2 |      2 |00:00:00.01 |       |       |          |
|   9 |     UNION-ALL    |      |      1 |        |      2 |00:00:00.01 |       |       |          |
|  10 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|  11 |      FAST DUAL   |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((INTERNAL_FUNCTION("B"."COL2") OR "B"."COL2" IS NULL))
   2 - access("A"."COL1"="B"."COL1")

What we see is that both execution plans has 11 operations, but the main different is in JOIN operations. In case ANSI we have NESTED LOOPS OUTER which loops 3 times through B for each record in A. In case non-ANSI syntax we have HASH JOIN OUTER join which executed only once but with additional overhead with filter predicate.

As result i could state that non-ANSI example looks much better especially in case with much data in source, let's check:

ANSI:

 

with
a as (select level col1, dbms_random.string(1, 3) col2 from dual connect by level <= 1000),
  b as (select level col1, dbms_random.string(1, 3) col2 from dual connect by level <= 100)
select A.COL2, B.COL2 from A left outer join 
                                             B on (A.COL1 = B.COL1 and B.COL2 in ('D', 'E'))
/   

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |      1 |00:00:01.47 |
|   1 |  SORT AGGREGATE                  |      |      1 |      1 |      1 |00:00:01.47 |
|   2 |   NESTED LOOPS OUTER             |      |      1 |      1 |   1000 |00:00:01.46 |
|   3 |    VIEW                          |      |      1 |      1 |   1000 |00:00:00.01 |
|   4 |     CONNECT BY WITHOUT FILTERING |      |      1 |        |   1000 |00:00:00.01 |
|   5 |      FAST DUAL                   |      |      1 |      1 |      1 |00:00:00.01 |
|   6 |    VIEW                          |      |   1000 |      1 |      0 |00:00:01.46 |
|*  7 |     VIEW                         |      |   1000 |      1 |      0 |00:00:01.46 |
|   8 |      CONNECT BY WITHOUT FILTERING|      |   1000 |        |    100K|00:00:00.11 |
|   9 |       FAST DUAL                  |      |   1000 |      1 |   1000 |00:00:00.01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter(("A"."COL1"="B"."COL1" AND INTERNAL_FUNCTION("B"."COL2")))

non-ANSI:

 

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |        |      1 |00:00:00.01 |       |       |          |
|   1 |  SORT AGGREGATE                  |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|*  2 |   FILTER                         |      |      1 |        |    900 |00:00:00.01 |       |       |          |
|*  3 |    HASH JOIN OUTER               |      |      1 |      1 |   1000 |00:00:00.01 |  1452K|  1452K| 1264K (0)|
|   4 |     VIEW                         |      |      1 |      1 |   1000 |00:00:00.01 |       |       |          |
|   5 |      CONNECT BY WITHOUT FILTERING|      |      1 |        |   1000 |00:00:00.01 |       |       |          |
|   6 |       FAST DUAL                  |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
|   7 |     VIEW                         |      |      1 |      1 |    100 |00:00:00.01 |       |       |          |
|   8 |      CONNECT BY WITHOUT FILTERING|      |      1 |        |    100 |00:00:00.01 |       |       |          |
|   9 |       FAST DUAL                  |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((INTERNAL_FUNCTION("B"."COL2") OR "B"."COL2" IS NULL))
   3 - access("A"."COL1"="B"."COL1")

As result we have elapsed time for NESTED LOOPS OUTER operation equal to 00:00:01.46 in ANSI case and 00:00:00.01 for HASH JOIN OUTER in non-ANSI case. It's important to note that in ANSI case we have 100000(100K) records in step ID = 8 which lead to use more resourses and consequently more time for execution.

Any way the example doesn't show that ANSI join syntax is something to be avoid, but shows that ANSI / non-ANSI join syntax may be treated differently by SQL engine.

"the pointless" triggers can cause "the problems"

I've just surfed via Christian Antognini blog and found the fact of perfomance issue(CPU overhead) with triggers on Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 (64-bit) Solaris 10 (SPARC) due to Oracle bug. In the example, for reason of integrity constraint each table has its own trigger even it doesn't support any integrity (check example by the link above). "Firing blank rounds" cause increasing of CPU usage.

Out of bugs i would add a comment:
In addition, DML operations inside the triggers could lead to large number of row-level exclusive locks for large number of tables (trigger's shower). More over it's usually very hard to predict how many triggers will be fired due to some system operations when triggers realize some bussiness rules and their execution depends on the exact case.

Installing Oracle Weblogic Server cluster (2 nodes) on Linux RHEL 5

Here is brief description how to install Oracle WebLogic Servers as cluster under Linux RHEL 5 assuming that there are 2 nodes in.

NODE1:

While installing WebLogic:

1) Create domain (say WLDM)
2) In optional configuration check as the following:

 Select Optional Configuration:
------------------------------

   1|Administration Server [x]
   2|Managed Servers, Clusters and Machines [x]
   3|RDBMS Security Store [ ]

3) Do necessary settings for Admin Console
4) DO necessary settings for each Managed server. Please note that you need to set up settings for the second node Managed server as well before it will be installed.

 

Configure Managed Servers:
--------------------------

Add or delete configuration information for Managed Servers. A typical production environment has one or more Managed Servers. Each Managed Server is an
instance of WebLogic Server used to host enterprise applications.

    |  Name*   | Listen address* | Listen port | SSL listen port | SSL enabled |
   _|__________|_________________|_____________|_________________|_____________|
   1| MS1     |  10.10.10.133   |    7003     |      7503       |    true     |
 2->| MS2     |  10.10.10.134   |    7003     |      7503       |    true     |
 
 

5) Configure Cluster.

 
Configure Clusters:
-------------------

Add or delete configuration information for clusters. A cluster consists of multiple server instances working together to provide increased scalability
and reliability.

    |   Name*   | Cluster messaging mode | Multicast address | Multicast port | Cluster address |
   _|___________|________________________|___________________|________________|_________________|
 1->| WLCL   |        unicast         |        N/A        |      N/A       |                 |
 
 

6) Assign Managed Servers to Clusters:

 

Assign Servers to Clusters:
---------------------------

Assign Managed Servers to a cluster in the domain.

    Cluster
    |_____WLCL [1]
         |_____MS1
         |_____MS2
   

7) Configure machines and assign machines to Managed servers. Please note there are two types of machines differed by non-Unix/Unix. You need to fill the only type for each of the node otherwise you will get duplication by machines.

8) Finish installing process by exact installing Domain right after all the setting commited.

 **** Domain Created Successfully! ****

NODE2:

Start installing Weblogic as you did in node1

1) Create domain the same as we pointed in node1
2) In optional configureation check as follows:

 

Select Optional Configuration:
------------------------------

   1|Administration Server [ ]
   2|Managed Servers, Clusters and Machines [x]
   3|RDBMS Security Store [ ]

3) Do necessary setting for exact MS2 Managed server that we did in node1:

 

Configure Managed Servers:
--------------------------

Add or delete configuration information for Managed Servers. A typical production environment has one or more Managed Servers. Each Managed Server is an
instance of WebLogic Server used to host enterprise applications.

    |  Name*   |   Listen address*   | Listen port | SSL listen port | SSL enabled |
   _|__________|_____________________|_____________|_________________|_____________|
 1->| MS2     | 10.10.10.134      |    7003     |      7503       |    true     |
 
 

4) Skip cluster and machine configureation settings
5) Finish installing process by exact installing Domain right after all the setting commited.

 **** Domain Created Successfully! ****

Now you can start node1 Admin Console and Managed server with Managed server at node2. Please note that Admin Console required to be start first once it controls the cluster.