Message-Id: <10610.116269@fatcity.com> From: VIVEK_SHARMA Date: Wed, 6 Sep 2000 11:09:47 +0530 Subject: RE: Re: Benchmark runs - Waits on enqueue , latch free - & AGAIN 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@yahoo.com] > Sent: Tuesday, September 05, 2000 9:25 PM > To: VIVEK_SHARMA > Cc: ORACLE-L@fatcity.com; oracledba@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 > To: 'K Gopalakrishnan' > Cc: ; > 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@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 > > > To: > > > Cc: ; > > > 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@yahoo.com' > > > > > Cc: 'ORACLE-L@fatcity.com'; 'oracledba@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@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!?