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)
