Re: Undesired sys statement on insert

On Mon, 29 Jul 2002 18:24:45 +0200, Domenico Vasile <domenico_vasile_at_libero.it> wrote:

>Hi all. Below is a sketch from a tkprof output file:
>
>************************************************************************
>********
>
>select c.name, u.name
>from
> con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1
>and
> c.owner# = u.user#
>
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 5743 1.84 2.17 0 0 0
>0
>Execute 5743 6.03 5.64 0 0 0
>0
>Fetch 5743 10.89 11.44 10 51687 17229
>5743
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 17229 18.76 19.25 10 51687 17229
>5743
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: SYS (recursive depth: 2)
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 1 NESTED LOOPS
> 1 HASH JOIN
> 1 TABLE ACCESS BY INDEX ROWID CDEF$
> 2 INDEX RANGE SCAN (object id 48)
> 1381 TABLE ACCESS FULL CON$
> 1 TABLE ACCESS CLUSTER USER$
> 1 INDEX UNIQUE SCAN (object id 11)
>
>************************************************************************
>********
>
>
>I get this during an insert of 5743 rows. It seems that for each rows
>inserted in the actual INSERT statement the previous select is
>automatically issued. Why does the statement require such an heavy
>parsing? Furthermore I have no constraint defined on my table. Why is
>the statement required at all? Is there a way to avoid (or to "smooth")
>the sys statement?
>TIA
>--Domenico

1 From your explain plan results it looks like you have statistics on the datadictionary (The SYS schema ). You should NEVER EVER do this. Please REMOVE them NOW!! (exec
dbms_utility.analyze_schema('SYS','DELETE')

The statement is querying the datadictionary. The datadictionary is cached in the library cache. The above results show your library cache is too small. Please increase the shared_pool_size parameter and bounce the instance.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jul 29 2002 - 12:59:10 CDT