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

Home -> Community -> Mailing Lists -> Oracle-L -> Migration to 9.2.0.3.0 (64 Bit) on Solaris 8 issues

Migration to 9.2.0.3.0 (64 Bit) on Solaris 8 issues

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Fri, 20 Jun 2003 01:39:32 -0700
Message-ID: <F001.005B5D4D.20030620011418@fatcity.com>

After Migrating a production Database from 8.1.7.4 to 9.2.0.3.0 (64 Bit) on Solaris 8 following NON-Documented parameters were set by Oracle Corp

_shared_pool_reserved_min_alloc=16384

This was done to take care of the ORA-4031 errors. This caused shared pool erros to STOP Occuring
though at the Cost of increasing the shared_pool_size to 637MB & shared_pool_reserved_size to 131 MB
Such values seem very high to us .

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared pool.

_library_cache_advice=false

This parameter takes care of library cache pin problem

_optim_enhance_nnull_detection=false

SELECT COUNT(*) GIVES WRONG RESULT with null values ( BUG:- 2920925 )

_multi_join_key_table_lookup=false (BUG:- 2446423)
Details:- QUERY RETURNS WRONG ANSWER WITH "ORDER BY" CLAUSE

_predicate_elimination_enabled=false

BUG:- 2257439
Details:- NULL VALUE IN PARTITION RETURNED IF PARTITION BOUND VALUE IS IN WHERE CONDITION M/c = SF15K
Database using Shared Servers (MTS)
Database Size = 250 GB
Hybrid Banking Application using Bind variables mostly though some amount of Literal SQLs also run
cursor_sharing = EXACT

We are considering trying out cursor_sharing = FORCE & gradually checking if shared_pool_size & shared_pool_reserved_size values can be reduced .

Any Else experienced such issues ? Any Advise ?

We are considering Migrating another 1000 GB Production database also to 9i & setting Similar parameters .

Following is a STATSPACK report of the Production Database already at 9i & having the above NON-Documented parameters already set .

DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ TBASUN 1820705732 tbasun 1 9.2.0.3.0 NO IB15KDB

            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- --------- -------------------
Begin Snap:    1437 18-Jun-03 11:00:03    2,686      49.2
  End Snap:    1446 18-Jun-03 12:00:05    2,936      51.0
   Elapsed:               60.03 (mins)


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            265,713.47              1,165.59
              Logical reads:             24,199.94                106.16
              Block changes:              1,275.65                  5.60
             Physical reads:              1,293.29                  5.67
            Physical writes:                108.35                  0.48
                 User calls:              7,869.31                 34.52
                     Parses:                348.92                  1.53
                Hard parses:                 19.96                  0.09
                      Sorts:                243.00                  1.07
                     Logons:                  8.93                  0.04
                   Executes:              4,874.94                 21.38
               Transactions:                227.97

  % Blocks changed per Read:    5.27    Recursive Call %:    12.26
 Rollback per transaction %:   68.52       Rows per Sort:   335.11

                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
latch free                      1,386,411     72,966     14,913     11      1.7
db file sequential read         3,606,772          0      5,690      2      4.4
log file sync                     327,290        273      4,104     13      0.4
log file parallel write           240,139    236,242        245      1      0.3


                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
library cache                52,729,876    1.7    0.4   5393      248,744   11.0

                         Get  Pct        Pin        Pct               Invali-
Namespace           Requests  Miss     Requests     Miss     Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY                   2,715    0.1          2,715    0.2          1        0
CLUSTER                  207    0.0            310    0.0          0        0
INDEX                  9,431    0.4          9,431    0.4          0        0
SQL AREA           1,137,467    5.8     21,417,402    0.3      5,463        0
TABLE/PROCEDURE      353,698    0.2        717,164    0.7      3,505        0
TRIGGER                   61    0.0             61    6.6          4        0
          -------------------------------------------------------------

Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: VIVEK_SHARMA
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
Received on Fri Jun 20 2003 - 03:39:32 CDT

Original text of this message

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