Re: Shared Pool: Can it be filled *over* 100% ?

"omlet" <notrolls_at_notrolls.omlet.org.notrolls> wrote in message news:<f5a1d74fd48ef2ea20f9c6aa9f15b062_at_localhost.talkaboutdatabases.com>...
> Reminding David Fitzjarrell of an earlier posting probably would be in the
> right place here:
>
> Cursors kernel code have a lot of overhead:
> The following are the most interesting ones:
>
> opiosq, opicca, opiexe, opifch or kernel calls kkspsc, kksumc, kkspfx,
> kkspin.
>
> When you issue a simple SQL statement; the following occurs:
> 1- aquires shared lock;
> 2- creation of a kgl object;
> 3- pinning the name's object heap zero in shared mode
> 3.1- might require checking if there is some context area that could
> be
> used by this user, by pinning the child objects, one by one, and seeing if
> usable.
> 3.2 release the pin locks
> 4- pin the parent heap zero in exclusive mode, create new child marked as
> not bind-qualified; lock the child kglob in breakable shared mode, release
> the parent pin lock, parse; release the pin lock and object lock on the
> child
> 5- bind all bind variables
> 6- pin parent heap zero in shared mode; check all matching children by
> pinning their heap zero to see if any is reusable by this user.
>
> I cannot help help it! I have to be offensive: "do the above till Daniel's
> cursorit is willing and dripping; lock it in breakable-share mode. On
> every dip, pin in S mode; do cleanups; if the dick slips signal an error
> if mid-fetch;"

I personally believe you have many errors in 'mid-fetch', cranially speaking, most notably the 'advice' you've provided in this post and the one prior to it, listed in all of its glory below:

 If you issue the same query 10 times a second; you are likely to have most
 of them unfinished; latches aquired but not released yet. and of course
 stats accumulated beyond 100% dependent on which stat you queried first!

Now, what all of that nonsense means is a mystery to everyone who reads this thread, I suspect. Issuing the same query 10 times a second from a single session is impossible. Issuing the same query 10 times a second from multiple sessions makes absolutely no sense; why would anyone use 10+ sessions to run the same query against v$sgastat and v$parameter? Oh, yes, I forgot, that's exactly how OMLET operates, generating multiple copies of useless statistics and hit ratios, ignoring the wait interface entirely. But, why would unreleased latches between sessions cause the SGA to show over 100% population? They won't. Period.

> You should be dis-barred from your job; fired! and Mike Ault should pay a
> heavy fine for providing you with the script!
>

If anyone should be removed from their 'job' it is you, Amjad Daoud. You've clearly shown your ignorance of Oracle in every post you've made, and with every 'spam-vertisment' for your OMLET. Arrogance and abuse are no substitute for knowledge. Of course, this is something you have not yet learned.

> At least you should have cached the cursor!!!!!!!!!!!!
>

As if you would know anything of Oracle to make a valid comment.

David Fitzjarrell Received on Sat Aug 14 2004 - 17:51:13 CDT