9 Replies Latest reply: May 17, 2012 10:10 AM by Nikolay Savvinov RSS

    Oracle Performance

    901509
      I am using oracle 11 G enterprise edition
      While monitoring performance in oracle we came across query

      UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS=:B2 WHERE STEP_ID=:B1

      which has slowed down Oracle's performance, the client has complained that the system was slow at that query run time.

      Can anyone please explain this query
        • 1. Re: Oracle Performance
          713555
          What did the plan say when you took it? you did get a plan didnt you... tell me you took a plan.

          .
          .
          .
          you didnt get a plan. did you?

          get a plan. show us the plan.
          • 2. Re: Oracle Performance
            jgarry
            deebee_eh wrote:
            What did the plan say when you took it? you did get a plan didnt you... tell me you took a plan.

            .
            .
            .
            you didnt get a plan. did you?

            get a plan. show us the plan.
            You sound like you think that is something he wrote. Could it possibly be something the Oracle default gee-whiz-our-self-tuning-database-is-so-great-it-always-works-perfectly-no-dba-will-ever-be-needed-again management software does wrong? Perhaps you have it on your system and you can look at the plan yourself? What if it is a table by rowid update? What would you do with that information?
            • 3. Re: Oracle Performance
              Mark Malakanov (user11181920)
              I think this table (MGMT_JOB_EXECUTION) is updated by DB Console or Agent.
              You can stop them if you do not use them.

              If it is updated too frequently, it may be that DB Console or Configuration Manager is misconfigured.

              Edited by: user11181920 on May 15, 2012 4:13 PM
              • 4. Re: Oracle Performance
                Nikolay Savvinov
                898506 wrote:
                I am using oracle 11 G enterprise edition
                While monitoring performance in oracle we came across query

                UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS=:B2 WHERE STEP_ID=:B1

                which has slowed down Oracle's performance, the client has complained that the system was slow at that query run time.

                Can anyone please explain this query
                Hi,

                1) can you quantitatively justify the users' claim of poor performance? I.e. give an example of something that normally runs within X seconds, but was taking Y (Y >> X) during that period?
                2) can you link the UPDATE statement to the system's poor performance? Is it robbing other SQL of some valuable resource (CPU, disk I/O bandwidth etc.)? Is it causing some horrible contention -- and if yes, what kind of contention?


                Best regards,
                Nikolay
                • 5. Re: Oracle Performance
                  901509
                  thanks for your prompt reply

                  I am actually new to database. i don't know anything about the first point you said. but the 2nd point you told is correct, it is using lot of resources at that time and not allowing our application SQL to run
                  • 6. Re: Oracle Performance
                    901509
                    Thanks for your prompt reply sir.

                    I am actually new to Oracle, can you please guide me to stop these unwanted updates by DB Console or Agent.
                    • 7. Re: Oracle Performance
                      713555
                      Has the box become so badly CPU bound that nothing else can run and youve narrowed it down to that one update statement? how did you do come to that conclusion?
                      • 8. Re: Oracle Performance
                        Billy~Verreynne
                        898506 wrote:
                        I am using oracle 11 G enterprise edition
                        While monitoring performance in oracle we came across query

                        UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS=:B2 WHERE STEP_ID=:B1

                        which has slowed down Oracle's performance, the client has complained that the system was slow at that query run time.
                        How do you know that this specific UPDATE SQL is the cause of the performance problem? Just because it was running around the same time does not mean it is too blame for the performance issue. That would be like saying that because the kettle was boiling at the same time of the performance problem, the kettle caused the performance problem. Totally nonsensical.

                        The MGMT_JOB_EXECUTION table is owned by the SYSMAN schema (part of the management s/w suite for Oracle). The primary key for this table is the column STEP_ID.

                        Which means that this so-called performance intensive and performance hurting update statement was a shareable SQL cursor doing a single row update via the primary key of that table. Calling this specific SQL cursor a performance problem would be mostly bollocks. The SQL is doing everything by the book ito being shareable, using bind variables, and hitting the table with the absolute minimum amount of I/O needed in order to update the relevant row.

                        The very first step in dealing with any problem in software engineering - from performance to design - is to ACCURATELY IDENTIFY THE PROBLEM.

                        Detecting a performance issue not an accurate problem description. It is a symptom. It needs further investigation and detailed analysis. And only once the problem is fully understood, is one in a position to determine the cause of that problem and determine a resolution for that problem.
                        • 9. Re: Oracle Performance
                          Nikolay Savvinov
                          Hi,

                          my question wasn't technical. People are very often confused about database performance problems, that's why it's important to establish what exactly the problem was and how it was identified. So, in your case, how do you know that database performance degraded -- user complaints, AWR report, explosive growth of CPU usage, other? What was the extent of the problem -- was it affecting the entire database, one schema, or an isolated report? And quantitatively, how bad was the problem?

                          The second point is even more important. You didn't really clarify how do you know that there is a connection between the global performance degradation and the UPDATE statement? The mere fact that it was running at the same time and that it made it to the top-SQL of an AWR report doesn't mean anything. Is there a real evidence that this statement alone consumed so much resources that other SQL had to wait for it? And if yes, what resource was it -- CPU, I/O, other?


                          Best regards,
                          Nikolay