RE: DATABASE SLOW(8170 ON SUN SOLARIS)

From: Abhishek Saxena <AbhishekS_at_KPITCummins.com>
Date: Tue, 4 May 2004 10:59:56 +0530
Message-ID: <4A1BE23A7B777442B60F4B4916AE0F13A67F07@sohm.kpit.com>

Hi Seema=20
The init parameter shared_pool_reserved_min_alloc is obsolete ...It = should not be used ...regarding all the other parameter=20

OPTIMIZER_INDEX_CACHING is presumably meant to the indicative of how = much of an index is cached, then setting it to your buffer hit ratio = would seem like a reasonable starting point.=20 This initialization parameter represents a percentage value, ranging = between the values of 0 and 99. The default value of 0 indicates to the = CBO that 0% of database blocks accessed using indexed access can be = expected to be found in the Buffer Cache of the Oracle SGA. This = implies that all index accesses will require a physical read from the =

I/O subsystem for every logical read from the Buffer Cache, also known =
as a 0% hit ratio on the Buffer Cache.  This parameter applies only to =
the CBO's calculations of accesses for blocks in an index, not for the =

blocks in the table related to the index. The cost calculated by the CBO is primarily comprised of physical I/O. = The actual formula is documented as=20
IO + CPU/1000 + NetIO*1.5
In this formula, "IO" represents physical I/O requests, "CPU" represents = logical I/O requests, and "Net I/O" represents logical I/O requests = to/from a remote database across a database link. This implies that physical I/O requests are the most expensive = component, so they make up the majority of the costing. =20
Rest of things seems to be fine ....but you have to concentrate on these = things first .
Abhishek Saxena
Oracle Apps DBA
KPIT Cummins Infosystem Limited=20
Ph : +91 20 25458277/78/79 Ext. : 1016 mail : abhisheks_at_kpitcummins.com=20
visit : http://www.kpitcummins.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Seema Singh Sent: Monday, May 03, 2004 11:04 PM
To: oracle-l_at_freelists.org
Subject: DATABASE SLOW(8170 ON SUN SOLARIS)

Hi ,

Couple of customers are noticing some performance issue. Earlier stats was as below.

                                       GET HIT                     =
PINHIT
NAME                     GETS  GETHITS   RATIO      PINS   PINHITS   =

RATIO

-------------------- -------- -------- ------- --------- --------- =
-------
SQL AREA               490135   334814     .68   1223638    903071     =
.74
TABLE/PROCEDURE        277663   276042     .99    873103    869099    =
1.00
BODY                      149      138     .93       137       124     =
.91
TRIGGER                 24129    24101    1.00     24129     24097    =
1.00
INDEX                      39        0     .00        39         0     =
.00
CLUSTER                  1834     1823     .99      2068      2051     =
.99
OBJECT                      0        0    1.00         0         0    =
1.00
PIPE                        0        0    1.00         0         0    =

1.00

                    PINHIT             GET HIT
     PINS   PINHITS   RATIO     MISSES   RATIO
--------- --------- ------- ---------- -------
  2123119   1798445   84.71      11079   99.48

Buffer cache hit ratios was also earlier 66%.

I changed couple of parameters .

Parameter Name                         Current value                  =20
purposed Value
db_block_buffers                                    14424                =


   =20

                     16000

shared_pool_reserved_size                    4194304                     =


   =20

         3670016

large_pool_size                                         614400           =


   =20

                       0

java_pool_size                                         20971520          =


   =20

                   15728640

optimizer_mode                                      CHOOSE               =


   =20

                FIRST_ROWS

optimizer_index_Adj                             100                      =


   =20

                       10

_shared_pool_reserved_min_alloc     4000                                 =


   =20

            0

Sytem was slow earlier but After above changed value couple of = customers=20
are complening some issues at time of login. Dictionary cahe ratios bump from .13% to 1.4%. Do you think can I reverse back _shared_pool_reserved_min_alloc 4000 =

value?
Any suggestions are most welcome!
Other ratios looks fine.

Mother's Day is May 9. Make it special with great ideas from the = Mother's=20
Day Guide! http://special.msn.com/network/04mothersday.armx

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 04 2004 - 00:29:44 CDT