Hi,
I am running the follwoing query and it is running very slow:
select * from v_mci_com_leader_summary where accountid=1011 and
intervalid=1490
EXPLAIN PLAN looks like:
ID Query Plan
---------- -----------------------------------------------------------
0 SELECT STATEMENTCost=402437
1 VIEW V_MCI_COM_LEADER_SUMMARY
2 SORT
3 HASH JOIN
4 VIEW T_VW_AUDIOCONFERENCECALL
5 UNION-ALL
6 NESTED LOOPS
7 TABLE ACCESS T_ACC_USAGE_1
8 TABLE ACCESS T_PV_AUDIOCONFERENCECALL_1
9 INDEX PK_T_PV_AUDIOCONFERENCECALL_1
10 NESTED LOOPS
11 TABLE ACCESS T_ACC_USAGE_2
12 INDEX IDX_T_ACC_USAGE_2
13 TABLE ACCESS T_PV_AUDIOCONFERENCECALL_2
14 INDEX PK_T_PV_AUDIOCONFERENCECALL_2
15 NESTED LOOPS
16 TABLE ACCESS T_ACC_USAGE_3
17 INDEX IDX_T_ACC_USAGE_3
18 TABLE ACCESS T_PV_AUDIOCONFERENCECALL_3
19 INDEX PK_T_PV_AUDIOCONFERENCECALL_3
20 VIEW V_MCI_COM_FEATURE_COUNT
21 UNION-ALL
22 SORT
23 NESTED LOOPS
24 NESTED LOOPS
25 INDEX PK_T_PV_FEATURECHARGES_1
26 TABLE ACCESS T_ACC_USAGE_1
27 INDEX PK_T_ACC_USAGE_1
28 INDEX PK_T_PV_AUDIOCONFERENCECALL_1
29 SORT
30 NESTED LOOPS
31 NESTED LOOPS
32 INDEX PK_T_PV_FEATURECHARGES_2
33 TABLE ACCESS T_ACC_USAGE_2
34 INDEX PK_T_ACC_USAGE_2
35 INDEX PK_T_PV_AUDIOCONFERENCECALL_2
36 SORT
37 NESTED LOOPS
38 NESTED LOOPS
39 INDEX PK_T_PV_FEATURECHARGES_3
40 TABLE ACCESS T_ACC_USAGE_3
41 INDEX PK_T_ACC_USAGE_3
42 INDEX PK_T_PV_AUDIOCONFERENCECALL_3
v_mci_com_leader_summary is a view based on 2 views t_vw_AudioConferenceCall
, v_mci_com_feature_count
t_vw_AudioConferenceCall is also a view and based on tables
T_ACC_USAGE_1
T_PV_AUDIOCONFERENCECALL_1
T_ACC_USAGE_2,T_PV_AUDIOCONFERENCECALL_2
T_ACC_USAGE_3,
T_PV_AUDIOCONFERENCECALL_3
AND v_mci_com_feature_count is also a view and based on tables
T_PV_FEATURECHARGES_1
T_ACC_USAGE_1
T_PV_AUDIOCONFERENCECALL_1
T_PV_FEATURECHARGES_2 ,
T_ACC_USAGE_2 ,
T_PV_AUDIOCONFERENCECALL_2
T_PV_FEATURECHARGES_3 ,
T_ACC_USAGE_3 ,
T_PV_AUDIOCONFERENCECALL_3
ROWS in all the tables involved in views is about 150,000
all the tables r analyzed with compute.
all the indexes r rebuild at frequent intervals.
SGA size is 250M , all data on RAID 5 compaq machines..
How can i improve the performance.
Thanks
-Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
