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).
