Your Parse time is high, and cpu is
close to elapsed, so you are almost
certainly hard-parsing all the time.
This is either a bug, or you have enough
parse activity going on, and a small enough
shared pool that you keep invalidating the
cursor (and it's dependents).
Did you report the
Thanks for the information - that test has
been on my TODO list for the last couple
of years. Apart from being useful information,
it also tells us that it's not the OP's problem,
as the number of different possibilities is too
low.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The
Jared,
Digging into it more, I found out that it's called from an
Informatica client. Apparently, the gist of the client-side
algorithim is as follows:
For each row in (some view)
Call generate_product_keys
MERGE (upsert) into product table
end loop
CREATE OR REPLACE PROCEDURE
Thomas,
The version count is the number of child cursors
present in the cache for this SQL. The cursor is
not being shared for some reason with 456 versions.
The 400m of memory seems a bit excessive.
There is a script at Jonathan's site with some info
about v$sqlarea and a script you can run
I recall James Morle saying something about
code not being sharable if the declared sizes
of the bind variables don't match. If Informatica
is using a 3GL to call anonymous pl/sql blocks
with different bind variables every time, perhaps
it is causing a bind variable mismatch.
As for the 400MB
Thanks Jonathan.
I was wondering about those memory columns possibly
acting in that manner. I've never used them to
track memory though, and wasn't sure how they might
act. Google and MetaLink didn't turn up anything
too useful in the regard.
Now that you mention it, I recall reading recently
Title: STATSPACK interpretation
Using
dbms_application_info package also causes that there are several versions of
same statement - but theyshare same execution plan. You cancheck if
this is the case by queryingv$sqlarea (module and action
columns).
Regards,
Joze
-Original Message
Jonathan,
Wouldn't bind variable issue that prevents cursor from
sharing be visible in bind_mismatch?
How can one simulate this?
var v varchar2(1)
begin select count(5) into :v from dual; end;
/
select address, sql_text from v$sql where sql_text
like '%count(5)%';
ADDRESS SQL_TEXT
Notes in-line.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see
FWIW. The database crashed again.I managed to get in a 10466:
BEGIN
GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ;
END;
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse
Thanks, Jonathan.
Of course you are right :-)
Playing with this a little longer, I can get up to 4
versions (child_number from 0 to 3) of the same pl/sql
cursor by changing bind variable sizes. It ceases
being sharable when bv size changes from 32 to 33,
from 128 to 129 and from 2000 to 2001:
Title: STATSPACK interpretation
We recently experienced a crash on our prod datewarehouse running 9.2.0.2 on
AIX 4.3.3. The cause of the crash was 4031 errors generated by background
processes (Oracle support has confirmed there is a bug involved), however,
since that crash occurred
What is taking place inside GENERATE_PRODUCT_KEYS() ?
Could be dynamic SQL of the worst kind in there. That
is, not using bind variables.
A 10046 trace level 4 or 12 will show you what is
going on there.
Jared
On Mon, 2003-12-22 at 12:39, Thomas Jeff wrote:
We recently experienced a crash on
13 matches
Mail list logo
