*** Comments by BECKER, BILL Fri Jun 20, 2003 -- 08:09:59 AM
We upgraded to 64-bit 9.2.0.3.0 on Solaris 9 a few weeks ago,
and are currently experiencing ORA-01733 "virtual column not allowed
here" errors - bug 2884797. This is a star transformation bug,
and you are likely to hit it if you are a datawarehouse with
star_transformation=true and cursor_sharing=exact. You can only get
a patch for this by filing a tar; we could not find any information
on this from a Metalink search. We are also experiencing various errors
from parallel server processes, and it's not clear if the patch
will fix these. Oh yeah, support first had us download the 32-bit
patch, which didn't work; they are not sure if a 64-bit patch exists,
and we are currently waiting on this.
version 8.1.6 - star_transformation bug crashes database
version 8.1.7 - star_transformation bug fixed; new bug (excuse me, feature)
ORA-00164 "autonomous transaction disallowed within distributed transaction"
version 9.2.0.3 - ORA-00164 fixed; new star transformation bug ORA-01733
new versions, new bugs.
____________________________________________________________________________
*** Original message by "VIVEK_SHARMA" <[EMAIL PROTECTED]>
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
