achive better JDBC and database performance by fooling oracle

From: Leon <lrzhemov_at_shaw.ca>
Date: 10 May 2002 12:10:20 -0700
Message-ID: <dac2c664.0205101110.16d431fa@posting.google.com>

Hi

I have found from tracing user sessions that on my system: Oracle 8.1.6.0 on linux
Client on linux thin jdbc 1.1

jdbc is calling

SELECT ATTR_NO, ATTR_NAME, ATTR_TYPE_NAME, ATTR_TYPE_OWNER FROM ALL_TYPE_ATTRS
WHERE OWNER = :1 AND TYPE_NAME = :2 ORDER BY ATTR_NO Due to poor implementation JDBC-ORACLE DB calls

99% of number of buffers gotten for consistent read, 
95% number of buffers gotten in current mode (usually for update)
75% elapsed time in seconds executing

spend for nothing.

SELECT ATTR_NO, ATTR_NAME, ATTR_TYPE_NAME, ATTR_TYPE_OWNER FROM ALL_TYPE_ATTRS
WHERE OWNER = :1 AND TYPE_NAME = :2 ORDER BY ATTR_NO

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse     7052      2.17       2.07          0          0          0  
        0
Execute   7052      2.15       2.25          0          0          0  
        0
Fetch     7052    530.78     613.52          0   25450669      28208  
        0

------- ------ -------- ---------- ---------- ---------- ----------

---

total    21156    535.10     617.84          0   25450669      28208  
        0

out of total

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse    34007     11.02      12.49          0          0          0  
        0
Execute  34007    127.48     170.21          0       3365        938  
    18952
Fetch    15065    536.71     623.83          0   25595402      28544  
     8922

------- ------ -------- ---------- ---------- ---------- ----------

---

total 83079 675.21 806.53 0 25598767 29482

    27874

We do not use object types in oracle database therefore I fool oracle by building empty table in user schema

After building fake ALL_TYPE_ATTRS table in web user schema

1)	performance up
2)	processor usage down
3)	latch contention down 

Even weblogic is not opening as many connections because of everything executes several times faster

I have had tremendous benefits from disabling this JDBC call. I wonder is it only relevant to my environment or thousand others installations could be benefited from this.

Basically you may end up buying twice less expensive hardware and use less processors ie licenses to achieve the same.

If somebody is running similar configuration and provide with tracing information related to ALL_TYPE_ATTRS call vs total I would appreciate this.

Thanks
Leon Received on Fri May 10 2002 - 14:10:20 CDT