Re: Apex performance

On Tue, 08 Jul 2014 09:08:19 -0700, rl wrote:

> Hi,
>
> Oracle 11.2.0.2 Apex version 4.2
>
> I have a stored procedure that contains a piece of sql that populates a
> table. The source for the data is a blob, this blob is processed by a
> function and cast into a table. Like this:
>
> insert /*+APPEND*/ into ecg_chart_cache (session_id,seq,channel_1)
> select v_session_id,
> seq, channel_1
> from table(get_ecg(p_trace_id => p_trace_id, p_start_seq => 1,
> p_end_seq => p_chunk_size, p_sample_every => p_sample_every));
>
> When this procedure is run from sqlplus it works fine. However when run
> from apex, it does an endless number of direct path reads against the
> lob segment containing the blob. The function itself simply reads the
> blob once in its entirety into a plsql variable then processes it,
> piping the rows back as they are extracted from the blob.
>
> I have compared the v$ses_optimizer_env from both sessions, but cant see
> any differences.
>
> Does anybody have any other ideas?
>
> Cheers
>
>
> Ralph

How about an exclusive table lock generated by the /*+ APPEND */ hint? Any waits on locks? Try putting a "commit" in the procedure.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com