RE: Re: Benchmark runs - Waits on enqueue , latch free - & AGAIN

Setting CURSOR_SPACE_FOR_TIME=TRUE Caused Performance to FALL

NOTE - CPU Utilization on APP Server = 51 % whereas CPU Utilization on DB Server = 67 %

Qs.2 What can be Done to make the CPU Utilization on APP Server GREATER than or EQUAL to CPU Utilization on DB Server ?

NOTE Previously On DB Server by increasing the init.ora parameter shared_pool_reserved_size to 40 M from 10 MB the CPU Utilization on APP server shot up from 33 % to 51 % as a result

Vivek Sharma
Phone 080 6588668

> -----Original Message-----
> From: VIVEK_SHARMA
> Sent: Wednesday, August 30, 2000 10:46 AM
> To: 'kaygopal_at_yahoo.com'
> Cc: 'ORACLE-L_at_fatcity.com'; 'oracledba_at_quickdoc.co.uk'
> Subject: Re: Benchmark runs - Waits on enqueue , latch free - &
> AGAIN
>
> NO packages pinned .
> Qs.1 How to pin the SYS packages ?
>
> PENDING Will check out by Setting CURSOR_SPACE_FOR_TIME=TRUE & revert back
>
> SCRIPT :-
> SQL> select owner || '.' || name OBJECT
> 2 , type
> 3 , to_char(sharable_mem/1024,'9,999.9') "SPACE(K)"
> 4 , loads
> 5 , executions execs
> 6 , kept
> 7 from v$db_object_cache
> 8 where type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
> 9 and owner not in ('SYS')
> 10 order by owner, name
> 11 /
>
> OBJECT
> --------------------------------------------------------------------------
> ------
> TYPE SPACE(K) LOADS EXECS KEP
> -------------- -------- ---------- ---------- ---
> TBAADM.LASP0003
> PACKAGE 4.7 1 38 NO
>
> TBAADM.LASP0003
> PACKAGE BODY 27.9 1 20 NO
>
>
> SQL>
> SQL> select to_char(sum(sharable_mem)/1024,'9,999,999.9') "TOTAL SPACE
> (K)"
> 2 from v$db_object_cache
> 3 where type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
> 4 and owner not in ('SYS');
>
> TOTAL SPACE
> ------------
> 32.6
>
> NOTE the menu option Corresponding to package LASP0003 quite well
> performing
> Hence NO pinning may be Required
>
>
> -----Original Message-----
> From: K Gopalakrishnan [SMTP:kaygopal_at_yahoo.com]
> Sent: Tuesday, August 29, 2000 3:45 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Benchmark runs - Waits on enqueue , latch free
> - & AGAIN
>
> Hi Vivek !
>
>
> Still benchmarking :)
>
> > > RESULT - Most of the waits Occuring on "library
> > cache"
> > > a few waits on row cache objects & shared pool
> > also exist
> > >
> > > Qs. How can this be Addressed ?
>
> Waits for library cache can be reduced (!!) if you set
> the paremeter CURSOR_SPACE_FOR_TIME. But in this case
> you need a little bigger shared pool size. I guess you
> have pinned most of the SYS packages and heavy reload
> packages in Shared Pool.
>
>
>
>
> =====
> With Regards !
> K Gopalakrishnan
> -----------------------------------------------------------
> #489,EastEnd Main Road,
Received on Wed Aug 30 2000 - 01:36:36 CDT