1 2 Previous Next 26 Replies Latest reply: Jan 3, 2013 8:57 PM by Maran Viswarayar RSS

    Performance issue

    969952
      Hi All,

      I Have created materialized views (MV) in one of the schemas. These MV's are occupying much CPU space. so it's effecting performance. SELECT statement contains JOIN conditions as well. Please have a look and give me your suggestions to avoid the performance issue and increase the instance efficiency.
      create materialized view VIEW1
      nocache
      logging
      nocompress
      noparallel
      build immediate
      refresh FORCE on demand
      with Primary key
      as
      <select Statement >
      < this select statement is having join conditions. >
      Thanks.
        • 1. Re: Performance issue
          BluShadow
          966949 wrote:
          Hi All,

          I Have created materialized views (MV) in one of the schemas. These MV's are occupying much CPU space.
          Materialized views do not 'occupy' CPU.
          CPU is used by the Oracle processes to deal with processing the data read through materialized views as it does with any query.
          so it's effecting performance. SELECT statement contains JOIN conditions as well. Please have a look and give me your suggestions to avoid the performance issue and increase the instance efficiency.
          create materialized view VIEW1
          nocache
          logging
          nocompress
          noparallel
          build immediate
          refresh FORCE on demand
          with Primary key
          as
          <select Statement >
          < this select statement is having join conditions. >
          You say you have a performance issue with your materialized view, yet you leave out the most crucial part of it... the query.

          Firstly read this:
          {message:id=9360002}

          and then read the two threads linked to by this FAQ: {message:id=9360003}

          When you've done that, post the required information so that people can help you.
          • 2. Re: Performance issue
            EdStevens
            966949 wrote:
            Hi All,

            I Have created materialized views (MV) in one of the schemas. These MV's are occupying much CPU space. so it's effecting performance. SELECT statement contains JOIN conditions as well. Please have a look and give me your suggestions to avoid the performance issue and increase the instance efficiency.
            create materialized view VIEW1
            nocache
            logging
            nocompress
            noparallel
            build immediate
            refresh FORCE on demand
            with Primary key
            as
            <select Statement >
            < this select statement is having join conditions. >
            Thanks.
            I have a car. It is blue. It doesn't run well. Please tell me what I need to do to make it run well.

            Come now. Please re-read your message and ask yourself how anyone could be expected to provide any useful information based on what you've written. You don't even show us the actual SELECT that implements the MV, much less any information about the tables, their indexes, etc. etc.
            • 3. Re: Performance issue
              969952
              Hi,

              Please find the below code.
              drop materialized view mv1;
              
              create materialized view mv1 
              nocache
              nologging
              nocompress
              noparallel
              build immediate
              refresh FORCE on demand
              with primary key
              as
              select a.empno,
              a.mgrid,
              b.dname,
              a.locid,
              c.salid,
              c.gradeid,
              c.uom,
              a.doj,
              a.qty,
              b.slno,
              upper(loc.locname) lname
              
              from emp e, dept d, salgrade c,location loc
              
              where e.id = b.id
              and a.mid = b.mid
              and b.cid = c.cid;
              it's taking more time to refresh the data. Can you please let me know is it possible to write a procedure using ADVISOR/ or any other way?

              After reviewing many docs as per your suggestions reviewed the docs for DBMS_ADVISOR.tune_mview and some other. But I am not able to write any.

              i have many Materialized views under the same schema. I want to write a package to keep all these views inside the package with best performance ( which were tuned well)

              Please help me out to write a Package or any other process for the above requirement.

              Thanks.
              • 4. Re: Performance issue
                JohnWatson
                This is mot the code you are running. It won't work because the aliases don't match:
                from emp e, dept d, salgrade c,location loc
                 
                where e.id = b.id
                and a.mid = b.mid
                and b.cid = c.cid;
                you cannot expect people to separate fact and fiction, man.
                • 5. Re: Performance issue
                  969952
                  I just entered the sample one... like this the code is having join conditions and all.
                  • 6. Re: Performance issue
                    JohnWatson
                    Let me get this straight: you want help with tuning one SQL, and so you have presented a completely different SQL.
                    • 7. Re: Performance issue
                      969952
                      Am sorry . I am trying to tune the Materialized view which is created on multiple tables by using JOIN conditions. But the performance is not good. After reviewing many docs came to know need to write the MV using ADVISOR or EXPLAIN_REFRESH... But am not able to implement the program using these packages/procedures.

                      for this I am requesting your suggestions.

                      Thanks.
                      • 8. Re: Performance issue
                        sb92075
                        966949 wrote:
                        Am sorry . I am trying to tune the Materialized view which is created on multiple tables by using JOIN conditions. But the performance is not good. After reviewing many docs came to know need to write the MV using ADVISOR or EXPLAIN_REFRESH... But am not able to implement the program using these packages/procedures.

                        for this I am requesting your suggestions.

                        Thanks.
                        HOW To Make TUNING request
                        SQL and PL/SQL FAQ
                        • 9. Re: Performance issue
                          969952
                          Hi,

                          I have done the following steps.

                          1. Created MV log as follows .
                          create materialized view log on test123 with rowid,sequence;
                          2. Created MV
                          CREATE MATERIALIZED VIEW MV_1
                          BUILD IMMEDIATE
                          REFRESH  ON DEMAND
                          ENABLE QUERY REWRITE
                          as
                          <SELECT Statement>
                          3. execute dbms_mview.refresh('test123');

                          but getting the below error
                          Error report:
                          ORA-06550: line 1, column 27:
                          PLS-00357: Table,View Or Sequence reference 'TEST123' not allowed in this context
                          ORA-06550: line 1, column 7:
                          PL/SQL: Statement ignored
                          06550. 00000 -  "line %s, column %s:\n%s"
                          *Cause:    Usually a PL/SQL compilation error.
                          *Action:
                          Please have a look and help me out.

                          Thanks.
                          • 10. Re: Performance issue
                            Fran
                            execute dbms_mview.refresh('test123');
                            you are refreshing the table, not the view. try:

                            execute dbms_mview.refresh('MV_1');
                            • 11. Re: Performance issue
                              969952
                              Hi All,

                              Thanks a lot.. got some idea and working on the scripts.
                              • 12. Re: Performance issue
                                969952
                                Hi,

                                Is it possible to keep all the MV's in a single package/Procedure?

                                please let me know.
                                • 13. Re: Performance issue
                                  sb92075
                                  966949 wrote:
                                  Hi,

                                  Is it possible to keep all the MV's in a single package/Procedure?

                                  please let me know.
                                  yes, but what is advantage to do so?
                                  • 14. Re: Performance issue
                                    969952
                                    Hi,

                                    Many redo logs are generating here. How can I avoid this ?

                                    Please let me know.
                                    1 2 Previous Next