Hi ,
Below SQL is executed for 4hours in user's db :
SELECT * FROM ( SELECT Lovalias.*,rownum Rno FROM (SELECT * FROM (
SELECT A.* FROM SMVW_USER_ACCOUNTS A, STTM_CUST_ACCOUNT B WHERE
A.CUST_AC_NO=B.CUST_AC_NO(+) AND
NVL(B.RECORD_STAT,:"SYS_B_0")<>:"SYS_B_1" ) WHERE (CUST_AC_NO) LIKE
:1 ORDER BY :"SYS_B_2" ) Lovalias ) WHERE Rno > :"SYS_B_3" AND
Rno <= :"SYS_B_4" DDLs of view used are as below :
CREATE OR REPLACE VIEW SMVW_USER_ACCOUNTS AS
(
select CUST_NO,
CUST_AC_NO,
BRANCH_CODE,
ACCOUNT_TYPE,
ACCOUNT_CLASS,
ACCOUNT_CAT
FROM (select cust_no,
cust_ac_no,
branch_code,
'A' ACCOUNT_TYPE,
account_class,
'A' ACCOUNT_CAT
from sttm_cust_account
where record_stat = 'O'
UNION (SELECT B.CUSTOMER_ID,
B.ACCOUNT_NUMBER,
B.BRANCH_CODE,
'L' ACCOUNT_TYPE,
' ',
'L'
FROM CLTB_ACCOUNT_MASTER B
WHERE B.PRODUCT_CODE IN
(SELECT PRODUCT_CODE
FROM SMVW_USER_PRODUCTS
WHERE USER_ID = GLOBAL.USER_ID)))
where cust_no in
(select distinct s.customer_no
from sttm_customer s, Smtb_User u, STTM_BANK b
WHERE 1 = 1
AND u.User_Id = Global.User_Id
AND ((Nvl(s.group_code, '###') = '###') OR
s.group_code IN
(SELECT GROUP_CODE
FROM stvws_user_group
WHERE USER_ID = Global.User_Id))
AND ((Nvl(b.Ac_Restr_Apply, 'N') = 'N') OR (Nvl(s.Staff,'N') = 'N') OR
(Nvl(u.Staff_Ac_Restr, 'N') = 'N') OR
((s.Staff = 'Y') AND
Nvl(s.Customer_No, '###') = Nvl(u.Customer_No, '#*#'))))); Below is view of STVW_USER_GROUP used in smvw_user_account view :
CREATE OR REPLACE VIEW FLEXLIVE.STVW_USER_GROUP AS
(SELECT a.user_id
, b.group_code
FROM smtbs_user a
, sttms_group_code b
WHERE a.group_code_allowed = 'D' AND b.group_type in ('C','I') AND b.auth_stat = 'A')
MINUS
SELECT a.user_id
, b.group_code
FROM smtbs_user a
, smtbs_user_group b
WHERE a.group_code_allowed = 'D' AND b.user_id = a.user_id
UNION
SELECT a.user_id
, b.group_code
FROM smtbs_user a
, smtbs_user_group b
, sttms_group_code c
WHERE a.group_code_allowed = 'A' AND c.group_type in ('C','I') AND b.user_id = a.user_id AND c.group_code = b.group_code AND c.auth_stat = 'A';
From the explain plan of the query the cost was 4097 on day 26th nov
and cost of query is 16481 on 14th dec (from the same environment)
Below is the explain plan
ID Plan Rows COST BYTES OPTIMIZER CPU_COST IO_COST
--------------------------------------- -------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------ --------------------------------------- ---------------------------------------
0 SELECT STATEMENT . 46 16481 3726 FIRST_ROWS 5490407307 15802
1 FILTER .
2 VIEW FLEXLIVE. 46 16481 3726 5490407307 15802
3 COUNT .
4 VIEW FLEXLIVE. 46 16481 3128 5490407307 15802
5 FILTER .
6 HASH JOIN OUTER . 46 16481 2898 5490407307 15802
7 NESTED LOOPS . 91 14330 4459 4934411450 13720
8 VIEW SYS.VW_NSO_1 1 13620 10 4928858958 13011
9 HASH UNIQUE . 1 13620 28 4928858958 13011
10 FILTER .
11 NESTED LOOPS . 1380958 13462 38666824 3652310611 13011
12 NESTED LOOPS . 1 3 14 38167 3
13 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 12 ANALYZED 8231 1
14 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
15 TABLE ACCESS FULL FLEXLIVE.STTM_BANK 1 2 2 ANALYZED 29936 2
16 TABLE ACCESS FULL FLEXLIVE.STTM_CUSTOMER 1380958 13459 19333412 ANALYZED 3652272443 13008
17 VIEW FLEXLIVE.STVW_USER_GROUP 2 10 30 24309033 7
18 SORT UNIQUE . 2 10 164 24309033 7
19 UNION-ALL .
20 MINUS .
21 NESTED LOOPS . 1 2 21 12840 2
22 TABLE ACCESS BY INDEX ROWID FLEXLIVE.STTM_GROUP_CODE 1 1 11 ANALYZED 4423 1
23 INDEX UNIQUE SCAN FLEXLIVE.PK01_STTM_GROUP_CODE 1 1 ANALYZED 525 1
24 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 10 ANALYZED 8416 1
25 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
26 NESTED LOOPS . 1 2 25 8941 2
27 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 10 ANALYZED 8416 1
28 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
29 INDEX UNIQUE SCAN FLEXLIVE.PK_SMTB_USER_GROUP 1 1 15 ANALYZED 525 1
30 NESTED LOOPS . 1 3 36 13365 3
31 NESTED LOOPS . 1 2 21 12840 2
32 TABLE ACCESS BY INDEX ROWID FLEXLIVE.STTM_GROUP_CODE 1 1 11 ANALYZED 4423 1
33 INDEX UNIQUE SCAN FLEXLIVE.PK01_STTM_GROUP_CODE 1 1 ANALYZED 525 1
34 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 10 ANALYZED 8416 1
35 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
36 INDEX UNIQUE SCAN FLEXLIVE.PK_SMTB_USER_GROUP 1 1 15 ANALYZED 525 1
37 VIEW FLEXLIVE. 91 710 3549 5552492 709
38 SORT UNIQUE .
39 UNION-ALL PARTITION .
40 TABLE ACCESS BY INDEX ROWID FLEXLIVE.STTM_CUST_ACCOUNT 1 3 32 ANALYZED 18330 3
41 INDEX RANGE SCAN FLEXLIVE.IX01_STTM_CUST_ACCOUNT 2 2 ANALYZED 10882 2
42 HASH JOIN . 1 24 47 34576271 20
43 TABLE ACCESS BY GLOBAL INDEX ROWID FLEXLIVE.CLTB_ACCOUNT_APPS_MASTER 1 2 35 ANALYZED 11509 2
44 INDEX RANGE SCAN FLEXLIVE.ID01_CUSTID 3 1 ANALYZED 4286 1
45 VIEW FLEXLIVE.SMVW_USER_PRODUCTS 804 22 9648 33884213 18
46 SORT UNIQUE . 804 22 15603 33884213 18
47 UNION-ALL .
48 MINUS .
49 SORT UNIQUE . 804 22 15603 33884213 18
50 UNION-ALL .
51 NESTED LOOPS . 795 8 15105 1086423 8
52 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 10 ANALYZED 7926 1
53 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
54 TABLE ACCESS FULL FLEXLIVE.CSTM_PRODUCT 795 7 7155 ANALYZED 1078496 7
55 FILTER .
56 TABLE ACCESS BY INDEX ROWID FLEXLIVE.CSTM_PRODUCT 1 1 12 ANALYZED 7945 1
57 INDEX RANGE SCAN FLEXLIVE.IX03_CSTM_PRODUCT 1 1 ANALYZED 4086 1
58 SORT UNIQUE . 10 230
59 NESTED LOOPS . 10 2 230 15887 2
60 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 10 ANALYZED 8126 1
61 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
62 INDEX RANGE SCAN FLEXLIVE.PK01_SMTB_USER_ACCESS_PRODUCTS 10 1 130 ANALYZED 7761 1
63 NESTED LOOPS . 8 7 256 61093 7
64 NESTED LOOPS . 10 7 256 61093 7
65 NESTED LOOPS . 10 2 230 15887 2
66 TABLE ACCESS BY INDEX ROWID FLEXLIVE.SMTB_USER 1 1 10 ANALYZED 8126 1
67 INDEX UNIQUE SCAN FLEXLIVE.PK01_SMTB_USER 1 1 ANALYZED 4086 1
68 INDEX RANGE SCAN FLEXLIVE.PK01_SMTB_USER_ACCESS_PRODUCTS 10 1 130 ANALYZED 7761 1
69 INDEX UNIQUE SCAN FLEXLIVE.PK01_CSTM_PRODUCT 1 1 ANALYZED 525 1
70 TABLE ACCESS BY INDEX ROWID FLEXLIVE.CSTM_PRODUCT 1 1 9 ANALYZED 4521 1
71 INDEX FAST FULL SCAN FLEXLIVE.IX07_STTM_CUST_ACCOUNT 104045 2149 1456630 ANALYZED 544977707 2082
Below are indexes present on tables :
INDEX_NAME TABLE_NAME COLUMN_NAME
IX05_STTM_CUSTOMER STTM_CUSTOMER GROUP_CODE
PK01_STTM_CUSTOMER STTM_CUSTOMER CUSTOMER_NO
UI01_STTM_CUSTOMER STTM_CUSTOMER UTILITY_PROVIDER_ID
UI02_STTM_CUSTOMER STTM_CUSTOMER SSN
X3_STTM_CUSTOMER STTM_CUSTOMER SWIFT_CODE
Below are indexes present on the table :
INDEX_NAME TABLE_NAME COLUMN_NAME
IX01_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT CUST_NO
IX03_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT MASTER_ACCOUNT_NO
IX03_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT BRANCH_CODE
IX04_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT AC_STAT_DORMANT
IX04_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT BRANCH_CODE
IX04_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT DORMANCY_DATE
IX05_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT BRANCH_CODE
IX05_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT AUTO_REORDER_CHECK_REQUIRED
IX05_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT CHEQUE_BOOK_FACILITY
IX05_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT AUTO_REORDER_CHECK_LEAVES
IX05_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT AUTO_REORDER_CHECK_LEVEL
IX07_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT ACCOUNT_CLASS
IX07_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT AUTH_STAT
IX07_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT RECORD_STAT
IX07_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT CUST_AC_NO
IX07_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT CCY
IX07_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT BRANCH_CODE
IX08_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT CLEARING_AC_NO
IX09_STTM_CUST_ACCOUNT STTM_CUST_ACCOUNT GOAL_REF_NO
PK01_ST
You're going to have some troubles here.
If you look at the plan, you can see lots of example of:
FILTER
VIEW
The complexity of your views means that they are being fully resolved first and THEN filters are applied to reduce the number of rows. So thats a performance risk because if any of those views take a while to query in isolation (with no predicates) then your query is going to be similarly problematic.
Have you considered using some *materialized* views for the views concerned ? This makes the processing much simpler, and indexes on the mviews can then be taken advantage of.
Also, cursor sharing also removes information from the optimizer, so the chances of a good plan become even more reduced.