Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Benchmark runs - Waits on enqueue , latch free - & AGAIN

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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Wed, 6 Sep 2000 14:43:50 +0530
Message-Id: <10611.116290@fatcity.com>


ORA 7.3.4.5.0
Thanks for the Script name . Will check it out

> -----Original Message-----
> From: K Gopalakrishnan [SMTP:kaygopal_at_yahoo.com]
> Sent: Wednesday, September 06, 2000 1:17 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Re: Benchmark runs - Waits on enqueue , latch free - &
> AGAIN
>
> Hi !
>
> Which version of Oracle?
> There are some scripts in
> http://www.ixora.com.au/scripts/param.htm
> to get the values.
>
>
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, September 06, 2000 7:44 AM
>
>
> >
> > Qs.1 Do you mean _kgl_latch_count (number of library cache latches)
> instead
> > of
> > _kgl_bucket_count (index to the bucket count array) ?
> >
> > Qs. 2 Which field in x$ksppi refers to the Current Value ?
> >
> > ADDR INDX KSPPINM
> > KSPPITY KSPPDESC
> > KSPPIFLG
> > -------- ----------
> > ------------------------------------------------------------
> > ---- ----------
> > ----------------------------------------------------------------
> > ----------
> > 007B91C0 154 _kgl_latch_count
> > 3 number of library cache latches
> > 0
> > 007B91EC 155 _kgl_bucket_count
> > 3 index to the bucket count array
> > 0
> > 007BA1BC 247 spin_count
> > 3 Number of times to spin on latch miss
> > 0
> >
> > NOTE - NOT able to Deduce the value from x$ksppi .
> > Value for ANOTHER parameter spin_count is 2000 which is nowhere visible
> > in x$ksppi table
> >
> > Qs.3 Are there any Commonly known values this param _kgl_latch_count can
> be
> > set to ?
> >
> > Qs.4 For your Alternative suggestion , Do you mean it (_kgl_latch_count)
> can
> > be Set
> > to the Count of Number of records in V$latch_children for latch=library
> > Cache ?
> >
> >
> >
> > > -----Original Message-----
> > > From: K Gopalakrishnan [SMTP:kaygopal_at_yahoo.com]
> > > Sent: Tuesday, September 05, 2000 9:25 PM
> > > To: VIVEK_SHARMA
> > > Cc: ORACLE-L_at_fatcity.com; oracledba_at_quickdoc.co.uk
> > > Subject: Re: Re: Benchmark runs - Waits on enqueue , latch free - &
> > > AGAIN
> > >
> > > Hi !
> > >
> > > The current value can be obtained from X$KSPPI.
> > > The other way (and recommedned by Steve ) is
> > > to select the count(*) from V$latch_children
> > > where latch=library cache, (look for the right
> > > columns in the views.i am just giving an idea).
> > >
> > >
> > > And i don't think this parameter is _that_dangerours.
> > >
> > >
> > > Best Regards,
> > > K Gopalakrishnan
> > > Bangalore, INDIA
> > >
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
> > > To: 'K Gopalakrishnan' <kaygopal_at_yahoo.com>
> > > Cc: <ORACLE-L_at_fatcity.com>; <oracledba_at_quickdoc.co.uk>
> > > Sent: Tuesday, September 05, 2000 12:11 PM
> > > Subject: RE: Re: Benchmark runs - Waits on enqueue , latch free - &
> AGAIN
> > >
> > >
> > > >
> > > > Do you mean _kgl_latch_count (number of library cache latches)
> instead
> > > of
> > > > _kgl_bucket_count (index to the bucket count array) ?
> > > >
> > > > Qs. How to Get the CURRENT Values for the Same ?
> > > > Qs. Is it OK to Change these parameters in ORA 7.3.4.5.0 on Digital
> > > Unix
> > > > 4.0 G ?
> > > >
> > > > NOTE - 2 Actions Carried Out :-
> > > > 1) Increased the RAM in DB server to 4 GB from 3 GB thereby making
> it
> > > Equal
> > > > to the RAM on APP Server
> > > > 2) Cached ALL the Packages of SYS User + Application Software
> Related
> > > > Oracle Users into the shared_pool
> > > >
> > > > RESULT - CPU Utilization on APP Server became Equal to That on DB
> Server
> > > ,
> > > > Both = 55 % Approx.
> > > > Qs. NOT Sure which of them made the Difference
> > > >
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: K Gopalakrishnan [SMTP:kaygopal_at_yahoo.com]
> > > > > Sent: Thursday, August 31, 2000 3:33 PM
> > > > > To: VIVEK_SHARMA
> > > > > Subject: Re: Re: Benchmark runs - Waits on enqueue , latch free -
> &
> > > > > AGAIN
> > > > >
> > > > > Hi Vivek !!
> > > > >
> > > > > No problem. There is a parameter called '_kgl_bucket_count'
> > > > > which controls the number of child latches for library cache.
> > > > >
> > > > > What is the current setting ( i guess it should be a PRIME
> number).
> > > > > SOme times increasing this will also help
> > > > >
> > > > > Best Regards,
> > > > > K Gopalakrishnan,
> > > > > Bangalore, INDIA
> > > > >
> > > > >
> > > > >
> > > > > ----- Original Message -----
> > > > > From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
> > > > > To: <kaygopal_at_yahoo.com>
> > > > > Cc: <ORACLE-L_at_fatcity.com>; <oracledba_at_quickdoc.co.uk>
> > > > > Sent: Wednesday, August 30, 2000 7:36 AM
> > > > > Subject: 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,
> > > > > > > Jayanagar IX Block East,
> > > > > > > Bangalore-560069, INDIA
> > > > > > > Ph # + (91)-(80)- 6543672
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > > > > _________________________________________________________
> > > > >
> > > > > Do You Yahoo!?
> > > > >
> > > > > Get your free @yahoo.com address at http://mail.yahoo.com
> > > > >
> > > > >
> > >
> > >
> > >
> > > __________________________________________________
> > >
> > > Do You Yahoo!?
> > >
> > > Talk to your friends online with Yahoo! Messenger.
> > >
> > > http://im.yahoo.com
> > --
> > Author: VIVEK_SHARMA
> > INET: VIVEK_SHARMA_at_infy.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Talk to your friends online with Yahoo! Messenger.
> http://im.yahoo.com
>
> --
> Author: K Gopalakrishnan
> INET: kaygopal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
Received on Wed Sep 06 2000 - 04:13:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US