This discussion is archived
1 2 3 4 Previous Next 54 Replies Latest reply: Jan 15, 2013 4:29 PM by jgarry Go to original post RSS
  • 45. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Thanks,

    But this is the not cause of hanging because the it occurred in the privious month.

    What is see now is lots of:
    Errors in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_13131.trc:
    ORA-04021: timeout occurred while waiting to lock object JSCUS.FUNC_GET_MTL_SRC
    Mon Jan  7 16:04:08 2013
    Completed checkpoint up to RBA [0x21567.2.10], SCN: 0x056e.75097ea6
    Mon Jan  7 16:13:14 2013
    Errors in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_16293.trc:
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    Mon Jan  7 16:13:30 2013
    This only occured affter I increased my redolog from 10Mb to 100Mb and undo retention from 1800 to 3600.
  • 46. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    From Dec 2012 to Current, I got all this ORA- in alert logs.
    ORA-01555 caused by SQL statement below (Query Duration=3824 sec, SCN: 0x056e.1b29d28a):
    ORA-01555 caused by SQL statement below (Query Duration=3276 sec, SCN: 0x056e.1b2bab0a):
    ORA-01555 caused by SQL statement below (Query Duration=4937 sec, SCN: 0x056e.1b29cf94):
    ORA-12012: error on auto execute of job 812
    ORA-01476: divisor is equal to zero
    ORA-06512: at "APPS.ORACLE_BEGINNING_BALANCES", line 22
    ORA-06512: at line 1
    ORA-01555 caused by SQL statement below (Query Duration=2735 sec, SCN: 0x056e.1c70f997):
    ORA-01555 caused by SQL statement below (Query Duration=2709 sec, SCN: 0x056e.1c70feeb):
    ORA-01555 caused by SQL statement below (Query Duration=2235 sec, SCN: 0x056e.1c712dd6):
    ORA-01555 caused by SQL statement below (Query Duration=2540 sec, SCN: 0x056e.1c7115ce):
    ORA-01555 caused by SQL statement below (Query Duration=2580 sec, SCN: 0x056e.1c71138f):
    ORA-01555 caused by SQL statement below (Query Duration=2682 sec, SCN: 0x056e.1c7105ea):
    ORA-01555 caused by SQL statement below (Query Duration=2715 sec, SCN: 0x056e.1c70feec):
    ORA-01555 caused by SQL statement below (Query Duration=2586 sec, SCN: 0x056e.1c71135c):
    ORA-01555 caused by SQL statement below (Query Duration=2650 sec, SCN: 0x056e.1c710a31):
    ORA-01555 caused by SQL statement below (Query Duration=2781 sec, SCN: 0x056e.1c70e8d5):
    ORA-01555 caused by SQL statement below (Query Duration=2686 sec, SCN: 0x056e.1c7105ea):
    ORA-01555 caused by SQL statement below (Query Duration=2842 sec, SCN: 0x056e.1c70dcb1):
    ORA-01555 caused by SQL statement below (Query Duration=2611 sec, SCN: 0x056e.1c7110dd):
    ORA-01555 caused by SQL statement below (Query Duration=2654 sec, SCN: 0x056e.1c710a31):
    ORA-01555 caused by SQL statement below (Query Duration=2751 sec, SCN: 0x056e.1c70f9a5):
    ORA-01555 caused by SQL statement below (Query Duration=2708 sec, SCN: 0x056e.1c71006e):
    ORA-01555 caused by SQL statement below (Query Duration=2817 sec, SCN: 0x056e.1c70e1a5):
    ORA-12012: error on auto execute of job 812
    ORA-01476: divisor is equal to zero
    ORA-06512: at "APPS.ORACLE_BEGINNING_BALANCES", line 22
    ORA-06512: at line 1
    ORA-12012: error on auto execute of job 812
    ORA-01476: divisor is equal to zero
    ORA-06512: at "APPS.ORACLE_BEGINNING_BALANCES", line 22
    ORA-06512: at line 1
    ORA-01555 caused by SQL statement below (Query Duration=3959 sec, SCN: 0x056e.2c69678e):
    ORA-01555 caused by SQL statement below (Query Duration=3652 sec, SCN: 0x056e.2c6fba6d):
    ORA-01555 caused by SQL statement below (Query Duration=3822 sec, SCN: 0x056e.2c6f5ee5):
    ORA-01555 caused by SQL statement below (Query Duration=4050 sec, SCN: 0x056e.2c6eef35):
    ORA-01555 caused by SQL statement below (Query Duration=4317 sec, SCN: 0x056e.2c6c0000):
    ORA-01555 caused by SQL statement below (Query Duration=4185 sec, SCN: 0x056e.2c6eef35):
    ORA-01555 caused by SQL statement below (Query Duration=4436 sec, SCN: 0x056e.2c6c7eaf):
    ORA-01555 caused by SQL statement below (Query Duration=4265 sec, SCN: 0x056e.2c6fbd9b):
    ORA-01555 caused by SQL statement below (Query Duration=4493 sec, SCN: 0x056e.2c6f5ccb):
    ORA-01555 caused by SQL statement below (Query Duration=4475 sec, SCN: 0x056e.2c700a46):
    ORA-01555 caused by SQL statement below (Query Duration=4548 sec, SCN: 0x056e.2c6fbb67):
    ORA-01555 caused by SQL statement below (Query Duration=4994 sec, SCN: 0x056e.2c6bfe87):
    ORA-12012: error on auto execute of job 812
    ORA-01476: divisor is equal to zero
    ORA-06512: at "APPS.ORACLE_BEGINNING_BALANCES", line 22
    ORA-06512: at line 1
    ORA-12012: error on auto execute of job 812
    ORA-01476: divisor is equal to zero
    ORA-06512: at "APPS.ORACLE_BEGINNING_BALANCES", line 22
    ORA-06512: at line 1
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_305.trc.
    ORA-01555 caused by SQL statement below (Query Duration=2593 sec, SCN: 0x056e.2ea897fe):
    ORA-01555 caused by SQL statement below (Query Duration=3 sec, SCN: 0x056e.2ea8767d):
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27984.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27962.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27960.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27960.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27960.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27962.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27960.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27980.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27980.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_27982.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_29920.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_29926.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_30024.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_29938.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32697.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32721.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32721.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32633.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32693.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32693.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32697.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32693.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32693.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_32719.trc.
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_640.trc.
    ORA-01555 caused by SQL statement below (Query Duration=1355891432 sec, SCN: 0x056e.45167aa1):
    ORA-01555 caused by SQL statement below (Query Duration=2812 sec, SCN: 0x056e.4539bbfe):
    ORA-01555 caused by SQL statement below (Query Duration=2821 sec, SCN: 0x056e.4539bbfe):
    ORA-01555 caused by SQL statement below (Query Duration=2947 sec, SCN: 0x056e.4539b19a):
    ORA-01555 caused by SQL statement below (Query Duration=3064 sec, SCN: 0x056e.4539bbfe):
    ORA-01555 caused by SQL statement below (Query Duration=3192 sec, SCN: 0x056e.4539b483):
    ORA-01555 caused by SQL statement below (Query Duration=3417 sec, SCN: 0x056e.4539b19f):
    ORA-01555 caused by SQL statement below (Query Duration=1355903502 sec, SCN: 0x056e.45490633):
    ORA-01555 caused by SQL statement below (Query Duration=3568 sec, SCN: 0x056e.4539b1bd):
    ORA-01555 caused by SQL statement below (Query Duration=3624 sec, SCN: 0x056e.4539adfd):
    ORA-01555 caused by SQL statement below (Query Duration=3785 sec, SCN: 0x056e.4539a811):
    ORA-01555 caused by SQL statement below (Query Duration=5960 sec, SCN: 0x056e.45e76ef1):
    ORA-01555 caused by SQL statement below (Query Duration=3767 sec, SCN: 0x056e.45f8367c):
    ORA-01555 caused by SQL statement below (Query Duration=3832 sec, SCN: 0x056e.45f8416d):
    ORA-01555 caused by SQL statement below (Query Duration=4071 sec, SCN: 0x056e.45f7f90a):
    ORA-01555 caused by SQL statement below (Query Duration=3947 sec, SCN: 0x056e.45f83662):
    ORA-01555 caused by SQL statement below (Query Duration=4272 sec, SCN: 0x056e.45f809eb):
    ORA-01555 caused by SQL statement below (Query Duration=4443 sec, SCN: 0x056e.45f809a4):
    ORA-01555 caused by SQL statement below (Query Duration=2837 sec, SCN: 0x056e.478f5dd5):
    ORA-01555 caused by SQL statement below (Query Duration=3071 sec, SCN: 0x056e.478f55fc):
    ORA-01555 caused by SQL statement below (Query Duration=4147 sec, SCN: 0x056e.47a03e98):
    ORA-01555 caused by SQL statement below (Query Duration=3369 sec, SCN: 0x056e.4803f3aa):
    ORA-12571: TNS:packet writer failure
    ORA-01555 caused by SQL statement below (Query Duration=1356271135 sec, SCN: 0x056e.4aa31485):
    ORA-01555 caused by SQL statement below (Query Duration=1356271135 sec, SCN: 0x056e.4aa15ade):
    ORA-01555 caused by SQL statement below (Query Duration=5380 sec, SCN: 0x056e.4a79dde8):
    ORA-01555 caused by SQL statement below (Query Duration=4342 sec, SCN: 0x056e.4a8ad054):
    ORA-01555 caused by SQL statement below (Query Duration=3829 sec, SCN: 0x056e.4a9b41f8):
    ORA-01555 caused by SQL statement below (Query Duration=3229 sec, SCN: 0x056e.4aa0872a):
    ORA-01555 caused by SQL statement below (Query Duration=2636 sec, SCN: 0x056e.4aa2c077):
    ORA-01555 caused by SQL statement below (Query Duration=6223 sec, SCN: 0x056e.4a7a0bdb):
    ORA-01555 caused by SQL statement below (Query Duration=2732 sec, SCN: 0x056e.4c5205a4):
    ORA-01555 caused by SQL statement below (Query Duration=4145 sec, SCN: 0x056e.4c311b27):
    ORA-01555 caused by SQL statement below (Query Duration=1356600538 sec, SCN: 0x056e.4c8b6efb):
    ORA-01555 caused by SQL statement below (Query Duration=1356604175 sec, SCN: 0x056e.4ce98c25):
    ORA-01555 caused by SQL statement below (Query Duration=1356610521 sec, SCN: 0x056e.4d8c44c0):
    ORA-20101: Begin Snapshot Id specified does not exist for this database/instance
    ORA-06512: at line 21
    ORA-01555 caused by SQL statement below (Query Duration=2745 sec, SCN: 0x056e.526e0179):
    ORA-01555 caused by SQL statement below (Query Duration=2913 sec, SCN: 0x056e.66a6f853):
    ORA-01555 caused by SQL statement below (Query Duration=2808 sec, SCN: 0x056e.66a744b1):
    ORA-01555 caused by SQL statement below (Query Duration=2892 sec, SCN: 0x056e.66a73411):
    ORA-01555 caused by SQL statement below (Query Duration=2812 sec, SCN: 0x056e.66a744b1):
    ORA-01555 caused by SQL statement below (Query Duration=5432 sec, SCN: 0x056e.71ff65ff):
    ORA-01555 caused by SQL statement below (Query Duration=5328 sec, SCN: 0x056e.72032ee6):
    ORA-01555 caused by SQL statement below (Query Duration=5773 sec, SCN: 0x056e.7200e3c4):
    ORA-01555 caused by SQL statement below (Query Duration=5817 sec, SCN: 0x056e.7200e409):
    ORA-01555 caused by SQL statement below (Query Duration=5931 sec, SCN: 0x056e.72015bf1):
    ORA-01555 caused by SQL statement below (Query Duration=6313 sec, SCN: 0x056e.7200e513):
    ORA-01555 caused by SQL statement below (Query Duration=6783 sec, SCN: 0x056e.71fb3ffa):
    ORA-01555 caused by SQL statement below (Query Duration=1357177631 sec, SCN: 0x056e.7266d745):
    ORA-01555 caused by SQL statement below (Query Duration=1357186279 sec, SCN: 0x056e.7274bee7):
    ORA-1184 signalled during: alter database add logfile group 3 ('/u02/oracle/o...
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-04021: timeout occurred while waiting to lock object JSCUS.FUNC_GET_MTL_SRC
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-01555 caused by SQL statement below (Query Duration=1357631213 sec, SCN: 0x056e.7524f39e):
    ORA-01555 caused by SQL statement below (Query Duration=1357631213 sec, SCN: 0x056e.7524ba7e):
    ORA-01555 caused by SQL statement below (Query Duration=1357631819 sec, SCN: 0x056e.75252dd6):
    ORA-01555 caused by SQL statement below (Query Duration=1357631819 sec, SCN: 0x056e.75259756):
    ORA-12570: TNS:packet reader failure
    ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23
    ORA-01555 caused by SQL statement below (Query Duration=6821 sec, SCN: 0x056e.753bacb4):
    ORA-000060: Deadlock detected. More info in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_18933.trc.
    Thanks,
  • 47. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    If I sort it unique I got 9 types:


    ORA-000060:
    ORA-01476:
    ORA-01555
    ORA-06512:
    ORA-1184
    ORA-12012:
    ORA-12570:
    ORA-12571:
    ORA-20101:
  • 48. Re: Performance Issue
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    If I sort it unique I got 9 types:


    ORA-000060:
    ORA-01476:
    ORA-01555
    ORA-06512:
    ORA-1184
    ORA-12012:
    ORA-12570:
    ORA-12571:
    ORA-20101:
    Now that is an IMPRESSIVE collection of errors.
    I am seriously surprised anything worthwhile is actually produced by the application.
  • 49. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    You might want to start a new thread with those trace files. Do you have some kind of support for that func? Maybe some apps forum?
  • 50. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Thanks,

    I am raising SR to Oracle Support. But I dont know which specific product is has error. I log it as "Order Management" issue.
  • 51. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    $ oerr ora 6512
    06512, 00000, "at %sline %s"
    // *Cause:   Backtrace message as the stack is unwound by unhandled
    // exceptions.
    // *Action:  Fix the problem causing the exception or write an exception
    // handler for this condition. Or you may need to contact your
    // application administrator or DBA.
  • 52. Re: Performance Issue
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    yxes2013 wrote:

    Is there no suggestions to check the V$LOCKS and V$BLOCKERS? I am suspecting that there is some process locking the resources
    hence all the programs goes slow. But it does not affect those users who are doing transactional entry (data encoding).

    How about a defective hardware? like network cards, disk controllers, etc. Is there a command in linux that show hardware error?
    You are running a very old version of Oracle, and hammering it with massively inefficient SQL.
    Every problem you have got is probably a side effect of intermittent resource starvation caused by the SQL.

    Stop looking for knobs to twiddle - fix the big obvious problems so that you can see if there's anything left afterwards that really needs fixing. You're doing the equivalent of putting diesel fuel into a 777 then asking if you need to inflate the tyres a bit more to help it take off.

    If your company is losing P5,000,000 per day because of these performance problems then they should think about spending P{thousands} to get them fixed quickly.


    Quick and Dirty hack: maybe, if you're lucky, your plans have gone bad because you haven't collected stats recently. If you want to to a special stats collection, though, make sure you know how to save current stats first and how to reload them if necessary. To check the state of your stats, you can check the last_analyzed column on dba_tables and dba_indexes.


    Regards
    Jonathan Lewis
  • 53. Re: Performance Issue
    yxes2013 Newbie
    Currently Being Moderated
    Thanks Jon,

    We had scheduled a gather stats for all schemas in the database every weekend since the start of implementation.

    Even if there is only 1 program running and there are no CPU contention since we have 12 core, still the problem persist(on some occasion within the day like in the afternoon)
    BUt these programs was running good earlier.

    The programs running slow on a certain days are the same program running good on other days.

    I can not change the logic of the program, but only to add more indexes. Can you help identify which columns need indexing based on the statspack or explain plan?

    Thanks,
  • 54. Re: Performance Issue
    jgarry Guru
    Currently Being Moderated
    If you have only one program running, and sometimes it runs fast and other times it runs slow, there are a couple of main reasons:

    1. The plan changes.

    2. The data asked for is different.

    These are not mutually exclusive (see the rainy Monday example, bind peeking).

    If you are asking for similar volumes of data, you might be on the cusp of a plan change - in other words, asking for last weeks data might have one plan, while this weeks data might be out of collected statistics range and Oracle may use strange defaults instead. (There are whole books written about the optimizer, I'd recommend Jonathan's, and he's posted much better explanations of these types of issues on his blog and in this forum). If this is the type of problem that is happening, you might want to look in the docs for Plan Stability.

    Simply adding more indices may or may not be a good idea, depending on whether that is the main problem. You should follow the instructions for posting a tuning request with plans for the good and bad times.

    Also, there's never only one program running, even when Oracle is bored.
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points