Performance issue while select from view..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Performance issue while select from view..

  1. #1
    Join Date
    Aug 2004
    Location
    bangalore
    Posts
    10

    Performance issue while select from view..

    Hi Oracle Tuners,

    I am facing performance issue while select data from a view. When created view, values are coming from number of materialized view. I am using functions to get column values of view.

    one sample query and plan is as follows: Your help is required...
    00:26:49 SQL> SELECT count(*) from cmp_customer_list_v WHERE 1=1 ORDER BY go_live_date ASC;

    COUNT(*)
    ----------
    722

    Elapsed: 00:00:09.27

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=171 Card=1 Bytes=9
    )

    1 0 SORT (AGGREGATE)
    2 1 VIEW OF 'CMP_CUSTOMER_LIST_V' (VIEW) (Cost=171 Card=885
    Bytes=7965)

    3 2 SORT (UNIQUE) (Cost=171 Card=885 Bytes=41631)
    4 3 UNION-ALL
    5 4 HASH JOIN (Cost=56 Card=884 Bytes=41548)
    6 5 HASH JOIN (Cost=44 Card=884 Bytes=21216)
    7 6 TABLE ACCESS (FULL) OF 'OA_REQUESTS' (TABLE) (
    Cost=26 Card=884 Bytes=7072)

    8 6 MAT_VIEW ACCESS (FULL) OF 'OA_CI_PROJECT_MV' (
    MAT_VIEW) (Cost=17 Card=1695 Bytes=27120)
    9 5 MAT_VIEW ACCESS (FULL) OF 'OA_CI_CUSTOMER_MV' (M
    AT_VIEW) (Cost=12 Card=2261 Bytes=52003)

    10 4 NESTED LOOPS (Cost=112 Card=1 Bytes=83)
    11 10 HASH JOIN (RIGHT ANTI) (Cost=111 Card=1 Bytes=60
    )

    12 11 TABLE ACCESS (FULL) OF 'OA_REQUESTS' (TABLE) (
    Cost=26 Card=1024 Bytes=4096)

    13 11 HASH JOIN (Cost=85 Card=2366 Bytes=132496)
    14 13 MAT_VIEW ACCESS (FULL) OF 'OA_CI_PROJECT_PHA
    SE_MV' (MAT_VIEW) (Cost=43 Card=2517 Bytes=22653)

    15 13 HASH JOIN (Cost=41 Card=1486 Bytes=69842)
    16 15 MAT_VIEW ACCESS (FULL) OF 'OA_CI_PROJECT_S
    TATUS_V_MV' (MAT_VIEW) (Cost=8 Card=1487 Bytes=22305)

    17 15 HASH JOIN (Cost=32 Card=1694 Bytes=54208)
    18 17 MAT_VIEW ACCESS (FULL) OF 'OA_CI_SERVICE
    _MV' (MAT_VIEW) (Cost=14 Card=1183 Bytes=14196)

    19 17 MAT_VIEW ACCESS (FULL) OF 'OA_CI_PROJECT
    _MV' (MAT_VIEW) (Cost=17 Card=1695 Bytes=33900)

    20 10 MAT_VIEW ACCESS (BY INDEX ROWID) OF 'OA_CI_CUSTO
    MER_MV' (MAT_VIEW) (Cost=1 Card=1 Bytes=23)

    21 20 INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (INDEX (U
    NIQUE)) (Cost=0 Card=1)

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool


    You may want to create appropiate INDEXES on the materialized views.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Unfortunately it is very hard to tune an SQL statement without knowing a great deal of background information about the data and the purpose of the query. The execution plan is helpful, although it would be more readable if you could format it in [code] tags to preserve indentation.

    The best thing to try first is to make sure all the tables involved have been analyzed and see what the optimizer makes of it. If you think it is using an inefficient approach, figure out what it's doing wrong and what information it might be missing to cause that.

    Any performance issue you may have is unlikely to be caused by the view itself.

    btw what is the purpose of WHERE 1=1?
    Last edited by WilliamR; 05-28-2006 at 06:03 AM.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    Where 1 = 1 is used normally to add dynamically more predicates

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Yes but in this case the query appears to have been entered at the SQL prompt, complete with "WHERE 1=1".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width