Home » RDBMS Server » Performance Tuning » ORA-03113 after performance tuning (Oracle 9.2.0.3, Windows 2003)
| ORA-03113 after performance tuning [message #354543] |
Mon, 20 October 2008 04:22  |
dzavy
Messages: 1
Registered: October 2008
|
Junior Member |
|
|
Hi,
I have a problem which I'm not able to solve myself.
We've recently updated our IS which uses Oracle 9.2.0.3. During the downtime we also changed some Oracle's parameters in order to improve performance - I'll list them:
I removed db_block_buffers = 40000
I changed compatible = 8.1.7.0.0 to compatible = 9.2.0
I added sga_max_size = 600000000, db_cache_size = 400000000, pga_aggregate_target = 200000000, cursor_sharing = similar
After that everything works except a few querys...
This long query causes mentioned ORA-03113 occuring and losing connection. Whne I remove a few conditions (OR e.CLSNUMBER =), it works fine. My tip was problem with cursor_sharing...as it is dynamic parameter, I changed back to exact (default value), but it didn't helped.
I've also tested in our test environment where everything works fine until setting cursor_sharing to similar...but setting it back to exact makes it working again.
I need to be sure what to change when requiring another downtime.
Any help appreciated, thanks in advance.
SELECT distinct SUBNUMBER,SUBNAME,SUBFIRSTNAME,SUBNATIONALID,SUBCLSNUMBER,SUBPOLICYNUMBER,SUBLAS
TUSER,CLSNAME, ((SUBENDDATE-sysdate+1)*(sysdate-SUBSTARTDATE))DATEDIFF,TO_CHAR(SUBSTARTDATE, 'DD-MON-YYYY') SUBSTARTDATE1,TO_CHAR(SUBENDDATE, 'DD-MON-YYYY')SUBENDDATE1, DECODE(SUBSTR(SUBLASTUSER, 1, 3), 'SYS', 1, 0) sortByLastUser, UPPER(SUBFIRSTNAME) sortByUpperFirstName, UPPER(SUBNATIONALID) sortByUpperNationalID, UPPER(CLSNAME) sortByUpperClsName FROM SAXADMIN.SUBSCRIBERS, SAXADMIN.CLAIMSCHEMES e WHERE e.CLSNUMBER=SUBCLSNUMBER AND UPPER(SUBNATIONALID) LIKE UPPER('809') AND ( e.CLSNUMBER = 348 OR e.CLSNUMBER = 352 OR e.CLSNUMBER = 363 OR e.CLSNUMBER = 349 OR e.CLSNUMBER = 372 OR e.CLSNUMBER = 358 OR e.CLSNUMBER = 360 OR e.CLSNUMBER = 412 OR e.CLSNUMBER = 379 OR e.CLSNUMBER = 354 OR e.CLSNUMBER = 330 OR e.CLSNUMBER = 409 OR e.CLSNUMBER = 347 OR e.CLSNUMBER = 369 OR e.CLSNUMBER = 368 OR e.CLSNUMBER = 397 OR e.CLSNUMBER = 365 OR e.CLSNUMBER = 366 OR e.CLSNUMBER = 356 OR e.CLSNUMBER = 295 OR e.CLSNUMBER = 351 OR e.CLSNUMBER = 350 OR e.CLSNUMBER = 322 OR e.CLSNUMBER = 294 OR e.CLSNUMBER = 329 OR e.CLSNUMBER = 374 OR e.CLSNUMBER = 371 OR e.CLSNUMBER = 377 OR e.CLSNUMBER = 326 OR e.CLSNUMBER = 408 OR e.CLSNUMBER = 336 OR e.CLSNUMBER = 319 OR e.CLSNUMBER = 381 OR e.CLSNUMBER = 411 OR e.CLSNUMBER = 357 OR e.CLSNUMBER = 398 OR e.CLSNUMBER = 392 OR e.CLSNUMBER = 324 OR e.CLSNUMBER = 396 OR e.CLSNUMBER = 394 OR e.CLSNUMBER = 395 OR e.CLSNUMBER = 323 OR e.CLSNUMBER = 340 OR e.CLSNUMBER = 367 OR e.CLSNUMBER = 353 OR e.CLSNUMBER = 298 OR e.CLSNUMBER = 384 OR e.CLSNUMBER = 373 OR e.CLSNUMBER = 376 OR e.CLSNUMBER = 341 OR e.CLSNUMBER = 346 OR e.CLSNUMBER = 362 OR e.CLSNUMBER = 383 OR e.CLSNUMBER = 359 OR e.CLSNUMBER = 361 OR e.CLSNUMBER = 303 OR e.CLSNUMBER = 364 OR e.CLSNUMBER = 370 OR e.CLSNUMBER = 304 OR e.CLSNUMBER = 331 OR e.CLSNUMBER = 305 OR e.CLSNUMBER = 410 OR e.CLSNUMBER = 387 OR e.CLSNUMBER = 355 OR e.CLSNUMBER = 297 OR e.CLSNUMBER = 413 OR e.CLSNUMBER = 345 OR e.CLSNUMBER = 344 OR e.CLSNUMBER = 334 OR e.CLSNUMBER = 296 OR e.CLSNUMBER = 332 OR e.CLSNUMBER = 308 OR e.CLSNUMBER = 335 OR e.CLSNUMBER = 378 OR e.CLSNUMBER = 337 OR e.CLSNUMBER = 320 OR e.CLSNUMBER = 407 OR e.CLSNUMBER = 393 OR e.CLSNUMBER = 391 OR e.CLSNUMBER = 390 OR e.CLSNUMBER = 389 OR e.CLSNUMBER = 388 OR e.CLSNUMBER = 342 OR e.CLSNUMBER = 386 OR e.CLSNUMBER = 299 OR e.CLSNUMBER = 375 OR e.CLSNUMBER = 382 OR e.CLSNUMBER = 380 OR e.CLSNUMBER = 306 OR e.CLSNUMBER = 312 OR e.CLSNUMBER = 300 OR e.CLSNUMBER = 301 OR e.CLSNUMBER = 343 OR e.CLSNUMBER = 333 OR e.CLSNUMBER = 338 OR e.CLSNUMBER = 328 OR e.CLSNUMBER = 325 OR e.CLSNUMBER = 339 OR e.CLSNUMBER = 313 OR e.CLSNUMBER = 302 OR e.CLSNUMBER = 309 ) ORDER BY sortByLastUser, SUBNAME, SUBFIRSTNAME;
[Updated on: Mon, 20 October 2008 04:23] Report message to a moderator |
|
|
|
|
|
| Re: ORA-03113 after performance tuning [message #354694 is a reply to message #354543] |
Mon, 20 October 2008 20:38  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
While you wait the week for Oracle to get back to you and tell you to
| Quote: | "upgrade to version xxx and try again, if the problem persists get back to us"
|
you can try putting your CLSNUMBER list into a table and then join to it, to see if your problem is possibly in the disjunctive form transformation.
As an aside, your post is messing up my browser. Next time format the code. You know.. by using the code formatter on OraFAQ...
SELECT DISTINCT SubNumber,
SubName,
SubFirstName,
SubNationalId,
SubclsNumber,
SubPolicyNumber,
SublAs tUser,
clsName,
((SubEndDate - SYSDATE + 1) * (SYSDATE - SubStartDate)) DatedIff,
To_char(SubStartDate,'DD-MON-YYYY') SubStartDate1,
To_char(SubEndDate,'DD-MON-YYYY') SubEndDate1,
DECODE(Substr(SubLastUser,1,3),'SYS',1,
0) SortByLastUser,
Upper(SubFirstName) SortByUpperFirstName,
Upper(SubNationalId) SortByUpperNationalId,
Upper(clsName) SortByUpperclsName
FROM sAxAdmIn.Subscribers,
sAxAdmIn.ClaimSchemes e
WHERE e.clsNumber = SubclsNumber
AND Upper(SubNationalId) LIKE Upper('809')
AND (e.clsNumber = 348
OR e.clsNumber = 352
OR e.clsNumber = 363
OR e.clsNumber = 349
OR e.clsNumber = 372
OR e.clsNumber = 358
OR e.clsNumber = 360
OR e.clsNumber = 412
OR e.clsNumber = 379
OR e.clsNumber = 354
OR e.clsNumber = 330
OR e.clsNumber = 409
OR e.clsNumber = 347
OR e.clsNumber = 369
OR e.clsNumber = 368
OR e.clsNumber = 397
OR e.clsNumber = 365
OR e.clsNumber = 366
OR e.clsNumber = 356
OR e.clsNumber = 295
OR e.clsNumber = 351
OR e.clsNumber = 350
OR e.clsNumber = 322
OR e.clsNumber = 294
OR e.clsNumber = 329
OR e.clsNumber = 374
OR e.clsNumber = 371
OR e.clsNumber = 377
OR e.clsNumber = 326
OR e.clsNumber = 408
OR e.clsNumber = 336
OR e.clsNumber = 319
OR e.clsNumber = 381
OR e.clsNumber = 411
OR e.clsNumber = 357
OR e.clsNumber = 398
OR e.clsNumber = 392
OR e.clsNumber = 324
OR e.clsNumber = 396
OR e.clsNumber = 394
OR e.clsNumber = 395
OR e.clsNumber = 323
OR e.clsNumber = 340
OR e.clsNumber = 367
OR e.clsNumber = 353
OR e.clsNumber = 298
OR e.clsNumber = 384
OR e.clsNumber = 373
OR e.clsNumber = 376
OR e.clsNumber = 341
OR e.clsNumber = 346
OR e.clsNumber = 362
OR e.clsNumber = 383
OR e.clsNumber = 359
OR e.clsNumber = 361
OR e.clsNumber = 303
OR e.clsNumber = 364
OR e.clsNumber = 370
OR e.clsNumber = 304
OR e.clsNumber = 331
OR e.clsNumber = 305
OR e.clsNumber = 410
OR e.clsNumber = 387
OR e.clsNumber = 355
OR e.clsNumber = 297
OR e.clsNumber = 413
OR e.clsNumber = 345
OR e.clsNumber = 344
OR e.clsNumber = 334
OR e.clsNumber = 296
OR e.clsNumber = 332
OR e.clsNumber = 308
OR e.clsNumber = 335
OR e.clsNumber = 378
OR e.clsNumber = 337
OR e.clsNumber = 320
OR e.clsNumber = 407
OR e.clsNumber = 393
OR e.clsNumber = 391
OR e.clsNumber = 390
OR e.clsNumber = 389
OR e.clsNumber = 388
OR e.clsNumber = 342
OR e.clsNumber = 386
OR e.clsNumber = 299
OR e.clsNumber = 375
OR e.clsNumber = 382
OR e.clsNumber = 380
OR e.clsNumber = 306
OR e.clsNumber = 312
OR e.clsNumber = 300
OR e.clsNumber = 301
OR e.clsNumber = 343
OR e.clsNumber = 333
OR e.clsNumber = 338
OR e.clsNumber = 328
OR e.clsNumber = 325
OR e.clsNumber = 339
OR e.clsNumber = 313
OR e.clsNumber = 302
OR e.clsNumber = 309)
ORDER BY SortByLastUser,
SubName,
SubFirstName;
Kevin
[Updated on: Mon, 20 October 2008 20:40] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Nov 26 09:37:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
