FWIW. The database crashed again. I managed to get in a 10466:
BEGIN
GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ;
END;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2737 1213.00 1184.60 0 0 0
0
Execute 2737 28.57 28.08 0 0 0
2737
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 5474 1241.57 1212.68 0 0 0
2737
Looks like maybe that it's an anonymous PL/SQL call just as Jonathan
described?
Thanks for the replies.
-----Original Message-----
Jared Still
Sent: Tuesday, December 23, 2003 2:34 PM
To: Multiple recipients of list ORACLE-L
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
somewhere that different size bind variables could
cause multiple versions. Probably James, but I can't
recall where I saw it.
Google, Metalinkm mailing lists and a plethora of books
and white papers have made it quite difficult, at least for
me, to always remember the source of tidbits such as this.
Back to the memory, I was somehow able to cause 2
version of the same SQL, as mentioned earlier, but
the reason for it did not appear in v$sql_shared_cursor.
Jared
On Tue, 2003-12-23 at 10:54, Jonathan Lewis wrote:
>
> 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 - I've often noticed oddities where
> a new entry is created, but "carries forward" a report
> of the memory requirements of earlier variants, so if you
> have 10 cursors, they don't report 10 units of memory, but
> 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is
> possible that you are seeing some effect like this.
>
>
>
> 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 http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, December 23, 2003 5:09 PM
>
>
> > 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 that looks
> > at the current memory requirements for a SQL statement.
> >
> > http://www.jlcomp.demon.co.uk/sqlarea.html
> >
> > Does the output match what you see in statspack?
> >
> > Also, the number of executions is much lower than
> > the version count, which is rather odd. There's a bug
> > in early 9i versions that would cause this, but was supposed to be
> > corrected by 9.2.0.2.
> >
> > In experimenting with this, I managed to get 4 different
> > sessions to create 2 versions of a cursor. I'm not sure
> > why as it was pl/sql and variables were used for the calling
> > parameters.
> >
> > A 'select * from v$sql_shared_cursor' did not reveal any reason for
> > it.
> >
> > After bouncing the database and trying this again, I couldn't
> > duplicate it.
> >
> > Maybe a couple of things to pursue here, but perhaps not
> > an abundance of help. :(
> >
> > Jared
> >
> >
> >
> > On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote:
> > > 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
> > >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L (or the
> name of mailing list you want to be removed from). You may also send
> the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Jeff
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
