Re: insert programs take 5 hrs

The SQL you quote is something that Oracle performs recursively when hard parsing a statement that contains a view. This tends to suggest that the driving code is either invalidating objects very frequently, or using lots of different SQL statements (possibly built as literal strings).

If the trace file is showing very large numbers of similar but different SQL statements, your problem may be in hard parsing. Check this by setting timed_statistics=true, and repeating the exercise.

Check v$sesstat (joined to v$statname) for:

    parse count (total)
    parse count(hard)
    parse time CPU
    parse time elapsed

All client/server dialogue is done through SQL*Net, even on a BEQ connection. If most of the time spent on this job is "SQL*Net message from client" then the client must be doing something unduly complicated that is slowing the whole process down.

A 'quick and dirty fix' if you are on a suitable version of Oracle and the client is building similar literal strings all the time is to set. "cursor_sharing = force" for the session to get Oracle to convert literals into system bind variables. This can reduce the cost of parsing quite significantly.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



res0asig wrote in message ...


>hi,

>  I need help in determining where the performance problem is for a batch

>program that

> inserts 26000 records into many tables. The batch program continuously

>builds a flat file and as it inserts into these tables.  The data in the

>tables is inserted through a views.  I did a trace on the batch program and

>I found the following sql statement being parsed a lot of times. "select

>text from view$ where rowid= :1".  All the views are valid.  the only waits

>i see are "sqlnet message from client" and "sqlnet message to client" in

the


>trace file.  However the batch program is a local connection. (BEQ).

>Could some one please help me in resloving this issue.

>

>-Thanks in advance

>sri

>

>