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

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<411bfd63_at_post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Sybrand
>
> > The query is just plain wrong, as I have demonstrated earlier today.
> > Multiple records will be selected from v$sgastat, except the 'free
> > memory' record. Those records will be summed *after* the cartesian
> > product with v$parameter has been determined. Try it yourself.
>
> I tried it and I saw no difference...
>
> SQL> select (sum(a.bytes)/max(b.value))*100 Shared_Pool_Pct
> 2 from v$sgastat a, v$parameter b
> 3 where a.pool = 'shared pool'
> 4 and a.name != 'free memory'
> 5 and b.name = 'shared_pool_size';
>
> SHARED_POOL_PCT
> ---------------
> 23.1099674
>
> SQL> select sum(a.bytes)
> 2 from v$sgastat a
> 3 where a.pool = 'shared pool'
> 4 and a.name != 'free memory';
>
> SUM(A.BYTES)
> ------------
> 6785116
>
> SQL> select b.value
> 2 from v$parameter b
> 3 where b.name = 'shared_pool_size';
>
> VALUE
> ---------------------------------------
> 29360128
>
> Anyway, such statistics are useless to know if the shared pool is correctly
> sized or not.
>
> Chris

Apparently you still contradict there is a cartesian product in the original query. This is telling about your lack of knowledge of SQL, and also about your audacity to keep contradicting someone with 15 years experience in Oracle, when you could have known you are just plain wrong.
Apologies would be in order, our get your money back from the instutition at which you learned SQL.

Sybrand Bakker
Senior Oracle DBA Received on Fri Aug 13 2004 - 07:29:38 CDT