4 Replies Latest reply: Nov 16, 2012 10:46 AM by Voltaire RSS

    Parallel hint in OBIEE 11g

    AnandKumar
      Dear All,

      I am new to OBIEE. Can any one tell me how to add parallel hint in 11g. Is Oracle BI Presentation Service ignores any DB Hint added to query while parsing. If yes, how to overcome this. We need to add parallel hint in few report to make things faster, i do not want to touch the table level hints as it is affecting my performance.

      I have gone through oracle doc ID 823193.1

      Thanks and Regards,
      Anand.
        • 1. Re: Parallel hint in OBIEE 11g
          Voltaire
          Hi Anand,

          Hint at report level can be achieved using evaluate as:
          Evaluate Function to be used as:
          EVALUATE(‘/*+PARALLEL(table,degree,4)*/%1′,Facts.”time”)  

          Change the table in the above to the appropriate table alias.
          Note: if a physical table has hint this will override the above statement.

          Hope this helps.

          Regards,
          Jay

          Edited by: Voltaire on Nov 15, 2012 8:57 AM
          • 2. Re: Parallel hint in OBIEE 11g
            AnandKumar
            Hi Jay,

            Thanks for your reply.

            can you please give a practical example by working in a sqlplus. I tried and got below error. Please help.
            SQL>  select * from t;
            
                     N
            ----------
                     1
            
            SQL>  select n from t;
            
                     N
            ----------
                     1
            
            SQL> select /*+PARALLEL (t,2)*/ n from t;
            
                     N
            ----------
                     1
            
            SQL>  select EVALUATE('/*+PARALLEL (t,2)*/',t."n") from t;
             select EVALUATE('/*+PARALLEL (t,2)*/',t."n") from t
                                                   *
            ERROR at line 1:
            ORA-00904: "T"."n": invalid identifier
            Regards,
            Anand.
            • 3. Re: Parallel hint in OBIEE 11g
              Srini VEERAVALLI
              You might using Subject area objects and database don't recognize.. I would suggest to use physical table name.
              • 4. Re: Parallel hint in OBIEE 11g
                Voltaire
                Hi Anand,

                The Alias is not from the query.

                You need to get it from the OBI report physical SQL. lets say when you get the physical sql from Administration --> Managed Sessions --> View Log

                select T5686.day as c1
                from
                tableowner.tablename T5686 /* tablename */
                where xxxxxx

                Then your evaluate function will look like:

                EVALUATE(‘/*+PARALLEL(T5686,degree,4)*/%1′,tablename.”day”)

                Hope this helps.

                Regards,
                Jay

                Edited by: Voltaire on Nov 16, 2012 8:46 AM