2 Replies Latest reply: Jul 26, 2012 10:26 PM by 892844 RSS

    Cube Performance Issue

    892844
      Hi,

      We are facing a severe peformance issues while viewing the data in the cube either through AWM or directly issuing the SQL on the view (on top of the cube). The cubes are created originally in 11.2.0.1 and later we have migrated to 11.2.0.3 (by taking the XML copy and restoring it). We are using Voyager to analyse the cubes which takes ages to fetch the data. Even a simple select sql on top of the small cube (that is hardly refreshed from 4600 records) is taking around 15 min to get executed.

      Interestingly, the performance is good in 11.2.0.1 when compared to 11.2.0.3. The difference is around 10 to 15 times.

      Not sure whether we are missing anything here, pls can you shed your thoughts on the possible causes/remedy.


      Environment Details
      Database - Oracle 11.2.0.3 on AIX 6.1
      AWM - 11.2.0.2.0A

      rgds,
      Prakash S
        • 1. Re: Cube Performance Issue
          Jameson White
          Hello Prakash,

          Seems you've hit a problem. 11.2.0.3 should have improved performance in your case. Have you filed an SR?

          Please include the following information.

          1) Are you in the same environment? If not how do they differ.
          2) Are the system parameters the same for each database?
          3) Attach a reproducible test case to the SR.
          4) If you have both environments please generate an AWR Report for each. Take a AWR snapshot before and after your tests then generate a report for those snap_id's.
          • 2. Re: Cube Performance Issue
            892844
            Hi Jameson - Thanks for your response.

            We thought that would be the case. Have raised a SR with oracle and they are investigating on it. We have also sent an EIFF file to Oracle for investigation.

            Both the DBs are in the same environment (AIX 6.1) and DBAs have confirmed both the DBs have the same system parameters.

            Even if we keep aside comparing to 11.2.0.1, for some reason 11.2.0.3 seems to be very slow. Even a simple cube (2 Dim and 2 Measures) with 9K records takes around 15 min to get refreshed and it takes ages to view the data.

            Havent generated the AWR report, will see if we can do the same.

            rgds,
            Prakash S