This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 3, 2013 6:57 PM by Maran Viswarayar RSS

Performance issue

969952 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I just entered the sample one... like this the code is having join conditions and all.
  • 6. Re: Performance issue
    JohnWatson Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi All,

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

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

    please let me know.
  • 13. Re: Performance issue
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

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

    Please let me know.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points