This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Aug 19, 2013 1:00 AM by Nicolas.Gasparotto RSS

Performance Problem Again

yxes2013 Newbie
Currently Being Moderated

Hi all,

 

We are encountering performance problem again

 

The batch process deletes 1M rows every night which took 30mins the usual.

But last night (12AM) it took more that 2hrs and hangs.

 

Does it help if I run gather_schena stats regularly when there is constant DELETE on the table?

 

Please help me check our ASH, AWR, ADDM to resolve the issue.

ADDM

https://app.box.com/s/7o734e70aa2m2zg087hf

ASH

https://app.box.com/s/xadlxfk0r5y7jvtxfsz7

AWR

https://app.box.com/s/x8ordka2gcc6ibxatvld

 

Thanks....

 

zxy

  • 1. Re: Performance Problem Again
    sb92075 Guru
    Currently Being Moderated

    answers exist if your are willing to RTFM

     

    http://www.oracle.com/pls/db121/homepage

  • 2. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    I already read that but its hard to interpret

  • 3. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    Hi all,

     

    Our Aix server has 8Gb physical memory. How much SWAP size should I allocate to help resolve the issue?

  • 4. Re: Performance Problem Again
    Asif Muhammad Guru
    Currently Being Moderated

    Hi yxes,

     

    What is your current swap size.

     

    SImple google search will reveal answer to your question.

    Anyways answer to your question is: 8-12 GB would be sufficient.

     

    But I dont think probably that might have been the cause.

     

    What is the SGA_TARGET or MEMORY_TARGET that the database is running on?

     

    Best Regards

  • 5. Re: Performance Problem Again
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    yxes2013 wrote:


    The batch process deletes 1M rows every night which took 30mins the usual.

    But last night (12AM) it took more that 2hrs and hangs.

     

    Processing does not hang -  a stuck CPU leads to a kernel crash. A very visible event.

     

    A process that seems to hang is almost always waiting for something in some form or another. It could be spinning waiting for some kind of lock/latch/mutex/semaphore, or waiting on synchronous calls (like waiting on I/O).

     

    That is the VERY FIRST thing to identify - as a process being slow/hanging is a meaningless symptom that needs to be diagnosed first, before a resolution can be implemented.

  • 6. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    Thanks bill and arm,

     

    This is there core program that takes a long time.

     

            DELETE FROM emp WHERE

    settlement_date<=TO_DATE('08-15-2013','MM-DD-YYYY')

             AND checker_id<=100;

     

    The table containts 3M, and everyday at 12midnight 1M+ rows is deleted. and then the next day will be inserted another 1M+

    How do I know if the table is fragmented?

    How do I speed up the delete?  By recreating the table periodically? create function based index? create partitioning?

     

    Any suggestions please.

  • 7. Re: Performance Problem Again
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    Oh my, not only you refuse to read docs, but also the reports you are providing. All it's quite clear writing out there.

    Surprisingly, you're still in charge.

    Any chance to go to school and learn reading ?

    At a first glance, in few seconds you could see:

    1. It is advised to run SQL Tuning Advisor for the given statement.

    2. Report shows a row lock contention against EMP table and recommands to check the application logic

    3. Report gives you the the blocking session

     

    STOP LOOKING FOR SUPPORT AND SPOON FEEDING OVER THE FORUM.

    If you do not know something, you have choice either to ask around yourself or leave the project for something doable for you (something which does not require reading skill).

     

    Nicolas.

  • 8. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    Ok fine , if it is the logic why was it running ok for a long time? and the performance issue just happened this week?

  • 9. Re: Performance Problem Again
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    yxes2013 wrote:

     

    Ok fine , if it is the logic why was it running ok for a long time? and the performance issue just happened this week?

    According to your thread title, it's not the first time.

    According to your previous posts, it's not the first time neither:

    App Hangs in 3 consecutive days

    Does it help performance?

    AWR, ASH, ADDM

     

    Nicolas.

  • 10. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    Hi Nic,

     

    Can I lock the tables for DELETE? I want the delete to be the priority and no one should be accessing the tables yet.

     

    Thanks

  • 11. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    Hi ARM,

     

    ***What is the SGA_TARGET or MEMORY_TARGET that the database is running on?


    Our server has 8Gb Physical Memory and 8Gb Swap.

     

    What  is the ideal SGA_TARGET and MEMORY_TARGET shouldbe?

     

    Our current setting is:

    ========

    SQL> show parameter memory

     

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    hi_shared_memory_address             integer     0

    memory_max_target                        big integer 5936M

    memory_target                                big integer 5936M

    shared_memory_address                 integer     0

     

    SQL> show parameter sga_

     

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    sga_max_size                         big integer 5936M

    sga_target                               big integer 0

     

    Thanks

  • 12. Re: Performance Problem Again
    sybrand_b Guru
    Currently Being Moderated

    To know the ideal configuration, you would need to consult the various v$*advisor views, discussed in the documentation, you've choosen not to read.

    As we don't have access to your highly sensitive system, we can not do this for you, not even for free.

    I think it is time to hand your manager your resignation. You are not a DBA, and you will never be more than a pest to these forums, abusing them with doc questions many times each day.

     

    -------------

    Sybrand Bakker

    Senior Oracle DBA

  • 13. Re: Performance Problem Again
    yxes2013 Newbie
    Currently Being Moderated

    Are you & SB Twins

     

    Good for you to say that because you have minimal loads/taks in your company and you  are paid high too

    For me, Im so overwhelmed with lots of assignments which include not less than the ff:

    GG, DBV, TDE, Ebiz, MySql, Sqlserver, Db2, Sybase, Foxpro, RMAN, Dataguard, Database security auditing, Database Admin, etc, & etc.

    And I am only paid USD1,500/month. Is this reasonable?

    Why is that all the dba's here  seem not complaining about their tasks?

    Does it mean most of you have minimal assignments with your respective companies and are paid high?

     

    Thanks,

  • 14. Re: Performance Problem Again
    sb92075 Guru
    Currently Being Moderated

    >Why is that all the dba's here  seem not complaining about their tasks?

    I know how to diagnose problems & find solutions that are already documented.

    It is fun solving new mysteries every day.

     

    Your problem is that you lack the willingness to RTFM  and inexperience which shows by all you misguided questions & postings.

1 2 Previous Next

Legend

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