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 Go to next message
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 #354549 is a reply to message #354543] Mon, 20 October 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support


Regards
Michel
Re: ORA-03113 after performance tuning [message #354694 is a reply to message #354543] Mon, 20 October 2008 20:38 Go to previous message
Kevin Meade
Messages: 2101
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

Previous Topic: Slow query - Please help.
Next Topic: Query running more timeand then time out message
Goto Forum:
  


Current Time: Tue Dec 06 15:52:55 CST 2016

Total time taken to generate the page: 0.08792 seconds