Impact on database by performing reverse engineer
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Impact on database by performing reverse engineer

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227

    Red face

    Hi all,

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

    Thanks in advance,
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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)


  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I don't think so. Its just a database connection which queries all the objects and the relationship betwwen objects.
    Reddy,Sam

  4. #4
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    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
    leonard905
    leonard905@yahoo.com

  5. #5
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Answer is no effect on performance.

  6. #6
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    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.
    leonard905
    leonard905@yahoo.com

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  9. #9
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    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.

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width