5 Replies Latest reply: Dec 28, 2012 12:30 AM by moreajays RSS

    Need to Improve FTS

    moreajays
      Hi Team,

      Recently we have migrated a 11gr2 Single Node prod. DB (ASM/AIX) to 11gr2 RAC 2 node(Linux) using Data Pump , Since then facing below issue

      Query:SELECT C1000000159, C1000000161, C1000000170, C1000002134, C536871133, C1000000151, C1000000000, C1000000001, C1, C3, C6 FROM ARADMIN.T2108 ;
      Table: Contains clob, num rows 5701535 , size 12 GB

      Old DB: plan
      Elapsed Time: 2 Hrs
      ---------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 5705K| 9299M| 346K (2)| 00:18:53 |
      | 1 | TABLE ACCESS FULL| T2108 | 5705K| 9299M| 346K (2)| 00:18:53 |
      ---------------------------------------------------------------------------


      New DB:Plan
      Elapsed Time: 4 Hrs

      ---------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 5701K| 9292M| 366K (1)| 01:13:18 |
      | 1 | TABLE ACCESS FULL| T2108 | 5701K| 9292M| 366K (1)| 01:13:18 |
      ---------------------------------------------------------------------------

      The application team don't want any alteration to this query (Although parallel hint has given 30 min of benefit) as it was responding in 2 Hrs before migration
      We have done below so far :

      1. gather system stats
      2. gather dict stats
      3. gather fixed obj stats
      4. export/ import of old db stats into new
      5. Applied parallel hint giving slight performance by reduction in 30 min.
      6. tried RULE , FIRST_ROWS hints still no luck
      7. Gathered diff types of stats on table (with/ without histogtram)

      It does rowid range scan when queried session longops

      let me know how to get back that same execution plan as old DB

      Thanks,
      Ajay More
      http://moreajays.blogspot.com
        • 1. Re: Need to Improve FTS
          Salman Qureshi
          Hi,
          let me know how to get back that same execution plan as old DB
          You already have same execution plan as your old database. Since execution plan is so simple and same in both versions, you would need to think out of the box.
          First go for session level 10046 trace. Enable trace for session, run your query and then disable the trace after 10 minutes ( no need to run for whole 2 hours).
          TKPROF and share the output here on the forum.

          I suspect that either you are facing some cluster level wait event which is slowing down the query, or, you might be facing "direct path read" wait event (I faced this problem with me, but, in your case, it may not be the issue).

          Also take a AWR snapshot before running the query, and another manual snapshot after you finish the trace and see what are top wait events and are there any buffer waits in TABLESPACE IO STATISTICS section of AWR.

          *please paste your code/outputs enclosed in
           tags to make it readable*
          
          Salman                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: Need to Improve FTS
            moreajays
            Hi,

            wait event is mostly "SQL*Net message from/to client" .
            generating AWR/trace meanwhile


            Thanks,
            Ajay More
            http://moreajays.blogspot.com
            • 3. Re: Need to Improve FTS
              moreajays
              Hi,

              Problem resolved .. by re-gathering sys stats & setting it as old prod using

              exec dbms_stats.set_system_stats( pname => '<PNAME>', pvalue => <value>);


              Thanks,
              Ajay More
              http://moreajays.blogspot.com
              • 4. Re: Need to Improve FTS
                Salman Qureshi
                Problem resolved .. by re-gathering sys stats & setting it as old prod using
                Regathering and setting are 2 different things. Which one helped you? You shold not be setting it same as your previous server if both hardwares have difference in physical configuraiton and speed/number of CPUs etc. Youa re supposed to collect fresh stats which "should" represent your current hardware.

                http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i41857

                If you are setting same like your previous server, it may help you in this query, but may create problems for your overall system.

                Salman
                • 5. Re: Need to Improve FTS
                  moreajays
                  Hi Salman,

                  Yes , It has been taken care .
                  Due to diffrence in CPU speed of old/New DB this problem arised , we have auto sys stats which is also improving query day by day.
                  Just for this particular query which was sev1 for us (One time requirement), we did old db stat setting, However here onwards totally dependent on new sys stats

                  Thanks,
                  Ajay More
                  http://moreajays.blogspot.com

                  Edited by: moreajays on Dec 28, 2012 12:00 PM