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

    Performance Problem Again

    yxes2013

      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

          answers exist if your are willing to RTFM

           

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

          • 2. Re: Performance Problem Again
            yxes2013

            I already read that but its hard to interpret

            • 3. Re: Performance Problem Again
              yxes2013

              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

                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
                  Billy~Verreynne

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    >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