4 Replies Latest reply: Jun 9, 2012 12:58 AM by Bobby Durrett RSS

    Slow Query Issues Oracle 11GR1

    788793
      Hi All,

      Oracle 11.1.0.6 on AIX 6.1. CPU-8 CORES, Memory - 32G db size - 500G

      Recently we did a OS Hardening(mainly on OS security) followed by upgrade of RAM (from 16G to 32G) in a production environment.

      We restarted the DB but did not restart the server.

      1> Just after the activity our Stored Programs in PL/SQL all running very slow.

      2> Other issues present from before the activity;

      a. High log file switch completion (avg > 140ms)

      b. High waits in db sequential reads, db file scattered read, direct path write temp

      c. Redo log file siizes are 500M, switching every 2 mins without much activity.

      3> data, redo, control, arch all share 2 mount points.

      4> We never run any dbms_stats as we recently went to production.

      5) After running EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL); the Stored Programs were more slow.

      6) ping and tnsping was Ok.

      What should be the action plan to resolve the immediate and long term issues..?
        • 1. Re: Slow Query Issues Oracle 11GR1
          Iordan Iotzov
          user13355115 wrote:
          Hi All,

          Oracle 11.1.0.6 on AIX 6.1. CPU-8 CORES, Memory - 32G db size - 500G

          Recently we did a OS Hardening(mainly on OS security) followed by upgrade of RAM (from 16G to 32G) in a production environment.

          We restarted the DB but did not restart the server.

          1> Just after the activity our Stored Programs in PL/SQL all running very slow.

          2> Other issues present from before the activity;
          It is expected that the upgrades/patches you did (OS and RAM) would not affect the IO times. The bigger RAM should have reduced the need to do IO though. You need address your IO system if you need faster responses there.
          >
          a. High log file switch completion (avg > 140ms)

          b. High waits in db sequential reads, db file scattered read, direct path write temp

          c. Redo log file siizes are 500M, switching every 2 mins without much activity.
          There must be some activity that generates that redo. It is either directly or indirectly (delayed block cleanout) generated by a user. The overall amount of redo (15G per hour) is kind of high, but not extreme for most modern hardware.

          >
          3> data, redo, control, arch all share 2 mount points.

          4> We never run any dbms_stats as we recently went to production.
          Please gather application tables/indexes statistics – they are essential for achieving optimal performance.

          >
          5) After running EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL); the Stored Programs were more slow.

          6) ping and tnsping was Ok.

          What should be the action plan to resolve the immediate and long term issues..?
          Iordan Iotzov
          http://iiotzov.wordpress.com/
          • 2. Re: Slow Query Issues Oracle 11GR1
            jgarry
            user13355115 wrote:
            Hi All,

            Oracle 11.1.0.6 on AIX 6.1. CPU-8 CORES, Memory - 32G db size - 500G

            Recently we did a OS Hardening(mainly on OS security) followed by upgrade of RAM (from 16G to 32G) in a production environment.

            We restarted the DB but did not restart the server.

            1> Just after the activity our Stored Programs in PL/SQL all running very slow.

            2> Other issues present from before the activity;

            a. High log file switch completion (avg > 140ms)

            b. High waits in db sequential reads, db file scattered read, direct path write temp

            c. Redo log file siizes are 500M, switching every 2 mins without much activity.

            3> data, redo, control, arch all share 2 mount points.

            4> We never run any dbms_stats as we recently went to production.
            Are you sure the default job isn't running?

            >
            5) After running EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL); the Stored Programs were more slow.

            6) ping and tnsping was Ok.

            What should be the action plan to resolve the immediate and long term issues..?
            Be sure all OS patches are there, and all I/O is configured correctly. Pay particular attention to async or cio or whatever special io your OS uses.

            Use your OS utilities to check memory utilization and cpu usage.

            Figure out what is slow. Get an hour long AWR report if you are licensed, or statspack if not.

            If licensed, use dbconsole performance screen to see what the top activity is.

            Use logminer to see what is in all that redo.
            • 3. Re: Slow Query Issues Oracle 11GR1
              Nikolay Savvinov
              Hi,

              first of all, things can't be just slow. They're always slow compared to something. What are you comparing your performance to? What is your frame of reference? Do you have AWR data from before to provide quantitative evidence of performance degradation? Do you have a UAT database with similar properties and comparable workload so that you could compare against it? Or are you just comparing your actual performance to your previous expectations (and if so, how do you know your expectations are reasonable)?

              Second of all, run an AWR report and post key sections (workload profile, top timed events, top SQL by elapsed time/CPU) here.

              Best regards,
              Nikolay
              • 4. Re: Slow Query Issues Oracle 11GR1
                Bobby Durrett
                Can you run your unix's version of this command?

                sar -d 5 1

                Do this while things are running and post the results here.

                Or give the average milliseconds for your disk waits like db file sequential read from an AWR report during a problem period.

                Should be less than 20 ms per read ideally.

                - Bobby