| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: STATSPACK interpretation
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
------- ------ -------- ---------- ---------- ---------- ----------
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" <ORACLE-L_at_fatcity.com>
> 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: jonathan_at_jlcomp.demon.co.uk
>
> 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: ListGuru_at_fatcity.com (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: jkstill_at_cybcon.com 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: ListGuru_at_fatcity.com (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: jeff.thomas_at_thomson.net 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: ListGuru_at_fatcity.com (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).Received on Tue Dec 23 2003 - 17:19:25 CST
 |
 |