Impact on database by performing reverse engineer

[leonard905]

Hi all,

Are there any significant performance degradation to a large db from reverse engineering objects using Designer's Design editor?

Thanks in advance,

[Shestakov]

What is "large db"?
1 -- number of database objects or
2 -- number of rows in some tables?

If u have first case, than it possible.
If second case not, because reingeneering process depend only from size
of dictionary, (how many objects have to proceed by designer)

[sreddy]

I don't think so. Its just a database connection which queries all the objects and the relationship betwwen objects.

[leonard905]

Hi Thanks for the reply.

By large db I mean a db with about 900 tables and about 2 tables with # of rows in excess of 2+ million. Other tables are on average 50K # of rows.

Thanks again

[irehman]

Answer is no effect on performance.

[leonard905]

Thanks.

However, from an Admin. point of view, can you briefly explain the internal process Oracle Desiger goes through when you reverse engineer (not the how-to!). What is accessed, copied,etc. There is no documentation about this process on Oracle's website.

Thanks again.

[marist89]

Designer queries the data dictionary, getting information about tables, constraints, etc. I/O on the SYSTEM tablespace will be extremely heavy during a reverse engineer operation...

[Shestakov]

Originally posted by irehman
Answer is no effect on performance.

I can't agree with this :
Why:
1) avg # of colums for each table 5-7 (then > 5000 rows in sys.col$ table)
2) when oracle have to work with dictionary tables it use not only
db cache, but dictionary and library cache too.
size of these areas not so big as db cache.
3) oracle have to check permitions to access for each objects in reingeneering process.
This is library/dictionary cache too.
as possible result --> oracle increase # of locks, and then we may have
degradation of perfomance with parse new "usual" sql statments.

[alapps]

Shestakov,

The effect of reverse engineering is minimal to other users of the database...

The system tablespace, as stated by marist89, will have heavy I/O during the reverse engineering process. However, the retrieval (Reverse Engineering does not update the dictionary) will have less effect than SQL that produces reports. In summary, building a report is more "expensive" than reverse engineering.

[Shestakov]

To alapps:

This is some information about size of dictionary:
This is medium db, but it keep designer repositary tables:

SQL> select count(*) from sys.source$;

COUNT(*)
----------
818623

SQL> select count(*) from sys.obj$;

COUNT(*)
----------
10142

SQL> select count(*) from sys.col$;

COUNT(*)
----------
45288

Some or all table are members of reingeneering process.

Then this is example only 1 view, that may use in reingeneering process.
and in the view some tables are X$ tables (x$kzsro) from shared memory.

SQL> select view_name,text from dba_views where view_name like 'ALL_TAB_COLUMNS';

VIEW_NAME TEXT
------------------------------ --------------------------------------------------------------------------------
ALL_TAB_COLUMNS select u.name, o.name,
c.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name,
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZO
NE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'), ut.name,
c.length, c.precision#, c.scale,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N'), c.col#, c.deflength,
c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), h.timestamp#, h.sample_size,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(c.charsetid, 0, to_number(NULL),
nls_charset_decl_len(c.length, c.charsetid)),
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
h.avgcln
from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u,
sys.coltype$ ac, sys.obj$ ot, sys.user$ ut
where o.obj# = c.obj#
and o.owner# = u.user#
and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
and bitand(c.property, 32) = 0 /* not hidden column */
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and (o.type# in (3, 4) /* cluster, view */

or
(o.type# = 2 /* tables, excluding iot - overflow and nested tables */

and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
and (o.owner# = userenv('SCHEMAID')
or
o.obj# in ( select obj#
from sys.objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)

What do u thing it may have effect to perfomance or not.