Re: Slow DB

I have seen this before when the optimizer_mode (init.ora) or the optimizer_goal (alter session) is set to all_rows or first_rows. You can get around this by using dbms_utility.analyze_schema to analyze the sys tables but this can cause other problems.

Alex Vilner wrote:
>
> 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 Fri Feb 27 1998 - 00:00:00 CST