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;
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
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)
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
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.
Where 1 = 1 is used normally to add dynamically more predicates
Yes but in this case the query appears to have been entered at the SQL prompt, complete with "WHERE 1=1".
Click Here to Expand Forum to Full Width