Slow DB

From: Alex Vilner <avilner_at_usa.net>
Date: 1998/02/26
Message-ID: <01bd42bd$f3d95280$2715a026@desk4.flexxperts.com>#1/1

Dear netters,

We have an Oracle 7.3.1 database, running on the IBM RS/6000 server, UNIX 4.1.
When I use any tool to read in table information from the database (i.e. columns
information OR when I just try to get back the list of tables) the response is
REALLY slow. The client is connecting to the server on TCP/IP protocol. Here's the extract from the trace file that was generated:

1. Connect and retrieve table list DIALOG CONNECT TO TRACE O73: LOGID=live SERVER_at_TNS:yogi DBPARM=DelimitIdentifier='NO'; (3255 MilliSeconds) SELECT * FROM SYS.SESSION_ROLES WHERE ROLE = 'DBA' (0 MilliSeconds) TABLE LIST: SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE OWNER <> 'SYS' AND TABLE_TYPE IN ('TABLE','VIEW') (0 MilliSeconds) SELECT S.OWNER, S.SYNONYM_NAME FROM SYS.ALL_SYNONYMS S, SYS.ALL_OBJECTS O WHERE (S.TABLE_OWNER <> 'SYS' AND O.OBJECT_NAME = S.TABLE_NAME AND O.OWNER = S.TABLE_OWNER AND O.OBJECT_TYPE IN ('VIEW','TABLE')) UNION SELECT Q.OWNER, Q.SYNONYM_NAME FROM SYS.ALL_SYNONYMS Q WHERE (Q.TABLE_OWNER
   <> 'SYS' AND Q.DB_LINK IS NOT NULL)
   (42948 MilliSeconds)

   ---

2. Get specific table information (in this case, ACH_ERRORS) COLUMNS INFORMATION: TABLE=ach_errors OWNER=live SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DATA_DEFAULT FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'ACH_ERRORS' AND OWNER = 'LIVE' ORDER BY COLUMN_ID (29679 MilliSeconds)

   ---

SELECT INDEX_NAME, UNIQUENESS FROM SYS.ALL_INDEXES WHERE TABLE_OWNER = 'LIVE' AND TABLE_NAME = 'ACH_ERRORS' (0 MilliSeconds)  PRIMARY KEY RETRIEVE:
SELECT SYS.ALL_CONS_COLUMNS.COLUMN_NAME,

SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME FROM  
SYS.ALL_CONSTRAINTS,SYS.ALL_CONS_COLUMNS WHERE 
SYS.ALL_CONSTRAINTS.CONSTRAINT_TYPE ='P' AND   
SYS.ALL_CONSTRAINTS.TABLE_NAME = 'ACH_ERRORS' AND   
SYS.ALL_CONSTRAINTS.OWNER = 'LIVE' AND   
SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME = SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME

AND SYS.ALL_CONSTRAINTS.TABLE_NAME = SYS.ALL_CONS_COLUMNS.TABLE_NAME AND    SYS.ALL_CONSTRAINTS.OWNER = SYS.ALL_CONS_COLUMNS.OWNER ORDER BY SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME,SYS.ALL_CONS_COLUMNS.POSITION (2307 MilliSeconds)

 FOREIGN KEY RETRIEVE:
SELECT
F.CONSTRAINT_NAME,F.OWNER,F.R_OWNER,P.TABLE_NAME,SYS.ALL_CONS_COLUMNS.COLUMN _NAME,F.DELETE_RULE FROM SYS.ALL_CONSTRAINTS F,

SYS.ALL_CONS_COLUMNS,SYS.ALL_CONSTRAINTS P WHERE F.OWNER = 'LIVE' AND
F.TABLE_NAME = 'ACH_ERRORS' AND  F.CONSTRAINT_TYPE ='R' AND
SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME = F.CONSTRAINT_NAME AND
SYS.ALL_CONS_COLUMNS.TABLE_NAME = 'ACH_ERRORS' AND
SYS.ALL_CONS_COLUMNS.OWNER = 'LIVE' AND P.OWNER = F.R_OWNER AND

P.CONSTRAINT_NAME = F.R_CONSTRAINT_NAME ORDER BY F.CONSTRAINT_NAME, SYS.ALL_CONS_COLUMNS.POSITION
(361601 MilliSeconds)

---

The last query is the absolute KILLER!!! I tried various connection settings to the same effect. It does not matter if I connect using native drivers, ODBC or some 3-rd party tools.

The database we're using has many tables where column names are the same, i.e. there are columns named l1, l2, l3, ... in good 80-100 tables. Would this have to do with slow response time? Are there other ways to speed it up?

Any ideas?

Alex Vilner Received on Thu Feb 26 1998 - 00:00:00 CST